PostgreSQL (everyone calls it “Postgres”) is an open-source relational database that has quietly become the default choice for most new backend projects. It’s fast, correct, feature-rich, and free. If you’re starting a project today and you don’t have a specific reason to pick something else, pick Postgres.

This post walks through installing it, connecting to it, creating a database, and running the queries you’ll actually use on day one. By the end you should have a working local Postgres and the muscle memory to do useful things with it.

What Postgres Actually Is#

• A relational database: data lives in tables with rows and columns, and tables relate to each other through keys.
• ACID-compliant: transactions are atomic, consistent, isolated, and durable. If a transaction fails halfway, nothing is applied.
• Server/client architecture: the Postgres server runs as a background process (usually on port 5432), and you connect to it from a client — a CLI, a GUI, or your application.
• SQL-based, with Postgres-specific extensions (JSONB, arrays, full-text search, window functions, extensions like PostGIS).

You don’t install “a database.” You install the Postgres server, then create databases inside it. One server can host many databases.

Installing Postgres#

macOS#

The easiest path is Postgres.app — a self-contained Mac app that bundles the server and psql. Download it, drag to /Applications, open it, click Initialize. That’s it.

If you prefer Homebrew:

brew install postgresql@16
brew services start postgresql@16

Linux (Debian/Ubuntu)#

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl enable --now postgresql

Linux (Arch)#

sudo pacman -S postgresql
sudo -iu postgres initdb -D /var/lib/postgres/data
sudo systemctl enable --now postgresql

Windows#

Download the installer from postgresql.org/download/windows. It includes the server, psql, and pgAdmin (a GUI).

Docker (works anywhere)#

If you don’t want to install anything globally, run Postgres in a container:

docker run --name pg \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  -d postgres:16

Your server is now listening on localhost:5432 with user postgres and password secret.

Connecting With psql#

psql is the official CLI client. It ships with every Postgres install.

psql -h localhost -U postgres -d postgres

• -h host
• -U user
• -d database to connect to

On Linux, the default install creates a postgres OS user that can connect via the Unix socket without a password:

sudo -u postgres psql

Once inside, your prompt becomes postgres=#. You’re now running SQL against the server.

psql commands worth knowing#

Meta-commands start with \ and are handled by psql itself, not the server.

Creating a User and Database#

Every database is owned by a role. Don’t use the postgres superuser for your app — create a dedicated role and a database for it.

CREATE ROLE appuser WITH LOGIN PASSWORD 'appsecret';
CREATE DATABASE appdb OWNER appuser;

Now connect as that user:

psql -h localhost -U appuser -d appdb

If Postgres rejects the password, it’s almost always pg_hba.conf — the file that decides who can connect how. On Linux it lives at /etc/postgresql/16/main/pg_hba.conf. The relevant line for local password auth is:

host    all    all    127.0.0.1/32    scram-sha-256

After editing, reload:

sudo systemctl reload postgresql

Your First Table#

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

A few things to notice:

• SERIAL is shorthand for an auto-incrementing integer column. For new code, prefer GENERATED ALWAYS AS IDENTITY — it’s the SQL standard form and behaves better with INSERTs that specify an explicit id.
• TIMESTAMPTZ stores a timestamp with time zone. Always prefer it over TIMESTAMP — the latter silently drops timezone info and causes pain later.
• TEXT is unbounded. Don’t use VARCHAR(n) unless you actually need the length constraint; in Postgres they’re the same type under the hood.

Insert, query, update, delete#

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

-- insert and get the id back
INSERT INTO users (email, name) VALUES ('bob@example.com', 'Bob')
RETURNING id;

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

-- update
UPDATE users SET name = 'Alice Smith' WHERE email = 'alice@example.com';

-- delete
DELETE FROM users WHERE id = 1;

RETURNING is a Postgres-specific clause that gives you back rows from an INSERT, UPDATE, or DELETE. It saves you a round-trip and it’s very handy.

Relationships and Joins#

CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title      TEXT NOT NULL,
  body       TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

• REFERENCES users(id) makes user_id a foreign key.
• ON DELETE CASCADE means if the user is deleted, their posts are too. Alternatives: SET NULL, RESTRICT (default — prevents the delete).

Joining:

SELECT u.name, p.title
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.created_at > now() - interval '7 days';

Indexes#

A table without the right indexes will get slow the moment it has real data. The primary key already has an index. Add indexes on columns you filter or join on frequently.

CREATE INDEX posts_user_id_idx ON posts (user_id);
CREATE INDEX users_email_lower_idx ON users (lower(email));

Check whether a query is using an index:

EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 42;

EXPLAIN ANALYZE runs the query and shows the execution plan with actual timings. Seq Scan on a big table is usually bad; Index Scan is usually good.

Transactions#

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If anything between BEGIN and COMMIT fails, run ROLLBACK instead and nothing is applied. This is the core ACID guarantee, and it’s real — Postgres takes correctness seriously.

JSONB — Because You’ll Need It#

Postgres has first-class support for JSON. Use JSONB (binary JSON), not JSON — it’s indexed and faster to query.

CREATE TABLE events (
  id      SERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

INSERT INTO events (payload) VALUES ('{"type": "signup", "source": "landing"}');

-- query a field
SELECT * FROM events WHERE payload->>'type' = 'signup';

-- index it
CREATE INDEX events_payload_idx ON events USING GIN (payload);

-> returns JSON, ->> returns text. GIN is the index type built for JSONB and arrays.

Backing Up and Restoring#

# dump a single database
pg_dump -h localhost -U appuser -d appdb -F c -f appdb.dump

# restore it
pg_restore -h localhost -U appuser -d appdb_new appdb.dump

-F c is the custom binary format — smaller and faster than plain SQL. For a text dump you can read and diff, drop the -F flag.

For full-cluster backups (all databases), use pg_dumpall.

Connecting From an Application#

Postgres speaks a wire protocol on 5432. Every language has a driver. The connection string format is standard:

postgres://appuser:appsecret@localhost:5432/appdb

• Node.js: pg (low-level), or an ORM like Drizzle or Prisma.
• Python: psycopg (v3) or SQLAlchemy.
• Go: pgx — fast, feature-rich, widely used.
• Rust: sqlx or tokio-postgres.

Always use a connection pool. Opening a fresh Postgres connection per request is expensive — each one forks a backend process on the server. A pool keeps a handful of connections alive and hands them out.

GUI Clients#

The CLI is enough for most things, but a GUI helps when exploring schemas or writing ad-hoc queries:

• TablePlus — polished, fast, closed-source.
• DBeaver — free, open-source, supports every database.
• pgAdmin — official, browser-based, feature-heavy.
• Postico — Mac-only, minimal, pleasant.

Things That Will Bite You#

A short list of day-one gotchas:

• NULL is not equal to anything, including itself. WHERE x = NULL never matches. Use WHERE x IS NULL.
• Identifiers are case-folded to lowercase unless quoted. SELECT * FROM Users and SELECT * FROM users are the same; SELECT * FROM "Users" is different.
• Strings use single quotes. Double quotes are for identifiers.
• SERIAL doesn’t play well with explicit id inserts — the underlying sequence doesn’t advance, so later inserts can collide. Prefer GENERATED ... AS IDENTITY in new schemas.
• Timezones: always use TIMESTAMPTZ, store UTC, convert at the edges.
• Don’t edit data in production with UPDATE or DELETE without a WHERE — you will hit it eventually, and it will hurt. Always BEGIN; first, inspect, then COMMIT; or ROLLBACK;.

Where to Go Next#

Once the basics click, the Postgres features worth learning next are:

• Migrations: tools like sqlx migrate, dbmate, flyway, or your ORM’s migration system. Never edit production schema by hand.
• Constraints: CHECK, UNIQUE, partial unique indexes — push invariants into the schema.
• Window functions: ROW_NUMBER(), LAG(), SUM() OVER (...) — for analytics queries that would otherwise need app-side loops.
• CTEs and recursive queries: WITH clauses make complex queries readable.
• Extensions: pg_stat_statements (query stats), pgvector (vector search), PostGIS (geospatial), pg_trgm (fuzzy text search).
• Replication and backups once you’re running in production.

The official Postgres documentation is genuinely excellent — readable, thorough, and the canonical source of truth. When something confuses you, read the docs first; the answer is almost always there.

This blog post was written with the help of Claude.