Scrolls
5 min read
On this page

Why PostgreSQL

The Database That Won

PostgreSQL has become the default answer to "which database should I use?" for good reason. It started as an academic project at UC Berkeley in the 1980s, survived decades of development, and emerged as the most capable open-source relational database available. While MySQL rode the LAMP stack wave and Oracle dominated enterprise budgets, Postgres quietly became the database that could do everything.

The "just use Postgres" philosophy is not laziness. It is the recognition that Postgres handles an absurdly wide range of use cases well enough that reaching for a specialized database is often premature optimization.

ACID Compliance & Reliability

Postgres is fully ACID compliant. Every transaction is atomic, consistent, isolated, and durable. This is not a marketing checkbox. It means:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Either both updates happen or neither does. If the server loses power between those two statements, neither change persists. You do not need application-level compensation logic. The database handles it.

Postgres uses Write-Ahead Logging (WAL) to guarantee durability. Every change hits the WAL before it touches data files. Crash recovery replays the WAL to restore consistent state. This is battle-tested across millions of production deployments.

Extensibility

Postgres was designed from the ground up to be extensible. This is its superpower. You can add:

  • Custom data types
  • Custom operators
  • Custom index types
  • Custom aggregate functions
  • Custom procedural languages
  • Extensions that fundamentally change database behavior
-- PostGIS adds an entire geospatial engine
CREATE EXTENSION postgis;

-- pg_trgm adds trigram-based similarity search
CREATE EXTENSION pg_trgm;

-- pgcrypto adds cryptographic functions
CREATE EXTENSION pgcrypto;

The extension ecosystem is why Postgres keeps absorbing use cases that previously required separate systems.

Open Source & Community

Postgres is released under the PostgreSQL License, a liberal open-source license similar to BSD/MIT. There is no "enterprise edition" with the good features locked behind a paywall. Every feature is available to everyone.

The community is massive and fiercely competent. The core team reviews patches with an intensity that borders on obsessive. Features land in Postgres slowly compared to commercial databases, but they land correctly. The mailing lists are active. The documentation is among the best of any software project, period.

Major cloud providers (AWS RDS/Aurora, Google Cloud SQL/AlloyDB, Azure) all offer managed Postgres. Neon, Supabase, Crunchy Data, and others build entire businesses on Postgres. The ecosystem is deep and self-reinforcing.

What Postgres Handles That Surprises People

JSON & Document Storage

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (event_type, payload)
VALUES ('page_view', '{"url": "/pricing", "user_id": 42, "referrer": "google.com"}');

-- Query nested JSON fields
SELECT payload->>'url' AS url, payload->>'user_id' AS user_id
FROM events
WHERE payload @> '{"referrer": "google.com"}';

JSONB with GIN indexes gives you document-database-level flexibility inside a relational database. You do not need MongoDB for flexible schemas.

Full-Text Search

ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

CREATE INDEX idx_articles_search ON articles USING gin(search_vector);

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & replication') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

For most applications, Postgres full-text search eliminates the need for Elasticsearch. It supports stemming, ranking, phrase matching, and custom dictionaries.

Geospatial Data

With PostGIS, Postgres becomes a world-class geospatial database:

CREATE EXTENSION postgis;

SELECT name, ST_Distance(
    location::geography,
    ST_MakePoint(-73.9857, 40.7484)::geography
) AS distance_meters
FROM restaurants
WHERE ST_DWithin(
    location::geography,
    ST_MakePoint(-73.9857, 40.7484)::geography,
    1000  -- within 1km
)
ORDER BY distance_meters;

Time Series Data

With partitioning and BRIN indexes, Postgres handles time-series workloads efficiently:

CREATE TABLE metrics (
    time timestamptz NOT NULL,
    device_id int NOT NULL,
    value double precision NOT NULL
) PARTITION BY RANGE (time);

CREATE TABLE metrics_2024_01 PARTITION OF metrics
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE INDEX ON metrics USING brin(time);

TimescaleDB extends this further if you need specialized time-series features, but vanilla Postgres gets you surprisingly far.

When Postgres Is the Wrong Choice

It almost never is, but there are genuine cases:

Graph-Heavy Workloads

If your primary access pattern is traversing deep relationship graphs (social networks, knowledge graphs with 10+ hop traversals), a dedicated graph database like Neo4j will outperform Postgres. Recursive CTEs work for shallow graphs, but they hit a wall at depth.

Sub-Millisecond Key-Value Lookups at Extreme Scale

Redis and Memcached exist for a reason. If you need single-digit microsecond reads for simple key-value access patterns, an in-memory store is the right tool. Postgres is fast, but it is a disk-based system with process-per-connection overhead.

Petabyte-Scale Analytics

For truly massive analytical workloads (petabytes of data, complex aggregations across billions of rows), columnar stores like ClickHouse, DuckDB, or BigQuery are purpose-built. Postgres is a row-store. You can use the columnar extension or Citus, but native columnar databases will win at this scale.

Embedded / Edge Deployments

SQLite is unbeatable for embedded use cases: mobile apps, desktop applications, edge devices. Postgres requires a server process. If you need a database inside your application process, SQLite is the answer.

The "Just Use Postgres" Philosophy

The argument is simple: every additional database in your stack adds operational complexity. You need monitoring, backups, failover, connection management, and expertise for each one. Postgres can handle:

  • Relational data (its primary job)
  • Document storage (JSONB)
  • Full-text search (tsvector/tsquery)
  • Geospatial queries (PostGIS)
  • Time-series data (partitioning + BRIN)
  • Key-value caching (unlogged tables)
  • Job queues (SKIP LOCKED)
  • Pub/sub messaging (LISTEN/NOTIFY)
-- Postgres as a job queue
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Start with Postgres. Add specialized databases only when you have measured evidence that Postgres cannot handle the workload. Most teams never reach that point.

Common Pitfalls

  • Assuming Postgres cannot handle your scale. Postgres routinely handles thousands of transactions per second on modest hardware. Benchmark before reaching for something exotic.
  • Adding databases to the stack prematurely. Every new database is another system to operate, monitor, and back up. The operational cost is real.
  • Ignoring extensions. The extension ecosystem is where Postgres absorbs new capabilities. Check if an extension solves your problem before building a workaround.
  • Conflating "PostgreSQL" with "relational." Postgres is a relational database, but its type system and extensibility make it far more flexible than that label implies.
  • Using an old version. Each major release brings significant performance improvements and new features. Run a supported version (Postgres 14+ as of this writing).

Key Takeaways

  • Postgres is ACID compliant, extensible, open source, and backed by one of the strongest communities in software.
  • It handles JSON, full-text search, geospatial, and time-series workloads without additional infrastructure.
  • The "just use Postgres" philosophy is about reducing operational complexity, not about ignoring better tools when they are genuinely needed.
  • Postgres is the wrong choice for deep graph traversals, sub-millisecond key-value lookups, petabyte-scale analytics, and embedded deployments.
  • Start with Postgres. Specialize only when you have evidence that Postgres is the bottleneck.