I have a very large database (millions of rows) which has been indexed. I receive a diff of rows from another source that I need to update in my database.

The problem I am facing is that is it necessary to re-index the entire database/table again since it is a very expensive process and I don't want our site to be down for the period it is being indexed. Is there a better way or a way to add the partial index to the original one so that I do not need to reindex everything ?

I am using postgresql 9.1 as a database.

  • How significant are these diff updates (what percent of rows are changed)? Are you sure you need to reindex, have you actually observed excessive index bloat?
    – dbenhur
    Commented Mar 15, 2012 at 20:43
  • @dbenhur You mean I do not need to re-index ? I am getting the diffs on monthly basis. The size of the diff is not much but isn't fixed either. There are almost 200-300 rows in a diff.
    – krammer
    Commented Mar 16, 2012 at 8:29
  • 3
    Indexes are automatically updated when anything does INSERT,DELETE,UPDATE,COPY FROM. The main purpose for REINDEX is to remove "bloat" (see routine reindexing). If you have no bloat you don't need to do anything. Monthly updates to a few hundred rows out of millions of rows is not going to produce any significant inefficiencies.
    – dbenhur
    Commented Mar 16, 2012 at 15:12

2 Answers 2


After understanding your use better, I think the real answer is: you have no need to REINDEX.

REINDEX recreates the entire index. But indexes are maintained incrementally; as rows are inserted and deleted, or updated with new key values, the index is updated to reflect the change, as well as prior versions.

Because of the way MVCC works, when you delete a row, postgres can't actually delete the row or the index entry immediately, as there may be transactions that still have visibility to the row, so it just marks it deleted. VACUUM is run periodically to garbage collect dead tuples, But indexes can still retain bloat where there are dead entries to old row versions. So, frequent updates to indexed columns or massive deletes can produce many dead index entries which make the key density of index blocks lower and produce inefficiencies in index operations. This is called "index bloat".

You can detect whether you're suffering it using some scripts which inspect catalog views and calculate bloat (available through the prior link). If you do incur index bloat, periodic REINDEX operations may be called for. In your use case, you have several million rows, and on a monthly basis delete or modify patches on the order of a few hundred rows. Over the course of a year, this access pattern is likely to touch only a small fraction of a percent of the total rows, so any index bloat resulting will be minuscule and not justify the expense of a REINDEX operation.

  • @krammer, this is the the more likely answer to your problem in general.
    – swasheck
    Commented Mar 16, 2012 at 15:58

Postgres has the CONCURRENTLY keyword that takes no locks. It'll still be expensive but you can leave the table online.


However, there are caveats to indexing concurrently with which you should familiarize yourself.

  • The database wouldn't have any other write transactions while the index is being created. But it will still have the read requests from the client which according to my understanding would use the old index until the new indexes have been created. Would concurrent be a sane choice in this case as well ?
    – krammer
    Commented Mar 15, 2012 at 17:15
  • Based on the documentation, either should work since the normal indexing operation only blocks write.
    – swasheck
    Commented Mar 15, 2012 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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