PostgreSQL is the database equivalent of a Swiss army knife with a lathe, a welding rig and a small foundry bolted to it. Every feature is real. Every feature is also another thing that can be on fire at 02:00 while you read a wiki page last edited in 2013.

Every feature is a migration

Row-level security, logical replication, partitioning, JSONB indexing, full-text search, foreign data wrappers, a dozen index types and an extension ecosystem that will happily compile C into your database process. You will use three of these. The other nine will appear in a migration written by someone who left the company.

The sales pitch is that you never need another database. The reality is that you need a second database, a connection pooler, a migration tool, a bloat monitor and a person who understands the query planner. That is not a database. That is a platform team with a SQL parser.

The query you wroteparserewriteestimatemaybe an indexfetch pagewhen it feels like itClientParserRewriterPlannerExecutorBuffer CacheDisk
The query you wroteFour hops between you and an answer, three of which exist to apologise for the first.

Diagram: The query you wrote. Client connects to Parser (parse). Parser connects to Rewriter (rewrite). Rewriter connects to Planner (estimate). Planner connects to Executor (maybe an index). Executor connects to Buffer Cache (fetch page). Buffer Cache connects to Disk (when it feels like it).

VACUUM is a second job

MVCC keeps old row versions around so readers do not block writers. This is a good idea that produces dead tuples forever. Autovacuum is the process that cleans them up, using thresholds you did not set, on a schedule you cannot see, competing with the workload it is trying to clean up after.

When it falls behind you get table bloat, index bloat and, in the worst case, transaction ID wraparound, which is the database equivalent of a check-engine light that turns off the engine. The fix is to tune autovacuum. There are dozens of knobs. None of them are called just_work.

The VACUUM lifecyclecreateswakes upreclaimsif it is busylies about costmore workUPDATEDead TupleAutovacuumDiskTable BloatQuery Planner
The VACUUM lifecycleThe database cleans up after itself, eventually, if somebody is watching.

Diagram: The VACUUM lifecycle. UPDATE connects to Dead Tuple (creates). Dead Tuple connects to Autovacuum (wakes up). Autovacuum connects to Disk (reclaims). Disk connects to Table Bloat (if it is busy). Table Bloat connects to Query Planner and fails (lies about cost). Query Planner connects to Autovacuum asynchronously (more work).

One process per connection

PostgreSQL forks a backend process for every connection. Five hundred clients means five hundred processes, each with its own memory and its own copy of the catalog cache. The official answer is a connection pooler, which is a second piece of infrastructure whose only job is to hide the first piece's architecture.

The connection pool workaround500 clients20 backendsone process eachanother copygoodbyeAppPgBouncerPostgresForkCatalog CacheMemory
The connection pool workaroundA second database in front of the database, because the first one forked.

Diagram: The connection pool workaround. App connects to PgBouncer (500 clients). PgBouncer connects to Postgres (20 backends). Postgres connects to Fork (one process each). Fork connects to Catalog Cache (another copy). Catalog Cache connects to Memory and fails (goodbye).

The planner is a rumour engine

The planner chooses a plan from sampled statistics. The statistics are approximate, the estimates drift with data distribution, and the plan flips when you add an index or breathe on a column. EXPLAIN ANALYZE is not a debugging tool. It is a ritual.

None of this makes PostgreSQL bad at being a relational database. It makes it bad at being the only database you are allowed to mention in a architecture review.