CREATE 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.
They 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.
Transactions 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.
"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!
I 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.
Index 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.
Just to +1 this as a non-Postgres-user who briefly skimmed the docs:
REINDEX INDEX CONCURRENTLY
^ it can be rebuilt.
I'm honestly not sure if this is saving anything except re-defining (i.e. the literal "create index" statement), but they seem definitely not 100% useless despite being invalid. Maybe 99% useless, but "drop it in the background" doesn't seem particularly better either - leaving intermediate state for an admin to tackle by hand seems reasonable.
You 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).
I'm pretty sure it's just because it allows you to retry the index creation using REINDEX INDEX CONCURRENTLY, which allows you to avoid the exclusive lock created by DROP INDEX, because you can't always DROP INDEX CONCURRENTLY
Actually the index can only fail because of a deadlock or a unique failure.
Deadlocks are rare and are probably an Application bug and for unique failure you need to fix your database anyway and you either need to drop and recreate it or reinfect it (new stuff will use the index for unique checks even in invalid state but not for queries)
Hey 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.
Hey 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.
Do 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!
I have a job that runs nightly or weekly and performs a concurrent reindex on tables, which nicely covers cases like this.
Given that lock timeouts are very much "rescuable," I'm also thinking about addressing the problem right at the start (when adding migrations) by implementing retries. This approach at least would eliminate the need for any follow-up steps by developers and reduce additional cognitive load.
Concurrent 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.
Which 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.
That'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.)
We 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.)
exactly - 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.
We 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).
In 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.
For that exact reason, we have a rule that every DDL migration must be a separate migration in the framework. So if migration #2 out of 3 fails, only 1 migration will be marked as completed. If we see migration failed in CI/CD logs, it's immediately clear what exactly failed, and we can quickly revert what has managed to run (by applying the corresponding down migration), to return the DB to the previous valid state (if we decide to abort the release and fix the migrations first). Or we can rerun individual DDL migrations again, if it's a timeout issue.
We design migrations in such a way so that new schemas/data never conflict with already running (old) code. And new code is activated only after all migrations completed successfully. So in practice half completed migrations is rarely an issue for us.
But yeah, it requires more boilerplate. However, there's fewer surprises, compared to IF NOT EXISTS.
There's lots of cases where that doesn't really work, and can't logically work. An obvious example is:
drop index old;
create index new [..];
There's tons of examples like this where things can kind-of work with enough effort, but also not really – if the query takes 5 minutes without that index it's just going to timeout, so not that different from having broken code, and not much you can do about that too. You really want those two combined to be atomic.
And in cases of "create table new; drop table old" using the old table is often 1) a major hassle, and 2) often doesn't actually work all that well as it's not a tested path.
Either way, it seems MariaDB does support transactions for all of this if you disable "autocommit mode", judging from their docs? I haven't used MySQL/MariaDB in quite a few years, but I've never seen migrations for them done well without tons of hassle and it's a major reason I've typically recommended PostgreSQL. It's just eliminates an entire class of common headaches and errors.
At every point, there's always an index to use. We usually have to split releases into several "subreleases":
Release #1
(automatic migration step)
- alter the table
- add a new index which uses new columns (etc.)
(code release step)
- new code goes live and starts using the new index
Release #2 (we call it "post release")
- remove the old unused index
Automation, CI/CD help here. Yeah, it's somewhat error prone, but you usually get used to it. Not saying it's perfect, but manageable.
In any case, you can never make both SQL migration and code release atomic (if you use rolling updates with zero downtime) so you already have to split into such subreleases anyway.
> it seems MariaDB does support transactions for all of this
No, DDL cannot be used in a transaction in any version of MySQL or MariaDB.
> I've typically recommended PostgreSQL. It's just eliminates an entire class of common headaches and errors
That's fair, but it does create new headaches, for example the notion of an invalid index (as described in the post) simply does not exist in MySQL or MariaDB. Index creation does not block concurrent writes, and won't fail mid-stream due to a lock conflict with old row purging (equivalent to PG's vacuum). And if a DDL statement somehow does fail, e.g. due to an ill-timed host crash, it is fully cleaned up -- DDL is atomic, just not transactional.
One way to avoid the "half a migration" problem you mentioned up-thread is to use declarative schema management, which doesn't require tracking completion state of "migrations" at all: https://www.skeema.io/blog/2019/01/18/declarative/
There are tools that will break each statement into a different migration, and apply a larger grouping by the files you create.
I have actually never seen people adopt those. And the "enterprise" tools people tend to circle around seem to create more failure modes than it helps solving. But I imagine somebody uses them somewhere.
Without those tools, you can break your migration around the failure point and mark the first half as complete. You should have some way to mark them, because you always need some channel for "free" management of a database.
This issue is detectable, at least, to prevent cascading issues (it probably requires manual intervention to resolve though):
You have a started and finished columns and/or a state column on your migrations table;
Insert into migrations;
Run migration foo;
Update migrations set finished = now, state = finished where migration = foo;
Then your deploy tool refuses to run (and reports an error) when there are any non-finished migrations found.
Honestly if your migrations need `if not exist` regularly for basic stuff like creating tables or whatnot something is wrong - it implies you can't rely on other migrations having been performed at which point you have no reasonable way to expect that the end result will be correct.
We have a similar setup; but there are still scenarios where you need a IF NOT EXISTS. A classic example of a table on dev being close to empty, and a table on prod being huge and taking longer than the default timeout ~30 seconds.
Depending on what the timeout is (connection timeout, transaction timeout etc) the DDL operation _may_ still be running. But the migration "process" has failed. So you end up having to slightly modify the attempted migration to account for the fact the thing already exists.
It's an increasingly rare occurrence, probably a few times a year. Like anything the more we do it, the better we get at pulling left and preventing issues in the first place.
Or if you have a DDL user (which is smart; your app user should not be allowed to execute DDL), you can use `ALTER ROLE` for them to have whatever defaults you'd like.
MySQL has a similar variable, `max_execution_time`.
Yeah we are aware of statement_timeout. But it's not something we want to actively promote.
Most people copy and paste a previous migration and then amend it to their previous needs. So if the previous migration set the statement timeout to 1h, and the new migration is doing something we want to stop/prohibit like ADD COLUMN DEFAULT (which takes a lock) or SET NOT NULL on a huge table (which again can cause a lock). We want to prevent locking and prefer the migration to fail earlier and hit the default statement timeout. At which point a conversation occurs how best to do the DDL without effecting prod.
In terms of DDL DML users; we have it split into three-
DDL (Migrations)
DML (Data only, e.g. correcting data)
AppTier user (which is DML + a few more things)
Not trying to be snarky here (and this is by no means unique to your situation) but it sounds like what you have is a knowledge issue more than anything. Don’t apply things in prod you don’t understand. EDIT: by “you,” I mean people copy/pasting migrations and applying them, if that wasn’t clear.
The rise of RDS et al. has utterly killed the majority of people’s knowledge of RDBMS, if they had it to start with. It’s a shame. Also a solid and enjoyable career for me, so I suppose it’s not all bad.
Eh, I'm 50/50 on this. I gave up long ago about people expecting to know and understanding about _everything_ they are doing. I've come to accept that some people just want to code etc.
I pick and choose my battles and where possible enforce certain rules (e.g. you have to have `timestamp with time zone` etc etc) at build time.
I personally find learning about stuff fun! It's why I've grown as an engineer and now have the capability to debug past the code stack into the database.
You have to pick and choose your battles, those engineers who shown interest (what is an index and why do we need it type of questions) I take my time to educate and move the critical mass/inertia in the right direction.
I understand, and the same with PostgreSQL. The only time you might need IF NOT EXISTS is when you are juggling multiple DB update scripts from different development branches and want to avoid dependencies (and probably creating a new problem). EDIT: Also, accounting for indexes or other patches applied live on production when firefighting. The update also needs to happen in dev and staging environments, but not explode when the update gets run on production.
What terrifies me though is the CONCURRENTLY, which indicates automated DB migrations doing stuff outside of transaction boundaries. This stuff should be rare, to the point it is applied manually under supervision. Any step outside the transaction boundary can fail and needs a recovery plan. And you can't automate it because part of the recovery plan might be to fix the networking glitch to see if your statement failed, succeeded but failed to report success, or is ongoing with an uncertain future. And if you have multiple migration steps, that is multiple intermediate database states that you probably haven't tested your app against (say, timeouts because you dropped a necessary index before creating a new one, hopefully one that doesn't take several hours to build).
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);
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.
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?
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.
60 comments
[ 4.7 ms ] story [ 135 ms ] threadWhy isn't index creation done the same way?
The docs explain the mechanics further: https://www.postgresql.org/docs/current/sql-createindex.html...
Your transaction isolation mode defines how they compose.
Unless you are asking something different.
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.
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.
So you can interleave writes and (partially complete) indexing.
If you want transactional index creation, just omit CONCURRENTLY.
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.
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.
And how do you clean up after an aborted index build?
[1]: https://www.postgresql.org/docs/current/sql-reindex.html
I'm honestly not sure if this is saving anything except re-defining (i.e. the literal "create index" statement), but they seem definitely not 100% useless despite being invalid. Maybe 99% useless, but "drop it in the background" doesn't seem particularly better either - leaving intermediate state for an admin to tackle by hand seems reasonable.
https://www.postgresql.org/docs/current/sql-dropindex.html
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.
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
That all said, still would be cool to make this the default in Active Record. Nice idea!
I have a job that runs nightly or weekly and performs a concurrent reindex on tables, which nicely covers cases like this.
Given that lock timeouts are very much "rescuable," I'm also thinking about addressing the problem right at the start (when adding migrations) by implementing retries. This approach at least would eliminate the need for any follow-up steps by developers and reduce additional cognitive load.
The ideal goal being "The system just works"™ :D
Lock Timeout Retries [experimental] https://github.com/ankane/strong_migrations
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.
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?
(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.)
Maybe idempotency was a design goal.
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.
We design migrations in such a way so that new schemas/data never conflict with already running (old) code. And new code is activated only after all migrations completed successfully. So in practice half completed migrations is rarely an issue for us.
But yeah, it requires more boilerplate. However, there's fewer surprises, compared to IF NOT EXISTS.
And in cases of "create table new; drop table old" using the old table is often 1) a major hassle, and 2) often doesn't actually work all that well as it's not a tested path.
Either way, it seems MariaDB does support transactions for all of this if you disable "autocommit mode", judging from their docs? I haven't used MySQL/MariaDB in quite a few years, but I've never seen migrations for them done well without tons of hassle and it's a major reason I've typically recommended PostgreSQL. It's just eliminates an entire class of common headaches and errors.
Release #1
Release #2 (we call it "post release") Automation, CI/CD help here. Yeah, it's somewhat error prone, but you usually get used to it. Not saying it's perfect, but manageable.In any case, you can never make both SQL migration and code release atomic (if you use rolling updates with zero downtime) so you already have to split into such subreleases anyway.
Of course; I'm just saying this is why people want idempotency and use "if not exists", especially in MariaDB/MySQL land.
No, DDL cannot be used in a transaction in any version of MySQL or MariaDB.
> I've typically recommended PostgreSQL. It's just eliminates an entire class of common headaches and errors
That's fair, but it does create new headaches, for example the notion of an invalid index (as described in the post) simply does not exist in MySQL or MariaDB. Index creation does not block concurrent writes, and won't fail mid-stream due to a lock conflict with old row purging (equivalent to PG's vacuum). And if a DDL statement somehow does fail, e.g. due to an ill-timed host crash, it is fully cleaned up -- DDL is atomic, just not transactional.
One way to avoid the "half a migration" problem you mentioned up-thread is to use declarative schema management, which doesn't require tracking completion state of "migrations" at all: https://www.skeema.io/blog/2019/01/18/declarative/
I have actually never seen people adopt those. And the "enterprise" tools people tend to circle around seem to create more failure modes than it helps solving. But I imagine somebody uses them somewhere.
Without those tools, you can break your migration around the failure point and mark the first half as complete. You should have some way to mark them, because you always need some channel for "free" management of a database.
Anyway, transactional DDL is great.
You have a started and finished columns and/or a state column on your migrations table;
Insert into migrations; Run migration foo; Update migrations set finished = now, state = finished where migration = foo;
Then your deploy tool refuses to run (and reports an error) when there are any non-finished migrations found.
Honestly if your migrations need `if not exist` regularly for basic stuff like creating tables or whatnot something is wrong - it implies you can't rely on other migrations having been performed at which point you have no reasonable way to expect that the end result will be correct.
Depending on what the timeout is (connection timeout, transaction timeout etc) the DDL operation _may_ still be running. But the migration "process" has failed. So you end up having to slightly modify the attempted migration to account for the fact the thing already exists.
It's an increasingly rare occurrence, probably a few times a year. Like anything the more we do it, the better we get at pulling left and preventing issues in the first place.
MySQL has a similar variable, `max_execution_time`.
Most people copy and paste a previous migration and then amend it to their previous needs. So if the previous migration set the statement timeout to 1h, and the new migration is doing something we want to stop/prohibit like ADD COLUMN DEFAULT (which takes a lock) or SET NOT NULL on a huge table (which again can cause a lock). We want to prevent locking and prefer the migration to fail earlier and hit the default statement timeout. At which point a conversation occurs how best to do the DDL without effecting prod.
In terms of DDL DML users; we have it split into three-
DDL (Migrations) DML (Data only, e.g. correcting data) AppTier user (which is DML + a few more things)
The rise of RDS et al. has utterly killed the majority of people’s knowledge of RDBMS, if they had it to start with. It’s a shame. Also a solid and enjoyable career for me, so I suppose it’s not all bad.
I pick and choose my battles and where possible enforce certain rules (e.g. you have to have `timestamp with time zone` etc etc) at build time.
I personally find learning about stuff fun! It's why I've grown as an engineer and now have the capability to debug past the code stack into the database.
You have to pick and choose your battles, those engineers who shown interest (what is an index and why do we need it type of questions) I take my time to educate and move the critical mass/inertia in the right direction.
What terrifies me though is the CONCURRENTLY, which indicates automated DB migrations doing stuff outside of transaction boundaries. This stuff should be rare, to the point it is applied manually under supervision. Any step outside the transaction boundary can fail and needs a recovery plan. And you can't automate it because part of the recovery plan might be to fix the networking glitch to see if your statement failed, succeeded but failed to report success, or is ongoing with an uncertain future. And if you have multiple migration steps, that is multiple intermediate database states that you probably haven't tested your app against (say, timeouts because you dropped a necessary index before creating a new one, hopefully one that doesn't take several hours to build).
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);
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?
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.
In general, it's not something that happens. But that is how we've resolved it in the past.