Well, the short answer is: NEVER. But given how often I see people ask about it, I'll try to expand my answer a bit…
Tag: bloat
Bloat removal without table swapping
Some time ago I wrote about my favorite method of bloat removal. Around one year earlier, I wrote about another idea for bloat removal. This older idea was great – it didn't involve usage of triggers, overhead on all writes, table swapping. It had just one small, tiny, minuscule little issue. It was unbearably slow.
My idea was explored by Nathan Thom, but his blogpost disappeared.
Recently, Sergey Konoplev wrote to me about his tool, that he wrote using the same idea – updating rows to move them to other pages. So I decided that I have to check it.
Bloat removal by tuples moving
Looong time ago, I wrote a piece about removing bloat by moving rows away from the end of table, and vacuuming it.
This is/was very slow, and was optimized (to some extent) by Nathan Thom, but his blogpost vanished. Besides, later on we got great tool: pg_reorg (or, as it's currently named: pg_repack).
But recently I was in position where I couldn't pg_reorg. So I had to look for other way. And I found it 🙂
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!
Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index
On 25th of January, Tom Lane committed patch:
Implement ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX. This feature allows a UNIQUE OR pkey CONSTRAINT TO be created USING an already-existing UNIQUE INDEX. While the CONSTRAINT isn't very functionally different from the bare index, it's nice TO be able TO do that FOR documentation purposes. The main advantage OVER just issuing a plain ALTER TABLE ADD UNIQUE/PRIMARY KEY IS that the INDEX can be created WITH CREATE INDEX CONCURRENTLY, so that there IS NOT a long INTERVAL WHERE the TABLE IS locked against updates. ON the way, refactor SOME OF the code IN DefineIndex() AND index_create() so that we don't have to pass through those functions in order to create the index constraint's catalog entries. Also, IN parse_utilcmd.c, pass around the ParseState pointer IN struct CreateStmtContext TO save ON notation, AND ADD error location pointers TO SOME error reports that didn't have one before. Gurjeet Singh, reviewed by Steve Singer and Tom Lane
Continue reading Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index
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