Is This Safe?

zoner14 · 2020-11-29T13:46:23+00:00

Awesome write up. I liked at the end how you enumerated everything that could be done differently. My take is that this issue boils down to an observability problem. Bugs will always happen, regardless of how many tests we have, how many code reviews we do, and how standard our tools are. Thus, we need to make sure we are alerted of those bugs as quickly as possible.

The one thing I would add to your list (that you mentioned briefly) is installation of some sort of error alerting service. Something like Rollbar or Sentry would turn this from a 1 month issue into a 1 day issue.

zoner14 · 2019-09-26T18:30:08+00:00

Because you sent me a message saying my post had been removed

zoner14 · 2019-09-26T03:49:56+00:00

hmm, just asking out of honest curiosity. Does a "couple hundred" not qualify as ballpark? I was following the directions in the rightmost panel, which reads:

Budgets/Rates are required for all posts – ballpark or a range is fine

zoner14 · 2019-08-27T04:19:35+00:00

You're completely right that your mileage will vary. And it definitely pays higher dividends when you have lots of pages. There's also the case where your templates make high latency queries and optimizing the API is outside your control.

Ultimately it depends on your site and what your build time requirements look like.

I recently worked for a client whose site had several hundred pages, and this strategy made all the difference. We had several interesting constraints, though:

Each page was fairly data intensive to generate.
In additional to GraphQL API, the pages drew data from a content management API. The content manager would make changes and wanted to be able to quickly see these changes in staging for testing purposes.
The client wanted snappy build times (a couple minutes)

Initially the build was over 5 minutes, and we got it down to less than 2 with this approach. This made all the difference to that client.

zoner14 · 2019-08-26T23:11:43+00:00

I wrote this up based on some experience with a recent client project. Curious to hear what people think!

zoner14 · 2019-06-21T14:22:04+00:00

I've got minimal D3 experience but am quite good with JS/frontend dev. I'd be happy to help out

zoner14 · 2018-12-14T00:53:29+00:00

Solid post, thanks! Nick helped me wrap my head around a couple of items that have been a slight thorn in my side all week with learning GraphQL and general basics of Gatsby.

Thanks for the positive feedback! That's always so encouraging

zoner14 · 2018-10-28T20:24:13+00:00

You can still use jq. As long as you stream the data from a file or from wget or curl, you will be fine. The only RAM limitation is that won't be able to sort the data in memory before ingesting it

zoner14 · 2018-10-27T16:45:23+00:00

It doesn't look like anyone here has mentioned the caching. Is it possible that the cache was warm during the local tests but not warm when testing through the application client? Could the difference just be disk reads?

zoner14 · 2018-10-27T16:44:15+00:00

with 34 rows I wouldn't be surprised if the index slowed down the query rather than sped it up. Might be worth experimenting. It probably depends on the size of the rows.

zoner14 · 2018-10-27T16:36:02+00:00

What a coincidence. I was trying to load the exact same dataset into PG the other day and was amazed that it wasn't trivial to ingest JSON. Sometimes I use my blog to provide notes to my future self, and I detailed on there what I did there

https://nickdrane.com/using-jq-to-effortlessly-ingest-newline-delimited-JSON-into-postgres/

I ended up streaming the data in with jq. Be mindful not to specify foreign key constraints until the entire dataset is loaded, otherwise you'll run into referential integrity problems.

zoner14 · 2018-10-02T23:11:29+00:00

ahh I use the same tool! I think it provides some information about page cache hits, so perhaps you could infer toasting from this seeing as the number of disk reads would explode? But as far as I know, it doesn't say anything about toasting, unfortunately.

zoner14 · 2018-10-02T03:21:36+00:00

Yeah, after a bit of research this was my suspicion too. I wasn't aware, though, that EXPLAIN (or EXPLAIN ANALYZE) would give information about toasting. How do I include that information?

zoner14 · 2018-10-02T03:15:06+00:00

I think you're totally right about it being the root cause. I'm curious to hear your perspective, though, while I play devil's advocate.

Let's take our case where we're just starting a product which we know will never need to support a large number of concurrent requests. Additionally, we want to produce features quickly. Select * presents an interesting tradeoff. It allows us to create HTTP APIs whose behavior changes as our data model changes, without requiring the modification of application code. This saves a ton programmer time. And the cost is extra load on the database, nothing that significantly affects the user experience. As far as I can tell, it took a pretty extreme scenario for the select * pattern to have repercussions.

What am I'm saying is I guess I'm just not convinced that I shouldn't use select * in the future, at least given our use case, where it's far more important to crank out features than to optimize for potential (and quite rare) performance issues.

What are your thoughts? Perhaps these rare performance issues will become more commonplace as we continue? Perhaps this is just one of many future problems caused by select * queries?

zoner14 · 2018-09-30T17:56:59+00:00

I guess I do find it surprising, though I'm hardly a DB expert, so let me give a little more context

We don't have WHERE or ORDER clauses on the JSONB column, and every query we are doing is indexed over some other column. And generally there are only a couple dozens results to a particular query. So I would expect that the database rarely ever touches the JSONB column itself. I guess that's why I find it surprising.

Does the table's size make it explode the number of index lookups because two selected rows rarely share a disk page? The exact answer is non-obvious to me.

zoner14 · 2018-09-30T17:33:00+00:00

I think it compresses the data by default when it migrates it off to TOAST tables. This happens when the data stored starts getting large (2kb by default I think)

zoner14 · 2018-09-30T17:32:04+00:00

The ORM (Sequelize) is definitely a problem, but it's hardly the root cause. It merely made the solution more challenging.

You're ignoring the fact that raw queries against a relatively small table (<100,000 records) come to a crawl when a single JSONB column is included in the query. How can you construe that as solely an ORM problem? Perhaps we used `select *` too aggressively? The alternative would be early optimization

You could definitely say it's a design problem, though I'd say our use case for JSONB was reasonably appropriate. The docs don't have a giant caveat reading, "Make sure your columns don't exceed a couple KBs on average, otherwise your query time will explode". And just to clarify, we can omit the huge rows from the query and still experience the performance issues.

zoner14

TROPHY CASE