Hi all! I have a partitioned table with millions of rows per weekly partition. I am adding new fields, with null values and no default values 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 correct values. 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 or not: the docs at
http://www.postgresql.org/docs/current/static/sql-cluster.html are silent on
the matter, but do mention that an ANALYZE is in order.
Thanks!
François Beausoleil
smime.p7s
Description: S/MIME cryptographic signature
