This is because of the consistency model postgres uses: Roughly, the old row remains in existence as long as transactions started before the UPDATE are still running, and the new row exists alongside it for that duration. Transactions started before the UPDATE can't see the new row, and transactions started after the update can't see the old row, because the transaction id (txid) is added to the query and is compared to hidden columns on each table/row (xmin and xmax).
This is one of the things VACUUM cleans up, actually deleting those old rows once all the old transactions have ended.
But 40 columns should be a cakewalk for any RDBMS to handle. And I assume you're not storing KB's of data in the different columns? Because if your strings are large enough to be allocated as references to separate blob storage, rather than in the row, that could be a performance problem.
Bulk operations with millions of rows can also sometimes take way longer if you're doing them as a transaction that can be rolled back. If it's acceptable to disable that, that could be a huge improvement.
You can also sometimes find massive speedups in using bulk SQL statements (upserting 1000 rows per query, rather than 1 row per query) or CSV file import rather than SQL.
And obviously make sure you're using indexes wherever appropriate.
Because generally speaking, upserting ~10 million reasonably-sized records is the kind of thing that should only take a few minutes on an SSD. You're not going to do it in seconds, but it shouldn't be taking an hour or anything either.
Your database and SSD are functioning totally normally, as designed.
This seems like a bizarre rationale to make a database choice.
You might want to have a look at HOT [0] tuples if you haven't already.
[0] - https://www.cybertec-postgresql.com/en/hot-updates-in-postgr...
Eventually maybe PostgreSQL will get a second storage engine that uses an undo log instead, like the somewhat stalled zheap effort for instance.
That said the numbers of rows you are talking about are easy peasy for PostgreSQL.
Consider remodelling the data model and going up to 3NF [0], or to a higher normal form if that is more appropriate for your use case. Throwing different relational database engine types at the problem might help with bandaiding the problem for a while, but one day it will come back and bite you again. Wikipedia has a good starting point [1] for the 1NF ↝ 2NF ↝ 3NF ↝ … design steps. Depending on the nature of your data, you might have to consider the Boyce–Codd normal form (3NF++ so to speak). Maybe not.
Once you have remodelled the data model, selects and upserts will be very efficient, quick, and they won't result in the constant index update/rebuild for the entire single main table. You will have to rewrite your queries, but if the data model is normalised, joins will be straightforward, fun to write and will only update what has actually has changed in each, separate table.
If the data model normalisation is absolutely impossible (e.g. you don't own the data model but rather your customer does), consider replacing upserts with the MERGE statement.
[0] https://en.wikipedia.org/wiki/Third_normal_form
[1] https://en.wikipedia.org/wiki/Database_normalization#Example...
First is that MERGE is a much more versatile operator (not a compliment if you need a specific narrow functionality like upsert) whereas INSERT / ON CONFLICT was specifically added to make thread safe upsert simple. How would you do thread safe MERGE in Postgres, off the top of your head? It's something that ON CONFLICT solves for you.
Secondly, MERGE is only available starting with Postgres 15, which has just been released.
So no, don't replace upserts with MERGE unless there's a gery specific reason.
MERGE does have a number of its own peculiarities that are database engine specific and the MERGE behaviour is not portable across RDBMS's.
For instance, Oracle simply does not care about the transaction context width in which a MERGE is used, and the transaction will either succeed or fail no matter how long the transaction takes. Whereas MS SQL Server is very, very touchy and will kill off the transaction if, say, an index update caused by a MERGE is taking too long (from the SQL Server perspective). So it is better to minimise the transaction context containing a MERGE for the MS SQL Server and carefully assess the index update performance. I would expect Postgres to have the behaviour closer to that of Oracle, but I have not looked into it.
More generally speaking (and if we ignore MERGE implementation specific details for a moment), remember that the UPSERT pattern has come about as a workaround for the missing MERGE functionality which was added into the specification very late, and vendors were slow to implement it. MERGE covers a few very useful use cases, especially for complex UPSERT scenarios, but it does not obviate UPSERT's in simple scenarios. Both are useful.
Regarding SQL Server - it's not true that SQL Server will kill transactions based on some timeout (unless there's a deadlock detected). I worked with SQL Server extensively, including using MERGE for upserts (btw, you have to use serializable isolation to make MERGE thread-safe, so it's not so easy to get rid of transactions altogether). SQL Server doesn't kill transactions, except when there's a deadlock. FWIW, in SQL Server community there's a well-known notion of MERGE being buggy (even though it's been supported for like 15 years!) and hard to reason about (especially when there's triggers on the involved columns), see for example: https://www.mssqltips.com/sqlservertip/3074/use-caution-with...
I agree with you that Postgres' MERGE is most likely modeled after Oracle, but haven't looked into it either.
I, for one, find the MERGE syntax to be easier to read, more flexible and versatile and, most importantly, standardised. It is, effectively, UPSERT++ if you like, as it also allows one to delete rows from a table if there is a condition match. Compare
MERGE
target
USING
source
ON
target.col1 = source.col1 AND
target.col2 = source.col2 AND
…
WHEN MATCHED THEN
UPDATE SET -- or DELETE
target.col3 = source.col3,
…
WHEN NOT MATCHED BY TARGET THEN
INSERT (…) VALUES (…)
INSERT INTO target (…)
VALUES
(source.col1),
(…)
ON CONFLICT (target.col1)
DO UPDATE
SET target.col3 = source.col3;
I also deem MERGE, as a technical term, to be more concise and much closer semantically to the intent it describes as opposed to INSERT ON CONFLICT. Rows are routinely updated in a database (it is its job after all), and a row update operation is not inherently a conflicting update. Conflicts (semantically) confer exceptional situations that have to be dealt with, well, in exceptional ways. But I digress as it is more of a lingustic subject.
> […] it was added rather as a proper solution, but for a more limited use case which is popular in OLTP workloads: simple thread-safe upsert.
As far as SQL (the language and the standard) is concerned, SQL is unaware of threads or the thread safety; SQL is concerned with transactions and with the transactional integrity. The database provides and ensures the ACID behaviour and guarantees, and it may or may not even use threads to accomplish it (as a matter of the fact, we know that most do but it is an implementation detail).
And MERGE is perfectly suitable for a variety of processing scenarios OLTP including. In one of my past projects from a few years back, replacing a series of handrolled UPSERT's with a single carefully written MERGE yielded a 1500% performance increase for real time freight item scan events flowing into the main table (granted, the inability to improve the shoddily designed schema was a hard constraint therefore a certain level of creativity was necessary). It was for a 10+ million processing events per hour scale, which is not huge but substantial.
> MERGE definitely has its place in complex ETL-style workloads
As an aside note, ETL workloads are not expected to modify a database in place which would otherwise make them one of the most vile and reviled integration anti-patterns. The (E) and (L) steps imply a distinct source and a distinct target, and the (T)ransform step takes place outside the DB, either in the integration layer or is done by a specialised ETL tool. That aside, I fail to see what is so ETL specific about MERGE; if there is a complex UPSERT scenario, a MERGE could be a good candidate (or not), the onus is on the engineer to carry out the analysis and make the right choice.
> Regarding SQL Server - it's not true that SQL Server will kill transactions based on some timeout (unless there's a deadlock detected).
It is true (or it was in SQL Server 2012). In the project I have mentioned earlier, I inherited a design and an implementation that were nothing short of a unmitigated dumpster fire, which also included «let's add another few random compound indices because what could possibly go wrong?». That had to be scrapped and re-engineered from scratch, as it turned out that SQL Server had a penchant for allotting a specific time window for a transaction to complete (irrespective of whether the transaction was serialised or not), and if an index update was taking longer than the time window allotted to the transaction, the DB engine would kill the transaction off. Such a peculiar behaviour was so unexpected that it caused multiple catastrophic cascading failures during the first production deployment attempt, and the deployment had to be aborted and rolled back. I had to enlist a DBA to work on the post-mortem and the subsequent redesign to understand the root cause. It turned out to be the documented SQL Server transaction engine behaviour. A painstakingly difficult, lengthy and time consuming low level index performance analysis and a subsequent meticulous complete index redesign solved the problem in the end.
When I say thread-safety, I just mean the general notion that running a certain SQL statement or procedure concurrently from multiple processes/threads/users/connections will result in correct execution without race conditions.
SQL is definitely concerned with something that is very close to the notion of thread-safety, namely transaction isolation (I in ACID). It's the property that controls concurrent execution of queries in the database, and it deals with what is called "phenomena" in SQL literature. Phenomena is essentially a set of specific types of race conditions which occur under different isolation levels.
And because default isolation level in most popular DBs is "Read Committed", - that is, a very relaxed isolation level allowing a lot of race conditions, - some of pretty basic operations such as upsert/merge are not thread-safe (or, if you dislike this term, you may say "have race conditions", or "do not avoid certain phenomena").
> It turned out to be the documented SQL Server transaction engine behaviour.
Would appreciate the link - it's either something that I haven't seen, or we're just using different terms for something deadlock-related.
>(T)ransform step takes place outside the DB In a perfect world probably yes, but in reality there's plenty of cases where you have some staging tables that are then merged with production tables - that's where MERGE is a good candidate, as it can handle all three of insert, update and delete.
Cheers!
Postgres and other OLTP databases are a better fit.
[0] - https://www.timescale.com/blog/building-columnar-compression... [1] - https://www.timescale.com/blog/timescaledb-2-3-improving-col...
This is very different from columnar/column-oriented databases which are still (usually) relational and just store data by columns instead of by rows for OLAP (large-scale analytics) usage.
Not the same-same as a "column-store" like Druid or Clickhouse or Pinot.
[Disclosure: I work at ScyllaDB.]
You might benchmark it against a couple other database flavors, I suspect most dbs would have issues, although maybe not the same issues as postgres
[1] https://www.cockroachlabs.com/docs/stable/column-families.ht...
With Postgres it seems to be inefficient at storing rows with large amounts of columns, and "upserting" data (using UPDATEs or INSERT ON CONFLICT) results in huge amounts of disk writes, I think because Postgres writes the entire row even if a single column has been updated.
I could store some of the regularly updated columns in a separate table, but I'd thought I'd ask, is there a database out there that is more suited for this type of workload? Because I feel like "just use postgres for everything" is not the answer here.