PostgreSQL (often just “Postgres”) is a relational database. It stores data in tables with rows and columns, and you query it with SQL.

It’s the most popular open-source database for production applications, and for good reason — it’s reliable, feature-rich, and handles everything from a side project to millions of users.

How it works

You define tables, insert data, and query it with SQL:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

SELECT * FROM users WHERE email = 'alice@example.com';

Tables can reference each other (that’s the “relational” part):

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  user_id INTEGER REFERENCES users(id)
);

-- Get all posts with their author name
SELECT posts.title, users.name
FROM posts
JOIN users ON posts.user_id = users.id;

Why developers choose PostgreSQL

• ACID compliant — your data is safe, even during crashes
• JSON support — store and query JSON alongside relational data
• Full-text search — built-in search without Elasticsearch
• Extensions — PostGIS for geospatial, pgvector for AI embeddings
• Scales well — handles millions of rows without breaking a sweat
• Free and open source — no licensing costs, ever

PostgreSQL vs. alternatives

Database	Type	Best for
PostgreSQL	Relational (SQL)	Most applications, complex queries
MySQL	Relational (SQL)	WordPress, simpler use cases
SQLite	Relational (SQL)	Embedded, local-first, small apps
MongoDB	Document (NoSQL)	Flexible schemas, rapid prototyping
Redis	Key-value (in-memory)	Caching, sessions, queues

Where to host PostgreSQL

• Supabase — free tier, managed Postgres with auth and APIs
• Neon — serverless Postgres, generous free tier
• Railway — simple managed Postgres
• AWS RDS — production-grade, more setup
• Local — brew install postgresql or Docker

Using PostgreSQL in your app

Most developers use an ORM or query builder instead of raw SQL:

• Prisma (Node.js/TypeScript) — type-safe ORM
• Drizzle (Node.js/TypeScript) — lightweight, SQL-like
• SQLAlchemy (Python) — full-featured ORM
• Django ORM (Python) — built into Django

For the full command reference, see the PostgreSQL cheat sheet and SQL cheat sheet.