On Tue, Mar 21, 2017 at 4:24 PM, James Parks <james.parks@xxxxxxxxxx> 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@xxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
