PostgreSQL is a free, open-source relational database that is, by virtually every technical measure, superb. It supports ACID transactions, full-text search, JSON documents, geospatial queries, pub/sub notifications, custom types, window functions, CTEs, lateral joins, and — with extensions — approximate nearest neighbors, cron scheduling, columnar storage, and graph traversal. It is the database that can do everything.
And that, as the Lizard has observed from its position on a warm laptop lid, is the problem.
“Postgres? MySQL? SQLite? But those aren’t CUSTOM. They don’t have our specific—”
“SQLite has been in production since 2000. It runs on literally every phone on Earth.”
— The Caffeinated Squirrel and riclib, The Databases We Didn’t Build
The Genuinely Good Part
Let us be clear, because the Lizard insists on honesty even when satire beckons: PostgreSQL is genuinely excellent. It is not Redis (a cache masquerading as a religion). It is not MongoDB (a JSON drawer that once told people they didn’t need transactions). It is not Oracle (which charges per CPU core for the privilege of doing what Postgres does for free, and then sends auditors to make sure you’re paying).
PostgreSQL is the real thing. It has been developed continuously since 1996. Its ancestor, POSTGRES, was a research project at UC Berkeley in 1986 — which means its lineage predates the World Wide Web. It is free. It is open source. It is maintained by people who genuinely care about correctness and who will argue about WAL segment sizes with the intensity of medieval theologians debating transubstantiation.
If you need a database that does everything, PostgreSQL is the database that does everything. The question is whether you need a database that does everything.
“Just Use Postgres”
There is a famous article — practically a commandment at this point — that says: just use Postgres. Need a message queue? Postgres can do that (LISTEN/NOTIFY, plus SKIP LOCKED for job queues). Need full-text search? Postgres can do that (tsvector, tsquery, ranked results). Need caching? Postgres can do that (materialized views, UNLOGGED tables). Need cron jobs? Postgres can do that (pg_cron). Need a document store? Postgres can do that (jsonb, with indexes). Need pub/sub? Already mentioned. Need geospatial? PostGIS. Need a graph database? ltree and recursive CTEs.
The philosophy is seductive. One database. One operational surface. One thing to monitor, back up, and understand. No Redis. No Elasticsearch. No RabbitMQ. No separate cron daemon. Just Postgres.
The Caffeinated Squirrel finds this philosophy intoxicating. “It can do EVERYTHING!” the Squirrel announces, arriving with a diagram showing Postgres at the center of a solar system where every planet is a use case and every orbit is a CREATE EXTENSION.
The Lizard blinks.
“Can do everything,” the Lizard observes, “is not the same as should do everything.”
The Server Problem
PostgreSQL requires a server. Not in the abstract, architectural sense — in the literal, physical, “something must be running and listening on port 5432” sense.
This means:
- A process that must be started, monitored, and restarted
- A
pg_hba.confthat must be configured (and which will, at some point, lock you out of your own database at 3 AM) - Connection pooling (because PostgreSQL creates a process per connection and will eventually run out of processes, which is when you discover PgBouncer)
- Backups that require
pg_dumpor WAL archiving or a third-party tool - Upgrades that sometimes require
pg_upgradeand sometimes require a full dump-and-restore - Authentication, SSL certificates, and the quiet dread of seeing
FATAL: password authentication failed for user "postgres"when you definitely typed the right password
SQLite requires a file.
Not a server. Not a process. Not a configuration file for authentication. Not a connection pool. Not a separate backup strategy. A file. You copy it with cp. You back it up with scp. You deploy it by including it in your binary’s data directory. You open it by calling sql.Open("sqlite3", "data.db") and it works, immediately, without negotiation.
“The best database is one file.”
— riclib, The Databases We Didn’t Build
The Team Threshold
PostgreSQL is the database you use when you have a team. SQLite is the database you use when you are the team.
This is not a technical limitation. SQLite handles terabytes. SQLite serves thousands of concurrent readers. SQLite, in WAL mode, handles concurrent reads and writes with the casual competence of a technology that has been deployed on every smartphone on Earth since 2010.
The distinction is operational. PostgreSQL requires someone to be on call for the database. SQLite requires nothing, because there is nothing to be on call for. The database is a file. If the file exists, the database works. If the application crashes, the database is still there. If the server reboots, the database is still there. If you forget about it for six months and come back, the database is still there, patiently waiting, needing nothing, judging nothing.
The Solo Developer does not have a DBA. The Solo Developer does not have an ops team. The Solo Developer has scp, systemctl, and /health. In this context, PostgreSQL is not better than SQLite — it is more. More to run. More to monitor. More to configure. More to worry about at 2 AM.
“One person cannot maintain forty-seven microservices. One person can maintain one binary, one database, and one deployment script.”
— Solo Developer
The Blazer Years Incident
During Interlude — The Blazer Years, PostgreSQL appeared in its natural enterprise habitat: as “the stuff MongoDB can’t do,” sitting alongside Redis, Kafka, a data lake, two API gateways, and forty-seven microservices serving twelve hundred users.
The PostgreSQL query time for a balance check was twelve milliseconds. This was perfectly adequate. Nobody cared. Redis was added on top of it to save 11.7 of those milliseconds on 7% of requests. The service mesh added four hundred milliseconds of latency. The net result was a system that took 2.3 seconds to do what the old monolith did in 47 milliseconds.
PostgreSQL was not the problem in this story. PostgreSQL was the twelve-millisecond heartbeat buried under six layers of unnecessary infrastructure. The Enterprise Architect called it “well-optimized PostgreSQL,” which it was. The tragedy was not that PostgreSQL was slow — the tragedy was that PostgreSQL was fast and nobody noticed because they were too busy optimizing the layers on top of it.
“Well-optimized PostgreSQL. About 12 milliseconds for a balance check.”
— Enterprise Architect, shortly before the consultant blinked, Interlude — The Blazer Years
The Oracle Contrast
If PostgreSQL is the database that does everything for free, Oracle is the database that does the same things for the price of a small apartment.
Oracle charges per CPU core. Oracle sends license auditors. Oracle’s licensing terms are complex enough to require their own database to track (presumably also licensed per CPU core). Oracle’s sales team will visit your office in suits that cost more than your server and explain why you need Oracle Enterprise Edition with the Partitioning Option and the Advanced Security Option and the Diagnostic Pack and the Tuning Pack, each priced separately, each essential, each a recurring annual fee that arrives with the inevitability of winter.
PostgreSQL does all of this for zero dollars. The same ACID transactions. The same replication. The same partitioning. The same full-text search. For free. Maintained by a community of people who do it because they believe databases should be reliable, not because they believe databases should be profitable.
The Squirrel, in a rare moment of alignment with the Lizard, agrees: nobody should pay Oracle. The disagreement is about what to do with the savings. The Squirrel wants to spend them on Redis. The Lizard wants to spend them on nothing.
When PostgreSQL Is Right
PostgreSQL is right when:
- You have multiple applications that need to share one source of truth
- You have a team (more than one human) that needs concurrent schema management
- You need row-level security, roles, and fine-grained access control
- You are genuinely serving millions of requests and need connection pooling, replication, and read replicas
- Your data model requires transactions across multiple tables with foreign key constraints enforced at the database level
- You have a DBA, or can become one, and enjoy configuring
pg_hba.conf
PostgreSQL is more than you need when:
- You are one person with one binary and one laptop
- Your data fits in one file and your queries return in milliseconds
- Your deployment is
scpand restart - You do not have an ops team and do not want to become one
- The words “connection pool” make you tired
- You are the Solo Developer, and the thing you are optimizing for is not having to think about the database
The Lizard’s Verdict
The Lizard respects PostgreSQL. The Lizard does not use PostgreSQL.
The Lizard uses SQLite — because SQLite is one file, compiles with the binary, requires no process, needs no configuration, survives reboots, ignores network partitions (there is no network), and does not send authentication failures at 3 AM because there is no authentication, because there is no server, because there is no port, because there is only a file, and the file is the truth.
“THE BEST DATABASE IS SOMEONE ELSE’S.”
“THE BEST ORCHESTRATION IS A FOR LOOP.”
— The Lizard, The Databases We Didn’t Build
PostgreSQL is someone else’s database, and it is excellent. SQLite is also someone else’s database, and it is a file. For the Solo Developer, at 2 AM, deploying with scp, the file wins. Not because it is better. Because it is less. And less, as the Lizard learned in 488 bytes on an Amiga 500, is the point.
