PostgreSQL & Database Essentials for Backend Developers

A PostgreSQL JOIN combines rows from two tables wherever a condition matches, usually a foreign key equal to a primary key. INNER JOIN keeps only rows that match on both sides; LEFT JOIN keeps every row from the left table and fills the right side with NULL when nothing matches; RIGHT JOIN does the same for the right table, FULL OUTER JOIN for both, and CROSS JOIN pairs every row with every row. This post builds a small users-posts-comments schema and goes through the parts you'll use every week: keys, joins (with the actual result rows), indexes and transactions.

What a relational database actually is

PostgreSQL stores data in tables made of rows and columns. A table is a structured collection of rows, a row is one record (one user, one order), and a column is one attribute of that record (email, created_at). SQL is the language you use to both define that structure and query it. When your backend sends SELECT * FROM users WHERE id = 1, Postgres parses the query, plans the most efficient way to execute it, reads the relevant data from memory or disk, applies your filters, and returns the result, usually in milliseconds.

Creating a table

CREATE TABLE defines the shape of your data upfront: column names, data types, defaults, and constraints. A users table might have an auto-incrementing id as PRIMARY KEY, a full_name that's NOT NULL, an email marked UNIQUE so no two users can share one, and a created_at that defaults to CURRENT_TIMESTAMP so you don't have to set it manually on every insert. Choosing the right data type matters: INTEGER for whole numbers, VARCHAR(n) for text with a length limit, TEXT for text with no limit (in Postgres the two perform the same; the n in VARCHAR(n) is only a length check), NUMERIC(p,s) for exact decimals like money (never use floating point for currency), and JSONB for data that's genuinely semi-structured.

CRUD: the four operations everything builds on

Create, Read, Update, Delete map directly onto INSERT, SELECT, UPDATE, and DELETE. INSERT INTO users (full_name, email) VALUES (...) adds a row. SELECT * FROM users WHERE is_active = TRUE reads rows matching a condition. UPDATE users SET age = 30 WHERE email = '...' modifies existing rows. DELETE FROM users WHERE email = '...' removes them. There's also TRUNCATE, which wipes every row in a table almost instantly, much faster than DELETE for clearing an entire table, but with no per-row filtering, so use it carefully.

Primary keys, foreign keys, and how tables connect

A primary key uniquely identifies each row in a table — users.id, for instance. A foreign key is a column in one table that references the primary key of another, which is how relational databases connect data across tables without duplicating it everywhere. If posts.user_id references users(id), Postgres physically refuses to insert a post for a user_id that doesn't exist. That's the foreign key constraint protecting your data's integrity at the database level, not just in application code where it's easy to forget a check. Adding ON DELETE CASCADE means deleting a user automatically deletes their posts too, instead of leaving orphaned rows behind.

CREATE TABLE posts (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- use BIGINT if it may outgrow ~2 billion rows
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  body TEXT,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP -- timestamptz stores an exact moment, not a local clock reading
);

-- Postgres rejects this because user_id 999 doesn't exist in users:
INSERT INTO posts (user_id, title) VALUES (999, 'Orphaned post');
-- ERROR: insert or update on table "posts" violates foreign key constraint

PostgreSQL joins: querying across tables

Real applications almost never keep everything in one table. You'll write a JOIN in almost every real query, so it's worth getting comfortable with early. An INNER JOIN between posts and users on posts.user_id = users.id returns only posts that have a matching author. A LEFT JOIN returns every row from the left table regardless of whether a match exists on the right. That's useful for something like listing every user's post count, including users with zero posts, which an INNER JOIN would silently exclude. Combine that with COUNT() and GROUP BY and you get a reporting-style query almost for free.

-- INNER JOIN: only posts that have a matching author
SELECT posts.title, users.full_name
FROM posts
INNER JOIN users ON posts.user_id = users.id;

-- LEFT JOIN: every user, including ones with zero posts
SELECT users.full_name, COUNT(posts.id) AS post_count
FROM users
LEFT JOIN posts ON posts.user_id = users.id
GROUP BY users.id, users.full_name
ORDER BY post_count DESC;

Note the GROUP BY users.id, users.full_name. Grouping by name alone would merge two users who are both called Arun into one row with a combined post count.

One small dataset for every join type

