PostgreSQL vs MongoDB: flexible schema vs predictable ops—who hurts less later

The thesis: flexibility is not free

MongoDB’s pitch—document model, flexible schema, easy developer velocity—can be true. PostgreSQL’s pitch—relational model,
strong consistency, mature operational toolchain—can also be true. The trap is assuming the upsides are free.

In production, your “database choice” is mostly an “operational behavior choice.” PostgreSQL tends to reward structure:
explicit schema, constraints, transactions, and boring repeatable ops. MongoDB tends to reward discipline you must bring yourself:
consistency in document shapes, careful indexing, and rigorous operational hygiene around replica sets and write concerns.

If you have a team that likes formal boundaries—migrations, constraints, strict types—Postgres will hurt less later.
If you have a team that can keep document structure consistent without the database enforcing it, and you truly benefit
from nested documents, MongoDB can hurt less later. If you pick MongoDB because you don’t want to think about schema, you will
absolutely think about schema later, but now you’ll do it during incidents.

One quote worth keeping on a sticky note: Hope is not a strategy. — General Gordon R. Sullivan.
Databases turn hope into pager noise.

Interesting facts and context (why the defaults look the way they do)

• PostgreSQL started as POSTGRES at UC Berkeley in the 1980s, aiming for extensibility; that DNA explains custom types, extensions, and a “toolbox” mindset.
• MongoDB emerged in the late 2000s as a developer-friendly document store when web teams were tired of forcing JSON-shaped data into rigid ORM models.
• PostgreSQL’s MVCC model (multi-version concurrency control) is why reads don’t block writes—but also why vacuuming becomes a real operational duty.
• MongoDB’s early popularity rode the “web scale” era when sharding sounded like an inevitability, not a design choice with sharp edges.
• Postgres got JSONB (binary JSON storage and indexing) to meet modern app needs without giving up relational strengths; it shifted many “Mongo by default” decisions.
• MongoDB added multi-document transactions later, which narrowed the gap—but also introduced more performance and tuning nuance in transactional workloads.
• Postgres logical replication matured into a practical tool for migrations, partial replication, and upgrades—useful when “downtime is unacceptable” becomes a sudden requirement.
• MongoDB’s operational story is replica-set-first: elections, read preferences, and write concerns are core concepts, not optional tuning knobs.

These aren’t trivia. They explain why each system “nudges” you into certain architectures—and why resisting those nudges can get expensive.

Data modeling reality: documents, rows, and the lies we tell ourselves

MongoDB’s biggest win: locality and natural aggregates

When a “thing” in your domain is naturally hierarchical—an order with line items, shipping addresses, and state transitions—documents
can map well. One read gets everything. One write updates the whole thing. That’s not just convenience; it’s fewer round trips and
less join logic.

But documents come with a permanent question: embed or reference? Embed for locality; reference for shared entities and growth limits.
If you embed too much, documents bloat and updates rewrite lots of bytes. If you reference too much, you re-invent joins in application code,
usually with no transactional safety across collections unless you pay the transaction cost.

PostgreSQL’s biggest win: enforced invariants

Postgres shines when correctness matters and relationships matter. Foreign keys, unique constraints, check constraints, and triggers aren’t
“enterprise bureaucracy.” They’re how you prevent your data from quietly turning into a haunted attic full of half-finished objects.

Postgres also gives you JSONB, which lets you store flexible attributes without forfeiting the ability to index and query them. This is the
practical middle ground: keep the core relational, and allow “bag of attributes” for the long tail. Most systems have a long tail.

The lie: “We’ll normalize later” / “We’ll clean documents later”

Later is when you have more data, more dependencies, more customer expectations, and less tolerance for downtime. “Later” is when every
cleanup becomes a live migration with real risk. Schema work is like flossing: skipping it is a time-saver until it isn’t.

Joke #1: Your schema is like your tax receipts—ignoring it feels great right up until someone audits you.

Transactions and consistency: what your app is implicitly betting on

This section is where production incidents are born. Not because people don’t know what ACID is, but because they assume their system
behaves like the last database they used.

PostgreSQL: strong defaults, sharp tools

Postgres defaults to strong transactional guarantees. If you update two tables in one transaction, you either commit both or neither.
Constraints fire at the database boundary. You can choose isolation levels; you can also shoot yourself in the foot with
long transactions that bloat MVCC history and stall vacuum. Postgres will let you be clever. Sometimes you should not be clever.

MongoDB: pick your consistency model explicitly

MongoDB can be strongly consistent in practice if you use the right knobs: majority write concern, appropriate read concern, and reasonable
read preferences. It can also be “fast but surprising” if you read from secondaries, allow stale reads, or accept writes that haven’t been
replicated. That’s not a moral failing; it’s a choice. The problem is when it’s an accidental choice.

If your business logic requires “money moved exactly once,” you want a system that makes violating invariants hard.
Postgres does that by default. MongoDB can do it, but you must design for it: idempotency keys, transactional sessions where needed,
and operational settings that match your correctness requirements.

Performance “shapes”: what gets fast, what gets weird

MongoDB: fast reads until your indexes don’t match reality

MongoDB can be extremely fast when queries align with indexes and documents are well-shaped. The pain arrives when teams add new query
patterns weekly, and the indexing strategy becomes reactive. Worse: flexible schema means a query might need to handle multiple shapes
and missing fields, which can lead to selective indexes that don’t behave like you think.

You’ll also meet the “document growth tax.” If documents grow over time—adding arrays, adding nested fields—updates can become heavier.
Storage fragmentation and write amplification start to show up. This is not theoretical; it’s what happens when “profile timeline”
becomes “profile timeline plus activity feed plus settings plus everything.”

PostgreSQL: joins are fine; bad plans are not

Postgres can handle joins at scale, but only if statistics are healthy and queries are sane. When performance tanks, it’s often because:
missing indexes, wrong join order due to stale stats, or parameterized queries that produce generic plans that are awful for some values.
The fix is usually visible in EXPLAIN (ANALYZE, BUFFERS). Postgres is honest if you ask it the right way.

The “shape” difference that bites ops

MongoDB problems often look like “CPU pegged on primary + cache misses + replication lag.” Postgres problems often look like
“I/O saturated + autovacuum behind + one query doing something deeply unfortunate.” Both can be diagnosed quickly,
but the mental models differ.

Indexing: the silent budget line item

Indexes are how you buy performance with storage and write cost. Both databases punish you for over-indexing. Both punish you harder for
under-indexing. The difference is how easy it is to accidentally index yourself into operational pain.

PostgreSQL indexing pitfalls

• Adding indexes “because read is slow” without checking write overhead or bloat.
• Not using partial indexes where appropriate, leading to massive indexes that serve only a fraction of queries.
• Ignoring fillfactor and HOT updates, increasing bloat and vacuum pressure.
• Forgetting that an index is also something that must be vacuumed and maintained.

MongoDB indexing pitfalls

• Compound indexes that don’t match sort + filter order, causing scans.
• Indexing fields that are missing in many documents, creating low-selectivity indexes.
• Letting TTL indexes act like a “free delete job,” then discovering they create write pressure and replication lag during cleanup windows.
• Building large indexes on a busy primary without planning, then acting surprised when latency spikes.

Replication and failover: predictable pain vs surprise pain

PostgreSQL: replication is straightforward; failover is your job

Postgres physical replication is battle-tested. But automatic failover isn’t a single built-in feature; it’s an ecosystem choice
(Patroni, repmgr, Pacemaker, managed services). When people say “Postgres failover is hard,” they usually mean “we didn’t decide,
test, and rehearse how failover works.”

Postgres replication also makes you confront WAL retention, replication slots, and disk growth. Ignore those and you’ll learn how fast
a disk can fill at 03:00.

MongoDB: failover is built-in; semantics are your job

Replica sets elect a primary. That’s great. But your application must handle transient errors, retryable writes, and the reality that
“primary” can move. Also, your read preference policy defines whether users see stale data during certain failure modes.

MongoDB’s operational pain tends to arrive when people treat elections as rare events. They aren’t. Networks flap. Nodes reboot.
Kernel updates happen. If your client behavior is not tested against stepdowns, you don’t have HA; you have optimism.

Backups and restore: your only real SLA

Backups are not the files you copy. Backups are the restores you have tested. Everything else is arts and crafts.

PostgreSQL

The gold standard is base backups plus WAL archiving (point-in-time recovery). Logical dumps are fine for smaller systems or migrations,
but they’re not a time machine. The operational question is: can you restore to a new cluster, verify consistency, and cut over
without improvisation?

MongoDB

You can do snapshot-based backups, filesystem-level snapshots (carefully, with consistency guarantees), or mongodump-style logical backups.
The critical part is understanding whether your backup captures a consistent view across shards and replica sets (if applicable).
Sharded clusters complicate everything. They always do.

Schema changes: migrations vs “just ship it”

Postgres migrations are explicit and therefore manageable

In Postgres, schema changes are a first-class workflow. That means you can review them, stage them, and roll them forward deliberately.
You can still mess it up (locking DDL in peak hours is a classic), but at least the work is visible.

MongoDB schema changes are implicit and therefore sneaky

In MongoDB, schema changes often happen as a side effect of deploying new code. Old documents remain in old shapes until touched or
backfilled. That can be an advantage—gradual migration with no big DDL lock moment. It can also be a long-running inconsistency that
leaks into analytics, search indexes, and customer-facing behavior.

The operational question is simple: do you prefer one big controlled migration, or many small partial migrations with a longer period of mixed reality?
Either can work. Mixed reality tends to become permanent unless you enforce a cleanup deadline.

Practical tasks with commands: what to run, what it means, what to do next

These are not “tutorial commands.” These are the things you run when a graph looks wrong and you need to decide what to do in the next 15 minutes.
Each task includes: command, what the output means, and the decision you make.

PostgreSQL tasks

1) Check active queries and whether you’re blocked

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "select pid, usename, state, wait_event_type, wait_event, now()-query_start as age, left(query,120) as q from pg_stat_activity where state <> 'idle' order by age desc;"
 pid  | usename | state  | wait_event_type |  wait_event   |   age    | q
------+--------+--------+-----------------+---------------+----------+------------------------------------------------------------
 8421 | app    | active |                 |               | 00:02:14 | SELECT ... FROM orders JOIN customers ...
 9110 | app    | active | Lock            | relation      | 00:01:09 | ALTER TABLE orders ADD COLUMN ...
 8788 | app    | active | Lock            | transactionid | 00:00:57 | UPDATE orders SET ...

Meaning: Long-running DDL waiting on locks, plus queries waiting on transaction locks.
Decision: If DDL is blocking business traffic, cancel the DDL (or the blocker), reschedule with safer migration strategy.

2) Find who is blocking whom

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "select blocked.pid as blocked_pid, blocked.query as blocked_query, blocking.pid as blocking_pid, blocking.query as blocking_query from pg_locks blocked_locks join pg_stat_activity blocked on blocked.pid=blocked_locks.pid join pg_locks blocking_locks on blocking_locks.locktype=blocked_locks.locktype and blocking_locks.database is not distinct from blocked_locks.database and blocking_locks.relation is not distinct from blocked_locks.relation and blocking_locks.page is not distinct from blocked_locks.page and blocking_locks.tuple is not distinct from blocked_locks.tuple and blocking_locks.virtualxid is not distinct from blocked_locks.virtualxid and blocking_locks.transactionid is not distinct from blocked_locks.transactionid and blocking_locks.classid is not distinct from blocked_locks.classid and blocking_locks.objid is not distinct from blocked_locks.objid and blocking_locks.objsubid is not distinct from blocked_locks.objsubid and blocking_locks.pid != blocked_locks.pid join pg_stat_activity blocking on blocking.pid=blocking_locks.pid where not blocked_locks.granted;"
 blocked_pid |   blocked_query    | blocking_pid |     blocking_query
------------+---------------------+--------------+-------------------------
 8788       | UPDATE orders SET.. | 6502         | BEGIN; SELECT ...;

Meaning: A transaction holding locks is blocking updates.
Decision: Kill the blocking session if it’s safe, or fix the app pattern (e.g., long transactions).

3) Check replication lag

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "select application_name, state, sync_state, write_lag, flush_lag, replay_lag from pg_stat_replication;"
 application_name |   state   | sync_state | write_lag | flush_lag | replay_lag
------------------+-----------+------------+-----------+-----------+------------
 pg02             | streaming | async      | 00:00:02  | 00:00:03  | 00:00:05

Meaning: Replica is a few seconds behind.
Decision: If lag grows, reduce write spikes, check I/O on replica, or move heavy reads off primary carefully.

4) Identify slow queries by total time

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "select queryid, calls, total_exec_time::int as total_ms, mean_exec_time::int as mean_ms, rows, left(query,120) as q from pg_stat_statements order by total_exec_time desc limit 5;"
 queryid  | calls | total_ms | mean_ms | rows  | q
----------+-------+----------+---------+-------+------------------------------------------------------------
 91233123 | 18000 | 941200   | 52      | 18000 | SELECT * FROM events WHERE user_id=$1 ORDER BY ts DESC LIMIT 50

Meaning: A frequent query dominates total time.
Decision: Add the right index, rewrite query, or cache at application edge—based on plan analysis.

5) Explain a query with buffers to see I/O pain

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "explain (analyze, buffers) select * from events where user_id=42 order by ts desc limit 50;"
 Limit  (cost=0.43..12.77 rows=50 width=128) (actual time=0.212..24.981 rows=50 loops=1)
   Buffers: shared hit=120 read=1800
   ->  Index Scan Backward using events_user_id_ts_idx on events  (cost=0.43..4212.10 rows=17000 width=128) (actual time=0.211..24.964 rows=50 loops=1)
         Index Cond: (user_id = 42)
 Planning Time: 0.188 ms
 Execution Time: 25.041 ms

Meaning: Many buffer reads indicate disk I/O; index exists but still pulling lots of pages.
Decision: Consider covering index, reduce row width, or improve cache (RAM) if working set is larger than memory.

6) Check bloat-ish signals and vacuum health

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "select relname, n_live_tup, n_dead_tup, round(100.0*n_dead_tup/greatest(n_live_tup,1),2) as dead_pct, last_vacuum, last_autovacuum from pg_stat_user_tables order by n_dead_tup desc limit 5;"
 relname | n_live_tup | n_dead_tup | dead_pct |     last_vacuum     |   last_autovacuum
---------+------------+------------+----------+---------------------+---------------------
 events  | 94000000   | 21000000   | 22.34    |                     | 2025-12-30 01:02:11

Meaning: Lots of dead tuples; autovacuum ran, but may be behind relative to churn.
Decision: Tune autovacuum for hot tables, consider partitioning, and investigate long transactions preventing cleanup.

7) Check WAL and replication slot risk

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "select slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained from pg_replication_slots;"
 slot_name | active | retained
-----------+--------+----------
 wal_slot  | f      | 128 GB

Meaning: Inactive slot retaining 128 GB of WAL. Disk-fill risk.
Decision: If the consumer is gone, drop the slot; if not, fix the consumer and increase disk or retention plans.

8) Check checkpoint pressure

cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "select checkpoints_timed, checkpoints_req, round(100.0*checkpoints_req/greatest(checkpoints_timed+checkpoints_req,1),2) as req_pct, buffers_checkpoint, buffers_backend from pg_stat_bgwriter;"
 checkpoints_timed | checkpoints_req | req_pct | buffers_checkpoint | buffers_backend
------------------+-----------------+---------+--------------------+----------------
 120              | 98              | 44.96   | 81234012           | 12999876

Meaning: Many requested checkpoints; backend buffers flushed by writers—latency spikes likely.
Decision: Tune checkpoint settings, evaluate WAL volume, and consider faster storage or batching writes.

MongoDB tasks

9) Verify replica set health and who is primary

cr0x@server:~$ mongosh --host mongo01:27017 --quiet --eval 'rs.status().members.map(m => ({name:m.name,stateStr:m.stateStr,health:m.health,uptime:m.uptime,lag:m.optimeDate}))'
[
  { name: 'mongo01:27017', stateStr: 'PRIMARY', health: 1, uptime: 90233, lag: ISODate('2025-12-30T02:10:01.000Z') },
  { name: 'mongo02:27017', stateStr: 'SECONDARY', health: 1, uptime: 90110, lag: ISODate('2025-12-30T02:09:58.000Z') },
  { name: 'mongo03:27017', stateStr: 'SECONDARY', health: 1, uptime: 89987, lag: ISODate('2025-12-30T02:09:57.000Z') }
]

Meaning: Cluster healthy; secondaries a few seconds behind.
Decision: If health drops or lag grows, stop doing heavy reads on secondaries, check disk and network, and verify write concern settings.

10) Check current operations for lock contention or slow work

