PostgreSQL Concepts Overview
I've been getting more and more full stack back end interviews lately, and honestly, I've been rusty after working primarily in the front end. This is part of my refresh and study series to brush up on core concepts.
1. Schema design: pull the nouns.
Describe the app in one sentence: "shop owners build menus that customers view." The nouns the app must remember become your tables, drop nouns you don't plan to store: shop owners, shops, categories, items, addons. Break big nouns into parts (a menu is really categories holding items with addons).
shop_owners
id created_at first_name last_name email password 1 2026-08-19 Jane Doe jane@example.com (hashed)
shops (menu)
id owner_id created_at name address 1 1 2026-08-19 Sunrise Coffee 123 Main St
categories
id shop_id created_at name 1 1 2026-08-19 Hot Drinks
items
id category_id created_at name description price 1 1 2026-08-19 Latte Espresso with steamed milk 5.50
addons
id item_id created_at name price 1 1 2026-08-19 Oat milk 1.00
2. Every table gets the same skeleton (personal preference).
Column order: id, foreign keys, created_at, then the actual data.
shops: id, owner_id, created_at, name, address
Tables plural (shops), columns singular, foreign keys named parent_id (shop_id, item_id).
3. Children point up. Parents stay silent.
One shop has many categories. The category stores shop_id; the shops table stores no data about its categories, it has no idea they exist. The connection is made entirely from the child side. To find a shop's categories, search the categories table for rows where shop_id matches the shop's id.
One value = a column. Many things = a new table pointing up.
Primary key = a row's own unique id. Foreign key = a column holding another row's id. The FK column IS the relationship.
4. Joins reach data your table doesn't have.
Items don't know their shop, but their category does. A JOIN temporarily merges each item row with its category row, so the category's shop_id becomes filterable:
SELECT items.*
FROM items
JOIN categories ON items.category_id = categories.id
WHERE categories.shop_id = 'abc';
One query instead of three round trips.
5. Index your foreign keys.
Without an index, finding rows means checking every single row. An index is a sorted copy of one column, searchable by halving: check the middle, too high or too low, throw away half, repeat. A million rows takes ~20 checks instead of a million.
CREATE INDEX ON addons (item_id);
One line, once, auto-maintained forever. WHERE and JOIN almost always search foreign key columns, so index every one.
6. Deletes chain down.
Create top-down (parent before child), delete bottom-up, or set ON DELETE CASCADE so deleting a parent kills all descendants down the chain.
7. Many-to-many needs a junction table.
Actors/movies: neither side can hold the other's id, so a third table stores id pairs, one row per connection: actors_movies (actor_id, movie_id).
8. NULL is an empty cell.
Check it with IS NULL, never = NULL; the latter silently returns zero rows.
9. Any "per X" question is GROUP BY.
SELECT category_id, COUNT(*)
FROM items
GROUP BY category_id;
Collapses rows sharing a value into one row per group. On its own that's just a unique list; usually you add a function like COUNT or SUM to compute a stat per group, which is the point. Output order is random unless you add ORDER BY.
10. SQL executes in a different order than it's written.
FROM → JOIN → WHERE → GROUP BY → SELECT → ORDER BY, even though it's written SELECT-first.
Sources