3 ms·

I've read several times now that the tradeoff for upsert taking so long was that the implementation is "safe". Is MySQL's / other DB's version of this "unsafe"

by robbles 11y ago

I've read several times now that the tradeoff for upsert taking so long was that the implementation is "safe". Is MySQL's / other DB's version of this "unsafe" somehow? Or was there a particular architecture issue specific to Postgres that made this so difficult to implement without bugs?

spacemanmatt 11y ago
Not especially more difficult than other architectures, but their quality bar is typically much higher. YMMV.
luckycharms810 11y ago
I think one of the quirks you can run in to with MySQL's implementation is that even the DB decides to do an update, you end up burning an auto incremented key. Curious to see what PG ends up doing here.
- pilif 11y ago
  > you end up burning an auto incremented key. Curious to see what PG ends up doing here I haven't tried, but Postgres always uses values in sequences in similar cases. The good news is that there are so many values in a 64 bit sequence that this really shouldn't matter. I much prefer some values in sequences to be not used over even the tiniest possibility of a value being used twice accidentally.
- anarazel 11y ago
  You pretty much have to do so. The conflict could be on the column with the default value after all. And obviously you can't just rollback the sequence/autoincrement value after deciding to update because that'd either require locking the sequence for the duration (horrible for concurrency) or would pose problems with other sessions already having used further values.
zimpenfish 11y ago
In the previous discussion, someone mentioned MSSQL (and T-SQL)'s issues with MERGE: http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ http://www.mssqltips.com/sqlservertip/3074/use-caution-with-... And also this: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...
jandrewrogers 11y ago
The difficulty is supporting high concurrency AND correctness AND high performance with this feature. Most databases that have supported this in the past have sacrificed one of these; PostgreSQL requires an implementation with no impact to existing users and without weakening the guarantees people are used to. In most good databases you can compose an upsert transactionally from a set of independent operations. The problem is that this is slow because you are plumbing the database engine multiple times for an operation that could be executed in one shot in principle. A native upsert implementation provides a fast, single operation. In the case of PostgreSQL though, they had to support this in the context of high-performance, high-concurrency, and correctness which makes it tricky because it is relatively complex to implement under those constraints.
- ryanjshaw 11y ago
  This implementation of upsert is especially nice because it is atomic [1]: <literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic <command>INSERT</command> or <command>UPDATE</command> outcome - provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. I don't know about Oracle or MySQL, but the SQL Server implementation of MERGE requires you to carefully consider which locks to use and it's very easy to shoot yourself in the foot with. [1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=doc/src/sgml/ref/insert.sgml;h=c88d1b7b50a30dacf4c1809e73e84eacf2ca3611;hp=a3cccb9f7c79a5bac71ed35a96d171e9b9587041;hb=168d5805e4c08bed7b95d351bf097cff7c07dd65;hpb=2c8f4836db058d0715bc30a30655d646287ba509 http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdif...

by robbles 11y ago

spacemanmatt 11y ago

Not especially more difficult than other architectures, but their quality bar is typically much higher. YMMV.

luckycharms810 11y ago

I think one of the quirks you can run in to with MySQL's implementation is that even the DB decides to do an update, you end up burning an auto incremented key. Curious to see what PG ends up doing here.

pilif 11y ago
> you end up burning an auto incremented key. Curious to see what PG ends up doing here I haven't tried, but Postgres always uses values in sequences in similar cases. The good news is that there are so many values in a 64 bit sequence that this really shouldn't matter. I much prefer some values in sequences to be not used over even the tiniest possibility of a value being used twice accidentally.
anarazel 11y ago
You pretty much have to do so. The conflict could be on the column with the default value after all. And obviously you can't just rollback the sequence/autoincrement value after deciding to update because that'd either require locking the sequence for the duration (horrible for concurrency) or would pose problems with other sessions already having used further values.

zimpenfish 11y ago

In the previous discussion, someone mentioned MSSQL (and T-SQL)'s issues with MERGE: http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ http://www.mssqltips.com/sqlservertip/3074/use-caution-with-... And also this: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...

jandrewrogers 11y ago

The difficulty is supporting high concurrency AND correctness AND high performance with this feature. Most databases that have supported this in the past have sacrificed one of these; PostgreSQL requires an implementation with no impact to existing users and without weakening the guarantees people are used to. In most good databases you can compose an upsert transactionally from a set of independent operations. The problem is that this is slow because you are plumbing the database engine multiple times for an operation that could be executed in one shot in principle. A native upsert implementation provides a fast, single operation. In the case of PostgreSQL though, they had to support this in the context of high-performance, high-concurrency, and correctness which makes it tricky because it is relatively complex to implement under those constraints.

ryanjshaw 11y ago
This implementation of upsert is especially nice because it is atomic [1]: <literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic <command>INSERT</command> or <command>UPDATE</command> outcome - provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. I don't know about Oracle or MySQL, but the SQL Server implementation of MERGE requires you to carefully consider which locks to use and it's very easy to shoot yourself in the foot with. [1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=doc/src/sgml/ref/insert.sgml;h=c88d1b7b50a30dacf4c1809e73e84eacf2ca3611;hp=a3cccb9f7c79a5bac71ed35a96d171e9b9587041;hb=168d5805e4c08bed7b95d351bf097cff7c07dd65;hpb=2c8f4836db058d0715bc30a30655d646287ba509 http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdif...