Why too many tables in one database hurt PostgreSQL

Databases with tens or hundreds of thousands of tables usually get there by one of three routes:

• Per-tenant schemas: every customer gets its own set of tables (tenant_1234.orders, tenant_1234.invoices, ...) because that felt like clean isolation;
• Partition maximalism: daily partitions kept forever across dozens of tables, each partition carrying indexes, constraints, and statistics;
• ORM and tool churn: frameworks that create a table per entity revision, per report, or per import job and never drop them.

All three produce the same failure shape, and it arrives gradually: nothing breaks at 5,000 tables, something is vaguely slow at 50,000, and at 500,000 you are debugging memory pressure and multi-minute pg_dump runs.

Why too many tables hurt

Relcache: metadata cached per backend

Every backend process keeps its own relation cache — the relcache — holding parsed metadata for each relation it has touched: tuple descriptor, indexes, rules, triggers, statistics pointers. This is a per-process cache in backend memory, populated lazily on first access and kept for the life of the backend (see src/backend/utils/cache/relcache.c in the PostgreSQL source). Two consequences follow:

• the memory cost of a touched table is paid once per backend, so a pool of 200 connections that each touch 20,000 tables carries the metadata 200 times;
• nothing reclaims it while the connection lives, so long-lived pooler connections in session mode grow monotonically — in a container this is exactly the anonymous-memory growth that ends in an OOM kill.

PostgreSQL 14+ exposes this through pg_backend_memory_contexts: metadata accumulates under CacheMemoryContext and its children, and you can watch it grow as a backend touches more relations.

System catalogs grow, and everything that walks them slows down

Each table is not one catalog row. A table with five columns, a primary key, and one index adds rows to pg_class, pg_attribute, pg_index, pg_constraint, pg_depend, pg_description, pg_statistic, and more — a dozen or so rows per table at minimum, per the system catalogs layout. Consequences at scale:

• metadata introspection slows down: \d in psql, ORM schema reflection at application startup, and GUI tools that enumerate the catalog;
• pg_dump walks and locks every relation, so backup duration grows with table count even when data volume is flat;
• catalogs themselves bloat and need vacuum like any other table — autovacuum on a huge pg_class becomes a real workload.

How many is too many

These are field experience values, not documented thresholds — the actual breaking point depends on connection count, columns per table, and how many relations each backend touches:

• a few thousand tables: fine on any reasonable setup;
• tens of thousands: noticeable — slower connection warm-up, slower dumps, relcache memory visible in monitoring;
• hundreds of thousands: incident territory — backends holding gigabytes of relcache, OOM risk in containers, pg_dump measured in hours.

The multiplier that matters is tables touched per backend × concurrent backends, not the raw count. 50,000 tables that each request touches 50 of are far cheaper than 50,000 tables all touched by every request.

Diagnosing the problem

Count relations by kind, excluding system schemas:

SELECT c.relkind, count(*)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY c.relkind
ORDER BY count(*) DESC;

relkind: r table, p partitioned table, i/I index, S sequence, t TOAST table, v/m views. If the count is dominated by indexes, the tables underneath are still the root cause — each one drags its indexes along.

Find where the tables concentrate:

SELECT n.nspname,
       count(*) FILTER (WHERE c.relkind = 'r') AS tables,
       count(*) FILTER (WHERE c.relkind = 'i') AS indexes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY n.nspname
ORDER BY tables DESC
LIMIT 20;

Thousands of schemas with identical table names is the per-tenant signature.

Measure catalog size and per-backend metadata memory:

-- Largest system catalogs
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total
FROM pg_class
WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

-- Metadata memory of the current backend (PG 14+;
-- other backends require superuser or pg_read_all_stats)
SELECT name, pg_size_pretty(sum(used_bytes)) AS used
FROM pg_backend_memory_contexts
GROUP BY name
ORDER BY sum(used_bytes) DESC
LIMIT 15;

A CacheMemoryContext that grows with the number of distinct relations a backend has touched — and never shrinks — confirms the relcache cost.

Remediation paths

Merge per-tenant tables into shared tables with Row-Level Security. One orders table with a tenant_id column and an RLS policy preserves the isolation guarantee while collapsing the table count by orders of magnitude; see Row Security Policies and our Security page. The migration is mechanical (union the tenant tables in, add the column, backfill) but test RLS plan behavior on your query shapes — policies are applied per query and interact with indexes on tenant_id.

Cap partition counts deliberately. Choose partition granularity from retention and query patterns, not from calendar habit: monthly instead of daily, and drop or detach old partitions instead of keeping history online forever. Details below.

Split by database. If tenants genuinely need hard separation, a database per tenant bounds the blast radius better than a schema per tenant — catalogs and relcache are per database, and connections are too. The trade-off is connection management and cross-tenant reporting.

Drop what the ORM forgot. Audit for tables with zero scans and zero tuples over a pg_stat_user_tables window and remove them; schema hygiene is cheaper than any of the above.

The boundary with partitioning

Partitioning is not an escape hatch from this problem — partitions are tables. Every partition has its own pg_class entry, its own relcache footprint per backend that touches it, and usually its own indexes. Declarative partitioning merely automates the routing; the metadata cost is additive.

The partitioning documentation notes that the planner handles hierarchies of up to a few thousand partitions reasonably well when pruning eliminates most of them at plan time; planning time and memory grow when many partitions survive pruning. So the practical limits compose: a table partitioned into 5,000 children, queried without a partition-key filter, from a 200-connection pool, combines worst-case planning cost with worst-case relcache multiplication. Keep partition counts in the hundreds where possible, always query with the partition key, and let retention (DROP PARTITION / DETACH) — not storage capacity — decide how many partitions stay online.

For the data-modeling side of choosing between shared tables, schemas, and partitions, see Data modeling.

Audit my PostgreSQL 18 database for excessive table count.

Context:
- ~<N> tables across <M> schemas, <K> concurrent connections via <PgBouncer/direct>
- Suspected pattern: <per-tenant schemas / heavy partitioning / ORM churn>
- Container memory limit: <value>

1. Give the diagnostic queries: relation counts by relkind and schema, catalog sizes, pg_backend_memory_contexts observation of CacheMemoryContext.
2. Based on a per-tenant schema pattern, draft a migration plan to shared tables with tenant_id + Row-Level Security, including rollback.
3. If partitioning is the driver, propose a partition-count cap and retention scheme from my query patterns: <describe>.
4. List what to monitor during and after the migration to prove relcache memory and metadata latency actually improved.

Verify the plan on a restored copy first — catalog surgery and RLS rollout are exactly the changes that deserve a rehearsal.

Related pages

• Row-Level Security setup → Security
• Choosing table layouts → Data modeling
• Connection and memory budgeting → Server configuration
• When metadata memory meets the cgroup limit → PostgreSQL memory management and OOM in containers