patternsqlModerate
Drop an index in postgresql
Viewed 0 times
postgresqldropindex
Problem
I had created an index wrongly and now I am trying to drop that index. Since the table is large, dropping the index is taking lot of time. Is there any other way to drop the index quickly?
Solution
You could try
-
CONCURRENTLY
Drop the index without locking out concurrent selects, inserts,
updates, and deletes on the index's table. A normal DROP INDEX
acquires exclusive lock on the table, blocking other accesses until
the index drop can be completed. With this option, the command
instead waits until conflicting transactions have completed.
-
Here's the documentation for postgres 9.2:
http://www.postgresql.org/docs/9.2/static/sql-dropindex.html
Note: This feature is not available before postgres 9.2.
-
Here's the documentation for postgres 9.1:
http://www.postgresql.org/docs/9.1/static/sql-dropindex.html
-
This is similar to
http://technet.microsoft.com/en-us/library/ms176118.aspx
DROP INDEX [ CONCURRENTLY ] name-
CONCURRENTLY
Drop the index without locking out concurrent selects, inserts,
updates, and deletes on the index's table. A normal DROP INDEX
acquires exclusive lock on the table, blocking other accesses until
the index drop can be completed. With this option, the command
instead waits until conflicting transactions have completed.
-
Here's the documentation for postgres 9.2:
http://www.postgresql.org/docs/9.2/static/sql-dropindex.html
Note: This feature is not available before postgres 9.2.
-
Here's the documentation for postgres 9.1:
http://www.postgresql.org/docs/9.1/static/sql-dropindex.html
-
This is similar to
ONLINE = ON option in SQL Server:http://technet.microsoft.com/en-us/library/ms176118.aspx
Context
StackExchange Database Administrators Q#49896, answer score: 12
Revisions (0)
No revisions yet.