7

I'm not sure how to answer this from the documentation, and am unable to find an answer through searching.

I'm wondering: if there's an existing table which is being updated (having data added to it), could I lose any of the data by adding an index to the table?

I don't mind things slowing down a bit, but I don't want to lose data as a result of adding an index, due to the table being locked or something whilst the index is being created.

To be clear, there is an existing table within a database, data is being added to the table, and I would like to add an index to the table. I am unsure if I could lose some of the data being added to the table whilst the index is being made or something.

3 Answers 3

9

No, you won't lose data.

While the CREATE INDEX is running, Postgres will block all write access to the table. So any INSERT/UPDATE or DELETE statement that is started after the CREATE INDEX, will be forced to wait until the index is created (and COMMITed).


The situation is different if you use CREATE INDEX CONCURRENTLY. In that case the write access is not blocked and the DML will work "as usual". The CREATE INDEX might need longer to finish though.

If something goes wrong curing CREATE INDEX CONCURRENTLY, then the index is marked unusable, but still no data is lost. One such situation is the creation of a UNIQUE index and while it's running duplicates are inserted into the table.

More details on the concurrently option can be found in the manual

4
  • thanks - "the index is marked unusable", at which point I presume manual clean up is required?
    – baxx
    Nov 29, 2022 at 11:34
  • In the non-concurrently case, while Postgres will indeed not lose any data (i.e. already committed data), it is possible for the client to have issues with things taking too long (timeouts) and for incoming data coming from wherever it comes from to be lost en route. I don't think it could generate deadlocks, though, could it?
    – jcaron
    Nov 29, 2022 at 12:30
  • @jcaron: I took "lose data" as data being lost due to the working of CREATE INDEX, not because the application can't handle a timeout. I think any statement that waits for some database resource can cause a deadlock as soon as more than one concurrent transaction is involved. But if the transaction running the CREATE INDEX is chosen as the deadlock victim, then no data will be lost. The index just won't be created. Nov 29, 2022 at 12:37
  • 1
    @baxx: yes, you would need to DROP the index and retry. Nov 29, 2022 at 12:38
8

The INSERT will be blocked on a lock while an index creation is going on. We can't tell you how your client will respond to that. Maybe it will wait patiently and then resume, maybe it will get a timeout, throw errors, fail to catch them, and crash out in disgrace--losing whatever data it had had internally cached which was not yet saved. Maybe it will release the Kraken.

If you create the index with CONCURRENTLY it does rearrange the timings and the odds, but doesn't fundamentally change things. It will shorten how long the access exclusive lock is held once acquired, but how the client responds to even a shortened lock is still up to the client. And if there is more than one client, it is still possible for some of them to be blocked for an unbounded amount of time.

4

A simplified view over the modern SQL-based database engines:

A sane database engine (not only Postgres, but also MySQL, MS SQL, Oracle, SQLite, etc...) should never corrupt the data because of running concurrent queries.

This holds true both for the data residing in the database AND for the data returned as a query result.

The result will always be as if the queries were ran one after another.

In some cases one of the queries that run together may fail with an explicit error message, but the net result will be as if the failed query never started.


In regard to adding an index:

The database is expected to deliver exactly the same results with and without index. The performance may differ and this is why indexes are created in the first place, but the data stored and returned should be the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.