Re: Autovacuum stuck for hours, blocking queries

Hi Tim,

Am 2017-02-17 um 17:02 schrieb Tim Bellis:
> 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.

OK, then I'll try two other suggestions:

- use table partitioning (
https://www.postgresql.org/docs/9.3/static/ddl-partitioning.html )

- if the number of rows you need to keep is small, you could try
something like this:
LOCK TABLE yourtable <a suitable LOCKMODE>;
CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE <keep>;
TRUNCATE yourtable;
INSERT INTO yourtable SELECT * from keep;
COMMIT;

Best regards,

-hannes

> -----Original Message-----
> From: Hannes Erven [mailto:hannes(at)erven(dot)at]
> Sent: 17 February 2017 11:47
> To: pgsql-general(at)postgresql(dot)org
> Cc: Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com>
> 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
>
>
>