Re:Features I'd Like in PostgreSQL
by jeltz on Saturday January 28, 2023 @10:03PM
(#34564445)
> so you can avoid lock contention around the single latest index page.
That is usually the total opposite of what you want. There are some optimizations for inserting to the last page but primarily it is because you want to to sequential inserts. So if you want to avoid contention on the last page you should insert ordered by (connection ID, sequential ID or high resolution timestamp). That way every connection will do sequential inserts on its own page.
Re:Features I'd Like in PostgreSQL
by srcreigh on Sunday January 29, 2023 @07:14PM
(#34574239)
Nice this makes sense. Seems like storing connection_id in the table would make it difficult to look up data later? It seems like you could use (customer_id, seq_id) for similar effect.
That is usually the total opposite of what you want. There are some optimizations for inserting to the last page but primarily it is because you want to to sequential inserts. So if you want to avoid contention on the last page you should insert ordered by (connection ID, sequential ID or high resolution timestamp). That way every connection will do sequential inserts on its own page.