Two tables are enough to see every join. Kiran has no posts, and one imported post has no author: its user_id is NULL, which a foreign key allows. Users also have an optional referred_by pointing at another user, for the self join. Run this in an empty database and try each query; the results under each one are what PostgreSQL 18 returned when I ran them. The full syntax is in the PostgreSQL docs on table expressions and joined tables.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  referred_by INTEGER REFERENCES users(id)
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  title TEXT NOT NULL
);

INSERT INTO users (id, full_name, referred_by) VALUES
  (1, 'Arun', NULL),
  (2, 'Meera', 1),
  (3, 'Kiran', 2);

INSERT INTO posts (id, user_id, title) VALUES
  (10, 1, 'Indexing basics'),
  (11, 1, 'Joins explained'),
  (12, 2, 'JSONB tips'),
  (13, NULL, 'Imported post');

INNER JOIN: only rows that match

Kiran (no posts) and the imported post (no author) both disappear. A bare JOIN is an inner join, because INNER is the default.

SELECT u.full_name, p.title
FROM users u
INNER JOIN posts p ON p.user_id = u.id
ORDER BY p.id;

-- full_name | title
-- ----------+-----------------
-- Arun      | Indexing basics
-- Arun      | Joins explained
-- Meera     | JSONB tips
-- (3 rows)

LEFT JOIN: every row from the left table

Kiran comes back, with NULL where the post would be. This is the join for "all users, plus their posts if they have any", and the one behind the post-count query above.

SELECT u.full_name, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
ORDER BY u.id, p.id;

-- full_name | title
-- ----------+-----------------
-- Arun      | Indexing basics
-- Arun      | Joins explained
-- Meera     | JSONB tips
-- Kiran     | NULL
-- (4 rows)

RIGHT JOIN: every row from the right table

Now the imported post appears with no author and Kiran is gone. A RIGHT JOIN is a LEFT JOIN with the tables swapped, and most people write FROM posts p LEFT JOIN users u instead, because it reads left to right.

SELECT u.full_name, p.title
FROM users u
RIGHT JOIN posts p ON p.user_id = u.id
ORDER BY p.id;

-- full_name | title
-- ----------+-----------------
-- Arun      | Indexing basics
-- Arun      | Joins explained
-- Meera     | JSONB tips
-- NULL      | Imported post
-- (4 rows)

FULL OUTER JOIN: everything from both sides

Matched rows, plus the user with no posts, plus the post with no user. It's the join for reconciliation work, like comparing two lists and finding what's missing on either side.

SELECT u.full_name, p.title
FROM users u
FULL OUTER JOIN posts p ON p.user_id = u.id
ORDER BY u.id, p.id;

-- full_name | title
-- ----------+-----------------
-- Arun      | Indexing basics
-- Arun      | Joins explained
-- Meera     | JSONB tips
-- Kiran     | NULL
-- NULL      | Imported post
-- (5 rows)

CROSS JOIN: every combination

There's no ON clause: every row on the left is paired with every row on the right. 3 users and 2 sizes give 6 rows. Two tables of 10,000 rows give 100 million, so on real tables a CROSS JOIN is usually a mistake unless one side is tiny, like a list of sizes or dates.

SELECT u.full_name, s.size
FROM users u
CROSS JOIN (VALUES ('S'), ('M')) AS s(size)
ORDER BY u.id, s.size DESC;

-- full_name | size
-- ----------+-----
-- Arun      | S
-- Arun      | M
-- Meera     | S
-- Meera     | M
-- Kiran     | S
-- Kiran     | M
-- (6 rows)

Self join: a table joined to itself

Give the same table two aliases and join them like two different tables. Hierarchies work the same way: employees and managers, categories and parent categories. Using LEFT JOIN keeps Arun, whom nobody referred.

SELECT u.full_name AS user_name, r.full_name AS referred_by
FROM users u
LEFT JOIN users r ON u.referred_by = r.id
ORDER BY u.id;

-- user_name | referred_by
-- ----------+------------
-- Arun      | NULL
-- Meera     | Arun
-- Kiran     | Meera
-- (3 rows)

ON vs WHERE in a LEFT JOIN

The most common LEFT JOIN bug is filtering the right table in WHERE. WHERE runs after the join, so the NULL rows the LEFT JOIN added fail the condition and get thrown away, and you're back to an inner join. Put conditions on the right table inside ON instead.

