Reconsider skip-transaction mode

[glenjamin]

Hello

I'm looking at switching over our internal approach to migrations to use pgmigrate

However, I'm keen to retain CONCURRENTLY in our index definitions (we even discussed this briefly on HN some time ago). I see a previous discussion in #8 led to a conclusion that you wouldn't want to allow this

I've done a quick implementation of this over on our github fork, if you're open to it I'll turn this into a proper PR for submission: geckoboard#1

If you're not keen then I think we'll likely maintain our own fork, as this lib ticks every other box we have!

My rationale is this:

• The common case is that your development environments have small tables, your staging environment has smallish tables and your production environment has large tables.
• In production it is not safe to run CREATE INDEX without CONCURRENTLY, as it will block writes for an indeterminate amount of time
• Depending on how large your table is, the CREATE INDEX CONCURRENTLY will either complete successfully, or time out and not complete
• When it times out and doesn't complete, the migration is left INVALID and unusable

These are the constraints, and I think up to this point we're on the same page.

When these constraints are combined with the "all migrations must be transactional" constraint, you end up with the conclusion described in #8

1. All index additions must omit CONCURRENTLY
2. All index additions must include IF NOT EXISTS
3. These will safely apply to non-production environments
4. In production you must remember to to run an additional manual command before deployment to safely create the index
5. if you forget to run the additional manual command you take down production for some amount of time

However, in a mode where CONCURRENTLY + skip-transaction is allowed, you'd end up with

1. Index additions on large tables can include CONCURRENTLY
2. These will safely apply to non-production environments
3. If the production index can be created within the timeout, then it gets created safely
4. If the production index takes too long, the migration fails
5. If the migration fails, you must then take manual steps to safely build the index
   • You can try increasing the timeout and running migrations again
   • You can include IF NOT EXISTS on the index creation, and use REBUILD INDEX CONCURRENTLY to repair it

My claim is that because the no-tx approach in the best case requires no manual effort, and in the worst case requires roughly the same amount of manual effort as the standard tx-based approach, it's preferrable to allow opting in to CONCURRENTLY and no-tx.

In practice I'll likely combine pgmigrate with something like pgvet to ensure my team remembers all of these caveats.