Skip to main content

Database Technical Debt: How to Identify and Fix It

Quick answer: Database debt is the gap between the schema and access patterns you have and the ones your workload now needs: missing and unused indexes, wrong types, dead columns, untuned autovacuum. Inventory it from live catalog and statistics data rather than from memory, rank by cost times risk, and repay it on a schedule instead of during an incident.

Database technical debt is the accumulation of suboptimal schema designs, missing indexes, unoptimized queries, stale configurations, and deferred maintenance that degrades performance and increases operational costs over time. It compounds like financial debt: the longer it is ignored, the more expensive it becomes to fix. Common causes include schema decisions that were correct two years ago but no longer fit the workload, ORM defaults that were never tuned, indexes that were never created (or never removed), and configurations that were set during initial setup and never revisited. Whatever debt a database carries tends to stay invisible until it causes an incident.

Types of database technical debt​

Schema debt​

Schema decisions that were reasonable at one point but no longer fit the workload:

  • Wrong data types. Using text for fields that should be integer, varchar(255) everywhere because the ORM defaults to it, timestamp without time zone when you need time zone awareness.
  • Missing constraints. Foreign keys, NOT NULL constraints, and CHECK constraints that were skipped "to move fast" and now allow data integrity issues.
  • Unnecessary normalization. Excessive JOINs for data that is always accessed together.
  • Unnecessary denormalization. Duplicate data that gets out of sync.
  • Missing partitioning. Tables that grew to hundreds of millions of rows without partition strategies.
  • Unused columns. Columns added for features that were abandoned, still consuming storage and cluttering the schema.

Query debt​

Queries that work but waste resources:

  • N+1 patterns. ORM-generated query storms that fire hundreds of queries when a few would suffice.
  • SELECT * everywhere. Loading all columns when only a few are needed.
  • Unoptimized pagination. OFFSET-based pagination that degrades as page numbers increase.
  • Missing eager loading. Lazy loading defaults that were never addressed.
  • Application-side filtering. Fetching large result sets and filtering in code instead of in SQL.
  • Redundant queries. The same data fetched multiple times per request.

Index debt​

Indexes that are missing, unused, or redundant:

  • Missing indexes. Columns used in WHERE, JOIN, and ORDER BY clauses without corresponding indexes.
  • Unused indexes. Indexes created during development that no query uses in production. Each one costs storage, adds work to every INSERT, and adds work to VACUUM. Indexing an extra column also makes HOT updates less likely, which turns cheap in-page updates into full index maintenance. DELETE is not slowed directly, index entries for deleted rows are cleaned up later by VACUUM, not at DELETE time.
  • Redundant indexes. An index on (a) is usually redundant when (a, b) exists, because a multicolumn B-tree serves queries constrained on its leading columns. Confirm before dropping: (a) is not redundant if it is UNIQUE and (a, b) is not, if (a, b) is partial, if the two use different access methods, operator classes, or collations, or if (a) is much narrower and backs hot index-only scans that read only a.
  • Bloated indexes. Indexes that have grown much larger than necessary due to accumulated dead tuples.

Configuration debt​

Settings that do not match the current workload:

  • Default PostgreSQL settings. shared_buffers = 128MB on a server with 64 GB of RAM.
  • Untuned random_page_cost. 4.0 is not a legacy spinning-disk number; the docs describe it as an already-reduced default that assumes most random reads are served from cache. Lower it when your working set genuinely fits in memory and storage latency is low; raise it for storage with a high random-read penalty.
  • Conservative autovacuum. Default settings on tables with millions of writes per day.
  • Undersized work_mem. Causing sorts and hashes to spill to disk.

Operational debt​

Deferred maintenance that accumulates over time:

  • Table bloat. Dead tuples consuming storage and slowing scans.
  • Index bloat. Indexes growing beyond their efficient size.
  • Stale statistics. ANALYZE not running frequently enough on rapidly changing tables.
  • Outdated extensions. Running old versions of pg_stat_statements, PostGIS, or other extensions.
  • Missing monitoring. No visibility into what is happening inside the database.

How to identify database technical debt​

Query-Level assessment​

These queries require the pg_stat_statements extension. It must be listed in shared_preload_libraries and the server restarted, then CREATE EXTENSION pg_stat_statements; run in the target database. Reading other users' query text requires superuser or membership in pg_read_all_stats. Columns are total_exec_time/mean_exec_time on PostgreSQL 13 and later; on 12 and earlier they are total_time/mean_time.

-- Top resource-consuming queries
SELECT query, calls, total_exec_time, mean_exec_time,
       shared_blks_hit + shared_blks_read as total_buffers
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Queries with high buffer reads (inefficient I/O)
SELECT query, calls,
       shared_blks_read as disk_reads,
       shared_blks_hit as cache_hits,
       round(100.0 * shared_blks_read / greatest(shared_blks_read + shared_blks_hit, 1), 2) as miss_pct
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 20;

Index assessment​

-- Unused indexes (wasting write performance and storage)
-- Requires PostgreSQL 16+ for last_idx_scan. On 15 and earlier, drop that one
-- column, the rest of the query is unchanged and still works.
SELECT s.schemaname, s.relname, s.indexrelname, s.idx_scan, s.last_idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND i.indisvalid                 -- skip failed CREATE INDEX CONCURRENTLY leftovers
  AND NOT i.indisunique            -- unique indexes enforce uniqueness (implies NOT indisprimary)
  AND NOT i.indisexclusion         -- backs an EXCLUDE constraint; cannot be dropped
  AND NOT i.indisreplident         -- serves REPLICA IDENTITY USING INDEX
