PostgreSQL
What is it?
PostgreSQL is a powerful open-source relational database that stores data in tables and is known for correctness and rich features.
Explain like I'm 5
Why was it created?
It grew from academic research into a free, standards-compliant database so teams could store structured data reliably without expensive licenses.
Where is it used?
- Web and mobile app backends
- Analytics and reporting
- Financial and transactional systems
- Geospatial applications
Why should developers care?
Postgres is one of the most popular databases for new projects. Knowing it well transfers to almost any backend job.
How does it work?
Data lives in tables with defined columns and types. SQL queries read and write it, while transactions guarantee that related changes either all succeed or all fail together.
Real-world example
A banking app records a transfer as one transaction: the debit and credit either both commit or both roll back, so money is never lost.
Common use cases
- Storing structured application data
- Complex queries and joins
- JSON documents alongside relational data
- Geospatial queries with PostGIS
Advantages
- Strong data integrity (ACID)
- Rich feature set and extensions
- Handles both relational and JSON data
- Free and open-source
Disadvantages
- Can require tuning at very high scale
- Heavier than a simple key-value store for trivial needs
When should you use it?
When you need reliable structured data with relationships and strong consistency.
When should you avoid it?
For simple caching or when a specialized store fits better (e.g. pure full-text search at huge scale).
Alternatives
Related terms
Interview questions
Beginner
- What is a primary key?
- What does ACID mean?
Intermediate
- What is an index and when does it help?
- How do transactions work?
Senior
- How would you diagnose a slow query?
- When would you choose JSONB over normalized tables?
Common misconceptions
- "SQL databases can't scale" — Postgres scales well with proper indexing, replication, and partitioning.
- "Postgres can't store JSON" — it has first-class JSON and JSONB types.
Fun facts
- The name reflects its origin as the successor to the Ingres database ('Post-Ingres').
- It is sometimes just called 'Postgres'.
Timeline
- 1986 — POSTGRES project begins at UC Berkeley
- 1996 — Renamed PostgreSQL and adopts SQL
Learning resources
Quick summary
PostgreSQL is a feature-rich, reliable open-source relational database that's a safe default for most applications.
Cheat sheet
- Relational database with tables and SQL
- ACID transactions for data integrity
- Supports JSON and many extensions
- Great general-purpose default