Backend engineeringPostgreSQL 16Queries, Node.js, performance

PostgreSQL
Cheatsheet

Fifteen modules on the database most backends start with: SQL you write every day, the patterns behind fast queries, and how to call it safely from Node.js. Every terminal is real output from a seeded demo database.

Start at module 01 Open the cheat table Key terms in plain words

By Shree Kumar SharmaSQLnode-postgres 8

Key terms in plain words

Never used a database before? Start here. Every word below shows up later on this page, each with an everyday comparison drawn from an old records office full of ledgers. Underlined words in the modules link back to these cards.

The building blocks Keeping the data honest Asking questions Making it fast Changing data safely Running it for real

35 terms in 6 groups. Hover an underlined word anywhere on the page for a quick reminder, or click it to jump here.

The building blocks

PostgreSQL works like a records office. The office holds cabinets, the cabinets hold ledgers, and every ledger is ruled into lines and columns.

PostgreSQL: Think of it as the records office itself, with a clerk who never sleeps; A free program that stores data and answers questions about it. It runs as a server: a program that waits for requests, often on another machine, and your app connects to it to read and write. People shorten the name to Postgres.
Database: Think of it as one whole office building, with its own front door; A named collection of tables that belong together, usually one per app. One PostgreSQL server can hold many databases, and each keeps its data apart from the others.; For example Every example on this page runs in a database called shop.
Schema: Think of it as a labelled filing cabinet inside the office; A folder inside a database that groups tables. Every database starts with one called public, and that's where your tables go unless you say otherwise. The search path is the list of cabinets Postgres looks in when you name a table without its schema. People also say "schema" for the planned layout of your tables.; For example public.orders means the orders table in the public schema.
Table: Think of it as one ledger, ruled into columns before anyone writes in it; Where the data actually lives: a grid of rows and columns about one kind of thing. Customers go in one table, orders in another. You decide the columns up front, and every row has to fit them.; For example The shop has four tables: customers, products, orders and order_items.
Row: Think of it as one line written across the ledger; A single record in a table, such as one customer or one order. It has one value for every column. Some people call it a record or a tuple.; For example (1, 'shree@example.com', 'Shree', 'Delhi') is one row of customers.
Column: Think of it as one ruled column in the ledger, headed "Name" or "Amount"; One named piece of information that every row in the table has. Each column has a type, which decides what can be written in it.; For example email, name and city are columns of customers.
SQL: Think of it as the standard request form every clerk in every office understands; Structured Query Language, the language you use to talk to the database. You describe what you want ("customers in Pune") and Postgres works out how to get it. Each instruction is called a statement and ends with a semicolon.; For example SELECT name FROM customers WHERE city = 'Pune';
psql: Think of it as the front counter where you hand requests to the clerk; The program you open in a terminal to talk to Postgres directly. You type SQL and it prints the answer as a table. Commands that start with a backslash, like \dt, are shortcuts psql handles itself.

Keeping the data honest

Rules printed on the ledger so a clerk refuses a bad entry before it's ever written down.

Type: Think of it as the note at the top of a column: "dates only" or "money only"; Every column has a data type that says what kind of value it holds: whole numbers, exact money amounts, text, a moment in time and so on. Postgres refuses a value of the wrong kind, so a price can never be the word "cheap".; For example numeric(10, 2) holds money exactly; timestamptz holds a moment in time with its time zone.
Primary key: Think of it as the line number printed in the margin, never given out twice; The column that picks out exactly one row, so no two rows can share it and it can never be empty. Usually it's an id the database counts up for you. A surrogate key is one like that: a plain number with no meaning of its own.; For example id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
Foreign key: Think of it as a note on an order line saying "customer: see line 42 of the customers ledger"; A column that stores another table's primary key, so a row can point at the row it belongs to. Postgres checks the target really exists, and refuses to leave a pointer to nothing.; For example orders.customer_id points at customers.id.
Constraint: Think of it as a rule the clerk checks before writing anything down; A rule attached to a table that every write must pass. NOT NULL means the value must be filled in, UNIQUE means no repeats, and CHECK tests a condition like "price is above zero". A write that breaks one fails with an error.; For example CHECK (price > 0) stops a product from costing nothing or less.
NULL: Think of it as a box on the line left blank on purpose; The marker for "no value here" or "unknown". It isn't zero and it isn't empty text. Comparing anything with NULL gives unknown rather than yes or no, which trips up queries that don't expect it.
jsonb: Think of it as a sticky note of extra details clipped to the ledger line; A column type that holds a whole bundle of labelled values in JSON form, a common text format for structured data. It suits details that differ from row to row, like a product's specs. A GIN index makes searching inside it fast.; For example {"brand": "Sony", "specs": {"weight_g": 101}}
Array: Think of it as one box on the line that holds a short list instead of one word; A column that stores several values of the same type in order. Handy for small lists such as a product's tags, which you can then search with ANY.; For example tags text[] could hold {fitness,wireless}.

Asking questions

How you pull lines out of the ledgers, lay ledgers side by side and add things up.

