Database Indexes Management

Overview

Database indexes are critical for query performance but come with important trade-offs. Indexes are loaded into database memory and can cause Out of Memory (OOM) issues if not managed carefully. This guide covers best practices for creating, monitoring, and maintaining indexes.

⚠️ Critical Warning

Indexes consume database memory (RAM). Creating too many indexes or indexes on large tables without proper planning can lead to:

• Database OOM crashes
• Performance degradation
• Increased storage costs
• Slower write operations (INSERT, UPDATE, DELETE)

Best Practices

1. Plan Before Creating

• Analyze query patterns before adding indexes
• Use EXPLAIN ANALYZE to verify the index will actually be used
• Test on staging with production-like data volume first
• Measure the expected index size (see Measuring Index Size)

2. Index Creation Strategy

• Use CREATE INDEX CONCURRENTLY in production to avoid locking the table
• Schedule index creation during low-traffic periods
• Monitor database memory and CPU during creation
• Have a rollback plan ready

-- Safe index creation in production
CREATE INDEX CONCURRENTLY idx_admin_info_discharge_date
ON "AdminInformation" ("tenantId", "dischargeDate")
WHERE "dischargeDate" IS NOT NULL;

3. Choose the Right Index Type

• B-tree (default): Most common, good for equality and range queries
• Expression indexes: For queries on computed values (e.g., TO_CHAR(date, 'YYYY-MM'))
• Partial indexes: Include WHERE clause to index only relevant rows
• Composite indexes: Order columns by selectivity (most selective first)

-- Expression index example
CREATE INDEX idx_discharge_month
ON "AdminInformation" ("tenantId", TO_CHAR("dischargeDate", 'YYYY-MM'))
WHERE "dischargeDate" IS NOT NULL;

-- Partial index example (more memory efficient)
CREATE INDEX idx_active_medical_stays
ON "MedicalStay" ("tenantId", "state")
WHERE "deletedAt" IS NULL;

4. Monitor Index Usage

Unused indexes waste memory and slow down writes. Regularly audit and remove them:

-- Find unused indexes
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Measuring Index Size

Total Index Size Per Table

Use this query to see the total memory footprint of all indexes on each table:

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_indexes_size(quote_ident(schemaname) || '.' || quote_ident(tablename))) AS total_index_size,
  count(*) AS index_count
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
GROUP BY 1,2
ORDER BY pg_indexes_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) DESC;

Output example:

 schemaname |      tablename       | total_index_size | index_count
------------+----------------------+------------------+-------------
 public     | AdminInformation     | 45 MB            | 3
 public     | MedicalStay          | 32 MB            | 5
 public     | MedicalInformation   | 18 MB            | 2

Individual Index Details

Use this query to see each index with its size and definition:

SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(indexname))) AS index_size,
  indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename, indexname;

Output example:

 schemaname |      tablename       |           indexname                | index_size |                    indexdef
------------+----------------------+------------------------------------+------------+------------------------------------------------
 public     | AdminInformation     | AdminInformation_pkey              | 8192 kB    | CREATE UNIQUE INDEX ...
 public     | AdminInformation     | idx_admin_info_discharge_date      | 2048 kB    | CREATE INDEX ...
 public     | AdminInformation     | idx_admin_info_tenant_discharge    | 3072 kB    | CREATE INDEX ...

Index Usage Statistics

Monitor which indexes are actually being used:

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS times_used,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Memory Considerations

Estimating Memory Impact

1. Storage footprint: Use pg_relation_size() to see disk usage
2. RAM usage: Depends on shared_buffers and OS page cache
3. Hot indexes: Frequently accessed indexes stay in memory

PostgreSQL Memory Settings

Key configuration parameters to monitor:

-- Check current memory settings
SHOW shared_buffers;      -- Portion of RAM for caching data/indexes
SHOW effective_cache_size; -- Estimate of OS + PostgreSQL cache
SHOW work_mem;            -- Memory for sorting/hashing operations

Monitoring Database Memory

-- Check buffer cache usage
SELECT
  c.relname,
  pg_size_pretty(count(*) * 8192) AS buffered,
  round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer, 1) AS buffer_percent
FROM pg_buffercache b
INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY count(*) DESC
LIMIT 20;

Troubleshooting

Index Not Being Used

If your index isn't being used by the query planner:

1. Check statistics are up to date: ANALYZE table_name;
2. Verify the query matches the index: Use EXPLAIN to see the plan
3. Check selectivity: PostgreSQL may prefer seq scan for low selectivity
4. Increase random_page_cost: If using SSDs, lower this value