cr0x@server:~$ mongosh --host mongo01:27017 --quiet --eval 'db.currentOp({active:true, secs_running: {$gte: 5}}).inprog.map(op => ({opid:op.opid,secs:op.secs_running,ns:op.ns,command:Object.keys(op.command||{}),waitingForLock:op.waitingForLock}))'
[
  { opid: 12345, secs: 22, ns: 'app.events', command: [ 'aggregate' ], waitingForLock: false },
  { opid: 12388, secs: 9, ns: 'app.orders', command: [ 'update' ], waitingForLock: true }
]

Meaning: An update is waiting for a lock; could be a hotspot document or collection-level contention.
Decision: Identify the offending pattern (single-doc counter, unbounded array updates), then redesign to reduce contention.

11) Inspect slow query profile (when enabled) or use explain

cr0x@server:~$ mongosh --host mongo01:27017 --quiet --eval 'db.events.find({userId:42}).sort({ts:-1}).limit(50).explain("executionStats").executionStats'
{
  nReturned: 50,
  totalKeysExamined: 18050,
  totalDocsExamined: 18050,
  executionTimeMillis: 84
}

Meaning: Examined 18k docs to return 50. Index doesn’t match query shape.
Decision: Create a compound index like {userId:1, ts:-1} and re-check stats; avoid adding multiple near-duplicate indexes.

12) Check replication lag more directly

cr0x@server:~$ mongosh --host mongo01:27017 --quiet --eval 'db.adminCommand({replSetGetStatus:1}).members.map(m => ({name:m.name,state:m.stateStr,lagSeconds: (new Date()-m.optimeDate)/1000}))'
[
  { name: 'mongo01:27017', state: 'PRIMARY', lagSeconds: 0 },
  { name: 'mongo02:27017', state: 'SECONDARY', lagSeconds: 3.2 },
  { name: 'mongo03:27017', state: 'SECONDARY', lagSeconds: 4.1 }
]

Meaning: Secondaries are a few seconds behind.
Decision: If lag exceeds your tolerance, reduce write load, increase IOPS, tune journaling/checkpoint behavior (carefully), or scale out/shard with intent.

13) Check WiredTiger cache pressure (common performance limiter)

cr0x@server:~$ mongosh --host mongo01:27017 --quiet --eval 'var s=db.serverStatus(); ({cacheBytesUsed:s.wiredTiger.cache["bytes currently in the cache"], cacheMax:s.wiredTiger.cache["maximum bytes configured"], evicted:s.wiredTiger.cache["pages evicted by application threads"]})'
{
  cacheBytesUsed: 32212254720,
  cacheMax: 34359738368,
  evicted: 1902231
}

Meaning: Cache near max, with high eviction. You’re I/O-bound or memory-starved.
Decision: Add RAM, reduce working set (indexes, projections), or redesign queries to be more selective.

14) Check index sizes per collection (budget reality check)

cr0x@server:~$ mongosh --host mongo01:27017 --quiet --eval 'db.events.stats().indexSizes'
{
  _id_: 2147483648,
  userId_1_ts_-1: 4294967296,
  type_1_ts_-1: 3221225472
}

Meaning: Indexes are multiple GB. Working set might not fit cache.
Decision: Drop unused indexes, consolidate, or move cold queries to analytics storage. Indexes are not free.

15) Check shard distribution (if sharded) to catch hotspots

cr0x@server:~$ mongosh --host mongos01:27017 --quiet --eval 'db.getSiblingDB("config").chunks.aggregate([{$match:{ns:"app.events"}},{$group:{_id:"$shard",chunks:{$sum:1}}}]).toArray()'
[
  { _id: 'shard01', chunks: 412 },
  { _id: 'shard02', chunks: 398 },
  { _id: 'shard03', chunks: 401 }
]

Meaning: Chunk count looks balanced, but balance isn’t the same as load balance.
Decision: If one shard is hot, re-evaluate shard key and query routing; chunk balance alone can be a comforting lie.

16) Validate Postgres disk usage fast (because storage is always guilty until proven innocent)

cr0x@server:~$ df -h /var/lib/postgresql
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p2  1.8T  1.6T  150G  92% /var/lib/postgresql

Meaning: You’re close to full. Postgres hates full disks; everything gets exciting in the worst way.
Decision: Identify growth (WAL, tables, temp files), mitigate immediately, and add alerting before this happens again.

Joke #2: The only thing that scales infinitely is the number of indexes someone will propose during an outage.

Fast diagnosis playbook (bottleneck triage)

When latency spikes, don’t start by debating architecture. Start by locating the bottleneck. The goal is to identify
whether you are CPU-bound, I/O-bound, lock-bound, or network-bound, and whether the problem is one bad query or systemic pressure.

First: confirm the blast radius

• Is it one endpoint or everything?
• Is it writes only, reads only, or both?
• Is the database slow, or the app slow while the DB is fine (connection pool exhaustion, retries, timeouts)?

Second: check saturation signals

• CPU: If DB CPU is pegged and load average follows, look for a few expensive queries, missing indexes, or runaway aggregations.
• I/O: High read latency, high disk utilization, cache eviction spikes (WiredTiger) or buffer reads (Postgres) suggests working set doesn’t fit memory.
• Locks: Many sessions waiting on locks, or long-running transactions, points to contention or bad migration timing.
• Network: Spiky timeouts, replica elections, or cross-AZ chatter can masquerade as database problems.

Third: identify the top offender

• Postgres: Check pg_stat_activity for waits and pg_stat_statements for heavy queries; confirm with EXPLAIN (ANALYZE, BUFFERS).
• MongoDB: Check currentOp, slow queries (profiler/metrics), and explain("executionStats"); verify cache eviction and replication lag.

Fourth: pick the safest mitigation

• Cancel/kill the single worst query or job if it’s clearly pathological.
• Scale up temporarily (CPU/RAM/IOPS) if you need breathing room.
• Reduce load: rate limit, disable heavy endpoints, pause batch jobs.
• Make the smallest index change that fixes the dominant query pattern (and schedule proper follow-up).

Fifth: write down the “why” while it’s fresh

Do not trust future-you. Capture the query, the plan, the wait type, and the mitigation. The difference between a team that improves and a team
that relives the same outage is whether you can recreate the failure mode on purpose.

Three corporate mini-stories from the trenches

1) Incident caused by a wrong assumption: “Secondaries are safe to read from”

A mid-sized SaaS company ran MongoDB with a standard replica set. The app team wanted to reduce load on the primary, so they flipped reads for
certain “non-critical” endpoints to secondaries using a read preference. The endpoints were “just dashboards,” and dashboards were “not production.”
Everyone has said that sentence. Everyone has been wrong.

The incident started as a customer complaint: “My numbers go backwards.” It wasn’t just a UX issue; it triggered automated alerts and caused
support tickets to balloon. The dashboards were feeding other systems: renewal workflows, usage limits, and internal forecasting. A stale read became
a business logic input, which then made its way into actions that were absolutely critical.

The root cause wasn’t that MongoDB “lost data.” It did what it was configured to do: serve reads from secondaries that can lag. During a write-heavy
period, lag grew. The dashboards read old data, then a refresh read newer data, and users saw time travel. No one had written down the acceptable
staleness window, and no one had tested behavior during lag.

The fix wasn’t heroic. They changed the read preference back to primary for those endpoints and introduced explicit caching with a clear TTL and
“data may be up to N minutes old” semantics. They also added monitoring for replication lag with alert thresholds tied to business tolerance.
The result: fewer surprises, and the dashboards stopped causing fights.

2) Optimization that backfired: “Denormalize everything into one document”

Another company used MongoDB for user profiles. A performance review showed too many round trips to assemble the “user view” for the app:
profile info, preferences, subscription state, and a list of recent events. Someone proposed the obvious optimization: embed everything into the
user document and “just update it on writes.” One read, done.

It worked in staging. It even worked in production for a while. Then the app added more “recent events,” then more history, then some per-feature
settings. The user document grew steadily. Updates started to touch larger and larger documents. Latency crept up, then spiked. Replication lag
increased during peak traffic, and elections became more frequent because the primary was under sustained pressure.

The real backfire wasn’t only size. It was write amplification and contention. A single user’s hot document got updated by multiple concurrent
processes (events, billing, feature flags), creating a serial bottleneck. Some updates retried after transient errors, increasing load further.
The system became “fast reads, slow everything else,” which is a nice way of saying “pager”.

They unwound it into a hybrid: core profile fields remained embedded, but fast-changing and unbounded arrays moved into separate collections with
clear indexing. They also added an append-only event stream approach for “recent events” rather than repeatedly rewriting a growing array.
Reads became slightly more complex again, but the system stopped eating itself.

3) Boring but correct practice that saved the day: “Practice restores like fire drills”

A regulated org ran Postgres for transactional data. They were not glamorous. They had change windows, runbooks, and a weekly “restore rehearsal”
to a staging environment that mirrored production enough to be annoying. Engineers complained about the time sink. Managers complained about the cost.
Security complained about everything. Normal.

One day a storage-level issue corrupted a volume on a replica. Failover was clean, but the team discovered that their “known good” backups were
missing a small but critical piece: a recent change in WAL archive retention logic. They didn’t discover this during the incident. They discovered
it because the previous week’s restore rehearsal had already failed and been fixed. They had a tested path, a verified RPO, and a documented cutover.

The incident still hurt, because incidents always hurt. But it stayed within business tolerance. The postmortem wasn’t “we didn’t have backups.”
It was “we had rehearsed restores, so backups were real.” That difference is the line between an outage and a career event.

Common mistakes: symptoms → root cause → fix

1) Symptom: Postgres CPU is fine, but everything is slow and disks are hot

Root cause: Cache miss storm + poor indexing + large table scans, often made worse by stale stats or autovacuum lag.

Fix: Identify top queries with pg_stat_statements, run EXPLAIN (ANALYZE, BUFFERS), add targeted indexes, and tune autovacuum for hot tables.

2) Symptom: Postgres “random” bloat and growing storage, then sudden performance collapse

Root cause: Long-running transactions prevent vacuum cleanup, creating dead tuple accumulation and index bloat.

Fix: Find old transactions in pg_stat_activity, enforce transaction timeouts, redesign batch jobs, and consider partitioning.

3) Symptom: MongoDB primary pegs CPU during peak, replication lag grows, then elections

Root cause: Working set doesn’t fit cache + inefficient queries scanning too much + write-heavy patterns hitting hot documents.

Fix: Use explain("executionStats"), fix compound indexes, reduce document growth, and add RAM/IOPS where justified.

4) Symptom: MongoDB reads are “inconsistent” between requests

Root cause: Read preference points to secondaries and lag exists, or write concern isn’t majority and a rollback occurs after failover.

Fix: Align read/write concerns with correctness needs; avoid secondary reads for anything feeding business logic unless staleness is explicitly acceptable.

5) Symptom: Postgres failover worked, but app errors spike for minutes

Root cause: Client connection handling and DNS/endpoint update behavior not tuned; connection pools don’t recover cleanly.

Fix: Use a stable proxy/endpoint strategy, enforce retry logic that is safe, and test failover with production-like pools.

6) Symptom: MongoDB shard looks “balanced” but one shard is melting

Root cause: Shard key routes hot queries to one shard; chunk count balance doesn’t represent traffic distribution.

Fix: Revisit shard key based on query patterns; validate with per-shard metrics and targeted sampling of query routing.

7) Symptom: Postgres disk fills fast even though tables didn’t grow much

Root cause: WAL retention from replication slots or archiving misconfig, or runaway temp files from sorts/hash joins.

Fix: Check replication slots retained size, archive pipeline, and temp file usage; add disk alerts with real headroom thresholds.

8) Symptom: “Schema flexibility” becomes analytic chaos

Root cause: Multiple document shapes over time with no cleanup; downstream systems can’t rely on fields existing or types matching.

Fix: Establish schema contracts at app boundary, backfill old data on a schedule, and enforce validation rules where possible.

Checklists / step-by-step plan

Choosing Postgres without regretting it

1. Design invariants first: What must never happen? Encode it with constraints (unique, foreign keys, check constraints).
2. Plan for vacuum: Identify hottest tables, tune autovacuum thresholds, and monitor dead tuples.
3. Use JSONB intentionally: Keep core relational; store long-tail attributes in JSONB with targeted GIN indexes where needed.
4. Make migrations boring: Avoid long locks; prefer backfill + swap patterns; test on production-like data size.
5. Define backup/restore: Base backup + WAL archiving; rehearse restores; document RPO/RTO.
6. Decide on HA: Pick a failover approach, test it quarterly, and make app connection behavior compatible.

Choosing MongoDB without getting “flexible-schema debt”

