PostgreSQL Optimization and Safe Cleanup

1. Check for measurable opportunities

Use query and table statistics, plans, disk headroom, and user impact to choose an optimization. Confirm the observation window and statistics reset time. An index with few scans may still support rare critical work or constraints; review dependencies and application patterns before proposing removal.

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

2. Plan retention and removal before deleting

Obtain data-owner approval, confirm retention/legal-hold policy, test foreign-key cascades, estimate rows and WAL impact, and take or verify the required backup. First count and inspect the exact candidate set. Never use an unbounded delete as an emergency space-reclamation technique.

-- Preview the exact cutoff and batch size with the data owner.
SELECT count(*) AS candidate_rows,
       min(created_at) AS oldest_candidate,
       max(created_at) AS newest_candidate
FROM event_log
WHERE created_at < TIMESTAMPTZ '2025-01-01 00:00:00+00';

-- After approval, remove one bounded batch and inspect returned IDs.
BEGIN;
SET LOCAL lock_timeout = '2s';
WITH batch AS (
  SELECT event_id
  FROM event_log
  WHERE created_at < TIMESTAMPTZ '2025-01-01 00:00:00+00'
  ORDER BY created_at, event_id
  LIMIT 500
  FOR UPDATE SKIP LOCKED
)
DELETE FROM event_log AS target
USING batch
WHERE target.event_id = batch.event_id
RETURNING target.event_id;
-- Review affected rows and dependent effects before COMMIT.

Repeat small batches with pauses and monitoring; retain the approved cutoff in the change record. If the result is unexpected, use ROLLBACK. For large time-partitioned data, evaluate dropping an expired partition instead of row-by-row deletion, but only after verifying its entire date range, dependencies, and backup/retention obligations.

3. Reclaim space and remove objects carefully

Ordinary VACUUM (ANALYZE) makes dead-tuple space reusable inside the relation. It usually does not shrink the file on disk. VACUUM FULL rewrites a table and requires an exclusive lock and extra disk; use a planned maintenance window and tested capacity estimate, not an improvised cleanup.

For an approved unused index, verify constraint dependencies and application coverage, then schedule a concurrent drop:

DROP INDEX CONCURRENTLY public.orders_customer_date_idx;

Concurrent index DDL has restrictions and can fail if another operation conflicts. Confirm the exact schema-qualified object and inspect the result before closing the change.

4. Verify and track the result

1. Compare row counts and business invariants to the pre-change baseline.
2. Check query latency, lock waits, replication lag, WAL, disk, and vacuum health.
3. Confirm the intended object/data is gone and no dependent application feature broke.
4. Close the issue only with evidence, approval, and a follow-up owner for recurrence prevention.