In PostgreSQL, can data be lost by adding an index to a table which is being updated?

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.