Vryjdfkk

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?