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