Query and filter: Think of it as a request slip you hand to the clerk; A query is an SQL statement that asks for data, usually starting with SELECT. The WHERE part is the filter: the condition a row must meet to come back.; For example SELECT * FROM orders WHERE total > 20000
Upsert: Think of it as "correct this line if it's there, otherwise write a new one"; Update plus insert, in one statement. You try to insert a row, and if one with the same unique value already exists, Postgres updates that one instead. In SQL it's written ON CONFLICT ... DO UPDATE.
Keyset pagination: Think of it as leaving a bookmark, instead of counting pages from the front every time; Pagination means showing a long list a page at a time. OFFSET counts past every earlier row on every page, so it slows down as you go. Keyset pagination remembers the last row shown and asks for the ones after it, so page 1,000 is as quick as page 1.
Join: Think of it as laying two ledgers side by side and matching lines on a shared number; Combines rows from two tables where a key matches, such as each order with its customer. An inner join keeps only rows with a match on both sides; a left join keeps every row from the first table even without one.; For example FROM orders o JOIN customers c ON c.id = o.customer_id
Subquery: Think of it as a small question you have answered first, inside the big one; A query written inside another query, in brackets. The outer query uses its answer as if it were a table or a list of values.; For example WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
Aggregate and GROUP BY: Think of it as the clerk's tally sheet at the end of the month; An aggregate turns many rows into one number: count, sum, avg, max. GROUP BY splits the rows into piles first, so you get one total per pile, such as revenue per city.
CTE: Think of it as a scratch page where you work out a part answer and give it a name; Common Table Expression. A WITH name AS (...) block that names a subquery, so a long query reads top to bottom. WITH RECURSIVE repeats itself to follow a chain, such as who referred whom.
Window function: Think of it as a running total pencilled in the margin, while every line stays as it was; A calculation across a group of related rows that keeps every row, unlike GROUP BY. It gives you rankings, running totals and the previous row's value. PARTITION BY says which rows count as one group.; For example row_number() OVER (PARTITION BY city ORDER BY total DESC)

Making it fast

How Postgres avoids reading every line of every ledger to answer one question.

Index: Think of it as the alphabetical index at the back of a ledger; A sorted copy of one or more columns that points straight to the matching rows. Without one, Postgres reads the whole table. A composite index covers several columns, and their order matters. Each index also slows writes a little, so add the ones your queries need.; For example CREATE INDEX ON orders (customer_id, created_at DESC);
EXPLAIN and the planner: Think of it as asking the clerk to show their working before you trust the answer; Before running a query, the planner picks a route: read every row, or use an index, and in what order. EXPLAIN shows that plan; EXPLAIN ANALYZE runs the query too and adds real timings and row counts.

Changing data safely

What stops two clerks scribbling over each other, and what keeps saved reports up to date.

Transaction: Think of it as a batch of entries that are all signed off together, or torn up together; A group of statements between BEGIN and COMMIT that succeed or fail as one. ROLLBACK undoes the lot. A savepoint marks a spot inside it you can undo back to without losing everything.
Isolation level: Think of it as how much of another clerk's unfinished work you're allowed to see; The rule for what one transaction sees while others are changing data at the same time. The default, READ COMMITTED, shows each statement only what was committed before it began. Stricter levels prevent more surprises but may ask you to retry.
Lock: Think of it as a clerk's hand resting on a ledger line so nobody else edits it; A claim a transaction holds on a row or table so others wait until it's done. FOR UPDATE SKIP LOCKED lets workers pass over rows someone else holds, which is how many workers share a job queue without picking the same job.
View and materialized view: Think of it as a standard report layout the office keeps on file; A view is a saved query you can read like a table; it runs fresh each time. A materialized view stores the result instead, so it's fast to read but only as fresh as its last refresh.
Trigger: Think of it as a standing order: "whenever a line is changed, stamp today's date on it"; A function that Postgres runs on its own when rows are inserted, updated or deleted. It's handy for chores that must never be forgotten, like keeping an updated_at column current.

Running it for real

Words you meet once an app talks to Postgres every day.

Connection pool: Think of it as a small team of runners kept on standby between your app and the office; Opening a connection to the database is slow, so a pool keeps a few open and lends them out. The pg driver, the Node.js library that talks to Postgres, gives you pg.Pool. Make one per process and reuse it.
Query parameter: Think of it as filling in a printed form, rather than letting the visitor write their own instructions; A placeholder like $1 in the SQL, with the real value sent separately. The value can never change what the query does, which blocks SQL injection: the attack where typed input sneaks extra commands into a query.; For example pool.query('SELECT * FROM orders WHERE id = $1', [id])
Role: Think of it as a staff badge that opens some doors and not others; A user or group inside Postgres. GRANT gives a role a right, such as reading a table. Give your app only the rights it needs, so a bug or a leak can't do more damage.
VACUUM and MVCC: Think of it as the night cleaner who clears away crossed-out lines; Postgres never overwrites a row in place. It writes a new version and keeps the old one for anyone still reading it, which is called MVCC. VACUUM reclaims the dead versions, and autovacuum does it for you in the background.
Backup and restore: Think of it as photocopying every ledger and keeping the copies in another building; pg_dump saves a database to a file and pg_restore loads it back. A backup only counts once you've tried restoring it.

psql essentials

Connect, look around and change how results print. A handful of backslash commands cover most daily work in the psql shell.

Connect\d commands\x expanded\timing

Backslash commands are handled by psql itself, not sent to the server, so they need no semicolon. Everything else is SQL and runs when psql sees ;. Every terminal on this page is real output from PostgreSQL 16 against a demo shop database with 10,000 customers, 100,000 orders and 200,000 order lines.

Terminalpsql