1. Write a schema contract anyway: Define required fields, types, and versioning strategy for documents.
2. Embed with a limit: Avoid unbounded arrays and constantly growing documents; prefer append-only collections for event history.
3. Index from query patterns: For each critical endpoint: filter fields, sort order, and projection; build compound indexes accordingly.
4. Set read/write concerns deliberately: Decide the staleness and durability you can tolerate; don’t leave it to defaults and vibes.
5. Rehearse elections: Test client retry behavior during stepdowns; verify timeouts are realistic and idempotent.
6. Budget for cache: Monitor WiredTiger cache eviction; size RAM for working set, not for hope.

Hybrid approach that often wins: Postgres + JSONB (and discipline)

1. Put transactional truth in relational tables with constraints.
2. Use JSONB for evolving attributes and sparse optional fields.
3. Index only what’s queried; accept that not all JSON fields deserve indexes.
4. Keep analytics separate if query patterns become incompatible with OLTP.

FAQ

1) Should startups default to MongoDB for speed?

Default to what your team can operate. If you don’t have a strong discipline around document shape and indexing, Postgres will be faster in the only way that matters: fewer 2 a.m. surprises.

2) Is Postgres “slow at scale” because joins are expensive?

No. Bad plans are expensive. With correct indexes and stats, Postgres can handle large join workloads. When it fails, it’s usually because the query shape changed and nobody revisited indexing and vacuum.

3) Are MongoDB transactions “as good as Postgres” now?

They can provide strong guarantees, but you pay in complexity and sometimes throughput, especially if you lean on multi-document transactions heavily. If you need transactions everywhere, Postgres is the simpler bet.

4) Can Postgres handle flexible schemas like MongoDB?

Postgres can store and query JSONB effectively, and it’s often enough. But it’s not a free pass: you still need structure, and heavy JSON querying can become an indexing and bloat story. Use JSONB for the long tail, not as your entire database philosophy.

5) When does MongoDB clearly win?

When your domain objects are naturally document-shaped, you mostly access them as whole aggregates, and you can keep document structure consistent. Also when horizontal scaling via sharding is a known requirement and you’re ready to design for it from day one.

6) When does Postgres clearly win?

When you need strict correctness, complex relational queries, constraints as guardrails, and a mature operational toolbox. If you’re building billing, inventory, permissions, or anything that gets lawyers involved, Postgres is the calmer choice.

7) What’s the most common “we chose MongoDB and regret it” reason?

Treating “schema-less” as “structure-less.” The debt shows up as inconsistent documents, unpredictable query performance, and analytics pipelines that can’t trust the data. MongoDB doesn’t cause this; teams do—by not enforcing contracts.

8) What’s the most common “we chose Postgres and regret it” reason?

Underestimating operational work around vacuum, bloat, and migration locking—or running it like a toy until it’s mission-critical. Postgres is stable, but it expects you to do routine maintenance and capacity planning.

9) Should we run both?

Only if you have a clear separation of concerns and the operational maturity for two systems. Running two databases “because each is best at something” is valid. Running two because you couldn’t decide is how you double your on-call load.

10) Managed service or self-hosted?

If uptime matters and your team is small, managed usually wins. Self-hosting can be great when you need deep control and have staff who enjoy kernel updates and page cache conversations.

Next steps you can actually take

If you’re choosing between PostgreSQL and MongoDB for a new system, don’t start with ideology. Start with failure modes and operational habits.
Then decide what you can enforce with technology versus what you expect humans to remember.

1. Write down your invariants: what must never happen (double charges, orphaned records, stale reads powering decisions).
2. List your top 10 queries: shape, filters, sorts, expected latency, and growth expectations.
3. Pick your consistency stance: define acceptable staleness and durability; in MongoDB, encode it in read/write concerns; in Postgres, encode it in transactions and constraints.
4. Decide your backup story: how to restore, how long it takes, who performs it, and how often you rehearse.
5. Run a load test with production-like data size: not to get a benchmark number, but to surface query patterns that become operational landmines.
6. Choose the “hurts less later” option: the one that matches how your team actually behaves on a Tuesday afternoon and a Saturday night incident.

My opinionated default: if you don’t have a strong reason for MongoDB’s document model (and a plan to keep documents consistent), use PostgreSQL.
It’s not perfect, but it’s predictably imperfect—which is what you want when your pager is involved.