PostgreSQL

What is it?

PostgreSQL (usually just "Postgres") is a free, open-source relational database with over 30 years of development behind it. It stores data in tables and speaks SQL, like MySQL and SQLite - but it's known for its rigor and feature depth: strict data integrity, powerful indexing, and first-class JSON support, so it handles semi-structured data nearly as well as a document database.

When developers and companies need a database that will still be the right choice at a million users, Postgres is the default answer. Every major cloud offers it as a managed service.

When to use it

Choose Postgres for apps you expect to grow - it scales from a side project to serious production traffic without a migration, and its JSON columns give you flexibility when your schema is still evolving. It's the standard partner to Node.js in modern stacks. For tiny projects and learning, SQLite gets you going with zero setup; on shared hosting, MySQL is usually what's pre-installed.

Core concepts

• Client/server model - Postgres runs as a server process; your app connects to it over a connection string. This is what lets many app servers share one database safely.
• Schemas and types - columns have strict types (TEXT, INTEGER, TIMESTAMPTZ, JSONB…), and Postgres enforces them - bad data gets rejected, not silently stored.
• Constraints - NOT NULL, UNIQUE, and foreign keys make the database itself guarantee your data stays consistent.
• Indexes - the difference between a query taking milliseconds or seconds at scale. Postgres's indexing options are best-in-class.
• Transactions - group several changes so they all succeed or all roll back; essential for things like transferring money or placing orders.

A taste of the code

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      TEXT UNIQUE NOT NULL,
  settings   JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO users (email, settings)
VALUES ('[email protected]', '{"theme": "dark"}');

SELECT email FROM users
WHERE settings->>'theme' = 'dark';

Note the last query: that's SQL filtering inside a JSON column - relational structure and document flexibility in one database.

Your first steps

1. Learn SQL basics first - everything in Postgres builds on it.
2. Get a database: install Postgres locally, or skip setup entirely with a free managed instance from a cloud provider (Neon, Supabase, Railway all have free tiers).
3. Connect with the psql command-line tool or a GUI like pgAdmin, and create your first table.
4. Connect from code - pg for Node.js or psycopg for Python - using parameterized queries.
5. Model a real two-table relationship with a foreign key, and add an index to a column you query often.

Common beginner mistakes

• Quoted, mixed-case names. CREATE TABLE "UserAccounts" means you must write "UserAccounts" with quotes forever. Use lowercase snake_case names and skip the quotes.
• Forgetting indexes on foreign keys. PostgreSQL indexes primary keys automatically, but not the columns that reference them - add those yourself.
• TIMESTAMP instead of TIMESTAMPTZ for real-world events, which leads to time zone confusion.
• Opening a connection per request. Use a connection pool; Postgres connections are relatively expensive.
• Editing tables by hand in production. Keep schema changes in migration files under version control.

Getting it online

You don't need to run a server yourself. Neon and Supabase offer hosted PostgreSQL with free tiers, and AWS, Google Cloud and Azure all provide managed Postgres for production. Copy the connection string into an environment variable and your app can connect from anywhere.

Our Node + Express + PostgreSQL guide builds an API on a free hosted database and deploys it.

Go deeper