The Part of PostgreSQL We Hate the Most

Re:The Part of PostgreSQL We Hate the Most by dfox on Sunday October 20, 2024 @04:47PM (#41898326)

> Oracle and MySQL do not have this problem in their MVCC implementation because their secondary indexes do not store the physical addresses of new versions. Instead, they store a logical identifier (e.g., tuple id, primary key) that the DBMS then uses to look up the current version’s physical address. Now this may make secondary index reads slower since the DBMS has to resolve a logical identifier, but these DBMS have other advantages in their MVCC implementation to reduce overhead.

Interesting behavior of MySQL that I have observed (~500GB database, with a schema that is more of an document oriented than relational) is that when you update single row doing SELECT id WHERE something; UPDATE what WHERE id=id is orders of magnitudes faster than UPDATE what WHERE something. I somehow suspect that this is the reason for this behavior. But well, the normal workload will not do that and this only slows down ad-hoc DML when you fix some inconsistency.

[ Parent | on HN ]

Re:The Part of PostgreSQL We Hate the Most by whazor on Sunday October 20, 2024 @05:40PM (#41898716)

A SELECT is a readonly operation and can be performed in parallel. However, an UPDATE actually writes and might lock the table. Whereas UPDATE id=id allows for row level locking. There is also the risk of missing newly inserted records between the SELECT and the UPDATE.

[ Parent | on HN ]

Re:The Part of PostgreSQL We Hate the Most by nine_k on Sunday October 20, 2024 @05:43PM (#41898733)

SELECT FOR UPDATE was invented to address this,

[ Parent | on HN ]

Re:The Part of PostgreSQL We Hate the Most by fipar on Sunday October 20, 2024 @06:03PM (#41898851)

Or just select + update in a transaction, which with IIRC, with the default isolation level will use optimistic locking for the select part, unlike select for update.

[ Parent | on HN ]

Re:The Part of PostgreSQL We Hate the Most by nuttingd on Monday October 21, 2024 @10:45PM (#41910708)

You would need to use serializable isolation for this to hold true. Any isolation level less than serializable will use the snapshot that was active at the time of the select.

In Postgres, even with the serializable isolation level, all transactions that touch the same rows must also be using the serializable isolation level or it's not really enforced. This is one aspect of serializable isolation in Postgres that seemed like a major gotcha for real world application development. There's no future proof solution: new code can be added that doesn't use the serializable isolation, and then the assumptions of isolation from the earlier code are no longer valid.

FOR UPDATE is the only real solution in my eyes

[ Parent | on HN ]

Re:The Part of PostgreSQL We Hate the Most by fforflo on Monday October 21, 2024 @05:09AM (#41902118)

I have a couple of read-heavy >2TB Postgres instances, document-oriented too. You're right that bulk updates can be too slow. Too many times I end up doing the updates incremental (in batches) or even use COPY.

[ Parent | on HN ]

Re:The Part of PostgreSQL We Hate the Most by andruby on Monday October 21, 2024 @07:40AM (#41903043)

You also want to avoid long transactions to avoid lock contention. Every statement is also a transaction, so chunking it up helps a lot on busy databases.

[ Parent | on HN ]

Re:The Part of PostgreSQL We Hate the Most by atombender on Monday October 21, 2024 @11:06AM (#41904988)

Avoiding long transactions is also about preventing the transaction from holding back vacuuming. Postgres will not vacuum tuples that are still visible to old transactions (visible as the backend_xmin in the pg_stat_activity table).

Long transactions can also cause surprising locks, because many locks taken persist to the end of the transaction, even if the transaction is no longer doing anything. This can block DDL operations as well as things like REINDEX.

[ Parent | on HN ]