Re: autovacuum suggestions for 500,000,000+ row tables?

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
=:->