PostgreSQL（社区版）

Each feature = what it is + why it's genuinely good + what happens at the edge.

1. Extension ecosystem: the database that is most like an operating system Why it's real: PostGIS (the de facto GIS standard), pgvector (vector search), full-text search (tsvector/GIN), FDW (connecting external data sources), TimescaleDB (time series), Citus (distributed) — one kernel covering dozens of workloads through extensions, PG's deepest moat relative to MySQL. At the edge: extensions are C code running inside the database backend process; quality varies, and when one breaks it crashes the whole backend process; on a major-version upgrade every extension must have a new-version build or the entire upgrade stalls; running large-scale ANN on pgvector has operational costs (index builds, vacuum coordination) no lower than a dedicated vector database. "Install and use" is not "production ready" — every extension must be vetted separately.
2. Transactional DDL Why it's real: CREATE / ALTER / DROP and other DDL can run inside a transaction block and roll back as a unit — the reliability cornerstone of migration toolchains (Flyway/Liquibase/Alembic), something MySQL can't do. At the edge: DDL takes an ACCESS EXCLUSIVE lock; one uncommitted long transaction makes the DBA's ALTER TABLE queue indefinitely, and conversely DDL blocks the application; CREATE INDEX CONCURRENTLY specifically cannot run inside a transaction block — "transactional DDL" and "online DDL" are two different rule sets, and migration scripts need special handling for that.
3. MVCC reads never block writes + crash-recovery correctness reputation Why it's real: reads never block writes and writes never block reads; WAL + checkpoint crash recovery is battle-tested — the "most data-loss-proof open-source database" reputation comes from here. At the edge: the price of correctness is bloat (see deep dive 1); the reputation holds only if fsync=on and storage honestly honors fsync — cloud disks, virtualized storage, and some RAID cards have their own fsync semantics, and no matter how reliable PG is, it can't save you from lying storage.
4. Type system and SQL expressiveness Why it's real: JSONB, arrays, range types, hstore, ltree, window functions, CTEs (including recursive), EXCLUDE constraints — unmatched SQL expressiveness among open-source databases; complex queries don't need to be moved into application code. At the edge: JSONB updates rewrite the whole row (large documents go through TOAST), with significant write amplification under frequent partial updates; GIN indexes are expensive to maintain — do the math before building a GIN index on a write-heavy table. Expressiveness is developer honey, and also the source of performance debt.
5. License and neutrality: no vendor lock-in Why it's real: the PostgreSQL License is permissive enough for closed-source commercial use; no single vendor can "cut off supply" or change the license — for a CTO this is decade-scale architecture insurance. At the edge: neutrality = nobody is obligated to own your problem. The community doesn't sign SLAs; production support comes from your own team or a purchased third party — "the software is free, the responsibility is not." And: the moment you adopt a managed cloud edition, "no lock-in" is only half true (parameter whitelists, extension whitelists, restricted superuser).

Each = mechanism + behavior at the edge + selection implication + sources.

Deep dive 1 (storage & transactions): VACUUM and long transactions — one reporting query can freeze reclamation across the whole database

• Mechanism: under MVCC, UPDATE/DELETE produce dead tuples reclaimed by VACUUM; reclamation requires dead tuples to be invisible to all active snapshots. Any transaction holding an old snapshot — long queries, idle-in-transaction connections, prepared transactions, logical replication slots' catalog_xmin — raises the xmin horizon for the entire database, leaving autovacuum powerless against dead tuples database-wide.
• At the edge: an 8-hour reporting transaction on the primary doesn't manifest as "slow reports" but as bloat across all tables and indexes and steadily rising disk usage, with autovacuum seemingly running yet reclaiming nothing. The firefighting response is to break the circuit with idle_in_transaction_session_timeout and hunt the offender via pg_stat_activity.backend_xmin, not to add disk.
• Selection implication: reporting workloads always go to replicas; this is a "mechanism tax you can't dodge even with a commercial distribution" — confirm the team has this operational basic skill at selection time.
• Sources: official docs (routine vacuuming chapter) — official claim; community consensus.

Deep dive 2 (performance/operations): the autovacuum threshold formula — the big table that "never gets its turn"