ORDER BY pg_relation_size(s.indexrelid) DESC;

idx_scan is a cumulative counter, not a verdict. It resets on pg_stat_reset() and on an unclean shutdown or restore from base backup; it is per-server, so an index used only by read replicas shows idx_scan = 0 on the primary; and it will not reflect a quarterly report that has not run yet. On PostgreSQL 16 and later, check last_idx_scan alongside idx_scan, a NULL there plus a stats_reset far in the past is much stronger evidence than a zero count.

-- Tables with disproportionate sequential scans (missing indexes)
SELECT relname, seq_scan, idx_scan,
       round(100.0 * seq_scan / greatest(seq_scan + idx_scan, 1), 2) as seq_pct,
       pg_size_pretty(pg_table_size(relid)) as size
FROM pg_stat_user_tables
WHERE pg_table_size(relid) > 10485760  -- Tables > 10 MB
ORDER BY seq_scan DESC
LIMIT 20;

Table health assessment​

-- Tables with most dead tuples (bloat indicator)
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / greatest(n_live_tup, 1), 2) as dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Largest tables by total size (indexes + TOAST included).
-- NOTE: this is raw size, NOT bloat. For actual bloat use the pgstattuple
-- extension: pgstattuple('tablename') is exact but scans the whole relation;
-- pgstattuple_approx() is cheaper. Size alone says nothing about dead space.
SELECT n.nspname, c.relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       pg_size_pretty(pg_table_size(c.oid))          AS table_size,
       pg_size_pretty(pg_indexes_size(c.oid))        AS index_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p') AND n.nspname = 'public'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

Configuration assessment​

-- Key settings to check
SELECT name, setting, unit, boot_val, reset_val
FROM pg_settings
WHERE name IN (
    'shared_buffers', 'effective_cache_size', 'work_mem',
    'maintenance_work_mem', 'random_page_cost', 'effective_io_concurrency',
    'max_connections', 'autovacuum_vacuum_scale_factor',
    'autovacuum_analyze_scale_factor'
);

Prioritizing debt repayment​

Not all database technical debt is equally costly. Prioritize by impact:

Critical (Fix immediately)​

  • Missing indexes on frequently queried large tables
  • N+1 patterns on high-traffic endpoints
  • shared_buffers at default on production

High priority (Fix within weeks)​

  • SELECT * on wide tables with frequent queries
  • random_page_cost left at 4.0 on storage whose behavior clearly differs from the default's assumptions (an all-in-memory working set, or a high random-read penalty)
  • Unused indexes on write-heavy tables
  • Autovacuum configuration on large, active tables
  • Stale statistics on tables with rapid data changes

Medium priority (Fix within quarter)​

  • Schema type improvements
  • OFFSET-based pagination replacement
  • Redundant index cleanup
  • Extension upgrades

Low priority (Track for opportunistic fixes)​

  • Unused columns
  • Minor schema normalization adjustments
  • Cosmetic naming inconsistencies

Building a debt reduction process​

1. Inventory​

Run the assessment queries above. Document what you find. This is your technical debt ledger.

2. Quantify impact​

For each item, estimate:

  • Performance impact. How much database time does this waste?
  • Cost impact. How much extra compute does this require?
  • Risk. Could this cause an outage under load?

3. Prioritize​

Rank by impact and effort. Quick wins (adding a missing index, dropping a confirmed-unused index) should be done immediately.

4. Validate​

Test every fix on a database clone with production-like data. Measure before and after. Database technical debt fixes sometimes introduce new problems.

5. Track​

Keep a running list of known debt items. Allocate a percentage of engineering time to debt reduction. Track progress quarterly.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the catalog and statistics behind each item on this list, which foreign keys lack indexes, which indexes have never been scanned, how far behind autovacuum is, which queries dominate total_exec_time, so the debt is inventoried from real data rather than guessed at.

For any item on that list, the agent can validate the proposed fix before you commit to it, running it as an experiment against a clone and reporting the measured difference. That turns "we think this index will help" into a number, which is usually what debt work needs to get prioritised at all.

It surfaces, explains, and measures; it does not create or drop indexes, change settings, or open pull requests. Get started free →

Frequently asked questions​

How do I estimate the cost of database technical debt? Start with pg_stat_statements to find the top resource-consuming queries. Sum their total_exec_time as a share of all total_exec_time to get the fraction of database time spent on inefficient work. That fraction is an upper bound on what fixing them could save, not a realized saving: on a provisioned instance you bank nothing until you downsize a tier, and on serverless or consumption billing the saving tracks actual CPU-seconds much more directly. State which billing model you are on before converting a percentage to dollars.

How does database technical debt accumulate? Gradually. Schema decisions made under deadline pressure, ORM defaults left in place, indexes created for one-time queries and never removed, configuration set during initial deployment and forgotten. Each item is small. The aggregate impact is large.

Should I fix database debt or application debt first? Database debt often has a higher per-item impact because it affects every query and every user. A single missing index can slow an entire feature, and a single N+1 pattern can dominate a database's workload. Start with the database items that have the highest measurable impact.

Can I automate database debt detection? Yes. Run assessment queries on a schedule, track results over time, and alert on regressions. For continuous detection that surfaces and explains each item as it appears, platforms like DBGorilla provide this without building custom tooling, you still decide and apply the fixes.

How much time should we allocate to database debt reduction? Pick a standing share of database-adjacent engineering time and defend it, rather than fitting debt work into whatever is left over. However, if you have critical debt (missing indexes on high-traffic tables, production N+1 patterns), treat those as urgent fixes, not scheduled debt work.