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