PostgreSQL Tutorial

A practical PostgreSQL guide — data types, JSONB, transactions, and performance tips.

1 min read 131 words Updated Mar 6, 2026

PostgreSQL is a powerful open-source relational database with first-class JSON support.

Connect and create

psql -U postgres
CREATE DATABASE app;
\c app

Useful data types

CREATE TABLE events (
  id        BIGSERIAL PRIMARY KEY,
  payload   JSONB,            -- flexible documents
  tags      TEXT[],
  created   TIMESTAMPTZ DEFAULT now()
);

Querying JSONB

SELECT payload->>'user' AS who
FROM events
WHERE payload @> '{"type":"click"}';

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK on error

Wrap multi-step writes in a transaction so they succeed or fail together.

Performance basics

EXPLAIN ANALYZE
SELECT * FROM events WHERE payload @> '{"type":"click"}';

CREATE INDEX idx_events_type ON events ((payload->>'type'));

Use EXPLAIN ANALYZE to see the real query plan before adding indexes.