~/db $ psql postgres://app:app_pw@localhost:5432/shop -c '\conninfo'
You are connected to database "shop" as user "app" on host "localhost" (address "127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
shop=# \dt
          List of relations
 Schema |    Name     | Type  | Owner 
--------+-------------+-------+-------
 public | customers   | table | root
 public | order_items | table | root
 public | orders      | table | root
 public | products    | table | root
(4 rows)
shop=# \d orders
                                    Table "public.orders"
   Column    |           Type           | Collation | Nullable |           Default            
-------------+--------------------------+-----------+----------+------------------------------
 id          | bigint                   |           | not null | generated always as identity
 customer_id | bigint                   |           | not null | 
 status      | order_status             |           | not null | 'pending'::order_status
 total       | numeric(12,2)            |           | not null | 0
 created_at  | timestamp with time zone |           | not null | now()
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
Referenced by:
    TABLE "order_items" CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE

Command	Shows
`\l`	Databases
`\c shop`	Connect to another database
`\dt`	Tables in the search path
`\d orders`	Columns, indexes and constraints of one table
`\di+`	Indexes with their sizes
`\df`	Functions
`\du`	Roles
`\x auto`	Expanded rows when a row is too wide
`\timing on`	How long each statement took
`\e`	Edit the last query in $EDITOR
`\copy t TO 'f.csv' CSV HEADER`	Export from the client side
`\?`	Every meta command

Tables, types and constraints

Pick types that say what the data is, and let constraints refuse bad rows before any application code sees them.

Identity keystimestamptzjsonb and arraysCHECK and FK

Use bigint GENERATED ALWAYS AS IDENTITY for surrogate keys, timestamptz for every point in time, numeric for money and text for strings unless a length rule is real. Constraints are free documentation that the database enforces on every write.

schema.sqlSQL

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

CREATE TABLE customers (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE CHECK (email LIKE '%@%'),
  name        text NOT NULL,
  city        text,
  referred_by bigint REFERENCES customers (id),
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku    text NOT NULL UNIQUE,
  name   text NOT NULL,
  price  numeric(10, 2) NOT NULL CHECK (price > 0),
  tags   text[] NOT NULL DEFAULT '{}',
  attrs  jsonb NOT NULL DEFAULT '{}'
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  status      order_status NOT NULL DEFAULT 'pending',
  total       numeric(12, 2) NOT NULL DEFAULT 0,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id   bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES products (id),
  qty        int NOT NULL CHECK (qty > 0),
  unit_price numeric(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Terminalpsql

shop=# INSERT INTO products (sku, name, price) VALUES ('SKU-9999', 'Broken', -5);
ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (201, SKU-9999, Broken, -5.00, {}, {}).
shop=# INSERT INTO orders (customer_id) VALUES (999999);
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999999) is not present in table "customers".
shop=# INSERT INTO customers (email, name) VALUES ('user1@example.com', 'Dup');
ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(user1@example.com) already exists.

Need	Type	Avoid
Surrogate key	`bigint GENERATED ALWAYS AS IDENTITY`	`serial` (legacy)
Point in time	`timestamptz`	`timestamp` without a zone
Money	`numeric(12,2)`	`real`, `money`
Text	`text` with a CHECK if needed	`varchar(255)` by habit
Fixed set of values	`enum` or a lookup table	Free text
Flexible attributes	`jsonb`	`json` (no indexing, keeps whitespace)
Public identifiers	`uuid`	Exposing sequential ids

Insert, update, upsert, delete

Every write can hand back the rows it touched with RETURNING, and ON CONFLICT turns an insert into an upsert in one statement.

RETURNINGON CONFLICTUPDATE ... FROMDELETE ... RETURNING

RETURNING saves a second query to read generated ids and defaults. ON CONFLICT (col) DO UPDATE needs a unique index on that column, and EXCLUDED refers to the row you tried to insert. These examples run inside a transaction that is rolled back, so the data stays as it was.

Terminalpsql

shop=# BEGIN;
shop=# INSERT INTO customers (email, name, city)
shop-# VALUES ('shree@example.com', 'Shree', 'Delhi')
shop-# RETURNING id, created_at::date;
  id   | created_at 
-------+------------
 10002 | 2026-10-01
(1 row)
shop=# INSERT INTO products (sku, name, price) VALUES ('SKU-0001', 'Earbuds v2', 1999)
shop-# ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
shop-# RETURNING id, sku, name, price;
 id |   sku    |    name    |  price  
----+----------+------------+---------
  1 | SKU-0001 | Earbuds v2 | 1999.00
(1 row)
shop=# -- a data modifying CTE: update, then count what changed
shop-# WITH cancelled AS (
shop-#   UPDATE orders o SET status = 'cancelled'
shop-#   FROM customers c
shop-#   WHERE c.id = o.customer_id AND c.city = 'Pune' AND o.status = 'pending'
shop-#   RETURNING o.id
shop-# )
shop-# SELECT count(*) FROM cancelled;
 count 
-------
  1671
(1 row)
shop=# DELETE FROM orders WHERE id = 99999 RETURNING id, status, total;
  id   | status  | total  
-------+---------+--------
 99999 | shipped | 501.06
(1 row)
shop=# ROLLBACK;

Pattern	SQL
Insert many rows	`INSERT INTO t (a, b) VALUES (1, 2), (3, 4)`
Insert from a query	`INSERT INTO t (a) SELECT x FROM s`
Ignore duplicates	`ON CONFLICT DO NOTHING`
Upsert	`ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price`
Update with a join	`UPDATE t SET ... FROM s WHERE s.id = t.s_id`
Delete with a join	`DELETE FROM t USING s WHERE s.id = t.s_id`
Bulk load	`COPY t FROM STDIN (FORMAT csv)`

Filtering, sorting and pagination

WHERE, ORDER BY and LIMIT, plus the pagination choice that matters most: offsets get slower with every page, keysets do not.

WHEREILIKE, IN, BETWEENOFFSETKeyset pagination

OFFSET 50000 still reads and throws away 50,000 rows. Keyset pagination remembers the last row you showed and asks for rows after it, so page 1,000 is as fast as page 1. It needs a unique, indexed sort key, such as (created_at, id).

Terminalpsql

shop=# SELECT id, name, city FROM customers
shop-# WHERE city IN ('Delhi', 'Pune') AND name ILIKE '%99%'
shop-# ORDER BY id LIMIT 3;
 id  |     name     | city 
-----+--------------+------
  99 | Customer 99  | Pune
 199 | Customer 199 | Pune
 299 | Customer 299 | Pune
(3 rows)
shop=# SELECT status, count(*) FROM orders
shop-# WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'
shop-# GROUP BY status ORDER BY status;
  status   | count 
-----------+-------
 pending   |   476
 paid      |  1675
 shipped   |  2523
 cancelled |   434
(4 rows)
shop=# -- page 1
shop-# SELECT id, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 3;
  id   |          created_at           
-------+-------------------------------
 45625 | 2026-08-23 23:58:40.368035+00
 53854 | 2026-08-23 23:53:27.682741+00
 30521 | 2026-08-23 23:51:06.616973+00
(3 rows)
shop=# -- page 2 with OFFSET
shop-# SELECT id, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 3 OFFSET 3;
  id   |          created_at           
-------+-------------------------------
 33307 | 2026-08-23 23:50:27.552584+00
 45422 | 2026-08-23 23:49:09.020859+00
 70017 | 2026-08-23 23:47:28.235073+00
(3 rows)
shop=# -- the same page with a keyset: pass the last row of page 1
shop-# SELECT id, created_at FROM orders
shop-# WHERE (created_at, id) < ('2026-08-23 23:51:06.616973+00', 30521)
shop-# ORDER BY created_at DESC, id DESC LIMIT 3;
  id   |          created_at           
-------+-------------------------------
 33307 | 2026-08-23 23:50:27.552584+00
 45422 | 2026-08-23 23:49:09.020859+00
 70017 | 2026-08-23 23:47:28.235073+00
(3 rows)

Joins

Combine tables on matching keys. Inner joins keep matches, left joins keep every row on the left, and LATERAL runs a subquery per row.

INNERLEFTLATERALAnti join

Terminalpsql

shop=# SELECT o.id, c.name, o.status, o.total
shop-# FROM orders o
shop-# JOIN customers c ON c.id = o.customer_id
shop-# WHERE o.total > 20000
shop-# ORDER BY o.total DESC LIMIT 3;
  id   |     name      | status  |  total   
-------+---------------+---------+----------
 70244 | Customer 7636 | pending | 59331.04
 65438 | Customer 8641 | paid    | 54743.68
 56279 | Customer 9694 | shipped | 53760.52
(3 rows)
shop=# -- customers who never ordered (anti join)
shop-# SELECT count(*) FROM customers c
shop-# WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
 count 
-------
     1
(1 row)
shop=# -- each customer's latest order, one row per customer
shop-# SELECT c.id, c.name, last.id AS order_id, last.created_at::date
shop-# FROM customers c
shop-# CROSS JOIN LATERAL (
shop-#   SELECT id, created_at FROM orders o
shop-#   WHERE o.customer_id = c.id ORDER BY created_at DESC LIMIT 1
shop-# ) last
shop-# WHERE c.id <= 3;
 id |    name    | order_id | created_at 
----+------------+----------+------------
  1 | Customer 1 |    29708 | 2026-06-09
  2 | Customer 2 |    40238 | 2026-07-24
  3 | Customer 3 |    17383 | 2026-08-06
(3 rows)

Why it matters: NOT EXISTS is the safe way to find missing rows. NOT IN (subquery) returns nothing at all if the subquery yields a single NULL.

Join	Keeps
`JOIN` (inner)	Only rows with a match on both sides
`LEFT JOIN`	Every left row, NULLs where the right has no match
`FULL JOIN`	Every row from both sides
`CROSS JOIN`	Every combination; rarely what you want by accident
`LATERAL`	A subquery that can see the current left row
`EXISTS` / `NOT EXISTS`	Semi and anti joins, without duplicating rows

Aggregates and grouping

Summarise many rows into a few with GROUP BY, conditional counts with FILTER, and subtotals with ROLLUP.

GROUP BYFILTERHAVINGdate_trunc

Terminalpsql

shop=# SELECT date_trunc('month', created_at)::date AS month,
shop-#        count(*) AS orders,
shop-#        count(*) FILTER (WHERE status = 'cancelled') AS cancelled,
shop-#        round(avg(total), 2) AS avg_total
shop-# FROM orders
shop-# WHERE created_at >= '2026-05-01'
shop-# GROUP BY 1 ORDER BY 1;
   month    | orders | cancelled | avg_total 
------------+--------+-----------+-----------
 2026-05-01 |   5171 |       440 |  13313.83
 2026-06-01 |   4985 |       419 |  13581.26
 2026-07-01 |   5243 |       425 |  13471.68
 2026-08-01 |   3893 |       319 |  13478.99
(4 rows)
shop=# SELECT c.city, count(DISTINCT o.customer_id) AS buyers, sum(o.total) AS revenue
shop-# FROM orders o JOIN customers c ON c.id = o.customer_id
shop-# WHERE o.status <> 'cancelled'
shop-# GROUP BY ROLLUP (c.city)
shop-# HAVING sum(o.total) > 1000000
shop-# ORDER BY revenue;
   city    | buyers |    revenue    
-----------+--------+---------------
 Mumbai    |   1999 |  243611908.66
 Gurugram  |   2000 |  243644308.59
 Delhi     |   2000 |  247009321.97
 Bengaluru |   2000 |  248392521.72
 Pune      |   2000 |  249246978.58
           |   9999 | 1231905039.52
(6 rows)

Why it matters: WHERE filters rows before grouping, HAVING filters groups after. The FILTER clause replaces the old sum(CASE WHEN ... THEN 1 END) trick.

CTEs and recursive queries

WITH names a subquery so a long query reads top to bottom. WITH RECURSIVE walks trees and graphs, like a referral chain.

WITHWITH RECURSIVEData modifying CTEs

Terminalpsql

shop=# WITH big_spenders AS (
shop-#   SELECT customer_id, sum(total) AS spent
shop-#   FROM orders WHERE status <> 'cancelled'
shop-#   GROUP BY customer_id
shop-#   HAVING sum(total) > 120000
shop-# )
shop-# SELECT c.name, b.spent FROM big_spenders b
shop-# JOIN customers c ON c.id = b.customer_id
shop-# ORDER BY b.spent DESC LIMIT 3;
     name      |   spent   
---------------+-----------
 Customer 969  | 372822.31
 Customer 9192 | 363456.46
 Customer 3497 | 344157.69
(3 rows)
shop=# -- who referred customer 6, all the way up
shop-# WITH RECURSIVE chain AS (
shop-#   SELECT id, name, referred_by, 0 AS depth FROM customers WHERE id = 6
shop-#   UNION ALL
shop-#   SELECT c.id, c.name, c.referred_by, chain.depth + 1
shop-#   FROM customers c JOIN chain ON c.id = chain.referred_by
shop-# )
shop-# SELECT depth, id, name FROM chain;
 depth | id |    name    
-------+----+------------
     0 |  6 | Customer 6
     1 |  5 | Customer 5
     2 |  4 | Customer 4
     3 |  3 | Customer 3
     4 |  2 | Customer 2
     5 |  1 | Customer 1
(6 rows)

Why it matters: since PostgreSQL 12 a CTE used once is inlined like a subquery. Add MATERIALIZED when you want it computed exactly once.

Window functions

Calculate across related rows without collapsing them: rankings, running totals and the previous row's value.

OVERPARTITION BYrow_number, ranklag, running sum

An aggregate with OVER (...) keeps every row and adds a column. PARTITION BY restarts the calculation per group and ORDER BY inside the window makes it cumulative. Top N per group is the classic use.

Terminalpsql

shop=# -- top 2 orders per city
shop-# SELECT * FROM (
shop-#   SELECT c.city, o.id, o.total,
shop-#          row_number() OVER (PARTITION BY c.city ORDER BY o.total DESC) AS rn
shop-#   FROM orders o JOIN customers c ON c.id = o.customer_id
shop-# ) t WHERE rn <= 2;
   city    |  id   |  total   | rn 
-----------+-------+----------+----
 Bengaluru |  7289 | 53196.52 |  1
 Bengaluru | 97070 | 52062.28 |  2
 Delhi     | 34130 | 50400.52 |  1
 Delhi     | 89486 | 50099.88 |  2
 Gurugram  | 70244 | 59331.04 |  1
 Gurugram  | 65438 | 54743.68 |  2
 Mumbai    |  1583 | 51357.45 |  1
 Mumbai    | 26585 | 50414.29 |  2
 Pune      | 56279 | 53760.52 |  1
 Pune      | 23282 | 50716.20 |  2
(10 rows)
shop=# -- running total and change from the previous order, for one customer
shop-# SELECT id, created_at::date, total,
shop-#        sum(total) OVER (ORDER BY created_at) AS running,
shop-#        total - lag(total) OVER (ORDER BY created_at) AS diff
shop-# FROM orders WHERE customer_id = 42 ORDER BY created_at;
  id   | created_at |  total   |  running  |   diff    
-------+------------+----------+-----------+-----------
 42701 | 2025-03-17 | 18301.90 |  18301.90 |          
 23129 | 2025-04-09 | 28016.56 |  46318.46 |   9714.66
 63024 | 2025-05-12 | 11234.88 |  57553.34 | -16781.68
 14097 | 2025-06-28 | 13016.28 |  70569.62 |   1781.40
 56746 | 2025-08-20 | 18693.84 |  89263.46 |   5677.56
 85690 | 2025-09-09 | 14965.62 | 104229.08 |  -3728.22
 24575 | 2025-09-20 | 14116.26 | 118345.34 |   -849.36
 62527 | 2026-01-27 | 23079.06 | 141424.40 |   8962.80
 30399 | 2026-04-08 | 19543.84 | 160968.24 |  -3535.22
 21147 | 2026-08-07 | 14283.78 | 175252.02 |  -5260.06
(10 rows)

Function	Gives
`row_number()`	1, 2, 3 with no ties
`rank()` / `dense_rank()`	Ties share a rank; dense leaves no gaps
`lag(x) / lead(x)`	The value from the previous or next row
`first_value(x)`	The first value in the window
`sum(x) OVER (ORDER BY t)`	A running total
`avg(x) OVER (ROWS 6 PRECEDING)`	A 7 row moving average
`ntile(4)`	Quartiles

JSONB and arrays

Store flexible attributes as jsonb and lists as arrays, query inside them, and index them when they are filtered often.

-> and ->>@> containmentjsonb_setANY and unnest

-> returns jsonb and ->> returns text. @> asks whether the left side contains the right, and it is the operator a GIN index speeds up. Keep columns you filter, join or sort on as real columns; jsonb is for the long tail.

Terminalpsql

shop=# SELECT sku, attrs->>'brand' AS brand, (attrs #>> '{specs,weight_g}')::int AS grams
shop-# FROM products WHERE attrs @> '{"brand": "Sony"}' ORDER BY id LIMIT 3;
   sku    | brand | grams 
----------+-------+-------
 SKU-0001 | Sony  |   101
 SKU-0005 | Sony  |   105
 SKU-0009 | Sony  |   109
(3 rows)
shop=# SELECT attrs->>'brand' AS brand, count(*) FROM products GROUP BY 1 ORDER BY 2 DESC;
   brand   | count 
-----------+-------
 Sony      |    50
 Decathlon |    50
 Prestige  |    50
 Boat      |    50
(4 rows)
shop=# BEGIN;
shop=# UPDATE products SET attrs = jsonb_set(attrs, '{warranty_months}', '36')
shop-# WHERE id = 1 RETURNING attrs;
                                attrs                                 
----------------------------------------------------------------------
 {"brand": "Sony", "specs": {"weight_g": 101}, "warranty_months": 36}
(1 row)
shop=# ROLLBACK;
shop=# SELECT sku, tags FROM products WHERE 'wireless' = ANY (tags) AND 'fitness' = ANY (tags) LIMIT 2;
   sku    |        tags        
----------+--------------------
 SKU-0003 | {fitness,wireless}
 SKU-0007 | {fitness,wireless}
(2 rows)
shop=# SELECT tag, count(*) FROM products, unnest(tags) AS tag GROUP BY tag ORDER BY 2 DESC;
   tag    | count 
----------+-------
 wireless |   100
 audio    |   100
 kitchen  |    50
 fitness  |    50
(4 rows)

Operator	Does	Example
`->` / `->>`	Field as jsonb / as text	`attrs->>'brand'`
`#>>`	Nested path as text	`attrs #>> '{specs,weight_g}'`
`@>`	Contains	`attrs @> '{"brand":"Sony"}'`
`?`	Has a top level key	`attrs ? 'warranty_months'`
`jsonb_set`	Replace a value at a path	`jsonb_set(attrs, '{a}', '1')`
`\|\|`	Merge objects	`attrs \|\| '{"new": true}'`
`= ANY(arr)`	Value is in an array	`'audio' = ANY (tags)`
`&&`	Arrays overlap	`tags && '{audio,kitchen}'`

Indexes

B-tree for equality and ranges, composite for multi column filters, partial for hot subsets, expression for computed lookups, GIN for jsonb and arrays.

B-treeCompositePartialGIN

Postgres indexes primary keys and unique constraints for you, but not foreign keys. A composite index on (customer_id, created_at) serves filters on customer_id alone and sorting by date within it; column order matters. CONCURRENTLY builds without blocking writes, at the cost of taking longer.

indexes.sqlSQL

-- Foreign keys are not indexed automatically
CREATE INDEX CONCURRENTLY orders_customer_created_idx ON orders (customer_id, created_at DESC);

-- Partial: only the rows a worker keeps looking at
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

-- Expression: case-insensitive lookups
CREATE UNIQUE INDEX customers_email_lower_idx ON customers (lower(email));

-- GIN: containment queries on jsonb and arrays
CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
CREATE INDEX products_tags_gin  ON products USING gin (tags);

-- Covering: answer from the index alone, no table visit
CREATE INDEX order_items_product_idx ON order_items (product_id) INCLUDE (qty, unit_price);

Terminalpsql

shop=# CREATE INDEX CONCURRENTLY orders_customer_created_idx ON orders (customer_id, created_at DESC);
shop=# CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';
shop=# CREATE UNIQUE INDEX customers_email_lower_idx ON customers (lower(email));
shop=# CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
shop=# CREATE INDEX order_items_product_idx ON order_items (product_id) INCLUDE (qty, unit_price);
shop=# SELECT indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
shop-# FROM pg_stat_user_indexes WHERE relname IN ('orders', 'order_items')
shop-# ORDER BY pg_relation_size(indexrelid) DESC;
            index            |  size   
-----------------------------+---------
 order_items_product_idx     | 7944 kB
 order_items_pkey            | 6176 kB
 orders_pkey                 | 4408 kB
 orders_customer_created_idx | 3104 kB
 orders_pending_idx          | 200 kB
(5 rows)

Why it matters: the partial index covers about a seventh of the table, so it is a fraction of the size of a full one. Every index also slows writes, so add them for queries you actually run.

Type	Good for	Operators
B-tree	Equality, ranges, sorting	`= < > BETWEEN ORDER BY`
GIN	jsonb, arrays, full text	`@> ? && @@`
GiST	Ranges, geometry, nearest neighbour	`&& <->`
BRIN	Huge append only tables sorted by time	Ranges on correlated columns
Hash	Equality only	`=`

Reading EXPLAIN

EXPLAIN shows the plan; EXPLAIN ANALYZE runs the query and adds real timings. Compare the same query with and without an index.

EXPLAIN ANALYZEBUFFERSSeq Scan vs Index ScanRow estimates

Read the plan from the innermost node outwards. Check the node type, compare estimated with actual rows, and look at Buffers: each one is an 8 KB page read from cache (hit) or disk (read). Here is the same query without and then with the composite index.

Terminalwithout indexpsql

shop=# DROP INDEX orders_customer_created_idx;
shop=# EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
shop-# SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 5;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Limit (actual time=8.711..8.714 rows=5 loops=1)
   Buffers: shared hit=1572
   ->  Sort (actual time=8.709..8.710 rows=5 loops=1)
         Sort Key: created_at DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=1572
         ->  Seq Scan on orders (actual time=1.009..8.673 rows=10 loops=1)
               Filter: (customer_id = 42)
               Rows Removed by Filter: 99990
               Buffers: shared hit=1569
 Planning:
   Buffers: shared hit=106 read=1
 Planning Time: 0.372 ms
 Execution Time: 8.749 ms
(14 rows)

Terminalwith indexpsql

shop=# CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);
shop=# EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
shop-# SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 5;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Limit (actual time=0.058..0.069 rows=5 loops=1)
   Buffers: shared hit=8 read=3
   ->  Index Scan using orders_customer_created_idx on orders (actual time=0.057..0.068 rows=5 loops=1)
         Index Cond: (customer_id = 42)
         Buffers: shared hit=8 read=3
 Planning:
   Buffers: shared hit=124 read=1
 Planning Time: 0.396 ms
 Execution Time: 0.099 ms
(9 rows)

Why it matters: without the index, Postgres reads all 100,000 rows to keep 10, then sorts them. With it, the index already holds that customer's orders newest first, so it reads five entries and stops: about 11 buffers instead of 1,586.

Node	Means	Worry when
`Seq Scan`	Reads every row	The table is big and few rows come back
`Index Scan`	Walks the index, then visits the table	It returns most of the table
`Index Only Scan`	Answers from the index alone	Heap Fetches is high: run VACUUM
`Bitmap Heap Scan`	Collects matches, then reads pages in order	Rarely; good for medium result sets
`Nested Loop`	For each outer row, look up inner rows	The outer side is large
`Hash Join`	Builds a hash table of one side	It spills to disk (Batches > 1)
`Sort`	Sorts rows	`external merge` means it spilled; raise work_mem or index it

Transactions and locking

Group statements so they succeed or fail together, choose an isolation level, and build a safe job queue with FOR UPDATE SKIP LOCKED.

BEGIN / COMMITSAVEPOINTIsolation levelsSKIP LOCKED

Postgres runs at READ COMMITTED by default: each statement sees data committed before it started. A savepoint lets you undo part of a transaction. For queues, FOR UPDATE SKIP LOCKED lets many workers each claim a different row with no waiting and no double processing.

Terminalpsql

shop=# CREATE TABLE jobs (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, kind text, done_at timestamptz);
shop=# INSERT INTO jobs (kind) VALUES ('email'), ('email'), ('invoice');
shop=# BEGIN;
shop=# UPDATE products SET price = price * 1.1 WHERE id = 1;
shop=# SAVEPOINT before_risky;
shop=# UPDATE products SET price = -1 WHERE id = 2;
ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (2, SKU-0002, Product 2, -1.00, {kitchen}, {"brand": "Prestige", "specs": {"weight_g": 102}, "warranty_mont...).
shop=# ROLLBACK TO SAVEPOINT before_risky;
shop=# COMMIT;
shop=# -- a worker claims the next free job; others skip it instead of waiting
shop-# BEGIN;
shop=# SELECT id, kind FROM jobs WHERE done_at IS NULL
shop-# ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
 id | kind  
----+-------
  1 | email
(1 row)
shop=# UPDATE jobs SET done_at = now() WHERE id = 1;
shop=# COMMIT;
shop=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

Level	Prevents	Cost
`READ COMMITTED`	Dirty reads	Default; a re-read can see newer data
`REPEATABLE READ`	Plus non repeatable reads and phantoms	May fail with a serialization error; retry
`SERIALIZABLE`	Every anomaly	More retries under contention

Views, functions and triggers

Name a query with a view, cache an expensive one with a materialized view, and keep columns in sync with a trigger.

VIEWMATERIALIZED VIEWplpgsqlTriggers

Terminalpsql

shop=# CREATE MATERIALIZED VIEW daily_revenue AS
shop-# SELECT created_at::date AS day, sum(total) AS revenue, count(*) AS orders
shop-# FROM orders WHERE status <> 'cancelled' GROUP BY 1;
shop=# CREATE UNIQUE INDEX ON daily_revenue (day);
shop=# REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;
shop=# SELECT * FROM daily_revenue ORDER BY day DESC LIMIT 3;
    day     |  revenue   | orders 
------------+------------+--------
 2026-08-23 | 1842108.00 |    145
 2026-08-22 | 2109509.47 |    154
 2026-08-21 | 1871917.06 |    133
(3 rows)
shop=# ALTER TABLE customers ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();
shop=# CREATE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
shop-# BEGIN
shop-#   NEW.updated_at := now();
shop-#   RETURN NEW;
shop-# END $$;
shop=# CREATE TRIGGER customers_touch BEFORE UPDATE ON customers
shop-# FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
shop=# UPDATE customers SET city = 'Noida' WHERE id = 1;
shop=# SELECT id, city, updated_at > (SELECT updated_at FROM customers WHERE id = 2) AS touched_by_trigger
shop-# FROM customers WHERE id IN (1, 2) ORDER BY id;
 id |  city  | touched_by_trigger 
----+--------+--------------------
  1 | Noida  | t
  2 | Mumbai | f
(2 rows)

Why it matters: REFRESH ... CONCURRENTLY keeps the view readable while it rebuilds, and needs a unique index on the view.

From Node.js with pg

One pool per process, parameters for every value, and a helper that runs a transaction on a single client.

pg.Pool$1 parametersTransactionsbigint as string

Never build SQL by concatenating input. Pass values as parameters and the driver sends them separately. A transaction must stay on one connection, so check out a client with pool.connect() and always release it in finally. In NestJS, the TypeORM setup from the NestJS cheatsheet wraps the same pool.

pg-demo.tsTS

import pg from 'pg';

// One pool per process. Each query borrows a client and returns it.
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,   // postgres://user:pass@host:5432/shop
  max: 10,
  idleTimeoutMillis: 30_000,
});

interface OrderRow { id: string; status: string; total: string }

// Parameters ($1, $2) are sent separately from the SQL, so input can never change the query
async function recentOrders(customerId: number, limit = 3) {
  const { rows } = await pool.query<OrderRow>(
    'SELECT id, status, total FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT $2',
    [customerId, limit],
  );
  return rows;
}

// A transaction must run on ONE client, so check one out explicitly
async function withTransaction<T>(fn: (client: pg.PoolClient) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();          // always give the client back to the pool
  }
}

const rows = await recentOrders(42);
console.log(rows);

const newId = await withTransaction(async (c) => {
  const { rows } = await c.query<{ id: string }>(
    'INSERT INTO orders (customer_id, status) VALUES ($1, $2) RETURNING id', [42, 'pending'],
  );
  await c.query('INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES ($1, 7, 2, 499)', [rows[0].id]);
  return rows[0].id;
});
console.log('created order', newId);

try {
  await withTransaction(async (c) => {
    await c.query('INSERT INTO orders (customer_id) VALUES ($1)', [42]);
    await c.query('INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (1, 7, 0, 499)');
  });
} catch (err) {
  console.log('rolled back:', (err as Error).message);
}

console.log('pool', { total: pool.totalCount, idle: pool.idleCount });
await pool.end();

Terminaltsx

~/db $ npx tsx pg-demo.ts
[
  { id: '21147', status: 'shipped', total: '14283.78' },
  { id: '30399', status: 'paid', total: '19543.84' },
  { id: '62527', status: 'shipped', total: '23079.06' }
]
created order 100002
rolled back: new row for relation "order_items" violates check constraint "order_items_qty_check"
pool { total: 1, idle: 1 }

Why it matters: ids and totals arrive as strings. bigint and numeric can exceed what a JavaScript number holds exactly, so pg will not convert them for you. Parse them on purpose, or set a type parser.

Roles, maintenance and backups

Give the app only the rights it needs, watch what is running, keep statistics fresh and take backups you have tested.

GRANTpg_stat_activityVACUUM ANALYZEpg_dump

MVCC keeps old row versions around until VACUUM reclaims them; autovacuum does this for you, but a big batch job deserves a manual VACUUM ANALYZE so the planner sees the new data. Find slow or stuck sessions in pg_stat_activity.

Terminalpsql

shop=# CREATE ROLE reporting NOLOGIN;
shop=# GRANT SELECT ON orders, customers TO reporting;
shop=# SELECT relname AS table, n_live_tup AS rows, n_dead_tup AS dead,
shop-#        pg_size_pretty(pg_total_relation_size(relid)) AS total_size
shop-# FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 4;
    table    |  rows  | dead | total_size 
-------------+--------+------+------------
 order_items | 200001 |    0 | 25 MB
 orders      | 100001 | 1673 | 20 MB
 customers   |  10000 |    7 | 2264 kB
 products    |    200 |    3 | 144 kB
(4 rows)
shop=# VACUUM (ANALYZE) orders;
shop=# SELECT pid, state, wait_event_type, left(query, 40) AS query
shop-# FROM pg_stat_activity WHERE datname = 'shop';
 pid  | state  | wait_event_type |                  query                   
------+--------+-----------------+------------------------------------------
 2065 | active |                 | SELECT pid, state, wait_event_type, left
(1 row)

Terminalbash

~/db $ pg_dump -Fc -d shop -f shop.dump
~/db $ ls -lh shop.dump | awk '{print $5, $9}'
3.1M shop.dump
~/db $ pg_restore -l shop.dump | grep -c 'TABLE DATA'
5
# restore into a new database: createdb shop_copy && pg_restore -d shop_copy shop.dump

Task	Command
Logical backup, custom format	`pg_dump -Fc -d shop -f shop.dump`
Restore, parallel	`pg_restore -j 4 -d shop_copy shop.dump`
Cancel a query	`SELECT pg_cancel_backend(pid)`
Kill a session	`SELECT pg_terminate_backend(pid)`
Slowest queries	`pg_stat_statements` extension
Default privileges for new tables	`ALTER DEFAULT PRIVILEGES ... GRANT SELECT`

Which one do I need?

Start from what you are trying to do, then reach for the tool in the middle column. The last column takes you to the module that explains it.

I want to	Reach for	Example	Module
See a table's structure	psql	`\d orders`	01
Store money	numeric	`numeric(12,2)`	02
Insert or update in one go	ON CONFLICT	`ON CONFLICT (sku) DO UPDATE`	03
Get generated ids back	RETURNING	`INSERT ... RETURNING id`	03
Page through a big table	Keyset pagination	`WHERE (t, id) < ($1, $2)`	04
Find rows with no match	NOT EXISTS	`WHERE NOT EXISTS (...)`	05
Latest child row per parent	LATERAL	`CROSS JOIN LATERAL (... LIMIT 1)`	05
Count by condition	FILTER	`count(*) FILTER (WHERE ...)`	06
Walk a tree	WITH RECURSIVE	`UNION ALL ... JOIN chain`	07
Top N per group	row_number()	`OVER (PARTITION BY ... ORDER BY ...)`	08
Query inside JSON	jsonb operators	`attrs @> '{...}'`	09
Speed up a frequent filter	B-tree index	`CREATE INDEX ON t (a, b)`	10
Index a hot subset	Partial index	`... WHERE status = 'pending'`	10
Find out why it is slow	EXPLAIN ANALYZE	`EXPLAIN (ANALYZE, BUFFERS)`	11
Build a job queue	SKIP LOCKED	`FOR UPDATE SKIP LOCKED`	12
Cache a heavy report	Materialized view	`REFRESH ... CONCURRENTLY`	13
Query safely from Node	Parameters	`pool.query(sql, [id])`	14
Back up a database	pg_dump	`pg_dump -Fc`	15