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 =:->
Description: OpenPGP digital signature