If things get really bad, and VACUUM can’t keep up with the level of changes happening in your database, Andreas Scherbaum explains how old versions of data might suddenly become visible.
If part of your vacuum problem stems from apps trying to insert data that violates primary keys, Shane Borden suggests adding “ON CONFLICT DO NOTHING” to the app’s insert statements.
Hate scanning tables? Christoph Schiessl demonstrates using SET SESSION enable_seqscan = off to encourage Postgres to use indexes, and then setting it back to the default afterwards.
Technique of the Week:
Indexing Queue Tables
Sometimes we’ve got queue or processing tables where we’re constantly running queries like this:
|
1 |
select order_id from orders where is_processed = 0; |
We have to keep all our order history forever in this orders table, but we’re constantly checking for unprocessed orders to know what we need to box & ship next. An index on the is_processed column is kinda useless because the vast majority of our orders are is_processed = 1, and we’ll never query for that.
The solution is partial indexes:
|
1 2 |
CREATE INDEX is_processed_0 ON orders(order_id) WHERE is_processed = 0; |
This way, the index only stores the rows where is_processed = 0, which will be a very small subset of the table.
There are other uses for partial indexes as well, like enforcing uniqueness in certain conditions, and the partial index documentation goes into details.