Disable autovacuum on specific tables

From: Eliot Gable <egable+pgsql-general(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Disable autovacuum on specific tables
Date: 2012-10-24 19:57:43
Message-ID: CAD-6L_UQRrwFE5ejWbP2P3u2rgFF7-JTMnzU11bOZJHc_+BBhA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

In general, autovacuum seems to work well on most of the tables I deal
with. However, in a couple of specific cases, it seems to fail miserably. I
would like to switch to manual vacuuming on those tables and disable
auto-vacuuming for those tables alone. Is this possible? I searched the
docs and the mailing list archives, but it seems any time someone asks,
their question is side-stepped with something along the lines of "you are
probably doing something wrong, let's look at your situation" and it turns
out they generally are or they simply don't understand what vacuuming is
all about in the first place, so the question is never answered. So, before
we go further, if someone could just answer for the same of future searches
whether it is even possible, that would be great.

Here are the details about my specific reasoning, however, since I know you
are going to want to know:

I have a daemon which listens to events from a 3rd party system and keeps a
materialized view of the state of that 3rd party system in a small set of
tables. In certain use cases, this 3rd party system could generate as many
as 15,000 events per second which affect the materialized state of the
records in the tables.

The daemon must process these events and determine whether the event
inserts, updates, or deletes a record from the materialized view. It could
potentially do 50 - 100 inserts or deletes per second, or as many as 500
updates per second.

The materialized tables, if vacuumed properly, stay around 20 - 30 MB in
size. However, when usage spikes on the 3rd party system, there are so many
transactions going against these tables that the autovacuum process
constantly cancels itself so it doesn't block the pending transactions. The
usage spike can persist for a long duration of time and become so intense
that the size of the tables grow to 300 - 500 MB in size, each.

The database resides on a network appliance which has only a single 7200
RPM disk. When the table size grows to several hundred MB, the system
becomes I/O bound due to the disk seeking around everywhere inside those
large tables. Manually vacuuming the tables resolves the I/O bound state
and the system behaves "normally" again.

While the state updates generally need to be real-time, we do have some
windows of opportunity in which a vacuum can occur. The thread which writes
to the database maintains a queue of events to process and it can vacuum
the tables in between event processing. If we delay event processing every
30 - 60 seconds for the 100ms - 2s needed to vacuum analyze the tables, it
does not cause any particular problem for the system. And if we can
guarantee that the vacuum analyze will succeed (because nothing is writing
to those tables at the time the vacuum analyze runs), then it is worth the
small delay in processing the events to ensure the table is not growing out
of proportion. Assuming the tables have not grown to over 100 MB each,
there is enough performance available at currently conceivable loads to
ensure that any delay of 10s or less in processing events will quickly be
caught up.

However, this makes it unnecessary for the auto vacuum process to run on
these tables. So, is there some way I can disable the automatic vacuuming
of these tables?

I know I can disable auto vacuum on all tables and switch to manually
vacuuming all tables, but that then requires that all developers working on
this system remember to add any new tables to the list of tables to vacuum,
and it requires additional code to decide when it is appropriate to vacuum
those tables. The auto vacuum already does a great job on those tables, so
I do not see why I should disable it on those.

Thanks in advance.

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be
a crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live;
not live to eat.) ~Marcus Tullius Cicero

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shaun Thomas 2012-10-24 20:06:15 Re: Disable autovacuum on specific tables
Previous Message Alvaro Herrera 2012-10-24 18:44:27 Re: [pgsql-es-ayuda] como exportar separado por comas una tabla grande