PostgreSQL for Everything

Original Article ↗ Hacker News Discussion ↗

Advocates of PostgreSQL argue it can replace a wide range of infrastructure — from message queues and search engines to time-series and vector databases — letting small teams ship faster by running “just Postgres.” Others counter that while this works well at modest scale, specialized tools like ClickHouse, Kafka, Elasticsearch, or dedicated queues become necessary as data volumes, performance needs, and operational complexity grow. A recurring theme is to start with PostgreSQL (or even SQLite) as a simple default, then introduce additional systems only when concrete scaling or feature limits are actually reached.

Historical context: MySQL vs PostgreSQL

  • Early 2000s: MySQL was default in LAMP/PHP hosting, seen as faster partly because MyISAM skipped transactions and durability.
  • Over time, Postgres added features and stability, while MySQL’s design issues, poor defaults, and later Oracle acquisition pushed some users away.
  • MySQL had operational advantages early (easy installers, master–master replication), but Postgres became the “proper RDBMS” default from mid‑2000s onward.

SQLite vs PostgreSQL

  • Several commenters happily use SQLite “for everything” at small scale; NVMe + replication tools like Litestream are considered enough for many apps.
  • Others warn about SQLite’s weaker type system, lack of native DATE/TIME/TIMESTAMP, and divergent behavior vs Postgres, arguing dev environments should match production.
  • Some say SQLite is ideal for single‑server or embedded scenarios; Postgres is better once you need multiple writers, strict types, access control, and replication.

“Postgres for everything” – support

  • Many use Postgres as default: OLTP DB, JSON store, full‑text search, vector store, queue, message bus, logging, even basic analytics.
  • Extensions and features highlighted: JSONB, trigram indexes, PostGIS, property graphs (PG 19), pgvector/pgvectorscale, Timescale continuous aggregates, GPU ML (PostgresML), LISTEN/NOTIFY.
  • Argument: most apps are small/medium; one well‑understood, managed system beats a complex stack (Kafka, Elasticsearch, Redis, etc.) until scale forces change.

“Postgres for everything” – skepticism

  • Critics say articles oversell Postgres vs specialized tools:
    • Search: does not match Elasticsearch’s power; new Postgres‑based search projects try to close the gap.
    • OLAP/analytics: ClickHouse, BigQuery, columnar engines on object storage are vastly better for large, ad‑hoc analytics.
    • Time series and vectors: Timescale/pgvector can conflict with other workloads and degrade caches/CPU at high volumes.
    • Blobs: large BYTEA storage hurts buffers, backups, and WAL; object storage is often preferable.
  • Operational concerns: vacuum tuning, process‑per‑connection model, need for connection pooling, HA/sharding (Patroni, custom sharding) seen as non‑trivial.

Design philosophy and scale

  • Widely shared heuristic: “Start with Postgres (or what you know) until you discover why you can’t; then add specialized tools.”
  • Counterpoint: designing everything around a single Postgres instance can create a central bottleneck and complex migrations later; think about future scale and uptime requirements.
  • Overall consensus: Postgres is an excellent, often best default for most use cases, but not literally “for everything,” especially at very large scale or for highly specialized workloads.