• Mechanism: trigger threshold = autovacuum_vacuum_threshold (default 50) + autovacuum_vacuum_scale_factor (default 0.2) × row count; the autovacuum launcher polls once per minute (naptime) with at most autovacuum_max_workers (default 3) workers per round.
• At the edge: a 10-million-row table needs 2M+ dead tuples to trigger one vacuum; on write-heavy big tables vacuum never catches up with updates and bloat runs out of control. A community-tested case from 2026-09: a ten-million-row reporting table with autovacuum_count stuck at 0, having accumulated 240k dead tuples that "never qualified". The standard fix is per-table tuning (ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01)). Also note: VACUUM only marks index pages reusable and does not shrink indexes; index bloat requires REINDEX (PG 14+ supports online REINDEX CONCURRENTLY).
• Selection implication: per-table tuning for big tables is a go-live checklist item; monitor n_dead_tup / last_autovacuum in pg_stat_user_tables; monitor index bloat separately.
• Sources: official docs (autovacuum parameters chapter) — official claim; community-tested (dev.to index-bloat analysis, 2026-09).

Deep dive 3 (replication & DR): replication slots — the primary's "unlimited promise" on disk

• Mechanism: physical/logical replication slots prevent WAL recycling; max_slot_wal_keep_size defaults to -1 = unlimited (official docs' own words). If a replica goes offline and its slot isn't dropped, pg_wal on the primary grows without bound until the disk fills and the primary refuses writes.
• At the edge: the classic "double kill": replica hardware failure → slot goes idle → nobody alarms → days later the primary disk fills → the primary stops accepting writes. Mitigation is to set max_slot_wal_keep_size (e.g., 10GB), at the cost that an exceeded slot becomes invalidated and the replica must be rebuilt — trading "a rebuildable replica" for "a primary that doesn't die". Logical replication slots add insult to injury: their catalog_xmin simultaneously freezes VACUUM (deep dive 1), a double kill of WAL pile-up + table bloat.
• Selection implication: monitoring idle/lagging slots in pg_replication_slots is mandatory at go-live; whenever logical replication is used for CDC/migration, someone must own the slot lifecycle.
• Sources: official docs (runtime-config-replication, max_slot_wal_keep_size default -1) — official claim; community consensus.

Deep dive 4 (connections & concurrency): the process connection model and connection storms

• Mechanism: every client connection maps to a backend OS process (fork), with real memory and scheduling cost; max_connections is a hard limit, and new connections get FATAL: too many connections once it's hit. work_mem is per-operation; the real memory formula is connections × work_mem × concurrent operations.
• At the edge: the multiplicative effect of many microservice instances × per-instance pools, or serverless bursts, can exhaust connections in the quiet of the night when business is at its lowest — the symptom is "the app can't reach the database" while the database itself isn't loaded. The standard fix is PgBouncer (transaction mode) for pooling, sizing the pool backward from the database's budget.
• Selection implication: PG has no built-in connection pooler (verified absent); any high-concurrency-access POC must list the connection topology (direct / PgBouncer / app pools) as a mandatory test item.
• Sources: official docs (connections and authentication chapter) — official claim; community consensus.

Deep dive 5 (operations & upgrades): major-version upgrades with pg_upgrade

• Mechanism: data file formats are incompatible across major versions; pg_upgrade --link achieves minute-level-downtime upgrades via hard links; PG 18 enhancements: --swap reduces file operations, --jobs parallel checks, cross-version statistics inheritance (no more database-wide ANALYZE storm after upgrade; performance returns to steady state faster).
• At the edge: extensions are the biggest variable — every extension must have a build for the new major version, and one missing build stalls the whole upgrade; --link mode cannot be rolled back (unless you keep the old data directory at double the disk space); the official Docker image changed the PGDATA path in PG 18 (/var/lib/postgresql/18/docker) — swapping the tag and restarting fails to start (community-tested, 2025-10). Zero-downtime upgrades go through logical-replication rolling upgrades, but that's a different operational complexity.
• Selection implication: major-version upgrade drills go into the annual plan; produce an "extension compatibility list" before fixing the upgrade window; container deployments must watch the PGDATA path change.
• Sources: official docs (pg_upgrade) — official claim; community-tested (official Docker image PG 18 PGDATA path change, 2025-10).

Deep dive 6 (storage & performance): WAL, checkpoints, and full_page_writes write amplification

• Mechanism: all modifications are written to WAL first; with full_page_writes=on (default), the first modification of a page after a checkpoint writes the full page to WAL, guarding against torn pages; checkpoints themselves are periodic IO spike sources (max_wal_size / checkpoint_timeout control frequency).
• At the edge: real write amplification = WAL + index maintenance + full-page writes; before bulk loads/ETL, raising max_wal_size and lengthening the checkpoint interval is standard practice; on cloud disks billed per IOPS, WAL is a hidden line item on the bill — "writing 1GB of business data billed as 3GB on the cloud disk" is no exaggeration.
• Selection implication: write-heavy workloads must budget WAL bandwidth; the five settings of synchronous_commit are an explicit "durability for latency" knob that can be temporarily lowered for bulk loads.
• Sources: official docs (WAL configuration chapter) — official claim; community consensus.

Deep dive 7 (consistency/operations): 32-bit transaction ID wraparound — the ultimate write-refusing protection

• Mechanism: transaction IDs are 32-bit and wrap around; autovacuum freezes (freezes) old tuples and advances datfrozenxid; if the 2-billion limit (autovacuum_freeze_max_age) approaches without freezing, the database enters protective mode and refuses writes — PG's oldest and hardest correctness guardrail.
• At the edge: the symptom is "suddenly can't write", but the root cause is freeze lag that began months ago; the emergency response is a database-wide vacuumdb --freeze (extremely slow on large databases); day-to-day, monitoring the age of datfrozenxid catches it early.
• Selection implication: this is the ultimate embodiment of "correctness over availability" — choosing PG means choosing this value set; the monitoring item is mandatory, "later" is not acceptable.
• Sources: official docs (preventing transaction ID wraparound failures) — official claim; community consensus.

The good stuff, also with depth. Each = why it's real + edges and limits.

1. Free, no commercial lock-in
   • Why it's real: the PostgreSQL License is permissive — zero software cost, commercial use and closed-source distribution allowed; no single vendor can change the license or cut off supply.
   • Edges and limits: the software is free, not the operations — headcount dominates TCO, and one senior PG DBA's annual salary far exceeds any commercial license; managed cloud editions are billed by usage and aren't cheap — "saving on license fees" and "saving on total cost" are two different things.
   • Sources: official license statement; community consensus.
2. Ecosystem and talent supply
   • Why it's real: the most complete driver/ORM/toolchain, DBAs are easy to hire, abundant Chinese-language resources, and Stack Overflow-style questions almost always have answers.
   • Edges and limits: what's easy to hire is "someone who can use PG"; people who understand the deep waters are not; managed editions behave differently (parameter whitelists, extension whitelists, restricted superuser) — "it works on RDS" is not "it's the same self-hosted".
   • Sources: community consensus. observed_version: all versions (2026-09).
3. JSONB one-stop shop: relational + document hybrid workloads
   • Why it's real: JSONB + GIN indexes + rich operators/functions — most "semi-structured" scenarios no longer need MongoDB; one SQL stack handles it.
   • Edges and limits: JSONB updates rewrite the whole row; large documents with frequent partial updates suffer significant write amplification; GIN indexes are expensive to maintain; for ultra-high-frequency document updates, dedicated document stores still have the edge. The boundary of "one-stop" is update frequency.
   • Sources: official docs (JSON types chapter); community consensus. observed_version: all versions.
4. Extensions that install and just work
   • Why it's real: PostGIS, pgvector, full-text search — one CREATE EXTENSION command, GIS/vector/search scenarios work out of the box.
   • Edges and limits: see killer feature 1 — uneven extension quality, major-version upgrade coupling, production vetting cost. Between "installed" and "production-ready" lies one version upgrade.
   • Sources: community consensus. observed_version: PG 14–18 (2026-09).
5. Stability reputation: "the open-source database least likely to lose your data"
   • Why it's real: WAL + conservative defaults + a correctness-first engineering culture; crash recovery battle-tested; 5 years of major-version support, quarterly minor releases.
   • Edges and limits: the reputation comes from conservative defaults (shared_buffers 128MB, max_connections 100) — running production untuned is running naked; stable ≠ maintenance-free, and none of deep dives 1–7 disappear because "the reputation is good".
   • Sources: community consensus; version support policy (official: PG 18 supported until 2030-11, PG 14 EOL on 2026-11-12).

• One-line positioning: The default choice among open-source relational databases — a well-rounded machine for general OLTP, whose extension ecosystem is its deepest moat; choosing PG means choosing a value set: correctness first, neutral and lock-in-free, and internalizing the mechanism taxes of VACUUM, the connection model, and upgrades as team capability.
• Good for:
  • First choice for general OLTP, especially new projects that want to "not make mistakes first";
  • Extension scenarios: GIS (PostGIS), vector search (pgvector), full-text search;
  • JSONB hybrid workloads — "one SQL stack for relational + semi-structured";
  • Budget-sensitive teams willing to invest in DBAs; teams with strong multi-cloud portability requirements.
• Not for:
  • Native distributed write scaling (single-node PG is not the answer; look at Citus or distributed databases);
  • Teams with zero DBA investment wanting "install and forget" (either buy a managed edition or don't choose it);
  • Heavy reliance on the Oracle dialect with no appetite for rewrites (migration = rewrite without a commercial compatibility layer);
  • Serverless high-burst direct connections (skipping PgBouncer is burying your own landmine).
• Migration cost:
  • from_mysql: Medium. Dialect differences: quotes/case sensitivity/auto-increment (SERIAL/IDENTITY semantics differences)/strict GROUP BY/no DUAL; mature tooling (pgloader, ora2pg also supports MySQL sources).
  • from_oracle: Medium-high. PL/SQL → plpgsql rewrites; heavy work without a commercial compatibility layer; partitioned tables/advanced packages assessed item by item.
  • from_sqlserver: Medium-high. Mostly T-SQL dialect, identifier, and pagination-syntax differences.
  • from_commercial PG distributions: Low. Same protocol and ecosystem; but parts using commercial proprietary enhancements (Oracle-compatible objects, etc.) carry exit costs to handle.

• Version info: PostgreSQL 18.6 / 17.11 / 16.15 / 15.19 / 14.24 released 2026-08-13 (community announcement); PG 18 GA on 2025-09-25, supported until 2030-11; PG 14 EOL on 2026-11-12 — https://www.postgresql.org/about/news/postgresql-186-1711-1615-1519-1424-and-19-beta-3-released-3365/ (accessed 2026-09-29); cross-validated with AWS RDS release calendar and Neon's version support page
• PG 18 key features: async I/O (io_uring/worker), B-tree skip scan, virtual generated columns (default), uuidv7(), OAuth 2.0 authentication, OLD/NEW in RETURNING, cross-version statistics inheritance, parallel GIN builds, pg_upgrade --swap/--jobs — cross-referenced from multiple community PG 18 release analyses (collected 2026-09-29)
• Replication and WAL parameters: official docs runtime-config-replication (max_slot_wal_keep_size default -1), runtime-config-wal — https://www.postgresql.org/docs/current/runtime-config-replication.html (cited via third-party sources, 2026-09-29)
• VACUUM/bloat/freezing: official docs routine vacuuming chapter (mechanism); community-tested (dev.to index-bloat analysis, 2026-09)
• Upgrades and containers: official pg_upgrade docs; community-tested (official Docker image PG 18 PGDATA path change, 2025-10)
• MVCC/connection model/transaction isolation: official docs — official claim; community consensus
• PostgreSQL 19 Beta 4 (released 2026-09-24): the official announcement lists reverts of SQL/PGQ graph queries, online checksum toggling, and ALTER TABLE ... MERGE PARTITIONS / SPLIT PARTITIONS; the new REPACK command is retained and received crash/behavior fixes in Beta 4 — https://www.postgresql.org/about/news/postgresql-19-beta-4-released-3386/ (accessed 2026-10-02)
• Suggested next review: track PG 19 GA (Beta 4 released 2026-09-24, RC expected early October, GA possibly also in October) new features and upgrade notes; re-verify whether community native TDE has progressed (long-standing request; still verified absent as of this draft)