It comes up repeatedly that I need to load fresh data into a new
table, build indexes and vacuum - then drop the old table and
substitute the new.
Deleting old and inserting new records is too slow - not to mention
the vaccuming. Loading with COPY, then building indexes, then
vacuuming is very fast by comparison. I'm dealing with over 100
million records several times daily.
Users are disrupted by the time taken to delete... insert... vacuum.
Users are not disrupted at all if the fresh tables can be loaded with
data and have indexes built - then the old tables turned off and new
turned on.
Postgresql partioning makes this all almost possible: the master
automatically SELECTs from all the child tables - so it automatically
recoginize the new ones, and no loss when the old ones are dropped.
Just one thing would make it near perfect: if I could keep the master
from being able to SELECT from the new child table while I'm COPYing
data into the new child, building the index and vacuuming it - without
sending an error back to the user who is querying the master table.
I tried a CHECK constraint, a RULE - SELECT rule can't INSTEAD DO
NOTHING - #postgresql channel people say there's nothing that can do
this... revoking privs or changing the owner of the new child results
in an error back to the user who queries the master table. Tried
creating a dummy table with no records and making a rule for the child
to INSTEAD select from the dummy table but that was not allowed
because it would turn my non-empty child table into a view.
Thank you,
- April