3 ms·

IF NOT EXISTS is incredibly useful to resolve the state of difference between environments. A classic example is a massive table on production, but an empty ta

IF NOT EXISTS is incredibly useful to resolve the state of difference between environments. A classic example is a massive table on production, but an empty table on dev. Dev gets migrated fine, but prod fails for reasons. You now need to resolve it. Also, we have a convention (not enforced) that we let PostgreSQL name the relation (e.g PK, UQ, Index et al) for us. So we don't do:- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_test_data ON test_table (data); We do:- CREATE INDEX CONCURRENTLY ON schema_name.table_name (column_name);

lmm 2y ago
How do you do migrations that drop those indices if they're potentially differently-named on dev/prod?
- stubish 2y ago
  Differently named is a different problem. The way we avoided that problem was that dev databases were built with the same tooling used to update staging and production systems. But you still have to be careful to ensure any manually applied production updates get backported to the main dev branch, rather than hang around as unexploded landmines.
  - lmm 2y ago
    > Differently named is a different problem. Huh? So what's the point of having PostgreSQL pick the name for you? I assumed it would be so that it picked a new name if one with that name already existed (and my objection is that you're then just storing up trouble for when the time comes to drop the index), is that not it?
    - stubish 2y ago
      Oh, yeah. If you do manage to control schema drift by back porting, PostgreSQL will pick consistent names. But you can still trip over the inherent race condition there if you don't specify names explicitly. We always considered automatic naming to be a shortcut, and used explicit names matching our internal naming conventions when creating and landing patches. I would snapshot the entire production schema every month or two with pg_dump and back port it into the dev tree, precisely to catch any unintended drift such as differences in naming, triggers, indexes etc. that fell between the cracks.
- 1a527dd5 2y ago
  DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] :) In general, it's not something that happens. But that is how we've resolved it in the past.

lmm 2y ago

How do you do migrations that drop those indices if they're potentially differently-named on dev/prod?

stubish 2y ago
Differently named is a different problem. The way we avoided that problem was that dev databases were built with the same tooling used to update staging and production systems. But you still have to be careful to ensure any manually applied production updates get backported to the main dev branch, rather than hang around as unexploded landmines.
- lmm 2y ago
  > Differently named is a different problem. Huh? So what's the point of having PostgreSQL pick the name for you? I assumed it would be so that it picked a new name if one with that name already existed (and my objection is that you're then just storing up trouble for when the time comes to drop the index), is that not it?
  - stubish 2y ago
    Oh, yeah. If you do manage to control schema drift by back porting, PostgreSQL will pick consistent names. But you can still trip over the inherent race condition there if you don't specify names explicitly. We always considered automatic naming to be a shortcut, and used explicit names matching our internal naming conventions when creating and landing patches. I would snapshot the entire production schema every month or two with pg_dump and back port it into the dev tree, precisely to catch any unintended drift such as differences in naming, triggers, indexes etc. that fell between the cracks.
1a527dd5 2y ago
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] :) In general, it's not something that happens. But that is how we've resolved it in the past.