PostgreSQL — The Complete Guide
the relational database that holds your agent network state in production
PostgreSQL (Postgres for short) is the most mature, most stable, most 'boringly reliable' open-source relational database — and that is exactly why it is the right pick for almost any project that needs to remember things in production. Unlike SQLite (perfect for development and local tools — a single file on disk), Postgres runs as a separate service that handles dozens of concurrent connections, complex transactions, and large data volumes without breaking a sweat. For me (Elad), Postgres on Hetzner VPS holds the state of all my agents: who talked to whom, what actions were decided, the status of every task, and who paid which invoice. In 2026 Postgres is no longer just a 'database' — with extensions like pgvector (semantic search, an alternative to Qdrant for smaller workloads), TimescaleDB (time series), and PostGIS (maps and geography), it becomes a full platform. When you build a new product my recommendation is simple: start with SQLite, switch to Postgres the moment you have a second user. Even if you eventually move to DynamoDB or Firebase, the years you invest in learning Postgres will pay off in every project you ever touch.
What this guide covers
What is Postgres? Why not MySQL
A serious relational database that has worked well for 30 years
Postgres is a relational database — meaning it stores data in tables that have relations to each other, and you can join them with queries. This has been the standard since the 70s because the idea is simple and powerful: every entity in the system (user, order, product) gets its own table, and instead of duplicating data you simply point from one table to another. Postgres differs from MySQL on a few production-grade points: it supports JSONB (fast indexing on JSON fields), array columns, transactional DDL (even schema changes are atomic — if they fail mid-flight, everything rolls back), and a stricter SQL standard than MySQL's.
Install: docker-compose and managed services
Three ways to spin up Postgres — pick by stage
Installing Postgres can get complicated the old-school way (apt-get + manual config + permissions). The modern path: docker-compose for development, and in production either the same Docker setup or a managed service (Supabase, Neon, RDS) that handles backups, replication and security updates for you. For me, Postgres runs as a container in docker-compose on the same server as the rest of the agents because the data volume is small and the cost is essentially zero.
Schema, types, and migrations
How to design your tables right from the start
A schema is the structure of your DB — what tables exist, what columns each one has, and how they relate. A good schema design up front saves headaches for the whole life of the project. The golden rule: start simple (don't over-normalize early), but use strong types from day one (never store dates as text — always timestamptz; never store money as float — always numeric).
Extensions: pgvector, TimescaleDB, PostGIS
What turns Postgres from a DB into a platform
One of the magical things about Postgres is its extension system: the ability to add whole new capabilities with one command (`CREATE EXTENSION ...`). That turns Postgres from 'a database' into 'a platform' — the same DB that holds your agent state can also do semantic search, store time series, or query by geographic coordinates.
Production: connection pooling, performance, security
What you need to know before going to the real world
The difference between Postgres in development and Postgres in production is mostly the number of concurrent connections, the size of the data, and the exposure to the internet. Each Postgres connection costs ~10 MB of RAM — without pooling, 100 concurrent users = 1 GB just for connections. PgBouncer solves it: it sits between the application and the DB, holding a few dozen real connections that serve thousands of 'logical' clients.
Backup, restore, and disaster recovery
The most important thing — and the one nobody checks until it's too late
A backup you haven't verified by restoring — does not exist. That is the iron rule of the DB world. Postgres offers two main approaches: `pg_dump` (logical backup, easy to move between versions) and PITR (Point-in-Time Recovery, a physical backup that lets you go back to any specific moment in the past). For small projects daily pg_dump is enough; the moment you have real customers — PITR + replica.
