In previous posts in this series, I talked about how to read EXPLAIN output, and what each line (operation/node) means.
Now, in the final post, I will try to explain how it happens that Pg chooses “Operation X" over “Operation Y".
Tag: vacuum
Bloat happens
For various reasons, and in various cases, bloat happens. Theoretically autovacuum protects us all, but sometimes it doesn't. Sometimes someone disables it, or mis-configures, or bad planet alignment happens, and we end up in deep bloat.
What to do then? Vacuum? Vacuum Full? Cluster? No. pg_reorg!
Reduce bloat of table without long/exclusive locks
Some time ago Joshua Tolley described how to reduce bloat from tables without locking (well, some locks are there, but very short, and not really intrusive).
Side note: Joshua: big thanks, great idea.
Based on his idea and some our research, i wrote a tool which does just this – reduces bloat in table.
Continue reading Reduce bloat of table without long/exclusive locks
Waiting for 8.5 – VACUUM FULL change
Some time ago Josh Berkus wrote about possible changes in VACUUM FULL.
Now these changes came to life. By now, I mean 6th of January, when Takahiro Itagaki committed his patch:
Log Message: ----------- Support rewritten-based full vacuum as VACUUM FULL. Traditional VACUUM FULL was renamed to VACUUM FULL INPLACE. Also added a new option -i, --inplace for vacuumdb to perform FULL INPLACE vacuuming. Since the new VACUUM FULL uses CLUSTER infrastructure, we cannot use it for system tables. VACUUM FULL for system tables always fall back into VACUUM FULL INPLACE silently. Itagaki Takahiro, reviewed by Jeff Davis and Simon Riggs.
Waiting for 8.4 – Visibility maps
Yeah. This one patch alone is worth upgrading to 8.4.
On 3rd of December Heikki Linnakangas committed his patch. Commit message:
Introduce visibility map. The visibility map is a bitmap with one bit per heap page, where a set bit indicates that all tuples on the page are visible to all transactions, and the page therefore doesn't need vacuuming. It is stored in a new relation fork. Lazy vacuum uses the visibility map to skip pages that don't need vacuuming. Vacuum is also responsible for setting the bits in the map. In the future, this can hopefully be used to implement index-only-scans, but we can't currently guarantee that the visibility map is always 100% up-to-date. In addition to the visibility map, there's a new PD_ALL_VISIBLE flag on each heap page, also indicating that all tuples on the page are visible to all transactions. It's important that this flag is kept up-to-date. It is also used to skip visibility tests in sequential scans, which gives a small performance gain on seqscans.
Waiting for 8.4 – new FSM (Free Space Map)
On 30th of September, Heikki Linnakangas committed his patch that changes FSM:
Rewrite the FSM. Instead of relying on a fixed-size shared memory segment, the free space information is stored in a dedicated FSM relation fork, with each relation (except for hash indexes; they don't use FSM). This eliminates the max_fsm_relations and max_fsm_pages GUC options; remove any trace of them from the backend, initdb, and documentation. Rewrite contrib/pg_freespacemap to match the new FSM implementation. Also introduce a new variant of the get_raw_page(regclass, int4, int4) function in contrib/pageinspect that let's you to return pages from any relation fork, and a new fsm_page_contents() function to inspect the new FSM pages.