UPSERT support is really for OLTP databases with frequent transactions that may conflict. If you're doing periodic batch jobs then you can just use plain insert/update/delete (and I believe a common pattern for syncing to an analytics database would be to first dump all the data from the transactional system into a staging table, from which you can run easily comparative queries (e.g. to check for records no longer present in the transactional database) before updating the main tables.
Re ‘UPSERT is really for OLTP’, I find that claim surprising. If you mean the oracle UPSERT command, you may be right and should ignore my post, but if you mean upsert concept/terminology/MERGE-usage, I’m not sure I agree.
I admit I have no visibility into the true history of Oracle UPSERT vs ANSI merge (2003) and their initial target audience or the audience for early implementations. A web search on my part turned up no clarity. But I have three reasons I don’t perceive upsert as as OLTP-not-OLAP feature:
1) I vaguely recall seeing documentation from Microsoft around 2008 when they first implemented merge (I was excited at the time, forgive me) that it was for analytic use cases.
2) Anecdotally I did telecom+web “OLTP” programming for many years without hearing about the upset concept, but heard about upserts in my first few weeks working in the analytic (ETL) space and it was clearly a fundamental operation there.
I suppose the upsert concept was also fundamental for web crud operations and ensuring idempotency but nobody called it upsert back when I was doing that — we just wrapped an insert and update in a transaction. I acknowledge this is an anecdotal claim but it is my Bayesian prior.
3) Merge is a bit notorious for wonky transactional behavior in corner cases… so I’m not confident OLTP was/is the target audience.
Additionally I am not sure why you suggest batch usage implies you wouldn’t upsert. That’s a bit bizarre to me. The operation for moving data from staging tables (for dimensional/attribute data) to main target tables often uses upsert logic of some form.
I guess I would consider the defining feature of true UPSERT support as being an atomic operation that does not exhibit "wonky transactional behaviour". Which means Postgres's ON CONFLICT...DO UPDATE, and not MERGE.
Given that MERGE has this "wonky transactional behaviour", I don't really see the point of using it at all. I guess it gives a more convenient syntax in some cases, but it seems like it will always be possible to replicate the functionality by something like separate INSERT/UPDATE operatiosn (optionally wrapped in a transaction). And OLAP use cases are unlikely to have lots of concurrent transactions that are liable to issues with this approach.
So basically, while you might want to UPSERT in an OLAP use case, you probably don't need watertight atomic UPSERTs, which are the feature that requires special database support.
