Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Mar 15 '12 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 Mar 16 '12 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 Mar 16 '12 at 15:12
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
@krammer, this is the the more likely answer to your problem in general. –  swasheck Mar 16 '12 at 15:58
add comment

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

Edit:

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

share|improve this answer
    
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 Mar 15 '12 at 17:15
    
Based on the documentation, either should work since the normal indexing operation only blocks write. –  swasheck Mar 15 '12 at 17:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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