Hi all!
I have a partitioned table with millions of rows per weekly partition. I am adding new fields, with null values and no
defaultvalues to ensure I had a reasonable migration time. All downstream code knows how to work with null fields.
Presently, I'm migrating each partition individually to add NOT NULL, set a default value and update the table to have
correctvalues. Essentially, I'm doing this:
ALTER TABLE parent ADD COLUMN new_field int; -- adds the field to all child tables - runs quickly
-- the bulk of the data transfer
for each partition in partitions:
BEGIN;
UPDATE partition SET new_field = 0;
ALTER TABLE partition
ALTER COLUMN new_field SET NOT NULL
, ALTER COLUMN new_field SET DEFAULT 0;
COMMIT;
CLUSTER partition USING partition_pkey;
REINDEX TABLE partition;
VACUUM ANALYZE partition;
done
After I've clustered the table, must I reindex and vacuum as well? It is unclear to me if clustering a table reindexes
ornot: the docs at http://www.postgresql.org/docs/current/static/sql-cluster.html are silent on the matter, but do
mentionthat an ANALYZE is in order.
Thanks!
François Beausoleil