-- Wrong: Kiran disappears, because NULL LIKE 'J%' is not true
SELECT u.full_name, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE p.title LIKE 'J%'
ORDER BY u.id;
-- Arun  | Joins explained
-- Meera | JSONB tips

-- Right: the filter is part of the join, so every user stays
SELECT u.full_name, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id AND p.title LIKE 'J%'
ORDER BY u.id;
-- Arun  | Joins explained
-- Meera | JSONB tips
-- Kiran | NULL

-- Users with no posts at all (an anti-join). NOT EXISTS returns the same.
SELECT u.full_name
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE p.id IS NULL;
-- Kiran

Two more traps. With a LEFT JOIN, count a column from the right table, COUNT(p.id), not COUNT(*): COUNT(*) counts the NULL-filled row, so Kiran shows 1 post instead of 0. And when the join columns have the same name in both tables (say both had a user_id column), USING (user_id) is a fine shorthand for the ON equality, but avoid NATURAL JOIN: it joins on every column name the two tables share, so adding a column like created_at to both later silently changes what the query does.

JOIN performance: index the foreign key column

PostgreSQL always has an index on the referenced side of a foreign key (users.id is a primary key), but declaring the foreign key does not create an index on the referencing column, posts.user_id. That missing index is the most common reason a join is slow. On a test table with 500,000 posts, fetching one user's posts planned as a Nested Loop over a Seq Scan on posts, with an estimated cost of about 9,400. After adding the index, the same query used a Bitmap Index Scan and the estimate dropped to about 51.

-- A foreign key doesn't index posts.user_id for you
CREATE INDEX idx_posts_user_id ON posts(user_id);

EXPLAIN
SELECT u.full_name, p.title
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.id = 42;

-- Before the index: Nested Loop -> Seq Scan on posts p (Filter: user_id = 42)
-- After the index:  Nested Loop -> Bitmap Index Scan on idx_posts_user_id

EXPLAIN also shows which of the three join strategies the planner picked. A nested loop scans the inner side once per outer row, which is fast when that lookup uses an index. A hash join loads one side into a hash table and scans the other once. A merge join sorts both sides on the join key and walks them together. An index doesn't always win: a report joining all 50,000 users to all their posts read both tables in full with a hash join even with the index in place, because it needs every row anyway. You may also see a LEFT JOIN shown as Hash Right Join in a plan. The planner chose to hash the other table; the result is the same.

Indexes: the difference between fast and slow at scale

Without an index, Postgres has to scan every row in a table to find matches. That's fine at a thousand rows and painfully slow at ten million. An index is a separate, ordered structure that lets Postgres jump straight to matching rows instead. The three types you'll meet first:

• –B-tree: the default. Handles equality and range comparisons well (WHERE age > 25).
• –GIN: built for JSONB, arrays, and full-text search.
• –BRIN: suits very large, naturally ordered tables like time-series logs.

The rule of thumb: index columns that show up often in WHERE, JOIN, and ORDER BY clauses, but don't index everything. Every index slows down writes, since Postgres has to update it on inserts and on most updates. Put EXPLAIN ANALYZE in front of a slow query to see whether Postgres is using an index or scanning the whole table. It actually runs the query, so wrap an UPDATE or DELETE in BEGIN ... ROLLBACK before you analyze it. It's the most useful debugging habit for slow queries.

CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Check whether Postgres actually uses it:
EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 42;

-- Look for "Index Scan using idx_posts_user_id" (or "Bitmap Index Scan") in the output.
-- "Seq Scan" means it's reading every row instead. On a small table that's normal:
-- reading a few pages directly is cheaper, so Postgres skips the index on purpose.

Transactions: all or nothing

A transaction groups several operations into one unit: either everything succeeds and gets COMMITted, or anything fails and the whole thing ROLLBACKs, leaving the database exactly as it was before. The textbook example is a bank transfer. Subtracting 500 from one account and adding 500 to another has to happen together, because if the server crashes between the two statements without a transaction wrapping them, money silently disappears. Wrap both UPDATEs in BEGIN ... COMMIT, and Postgres guarantees that can't happen.

BEGIN;

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

COMMIT;

-- If anything between BEGIN and COMMIT fails, run ROLLBACK instead,
-- and both updates are undone as if they never happened.

