Re: Optimizing around retained tuples

From: Brad DeJong <Brad(dot)Dejong(at)infor(dot)com>
To: Claudio Freire <klaussfreire(at)gmail(dot)com>, James Parks <james(dot)parks(at)meraki(dot)net>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Optimizing around retained tuples
Date: 2017-03-24 16:00:03
Message-ID: CY1PR0201MB18979AFAA6D3BF8483E7A4C0FF3E0@CY1PR0201MB1897.namprd02.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-performance

On Tue, Mar 21, 2017 at 4:24 PM, James Parks <james(dot)parks(at)meraki(dot)net> wrote:
> What can I do to keep running long maintenance operations on large
> tables (SELECTing significant fractions of B, DELETEing significant
> fractions of B, running VACUUM FULL on B) without denying other
> Postgresql backends their ability to efficiently query table A?
>
> Anything is on the table for implementation:
> - moving tables to a different database / cluster / completely different DBMS system
> - designing an extension to tune either sets of queries
> - partitioning tables
> - etc

The PostgreSQL 9.6 old_snapshot_threshold feature may be useful for this situation.

From the patch proposal e-mail "... Basically, this patch aims to limit bloat when there are snapshots
that are kept registered for prolonged periods. ...".

I think that matches your description.

PgCon 2016 presentation - https://www.pgcon.org/2016/schedule/attachments/420_snapshot-too-old.odp
CommitFest entry - https://commitfest.postgresql.org/9/562/

On Tue, Mar 21, 2017 at 10:56 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> You're experiencing bloat because the transaction on B is preventing
> the xid horizon from moving forward, thus dead tuples from A cannot be
> reclaimed in case the transaction on B decides to query them.

Setting old_snapshot_threshold to a positive value changes that behavior.

Instead of holding on to the "dead" tuples in A so that the transaction
on B can query them in the future, the tuples are vaccuumed and the
transaction on B gets a "snapshot too old" error if it tries to read a
page in A where a tuple was vaccuumed.

There are also discussions on pgsql-hackers ("pluggable storage" and "UNDO
and in-place update") regarding alternate table formats that might work
better in this situation. But it doesn't look like either of those will
make it into PostgreSQL 10.

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2017-03-24 19:47:29 Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system)
Previous Message Andrew Kerber 2017-03-24 14:00:23 Re: Postgres not using all RAM (Huge Page activated on a 96GB RAM system)