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.

For a detailed comparison of database options, check Postgres vs SQLite vs MySQL.

FAQ

When should I use PostgreSQL over MongoDB?

Use PostgreSQL when your data has clear relationships (users have posts, posts have comments), you need complex queries with JOINs, or you need strong data integrity guarantees. MongoDB is better when your schema changes frequently or you’re storing deeply nested document-like data.

Is PostgreSQL hard to learn?

If you know basic SQL (SELECT, INSERT, UPDATE, DELETE), you already know how to use PostgreSQL. The fundamentals are the same as any relational database. Advanced features like window functions, CTEs, and extensions take more time but aren’t needed to get started.

Can PostgreSQL handle millions of rows?

Yes. PostgreSQL handles tables with hundreds of millions of rows routinely when properly indexed. With features like table partitioning, connection pooling, and read replicas, it scales to very large workloads without needing to switch to a different database.