Hi all -
For most of my database I use UUIDs as primary keys because, well, I
just like it better and like being able to generate a key in the
middle tier when I create new data. However, I have one table that
has a very fixed and immutable set of data with a few thousand
ingredients in it. This set very rarely changes (in fact when I
change it, I have to restart the whole website).. The UUIDs for each
ingredient are kinda a hassle to manage though, and I'm thinking in
this case I might just want to use a 32bit integer for the primary
key..
Almost every other table in the database has FK relationships with
these ingredients, and there's all sorts of JOINs against it. So,
making this change is gonna be fairly costly and require all sorts of
schema changes to everything. I have a few related questions on this
topic..
1) What's the best way to convert an existing table from UUIDs to a
serial INT column? I want to have Postgres just assign sequential
numbers starting at 1 to all the rows.
2) Once I do this, I obviously need to fix up all the foreign keys and
convert them from the old UUIDs to the new generated numeric value.
Is there an easy way to manage this sort of thing? I'm thinking about
keeping the old UUID as a non-PK column to use as a "mapping" as I
migrate the rest of the schema over. Is that a good approach?
3) I'm also worried about backup/restores/replication. I often add
new ingredients to my production database, and every so often backup
this database and restore it to my dev box so I can work with
production data. I need to make sure all these ingredient IDs stay
constant, just as the UUIDs did. Does pg_dump generate SQL that will
"re-create" the existing keys and set the identity watermark
correctly? If someday I have replication enabled, do the same IDs get
replicated consistantly? Any other "gotchas" here?
Mike
