On 11/5/15 12:14 AM, Jiří Hlinka wrote:
I'm doing simple UPDATES, INSERTs and DELETEs on this table, but frequency of these DMLs is _very_ high (it is a queue table used for a one-way selective [just part of data are replicated] replication of queries between two instances of the database, lets say from the main DB to data warehouse DB, therefore part of DML queries on tables of the main table is "copied" by a trigger to this queue table which is than used as a source for replicating changes into data warehouse DB) - this is the reason why the table needs VACUUM FULL at least twice a day, or better - running pg_repack on it at least twice a day.
That's a workload that is very problematic. It's why PgQ (used by londiste) swaps queue tables around and does truncates. Slony now does the same.
You'll probably be much happier either using PgQ, using some kind of partitioning on the queue so that you're just truncating, or switching to a different queuing solution altogether.
-- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
