PostgreSQL scaling starts by removing unnecessary work, then right-sizing compute, memory, storage, and connections. Declarative partitioning divides one logical table for pruning and lifecycle operations; replicas serve recovery or read workloads with lag; sharding distributes ownership at major complexity cost. Each technique addresses a specific bottleneck and changes correctness and operations.
Scale is bottleneck relocation. More hardware does not fix a lock convoy; a replica does not accelerate writes; partitioning does not make every query faster; sharding does not simplify anything.
Good candidates are very large tables with common predicates aligned to a stable partition key and operational needs such as dropping old trace partitions. Range partitioning by created_at can help retention, but queries must include time bounds for pruning. Tenant partitioning can create skew or too many partitions.
Partitioned uniqueness and foreign-key behavior have version-specific constraints; design against current documentation. Automate future partition creation and detect missing/default-partition growth.
Physical streaming replicas copy WAL and can lag. A read after write may not appear immediately. Define which endpoints tolerate staleness, measure replay delay, and route consistency-sensitive reads to the primary or coordinate by WAL position where architecture supports it.
Replicas are not backups: corruption, accidental deletion, and bad transactions replicate. HA is not disaster recovery.
Evaluation traces may partition naturally by time; knowledge chunks may not. Retrieval from a lagging replica can cite stale or deleted policy. Write-heavy embedding rebuilds need capacity/WAL planning. Dedicated vector/search infrastructure is justified only after measured PostgreSQL limits or independent scaling requirements—not by fashion.
Create a small time-partitioned trace table and use EXPLAIN to prove pruning with and without time predicates. Document a read-your-write test for a replica and the maximum tolerated lag for each route.
Choose whether SignalDesk audit events, messages, and knowledge chunks should be partitioned at current and 100× scale.
Acceptance criterion: each decision cites query predicates, lifecycle, size, skew, constraints, and operational cost—not row count alone.