5 ms·
Don't rely on IF NOT EXISTS for concurrent index creation in PostgreSQL
- deleted 2y ago[deleted]
- londons_explore 2y agoOther long-running operations use transactions... Do the operation on a snapshot of the data, and then evaluate if it can be committed. Why isn't index creation done the same way?
- AmericanChopper 2y agoCREATE INDEX CONCURRENTLY is not a transactional statement. If you want to create an index inside a transaction it will block all writes to the table until the index is created, which can take a rather long time on large tables.
- deleted 2y ago[deleted]
- singron 2y agoConcurrent index creation internally uses multiple transactions, so it can't itself be wrapped in a transaction. The docs explain the mechanics further: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY https://www.postgresql.org/docs/current/sql-createindex.html...
- zelphirkalt 2y agoThat immediately brings up the next question: Why are transactions not composable?
- londons_explore 2y agoIgnoring everything but the outer transaction would be technically correct - wonder why it isn't supported?
- deleted 2y ago[deleted]
- paulddraper 2y agoThey are? Your transaction isolation mode defines how they compose. Unless you are asking something different.
- jeltz 2y agoThey are. Just not in a way which would help. If a subtransaction commits the changes are still not visible to other connections until the top level transaction commits. The subtransactions are exposed to SQL as savepoints but my point still holds. I am not sure what would be needed to implement concurrent index build but subtransactions would not help.
- singron 2y agoTransactions cannot compose because nested transactions cannot simultaneously (1) ensure committed transactions are visible and (2) uncommitted transactions are not visible. If you don't care about visibility until the end and just want nested atomicity, you can use savepoints as a kind of nested transaction, but they won't be visible to other sessions until the top level transaction is committed. CREATE INDEX CONCURRENTLY has steps where it needs to make changes visible and wait until no transactions are open from before that point, so it can't use savepoints.
- paulddraper 2y agoThe entire point of CONCURRENTLY is to not be in a single transaction. So you can interleave writes and (partially complete) indexing. If you want transactional index creation, just omit CONCURRENTLY.
- riiii 2y ago[flagged]
- ars 2y ago"PostgreSQL doesn’t automatically remove the partially created index when the operation is aborted due to a lock timeout. This is intentional, as it allows for potential recovery or manual intervention" This argument doesn't make much sense. What's the usefulness of a partially created index? The entire rest of the article is working hard to explain how this invalid index is 100% useless and should be dropped! So this seems like a bug in PostgreSQL to me.
- victorbjorklund 2y agoYou can use reindex to rebuild an invalid index. But many prefer to just drop it and try again (like the author does). I'm sure there are scenarios where reindex is a much better option so not a bug (maybe a bad default).
- shayonj 2y agoI went looking in the postgres code and took it more as a "Feature request" where if the index building / table scanning, etc is interrupted for concurrent builds, PG could drop the index in the end. I can see how PG keeps it around however, since invalid indexes can be useful for administrators. However, for application developers - unless you are familiar with the internals, this comes off as a big surprise. Would love to learn more from folks who are more familiar with the internals.
- choilive 2y agoIndex creation can take a very long time for a large database, think hours or god forbid days. Restarting from scratch for an index that takes 18 hours to build is very painful.. doubly so back when concurrent index creation didn't exist.
- deleted 2y ago[deleted]
- andatki 2y agoHey Shayon. IF NOT EXISTS or the Active Record version "if_not_exists: true" can be pretty handy though when there's a valid index in production, to help drive schema definition consistency in all environments (prod, dev, CI, etc.). As you pointed out though, it only makes sense when the indexes being checked are valid. In my experience on large tables that are “busy”, sometimes indexes need to be added manually first from a utility session, perhaps inside tmux/screen that's detached from while they are created. This could take hours for large tables. Then once done, and the index is valid, an Active Record migration can be sent out using “if_not_exists: true” to make sure it’s applied everywhere. Your point that it could be misused unintentionally due to not knowing an index is INVALID is a good one, and I feel it should be part of how it works by default in Active Record. Had you considered trying to propose that to rails/rails? I would certainly support that PR (may be able to collaborate) and could add more examples and validation.
- shayonj 2y agoHey Andrew! It's great to see you here. I agree with everything you said, 100%. I'm honestly torn because I was the first one to recommend `if_not_exists` to engineers, but now I'm reconsidering because this can lead to very strange issues in production, especially for ActiveRecord applications. Maintaining an "invalid index reaper job" doesn't sound like a good long-term solution. I've suggested some proposals to Rails here and would love to propose a PR based on the solution that makes the most sense, too. https://github.com/rails/rails/issues/52583 https://github.com/rails/rails/issues/52583
- andatki 2y agoDo you do any alerting for INVALID indexes? For example, by default PgHero will display them prominently and I believe PgAnalyze does as well. My thought is to put the energy into making INVALID indexes highly visible. Perhaps combined with a process step. 1. Any CREATE INDEX CONCURRENTLY migrations go out in their own deployment. 2. Any queries that depends on that index being present means a PR has a process step asking the author to verify that the index exists and is valid. That way you wouldn't have to lose if_not_exists. That all said, still would be cool to make this the default in Active Record. Nice idea!
- limaoscarjuliet 2y agoConcurrent index creation or re-indexing is not all magic. It does scan the table multiple times and might lead to deadlocks if the table is heavily modified. Furthermore, failed concurrent index or re-index will leave invalid cursors behind that are not usable but incur the update costs. Of course, they consume the disk space. Use with caution.
- sgarland 2y agoWhich is why you should monitor DDL, manually or automatically, and alert you to invalid indices. This can be as simple as a cron that runs something like this: SELECT indrelid::regclass tbl, indexrelid::regclass idx FROM pg_index WHERE NOT indisvalid; And then sends that to Slack or whatever if it returns a result.
- dogsledsleddog 2y agoI hope I am missing something in this logic?: Broken indexes are intentionally left because indexing can be a long operation on a big db, so you may regret not just repairing them.. Let's delete and recreate indexes all the time, just in case because they might be left over.. Hope we don't encounter the reason they left broken indexes and wait a few hours for a rebuild?
- shayonj 2y agoReindex starts fresh on a temp index and then does a swap in the end - https://www.postgresql.org/docs/current/sql-reindex.html https://www.postgresql.org/docs/current/sql-reindex.html
- dogsledsleddog 2y agoThat's very helpful, but I still don't get the recommendation. The reindex seems to guarantee that any useful index will be used, the deletion and recreation seems to guarantee a period of bad performance as long as an index takes to build even if the old one was fine. (I.e. I could imagine a customer upgrading from a version that already backported an index to the new major version where it was added and deciding there's a performance regression.)
- kgeist 2y agoWe generally ban any IF NOT EXISTS in our team (we use MySQL) because it usually masks the real problem (why is the developer trying to create a table which already exists in the test environment? maybe there's a name conflict with another branch etc.)
- shayonj 2y agoexactly - i missed to talk about this in the post. I think using IF NOT EXISTS can be a sign of a higher degree issue as well. Being more mindful can help.
- lisper 2y ago> why is the developer trying to create a table which already exists Maybe idempotency was a design goal.
- kgeist 2y agoWe have a special table which remembers which migrations were already run. So creating a second migration which tries to create the table again is a sign something is wrong. It's probably not what the developer intended and can result in wrong assumptions about the table's schema, for example (they expected their version would be used but the new migration is actually skipped).
- arp242 2y agoIn general I agree, but in MySQL/MariaDB this is quite tricky because you can't do a lot of stuff in transactions; definitely seen problems where someone does; -- migrate-2024-08-12-foo.sql create table [..] alter table [..] insert into migrations values ('migrate-2024-08-12-foo'); And the create works, but the alter fails. And then you try to run it again and now the create fails because you have half a migration. Herp derp. And you often can't split those two statements either: that would be "half a migration" that won't work with the code. As far as I know there isn't really a good solution for this. It can be quite a pain. In PostgreSQL and SQLite this is not an issue as you can run all the above in a transaction. You indeed rarely need "if not exists" there.
- 1a527dd5 2y agoIF 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 agoHow do you do migrations that drop those indices if they're potentially differently-named on dev/prod?
- stubish 2y agoDifferently 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 agoOh, 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.
- karmakaze 2y agoInstead of saying what not to do, pick something that works. Use something to manage your migrations.