PostgreSQL index creation in production systems

Introduction

Managing indexes in a PostgreSQL database, especially in production environments, is crucial for maintaining optimal performance and ensuring data integrity. Indexes can significantly speed up query performance by allowing the database to quickly locate and access the data. However, creating and managing indexes in a live production system requires careful planning to avoid locking issues and performance degradation.

This blog post provides a list of essential SQL queries to monitor and manage the index creation process in PostgreSQL databases. These queries will help database administrators (DBAs) and developers ensure that indexes are created efficiently and without disrupting the normal operations of the database.

Note: The examples provided in this post are for PostgreSQL version 9.2.

The following are some of the SQL queries to monitor the index creation process in the database.

List all indexes of a table in a database

This query lists all the indexes of a specified table in a given schema. It helps you understand what indexes are already present on the table.

SELECT indexname,
       indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'student';

List the indexes that are in an invalid state

This query helps identify indexes that are in an invalid state, which can be useful for troubleshooting. Invalid indexes might not be used by the database for query optimization.

SELECT relname, pg_index.indisvalid
FROM pg_class,
     pg_index
WHERE pg_index.indisvalid = false
  AND pg_index.indexrelid = pg_class.oid;

Get PID of all the indexes in progress

This query retrieves the process IDs (PIDs) of all index creation operations currently in progress. It helps you monitor ongoing index creation activities.

SELECT pid, query
FROM pg_stat_activity
WHERE query LIKE 'CREATE INDEX%';

Cancel or terminate a query based on the PID

These queries allow you to cancel or terminate a query based on its PID, which can be useful if an index creation process is causing issues. Canceling a query attempts to stop it gracefully, while terminating it forcefully stops the process.

SELECT pg_cancel_backend(<PID>);
SELECT pg_terminate_backend(<PID>);

List locks acquired by the queries

This query lists the locks acquired by queries, which can help identify locking issues during index creation. It shows which queries are holding locks and for how long.

SELECT a.datname,
       l.relation::regclass, l.transactionid,
       l.mode,
       l.GRANTED,
       a.usename,
       a.query,
       a.query_start,
       age(now(), a.query_start) AS "age",
       a.pid
FROM pg_stat_activity a
         JOIN pg_locks l ON l.pid = a.pid
WHERE mode = 'ShareUpdateExclusiveLock'
ORDER BY a.query_start LIMIT 10;

Drop an index

This query drops an index if it exists, which can be useful for cleanup or re-creation purposes. Dropping an index can help improve performance if the index is no longer needed.

DROP INDEX IF EXISTS public.idx_student_id_name;

Create an index concurrently

Creating an index concurrently allows other operations to continue while the index is being created, reducing lock contention. This is useful in production environments where downtime needs to be minimized.

CREATE INDEX CONCURRENTLY idx_student_id_name ON public.student (student_id, name);

Create an index with an exclusive lock

Creating an index with an exclusive lock ensures that no other operations can modify the table while the index is being created, which can be useful for ensuring data consistency. However, it can cause downtime for other operations on the table.

CREATE INDEX idx_student_id_name ON public.student (student_id, name);