👋 Welcome to The Sev-1 Database.
I break down real-world database outages, PostgreSQL C-engine internals, distributed databases and production triage runbooks.
Free subscribers get our architecture deep dives and database engineering challenges.
Premium members 5$/month unlock full high-severity RCAs, deep DBA diagnostic toolkits, optimization cheatsheets etc.
If you find this breakdown valuable, consider subscribing or sharing it with your team:
Table of Contents
1. Incident Scenario
You run a multi-tenant SaaS application on a distributed PostgreSQL cluster using Citus with schema-based sharding (each customer tenant has their own dedicated schema, holding ~20 tables, custom types, sequences, and distributed functions).
Your business is growing fast: you now have 2,500 active tenants, resulting in over 50,000 distributed relations and dependent objects across the coordinator.
To handle increased traffic, you provision a brand-new worker node (worker_4) and execute the standard activation command on the coordinator:
SQL
SELECT citus_add_node('worker_4', 5432);
-- Or the metadata activation UDF:
-- SELECT start_metadata_sync_to_node('worker_4', 5432);
What happens next:
Coordinator starts churning CPU for several minutes.
Suddenly, the command crashes with:
ERROR: out of memory
DETAIL: Failed on request of size 8192 in memory context "metadata_sync_context".A few minutes later, distributed queries routed to
worker_4start throwing:
Plain text
ERROR: relation "tenant_1402.orders" does not existYour colleague suggests: “Let’s increase work_mem to 1GB, bump maintenance_work_mem”
2. Challenge
Before scrolling down to the breakdown, ask this to yourself:
Why did worker_4 throw relation does not exist even though shard placements were registered in the coordinator catalog?
Why does increasing work_mem completely fail to prevent this OOM?
What is start_metadata_sync_to_node() actually doing under the hood in C?
What are the downstream architectural traps of schema-based sharding as your tenant count continues to scale?
3. Engine Mechanics: Shell Tables vs. Physical Shards
If you dont know what is shell tables vs physical shards
Shell Tables vs. Physical Shards
Shell Table: A metadata-only representation of a distributed table on a worker node. It contains the table definition (schema, columns, indexes, etc.) but does not store actual data. Citus uses shell tables so DDL operations and metadata stay consistent across the cluster.
Physical Shard: Actual data-containing partition of a distributed table stored on a worker node. Queries on distributed tables ultimately read from and write to these physical shards.
In one line:
Shell Table = table structure and metadata.
Physical Shard = actual distributed data storage.
In schema-based sharding, a distributed table has two distinct identities:
[ Coordinator Node ]
├── Logical Shell: tenant_a.orders (in pg_class & pg_dist_partition)
└── Routing Map: Placement points to Worker 1
[ Worker 1 (Existing) ]
├── Logical Shell: tenant_a.orders (Used for parsing, planning, locking)
└── Physical Shard: tenant_a.orders_102034 (Actual data on disk)
[ Worker 4 (New) ]
├── Logical Shell: MISSING (Sync aborted mid-flight!)
└── Physical Shard: (Not yet placed)
Physical Shard Table (
orders_102034): Stores the actual tuples for that tenant. It only exists on the specific worker hosting that shard placement.Logical Shell Table (
tenant_a.orders): Contains column types, constraints, indexes, and schema definitions. It holds zero rows on workers that don’t own the shard, but it is mandatory for PostgreSQL to parse SQL, verify foreign keys, and execute distributed functions.
If a worker is missing the logical shell table, running a query or distributed function against it fails immediately with relation does not exist even if placement rows exist in the coordinator catalog.
4. Inside the C Engine: metadata_sync.c
When you add a metadata-capable worker, the coordinator executes start_metadata_sync_to_node() (src/backend/distributed/metadata/metadata_sync.c).
Here is the call graph:
start_metadata_sync_to_node('worker_4', 5432)
│
├── 1. Creates dedicated MetadataSyncContext
├── 2. Calls ActivateNodeList()
└── 3. Runs SyncNodeMetadataSnapshotToNode()
├── Drops old metadata snapshot on the worker
├── Re-creates distributed catalogs (pg_dist_*)
└── Calls SyncCitusTableMetadata()
└── Dispatches thousands of CREATE SCHEMA,
CREATE TABLE (shell), CREATE SEQUENCE,
and CREATE FUNCTION statements over libpq
5. Why Fail to Prevent the OOM
PostgreSQL memory is strictly compartmentalized:
Executor Memory (
work_mem): Used for sorting, hash joins, and aggregates. When exceeded, the engine spills tuples to temporary files on disk.Internal Memory (
MemoryContext/palloc): Used for query trees, cache-invalidation messages, and metadata DDL command buffers.MemoryContextallocations live in backend heap RAM and can NEVER spill to disk.
When synchronizing 50,000 objects in a single long transaction, the coordinator builds a massive dependency graph in memory, accumulating DDL command strings and cache-invalidation queues inside MetadataSyncContext.
Eventually, a memory allocation exceeds the process ceiling, and PostgreSQL raises out of memory. The size 8192 in the log detail is simply the size of the final allocation request that failed not the total memory consumed.
💡 Enjoying this breakdown? Share it with a teammate or database engineer who manages PostgreSQL at scale.
6. 10-Second Diagnostic SQL Checklist
When managing or scaling a Citus cluster, always verify metadata synchronization before routing traffic:
-- 1. Check metadata sync status across all workers
SELECT nodeid, nodename, nodeport, hasmetadata, metadatasynced, shouldhaveshards
FROM pg_dist_node;
What to look for:
nodename | hasmetadata | metadatasynced
----------+-------------+----------------
worker_1 | t | t
worker_2 | t | t
worker_3 | t | t
worker_4 | t | f <--- ⚠️ SYNC INCOMPLETE OR FAILED
SQL
-- 2. Verify worker-side catalog state
SELECT nodename, success, result
FROM run_command_on_workers('SELECT count(*) FROM pg_dist_partition');
(If result = 0 with success = true, the worker is reachable, but its distributed metadata is empty).
7. 4 Hidden Architectural Failures of Schema-Based Sharding
Metadata synchronization OOM is only the first failure mode. When you scale PostgreSQL past thousands of schemas, four subtle architectural bottlenecks emerge:
Per-Connection
relcacheMemory Bloat:
Each backend process caches metadata for touched tables in its private relcache. With 50,000 tables, if connections touch many schemas, backend memory balloons to 200MB–500MB per connection just storing catalog cache. 200 connections can silently consume 60GB of RAM on catalog cache alone.
DDL Lock Storms:
Adding a single column in schema-based sharding requires executing 2,500 separate ALTER TABLE statements, acquiring 2,500 ACCESS EXCLUSIVE locks. If one tenant has a running query, the entire migration blocks and stalls incoming application reads.
Autovacuum Scheduler Starvation:
Autovacuum workers spend immense time scanning 50,000 catalog entries, causing vacuum lag on the active tables that actually need cleanup.
Cross-Tenant Analytics Wall:
Cross-customer reporting requires dynamically unioning 2,500 distinct tables (SELECT * FROM t1.orders UNION ALL SELECT * FROM t2.orders...), blowing past query planning memory limits.
8. Architectural Decision Framework: Row-Based vs. Schema-Based
Before choosing schema-based sharding, use this decision framework:
Core Rule of Thumb:
If tenants share the same schema structure and you plan to scale beyond 1,000 tenants: Use Row-Based Sharding (
tenant_iddistribution column).If you must use Schema-Based Sharding (e.g. strict regulatory schema isolation): Set a hard limit of ~1,000 tenants per cluster, then shard across separate database clusters.
9. Upstream Evolution & Operational Guardrails
Upstream Stabilization (PR #6728 / Citus 11.3+):
Citus team introduced major memory stabilization by splitting metadata synchronization into nontransactional phases and aggressively resetting child memory contexts during batch execution. Ensure your cluster runs on modern builds.
Schema & Object Hygiene:
In multi-tenant architectures, obsolete tenant schemas, unused views, and abandoned index definitions aren’t just wasted disk they multiply the metadata synchronization dependency graph. Regularly drop decommissioned tenant objects.
Safe Worker Recovery:
If a worker’s metadata sync is corrupted or failed, stop propagation before retrying:
SQL
SELECT stop_metadata_sync_to_node('worker_4', 5432, true);
10. Level Up on The Sev-1 Database
This challenge illustrates the core philosophy of The Sev-1 Database: understanding the exact C-source call paths, memory structures, and catalog invariants so you can diagnose production outages with precision.
On thesev1database.com, we’ve built:
71 deep engine lessons with verified C source citations.
105 production runbooks with literal psql outputs.
102 lab-verified SQLSTATE error references.
👉 Start exploring with our 7-Day Free Pro Trial
Did you get the answers right? How does your team manage schema scale across distributed clusters? Let me know in the comments below!