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 🙂

Continue reading Bloat removal by tuples moving

Waiting for 9.1 – Writable CTE

On 25th of February, Tom Lane committed patch:

Support data-modifying commands (INSERT/UPDATE/DELETE) IN WITH.   
This patch implements data-modifying WITH queries according TO the           
semantics that the updates ALL happen WITH the same command counter VALUE,
AND IN an unspecified ORDER.  Therefore one WITH clause can't see the 
effects of another, nor can the outer query see the effects other than
through the RETURNING values.  And attempts to do conflicting updates will
have unpredictable results.  We'll need TO document ALL that.           
This commit just fixes the code; documentation updates are waiting ON
Marko Tiikkaja AND Hitoshi Harada

Continue reading Waiting for 9.1 – Writable CTE