Problems with concurrent Postgres indexes (and how to solve them)

Ken Alex Fassone
Aug 22, 2016 · 2 min read

Since I read the article linked below:

I started using indexes in Postgres using the `algorithm: :concurrently` flag and always thought ‘why is the concurrently flag not the default used by Postgres?’ Now, months after the above post, I found the answer.

In January, we had an increased load on the database which led us to start a book club here in carwow called ‘Postgres Connoisseurs’ (thanks to Pericles, the genius behind the name). Since then, I’ve started reading ‘PostgreSQL 9.0 High Performance’; a slightly outdated book but still the best in its genre.

What does concurrently do?

An index created concurrently will not lock the table against writes, which means you can still insert or update new rows. In order to execute the (slightly slower) index creation, Postgres will do the following:

  • Scans the table once to build the index;
  • Runs the index a second time for things added or updated since the first pass.

The above should generally work fine, but what happens if we run the following index and we insert a duplicate between the two steps?

Postgres will stop the creation of the index and it will be marked as ‘INVALID’; meaning that it won’t be used for queries, but it will still be updated like any other index.

How can I detect invalid indexes?

You can find all invalid indexes by running the following query:

How can I rebuild invalid indexes?

You have two choices:

  • Use the REINDEX command (not suggested);
  • Drop the index and try to re-build it again (suggested).

REINDEX won’t re-create the index concurrently, it will lock the table for writes while the index is being created. This is the reason why this solution is not optimal.

With the second option you should drop the index, try to solve the problem that occurred upon index creation (e.g. remove any duplicates) and then re-build the index using the CONCURRENTLY flag again.

Happy SQL-ing!

Interested in making an Impact? Join the carwow-team!
Feeling social? Connect with us on Twitter and LinkedIn :-)

carwow Product, Design & Engineering

What happens under the hood at

Ken Alex Fassone

Written by

carwow Product, Design & Engineering

What happens under the hood at

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade