0

I have a live app where I have some big tables (~100+M rows), which I recently truncated to ~20M rows. One behavior I've noticed after making the said change is that DB response time has escalated in some cases (SELECT queries highly affected). I've been doing research on why this could have happened.

One thing that has popped up is that I may need to reindex the indexes on these tables - original indexes may have become fragmented/unoptimized. However, it being a live environment, that could impose write locks. I've read that a better way to do this is:

  1. Create an index concurrently with a different name,
  2. Drop the original index
  3. Rename the new one to the old one

Being an accidental DBA, I want to be sure about how to do this. So here's an example of how I'll go about reindexing one index.

Please correct me (or improve my approach) if you feel something's left wanting:

Imagine the following table with 4 indexes:

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "id_answer_to_id" btree (answer_to_id, id DESC)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

Starting with "links_publicreply_submitted_by_id" btree (submitted_by_id). I'll try:

1) CREATE INDEX CONCURRENTLY temp_index ON links_publicreply (submitted_by_id DESC)

2) Then I'll run the following:

BEGIN;
DROP INDEX links_publicreply_submitted_by_id;
ALTER INDEX temp_index RENAME TO links_publicreply_submitted_by_id;
COMMIT;

Does that sound right? If not, please correct/improve as necessary.

Follow-up question: Should I attempt to re-index the primary key index in similar fashion?

Your Answer

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

Browse other questions tagged or ask your own question.