Tag: postgresql

Find (and Safely Drop) Unused Indexes in PostgreSQL

A quick query to find PostgreSQL indexes nobody uses, the four checks to run before dropping one, and how to drop it without blocking writes.

Indexes speed up reads, but they aren’t free. Every INSERT, UPDATE and DELETE has to maintain them, they take up disk, and they compete for memory in shared buffers. On a database that’s been around a few years, it’s common to find indexes that nobody has used in months. Here’s how to find them, and what to check before you drop one.

The query

PostgreSQL tracks how often each index is scanned in pg_stat_user_indexes. This query lists indexes with zero scans. It skips primary keys, unique indexes, and indexes that back a constraint, because those enforce rules even if no query reads them.

SELECT s.schemaname,
       s.relname        AS table_name,
       s.indexrelname   AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT EXISTS (
        SELECT 1 FROM pg_constraint c
        WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

The biggest unused indexes come first, so the top of the list is where the payoff is.

Before you drop anything, check these four things

  1. When were stats last reset? A zero only means something if the counters have been collecting for a while. Run SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); and aim for at least a few weeks of history, ideally covering a month-end.
  2. Do you have read replicas? Scan counts are tracked per server. An index used only by reporting queries on a replica will show zero on the primary. Run the query on every replica too.
  3. Are there rare jobs? Quarterly reports and yearly archive jobs may depend on an index that looks idle most of the time.
  4. Is the index new? A recently created index will naturally show low counts.

On PostgreSQL 16 and later, pg_stat_user_indexes also has a last_idx_scan column, which tells you when the index was last used, not just how many times.

Dropping safely

First save the definition, so you can recreate the index if something slows down:

SELECT pg_get_indexdef('schema_name.index_name'::regclass);

Then drop it without blocking writes to the table:

DROP INDEX CONCURRENTLY schema_name.index_name;

CONCURRENTLY can’t run inside a transaction block. If it fails partway through, check for a leftover invalid index and drop it again.

Wrap-up

Run the query once a quarter, verify across all replicas, and keep the saved definitions somewhere handy. Removing dead indexes is one of the cheapest write-performance wins you’ll find.

How to Extract and Replicate PostgreSQL Permissions When Migrating to a New Instance

Migrating a PostgreSQL database isn’t just about moving data—getting the right roles and permissions in place is critical for security and proper application function. This post demonstrates how to extract roles and permissions from your source instance and apply them to a new PostgreSQL environment.

Understanding Permissions in PostgreSQL

PostgreSQL controls access via roles. A role can represent either a database user or a group, and roles are granted permissions (privileges) on objects (tables, databases, schemas, etc.) using GRANT and REVOKE commands. These permissions can be viewed and managed at various levels:

  • Database: Control who can connect
  • Schema: Control access to groups of tables, functions, etc.
  • Objects: Control what actions (SELECT, INSERT, UPDATE, etc.) users can perform on tables, functions, or sequences.

You can view permissions using PostgreSQL’s psql meta-commands:

  • \l+ — Show database privileges
  • \dn+ — Show schema privileges
  • \dp+ — Show table and other object privileges

Step 1: Extract Roles from the Source Instance

To extract roles (users and groups) from your current PostgreSQL server:

pg_dumpall --roles-only > roles.sql

Note:

  • This command will export all roles (but not their passwords in managed services like Azure Database for PostgreSQL).
  • In cloud managed systems, you might not have the ability to extract passwords; you’ll need to set them manually on the target instance.

Step 2: Extract Role & Object Permissions

Extracting object-level permissions (like all GRANT and REVOKE statements) can be done while dumping database schema:

pg_dump -h <source_server> -U <username> -d <dbname> -s > db_schema.sql

Next, filter permission statements:

If you’re working in PowerShell (Windows), run:

pg_dump -h "psql-prod-01.postgres.database.azure.com" -U pgadmin -d "prod_db" -s | Select-String -Pattern "^(GRANT|REVOKE|ALTER DEFAULT PRIVILEGES)" -Path "C:\Path\to\db_schema.sql" | ForEach-Object { $_.Line } > C:\Path\to\permissions.sql

If you’re working on Mac/Linux, run:

pg_dump -h "psql-prod-01.postgres.database.azure.com" -U pgadmin -d "prod_db" -s | grep -E '^(GRANT|REVOKE|ALTER DEFAULT PRIVILEGES)' > perms.sql

This extracts all lines related to granting or revoking privileges and puts them in perms.sql.

Step 3: Prepare and Edit Scripts

  • Review the extracted roles.sql and `permissions.sql:
    • Remove any references to unsupported roles (like postgres superuser in cloud environments).
    • Plan to set user passwords manually if they weren’t included.

Step 4: Copy Roles and Permissions to the New Instance

  1. Recreate roles:textpsql -h <target_server> -U <admin_user> -f roles.sql
    • Remember to set or update passwords for each user after creation.
  2. Apply object-level permissions:textpsql -h <target_server> -U <admin_user> -d <target_db> -f perms.sql

Step 5: Validate Permissions

Connect as each role or user to ensure operations work as expected:

  • Use \dp tablename in psql to check table permissions.
  • Use the information_schema views (e.g., role_table_grants) to query permissions programmatically:SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants;

How do you check if logical replication is running on a postgres instance?

  1. Check if logical replication is enabled in the PostgreSQL configuration file:Open the PostgreSQL configuration file (postgresql.conf), usually located in the PostgreSQL data directory. Look for the wal_level parameter. Logical replication requires at least wal_level to be set to logical or replica. If it’s set to logical, then logical replication is enabled. You can find the location of the PostgreSQL data directory by querying the pg_settings system catalog table: SELECT name, setting FROM pg_settings WHERE name = 'data_directory';
  2. Check if logical replication slots are being used:Logical replication uses replication slots to keep track of the status of replication. You can check if any replication slots are in use by querying the pg_replication_slots system catalog table: SELECT slot_name, plugin, slot_type, database FROM pg_replication_slots; If there are slots with slot_type as logical, then logical replication is being used.
  3. Check if there are any active subscriptions:Logical replication involves publishers and subscribers. Publishers publish changes to the replication stream, and subscribers consume these changes. To see if there are any active subscriptions, you can query the pg_subscription system catalog table:SELECT * FROM pg_subscription; If there are subscriptions listed here, then logical replication is in use.
  4. Check if there are any replication connections:Logical replication typically involves connections from publishers to subscribers. You can check if there are any active replication connections by querying the pg_stat_replication system view:SELECT * FROM pg_stat_replication; If there are entries listed here, then logical replication connections are active.

By following these steps, you can determine if logical replication is running on your PostgreSQL instance.