One word in PostgreSQL unlocked a 9x performance improvement
jlongster.com
jlongster.com
This is not an uncharitable read btw. Actual quotes:
> The first thing I learned was how to insert multiple rows with a single INSERT statement: ...
> Scouring the docs I discovered the RETURNING clause of an INSERT statement.
That said, I was unaware of the big discovery in the post: Doing INSERT ... RETURNING ... ON CONFLICT DO NOTHING, and passing an array of rows to insert, the returning clause will return only the values from the rows that were actually inserted. IMO this is the "obvious" behavior, but it is still worthwhile to know it works!
I think this is why people love Postgres so much. 99 times out of 100, the "obvious" behaviour turns out to be the behaviour that Postgres actually implements.
All the RDBMS systems are guilty of it to some degree though.
E.g.
CREATE TABLE t (id SERIAL PRIMARY KEY, x INTEGER UNIQUE);
INSERT INTO t (x) VALUES (42), (43) ON CONFLICT DO NOTHING RETURNING *;
In simple CRUD cases yes. In many other scenarios, you might not. If your insert is a select statement, with joins and clauses, you can easily insert too much data, or no data at all.
I often use "insert into mytable(...) select ... from ... where ...", and I know I only want one row inserted.
In such cases checking that exactly one row was inserted is a very nice safeguard against buggy code/query, or in the freak case someone messed with the DB.
That said, I'm with you on the clickbait title. That really grinds my gears.
The problem with this blog post isn’t lack of novelty; rather, with the clickbait title, it piqued my interest but I ended up learning nothing, other than being reminded of my own terrible designs and queries back when I was a noob.
I'm pretty sure your first schema designs had relations (tables). I can't speak for relationSHIPS (foreign keys) though...
;)
I second what you said about reading a text book. I read through the entire PG documentation and a PG book my second year as an engineer and 7 years later its paid off big time. It’s one of those things where if you do it early, you get compounding benefits over your career.
There's a lot of incomplete and misunderstood information floating around in blog tutorials or potboiler textbooks.
https://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/d...
My personal favorite is I was generating a query that pulled out a dozen different fields from a large JSONb column. Naturally, you would think Postgres would read the JSONb field once, then pull out the individual fields from it. Instead, Postgres was reading the JSONb column once per each field. I figured out this was the case because the number of blocks read from EXPLAIN (ANALYZE, BUFFERS) went up proportionally to the number of fields I extracted from the JSONb column. Reading the JSONb column was especially expensive because Postgres needed to deTOAST[0] the JSONb column.
The obvious fix is to write a subquery to read the entire JSONb column and then have the outer query extract the individual fields, but that doesn't work! Postgres will inline the subquery, basically undoing your attempt to prevent the unnecessary accesses. In the end, the solution wound up being to add OFFSET 0 to the end of the subquery. That doesn't change the semantics of the query, but it does prevent Postgres from inlining the JSONb column access.
The first one can be done by effectively changing an @ to a # and mildly changing the create statements (from declare to create) - doing just this change I took a a 22+ hour long query(didnt want to wait any longer) to a <1 minute query.
Using a CTE to do this would also be suboptimial since it materializes the entire result of the subquery in memory. This is as opposed to the OFFSET 0 which would only materialize one row at a time.
My first reaction was that this was awful, but the more I thought about it, the more I was grateful that postgres (accidentally) gave me this knob to play with. Optimization of complex queries is a very tricky thing, and most engines won't do a good job of it 100% of the time. What I realized would have been a worse situation is if postgres sometimes picked a bad plan and there was little I could do to avoid it without reforming the query (a pain when you're generating the query with a query compiler already). Reordering the clauses effectively allowed me to ask the planner to roll the dice again.
Following this, I may or may not have gone on to build a cache for the system that timed queries and kept notes of "good" clause orders for common queries, resorting to random ones otherwise....
Personally, it feels like a bit of a fragile mess trying to trick a sometimes-clever-sometimes-dumb optimiser into doing what you want by subtle indirect hacks - because the interface doesn't give you a way to directly override bad automated optimiser decisions.
Oh, I'm not saying it wasn't a fragile mess...
Seriously I actually considered the fragility of it to be a positive, precisely because it wasn't a hard "optimizer decision" that I was forcing. If you force something like that, you've got to take full responsibility for it - the planner will lose all intelligence over a certain decision and no longer do the clever thing query planners do and take the current distribution of the database's data into account to allow it to make better decisions. When you upgrade the database and the query planner gets smarter (or dumber), you've got to re-evaluate whether that's still the right decision. I could imagine an app with a bunch of out of date hard-forced decisions to be crippling performance wise.
A look-aside of automatically deduced hints with a validity of less than a week seemed like a much lighter touch.
I used to work on non-database decision support tool that incorporated a custom optimiser which was used to spit out crude engineering designs for a particular kind of construction problem. The optimisation problem was difficult & the implementation to solve it was not state of the art: there were a few preprocessing stages that were used to lock in some early decisions using heuristics -- which helped massively reduce the search space, then a global optimisation approach was run on the remaining sub problem. The result was that the overall algorithm would locally optimise after perhaps locking in a bad early decision. Once the software was delivered to the client and in use by a small team of users I later discovered that the users had figured out that by running the software repeatedly with very small adjustments to input parameters (adjustments that should not obviously matter) they could bump the optimiser into outputting wildly different designs. It was a little bit like repeatedly pulling the handle on a one armed bandit until it eventually gave you a decent output.
It was clever of our users to figure this out but the overall UI/UX was appalling, they had to click and re run a somewhat slow batch process until it produced a reasonable result. It would have been much better to give the users a user interface where they could directly override or constrain parts of of the engineering design problem in an ergonomic way, and then let the optimisation algorithm loose to make the remainder of the decisions.
Do you have any references which shows this behaviour? Or a way to reproduce it?
I would also avoid json/xml 'support', it's a recipe for unintended consequences.
If timestamps are just wall clock timestamps. You should be looking at the SQL type Timestamp (without timezones) as putting a primary key around text fields is not optimal. Not 100% on PostgreSQL, but I would look at moving timestamp to Timestamp and group to varchar.
dotJS 2019 - James Long - CRDTs for Mortals (20:44): https://www.youtube.com/watch?v=DEcwa68f-jY
EDIT: btw, out of all the comments on the timestamp here, seems like only yours is not complaining about the format, and even points out that the article did not go in depth yet to specify the actual timestamp that he uses.
The moment your insertions can be done in a loop, _batch them_. Batch them, batch them, batch them. I can't stress this enough. Batch them.
Do keep in mind that your DBMS of choice can have a maximum amount of bound variables (SQLite has 999 maximum by default if I remember correctly. It can quickly become an issue.) If you fear that this might happen, split into multiple batches, batch those batches.
> timestamp TEXT
> PRIMARY KEY(timestamp, ...)
Dear god no. Make it an actual `timestamp` type, or an int if you can't.
I regularly encounter code running off of performance cliffs like this in rails application i'm working on -- and it's incredibly difficult to climb the application out of these cliffs ...
For example, a 21MB upload with a 72MB INSERT statement (100k messages inserted) fails but a 5MB upload with 30MB INSERT statement (40k messages inserted) works so author just limits to the smaller values and calls it a day. But clearly the new limits are still near the edge of the performance envelope and without knowledge of root cause of the error how do they know the error won’t resurface under different conditions (more load on server, less ram allocated to db, congested network, fuller disk)? How do they know a config change from an upgrade or from a fix to another problem won’t lead to another failure?
It saddens me to know in my gut that a lot of what passes for engineering happens this way. This is not proper debugging.
Not really true - they just don't have _any_ grasp on the problem aside from "Yeah I thought you were working on that last week, are you stuck on it? Why is it taking so long? - Oh so you do have a fix that works, cool so how soon can that be merged?"
Almost all of these types of situations lead back to miscommunication or someone in the discussion just not caring.
And yes multiple insert statements are better than individual ones and yes getting the IDs back is good. Different RDBMSs will handle this differently but batching means higher throughput at the cost of some latency which is often a worthwhile tradeoff.
OTOH, the wikipedia article they link to seems to imply a regular Merkle tree. I guess it's not possible to really tell without knowing which Merkle tree/trie library they use.
There are cases where the COPY api is not sufficient. For instance it cannot do the ON CONFLICT IGNORE thingy and instead fails the call. In that case we fallback to INSERT ON CONFLICT IGNORE - since in our application we do not expect too many duplicates this works well for us.
With 'jsob_to_recordset' there is only one parameter which is an array and it gets queried as a table. That has the advantage of avoiding different queries for different inputs (it can be prepared only once, it's harder to commit mistakes...). Also it doesn't have the overhead of creating a huge string.
Another option is copy to. Using pg copy node https://github.com/brianc/node-pg-copy-streams you can insert/query as much as you want from/to node streams without memory footprint. I've used it to pipe millions of records straight to an http response (to download a csv)
For the jsonb `INSERT INTO some_table (a, b) SELECT x.a, x.b FROM json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","b":"bar"}]') AS x(a int, b text);` That one is super flexible and it can handle thousands of records. Where you have the array it would be $1 and pass it as a parameter.
With streams it's trickier, probably you need to create a temporary table to pipe everything and then another query to insert where you want. Also you need to deal with async errors which is a pain. But you have 0 memory load. I've used it to insert/select millions of records ``` pool.connect(function(err, client, done) { var stream = client.query(copyFrom('COPY my_table FROM STDIN')); var fileStream = fs.createReadStream('some_file.tsv') fileStream.on('error', done); stream.on('error', done); stream.on('end', done); fileStream.pipe(stream); }); ```
Read the fine manual of your database. Then read it again. Chances are you’ll be very surprised.
It's designed around the idea of time-series data. It should give you better insert and query performance since it's geared towards that workload. Aside from that, you could even reap the storage benefits using native compression that it supports.
You can write a trigger on insert and make second insert there, or enclose first insert into CTE and then make second insert selecting from the first one
This would be nice to retrieve auto generated primary keys when doing bulk inserts. Can this be done deterministically?
Some ORMs (at least SQLAlchemy) do that already, they can fill primary keys in your objects during single inserts, and in dicts during bulk insert.
How is that sounding now?
I started generating insert statements of about 200k in length and it caused Amazon Aurora to start dropping connections.
If you insert 10 or 100 values, you'll get a 10x or 100x speedup, which is usually good enough.
> This is mostly the real code, the only difference is we also rollback the transaction on failure. It's extremely important that this happens in a transaction and both the messages and merkle trie are updated atomically.