PostgreSQL
Also known as: Postgres, psql, pg, PostgreSQL database
PostgreSQL (often called Postgres) is an open-source relational database.
It stores data in tables with rows and columns, enforces schemas and relationships, and supports complex SQL queries. It's widely considered the most feature-rich open-source SQL database, with support for JSON, full-text search, geographic data, and much more.
PostgreSQL, explained at three levels
At home
PostgreSQL is a database that stores information in organized tables -- like spreadsheets that can be connected together. You can ask it questions using SQL (Structured Query Language) and it gives you exact answers. It's free and used by many major websites.
At work
Most business applications need a database to store customers, orders, and transactions. PostgreSQL is a popular choice because it's reliable, free, handles complex queries well, and has decades of enterprise use behind it.
At home
PostgreSQL supports SQL standards plus extensions: JSONB (binary JSON for flexible document storage), arrays, full-text search (tsvector/tsquery), geometric types, range types, and window functions. Foreign keys enforce referential integrity. The EXPLAIN ANALYZE command shows query execution plans for optimization.
At work
PostgreSQL's ACID compliance (Atomicity, Consistency, Isolation, Durability) guarantees data integrity. MVCC (Multi-Version Concurrency Control) allows high read concurrency without locking. Common extensions: PostGIS (geographic queries), pg_vector (vector similarity search for AI), TimescaleDB (time-series), and pgcrypto (encryption).
At home
PostgreSQL's query planner chooses execution strategies based on table statistics (collected by ANALYZE). Index types include B-tree (default), Hash, GiST (geometric), GIN (full-text, arrays), and BRIN (range, for time-series). Partial indexes index only rows matching a condition, dramatically reducing index size for sparse data.
At work
Production PostgreSQL uses streaming replication (primary to one or more standbys) for HA and read scaling. Logical replication enables selective table replication and migration between versions. WAL (Write-Ahead Log) provides crash recovery and is the foundation for replication. Managed services (Amazon RDS, Aurora, Supabase, Neon) handle replication and backups; PgBouncer provides connection pooling for high-concurrency applications.
Real-world examples
At home
Aisha learned SQL using a local PostgreSQL database. She stored her book reading list, wrote queries to find all books by genre, and practiced joining tables -- all on her laptop for free.
At work
Jerome's company runs their entire SaaS application on PostgreSQL. They use read replicas for their analytics queries (preventing them from slowing down production writes), PostGIS for location-based features, and daily pg_dump backups to S3.
Common confusions
- PostgreSQL and MySQL are interchangeable — Both are popular open-source SQL databases, but PostgreSQL has richer features (JSON, arrays, advanced indexing, full-text search), stricter SQL standards compliance, and better support for complex queries. MySQL has a larger legacy installed base and is common in WordPress/LAMP stacks. Many teams prefer PostgreSQL for new projects.
- PostgreSQL doesn't scale — PostgreSQL scales vertically very well (large instances with hundreds of connections) and horizontally via read replicas. Services like Citus shard PostgreSQL horizontally. Instagram used PostgreSQL for years at massive scale.
For analysts
PostgreSQL is the database behind many BI tools and data stacks. Business analysts who know SQL can connect directly with DBeaver, psql, or Tableau to run ad-hoc queries, build reports, and explore data without waiting for engineering.
History & origin
PostgreSQL traces its roots to Ingres, a database research project at UC Berkeley in the 1970s. The POSTGRES project (Post-Ingres) started in 1986. The SQL standard query language was added in 1994 and the project was renamed PostgreSQL. It has been developed by a global community of volunteers ever since, with no single corporate owner. PostgreSQL is consistently ranked among the top databases in developer surveys and powers companies from Instagram to Spotify.