Re: [GENERAL] Autovacuum stuck for hours, blocking queries

The DELETE operations only deletes rows from the previous day. It's possible that there have been rows added that day which ought not to be deleted, so TRUNCATE wouldn't work.

But that was a helpful suggestion - thanks!

Tim

-----Original Message-----
From: Hannes Erven [mailto:hannes@erven.at] 
Sent: 17 February 2017 11:47
To: pgsql-general@postgresql.org
Cc: Tim Bellis 
Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Hi Tim,



Am 2017-02-15 um 18:30 schrieb Tim Bellis:
> I have a postgres 9.3.4 database table which (intermittently but 
> reliably)
 > gets into a state where queries get blocked indefinitely  > [..]
> Notes:
>   - This database table is used for about 6 million row writes per 
> day,
 >     all of which are then deleted at the end of the day.

If you are dumping the contents of the table anyways, why not use TRUNCATE instead of DELETE? It unlinks and recreates the table data files, requiring nearly zero IO and analyzing.
Or even drop, and recreate the table with the correct new structure so you do not even need to ALTER TABLE ?

I'm a bit skeptical of these suggestions since very competent people have already answered your post and did not come up with this... ;-) the only drawback I'm aware of is that TRUNCATE will immediatly free disk space on the OS level, so the table's space will not be "blocked". But probably the VACUUM you are currently performing will also eventually release the unused disk space, so this may or may not match the current behaviour.


Best regards,

	-hannes