On Tue, Mar 21, 2017 at 4:24 PM, James Parks <james.parks@meraki.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@gmail.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.