From: John Arbash Meinel <john@arbash-meinel.com>
To: Alex Stapleton <alexs@advfn.com>
To: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: autovacuum suggestions for 500,000,000+ row tables?
Date: Mon, 20 Jun 2005 10:20:37 -0500
Message-ID: <42B6DEC5.3080008@arbash-meinel.com> (raw)
In-Reply-To: <C59BA512-5630-4F6E-82DD-BFC9F87046E0@advfn.com>
References: <BE7E1F60-FA3A-4843-988E-AC2CC1FA22E5@advfn.com>
<6.2.0.14.0.20050620165134.03b61140@pop.interactivemediafactory.net>
<C59BA512-5630-4F6E-82DD-BFC9F87046E0@advfn.com>
Alex Stapleton wrote:
>
> On 20 Jun 2005, at 15:59, Jacques Caron wrote:
>
...
>> ANALYZE is not a very expensive operation, however VACUUM can
>> definitely be a big strain and take a looooong time on big tables,
>> depending on your setup. I've found that partitioning tables (at the
>> application level) can be quite helpful if you manage to keep each
>> partition to a reasonable size (under or close to available memory),
>> especially if the partitioning scheme is somehow time- related. YMMV.
>>
>> Jacques.
>
>
> That's not currently an option as it would require a pretty large
> amount of work to implement. I think we will have to keep that in
> mind though.
Remember, you can fake it with a low-level set of tables, and then wrap
them into a UNION ALL view.
So you get something like:
CREATE VIEW orig_table AS
SELECT * FROM table_2005_04
UNION ALL SELECT * FROM table_2005_05
UNION ALL SELECT * FROM table_2005_06
...
;
Then at least your individual operations are fast. As you insert, you
can create a rule that on insert into orig_table do instead ... insert
into table_2005_07 (or whatever the current table is).
It takes a little bit of maintenance on the DB admin's part, since every
month they have to create a new table, and then update all of the views
and triggers. But it is pretty straightforward.
If you are doing append-only inserting, then you have the nice feature
that only the last table is ever modified, which means that the older
tables don't really need to be vacuumed or analyzed.
And even if you have to have each table modified as you go, you still
can break up a VACUUM into only doing one of the sub tables at a time.
I don't know you db schema, but I thought I would mention that true
partitioning isn't implemented yet, you can still get something very
similar with views, triggers and rules.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, alexs@advfn.com
Subject: Re: autovacuum suggestions for 500,000,000+ row tables?
In-Reply-To: <42B6DEC5.3080008@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox