We have a serious problem with vacuum locking up our tables for
too long, (large amount of data + large number of updates == long
vacuum)
As a hack I'm thinking about using the RULE system to forward select
queries and alternate between two backing data tables.
the concept is:
front | CREATE RULE "_RETfront" AS ON SELECT TO front DO INSTEAD SELECT * FROM back1; /
x
back1 back2
The idea is that after several large updates, instead of vacuuming,
we do this:
-- suspend updating back1
truncate back2;
select * into table back2 from back1; -- is there a quicker way?
vacuum verbose analyze back2;
begin; -- rule update needs a lock to prevent falling through into 'front'
lock front; -- stops all queries to front -- is this really needed? -- will the next action (rule
drop/recreate)happen atomically?
drop rule _RETfront;
CREATE RULE "_RETfront" AS ON SELECT TO front DO INSTEAD SELECT * FROM back2;
update active_table set active = '2'; -- remeber who's the active table
end;
-- resume normal updating however we now update back2
after several updates repeate the same process except swap back2
with back1 and vice versa.
Ok, now I know this is evil, but will it work? Will queries on
'front' suffer any performance problems? The docs seem to indicate
that it won't however I just wanted to put this up and see if any
of the developers could offer insight as to whether I'm apt to
shoot myself in the foot doing this.
We really don't mind lagging the data updates, but stoping queries
for the 5 or so minutes it takes to vacuum is not an option. We need
to vacuum every twenty minutes or so otherwise the table becomes nearly
unusable.
Is there a faster way to duplicate tables under postgresql than
SELECT INTO?
Are we going to have problems dropping and adding rules in the middle
of a transaction?
thanks!
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]