Underneath, Postgres uses MVCC (Multi-Version Concurrency Control) to let readers and writers work at the same time without blocking each other or seeing half-finished changes. Two writers updating the same row still wait for each other. Isolation levels control exactly how much one transaction can see of another's in-progress work: READ COMMITTED (the default) sees only committed data as of each statement, REPEATABLE READ reads from one consistent snapshot for the whole transaction, and aborts with a serialization error if another transaction has already changed a row it tries to update, and SERIALIZABLE is the strictest, sometimes aborting a transaction outright if it detects a conflicting pattern. Under either of these two levels, your application code needs to be ready to retry.

Normalization vs. denormalization

Normalization means splitting data into separate related tables to avoid duplication: customers, products, orders, and order_items as four tables instead of stuffing customer name and product price into every single order row. It makes updates safer: change a customer's email once, in one place, instead of hunting down every order row that copied it. Denormalization is the deliberate opposite: storing a precomputed post_count on the users table, for instance, so you don't have to COUNT() and JOIN every time you render a profile. It trades faster reads for more complex writes and a real risk of that stored number quietly going stale if you forget to update it somewhere. Most real schemas are normalized by default, with denormalization added surgically where a specific read path is measurably too slow.

Relationship types you'll model constantly

• –One-to-one: a user and their profile. Use a foreign key plus a unique constraint.
• –One-to-many: a user and their posts. Use a plain foreign key on posts.user_id.
• –Many-to-many: students and courses. Use a join table (student_courses) with a composite primary key of both foreign keys.

JSONB and full-text search: where Postgres goes beyond "just a relational database"

JSONB stores JSON in a decomposed binary form rather than as plain text, which means you can index it (with a GIN index) and query into it directly: WHERE settings @> '{"theme":"dark"}' finds every row whose settings JSON contains that key-value pair. It's the right tool for flexible, semi-structured data, like user preferences, without needing a separate document database for it. Postgres also has native full-text search built in: converting text into a searchable vector with to_tsvector and matching it against a query with plainto_tsquery is enough for a lot of search needs (a help center, an admin search box, tag-based blog search) before you'd reach for something heavier like Elasticsearch.

-- JSONB: find users whose settings contain theme: dark
SELECT full_name FROM users
WHERE settings @> '{"theme": "dark"}';

CREATE INDEX idx_users_settings ON users USING GIN (settings);

-- Index the expression, or every search scans the whole table
CREATE INDEX idx_posts_body_fts ON posts USING GIN (to_tsvector('english', body));

-- Full-text search: find posts matching a search phrase
SELECT title FROM posts
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'database indexing');

The four-layer mental model

A backend developer ends up thinking about a database in four layers at once: correctness (constraints, keys, and transactions protecting data integrity), querying (joins, filtering, grouping producing useful results), performance (indexes and query plans determining how fast that happens at scale), and modeling (normalized schemas and relationship design shaping how maintainable the whole thing stays as it grows). Get comfortable with each layer roughly in that order — correctness and querying first, performance and deep modeling once the fundamentals feel automatic.

Common questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table, with NULLs in the right table's columns where there's no match.

Is JOIN the same as INNER JOIN in PostgreSQL?

Yes. INNER is the default, so JOIN posts ON ... is an inner join. LEFT, RIGHT and FULL make it an outer join, and the word OUTER is optional.

Does PostgreSQL automatically index foreign keys?

Only the referenced side, which must be a primary key or unique and so already has an index. The referencing column gets no index unless you create one, and you usually should if you join on it or delete parent rows.

Why does my LEFT JOIN return fewer rows than expected?

Almost always a WHERE condition on a column from the right table, which removes the NULL rows the LEFT JOIN added. Move that condition into the ON clause.

When do I need a transaction in PostgreSQL?

Whenever two or more writes must succeed or fail together, like moving money between accounts or creating an order with its line items. A single statement is already atomic on its own.

What to read next

Start by modeling a small real feature end to end, like users, posts and comments, and you'll hit almost every concept here in the order it matters. Next in the series: NestJS + Sequelize, which maps these tables onto TypeScript classes. For where PostgreSQL fits next to Redis, queues and search, see how I'm learning backend development. I'm a frontend engineer who builds the UI on top of APIs and databases like this one. If you need that, see my work or get in touch.