DEV Community

Mr. S Gupta
Mr. S Gupta

Posted on

PostgreSQL Deep Dive: Tables, Relations, Primary Keys, Foreign Keys, Indexes, and Transactions

In backend development, a database is never just a place to dump data. It's the actual foundation the rest of your application stands on. Get the design wrong, and the application slows down, data starts duplicating itself, bugs creep in, and future changes become genuinely painful. That's exactly why understanding database fundamentals matters so much for anyone building backend systems.

This piece walks through PostgreSQL from the ground up, what a database actually is, what a table is, what rows and columns mean, what primary keys and foreign keys do, how relationships get built, how indexes actually speed things up, and how transactions keep your data safe.

What PostgreSQL actually is

PostgreSQL is a relational database management system, or RDBMS for short. In plain terms, it's software that organizes, stores, and retrieves data for you. If you're building a social media app, you need somewhere to keep users, posts, comments, and likes, and PostgreSQL stores all of that in a structured, predictable way.

A database itself is just an organized collection where all of an application's data lives. A company's database might hold users, products, orders, and payments, all sitting as separate tables inside one database.

Tables, columns, and rows

In SQL databases, data lives inside tables. Think of a users table.

+----+---------+-------------------+
| id | name    | email             |
+----+---------+-------------------+
| 1  | Rahul   | rahul@test.com    |
| 2  | Aman    | aman@test.com     |
+----+---------+-------------------+
Enter fullscreen mode Exit fullscreen mode

You can think of a table pretty much like a spreadsheet. Each column represents one type of data, id, name, email, password, created_at, each with its own clear purpose. Each row, meanwhile, is one complete record, everything belonging to a single entry, like one specific user's full set of details.

Creating a table looks like this.

CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

That gives you three columns, id, name, and email. Adding data into it is just as direct.

INSERT INTO users
(name, email)
VALUES
('Rahul', 'rahul@test.com');
Enter fullscreen mode Exit fullscreen mode

And pulling it back out is a simple select.

SELECT *
FROM users;
Enter fullscreen mode Exit fullscreen mode

Primary keys, the thing that makes every row unique

A primary key is a column that uniquely identifies every single row in a table. Every user gets their own id, 1, 2, 3, and so on, and that id follows a few strict rules, it can never repeat, and it can never be null. It's there purely to identify each record without any ambiguity.

Two rows sharing the same id, like two rows both marked id 1, would be a genuine problem. Two users can absolutely share the same name, plenty of people are named Rahul, but their ids will always be different, and that's exactly the point. Names can collide, ids never should.

Foreign keys, and how relationships actually form

A foreign key is what creates a relationship between two tables. Say you have a users table and an orders table.

users

id
name

orders

id
user_id
amount
Enter fullscreen mode Exit fullscreen mode

Here, orders.user_id references users.id, and that reference is the foreign key. So if Rahul has id = 1, and an order exists with user_id = 1, that combination is really just saying "this order belongs to Rahul."

The most common relationship pattern by far is one-to-many, one user having multiple orders. A one-to-one relationship looks a bit different, think a user and their profile, where each user has exactly one profile record sitting in a separate table.

users

id
name

profiles

id
user_id
bio
Enter fullscreen mode Exit fullscreen mode

And then there's many-to-many, like students and courses, where one student can enroll in several courses, and one course can have many students enrolled. This kind of relationship typically needs an extra table in between, often called an enrollment or junction table, sitting right between students and courses to hold all those individual connections.

Bringing multiple tables together with JOIN

Once your data is spread across separate tables, the natural next question is, how do you actually pull related data back together? That's exactly what SQL's JOIN does, combining related data that lives across two or more tables.

Take a users table and an orders table.

users

id | name
1  | Rahul
2  | Aman

orders

id | user_id | amount
101 | 1 | 500
102 | 1 | 900
103 | 2 | 300
Enter fullscreen mode Exit fullscreen mode

If you want to know which user placed which order, an INNER JOIN gets you there by returning only the rows where a match actually exists.

SELECT
users.name,
orders.amount
FROM users
INNER JOIN orders
ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

That gives you Rahul matched with both his orders, and Aman matched with his one order, nothing more, nothing less, only where the relationship genuinely exists.

A LEFT JOIN behaves differently, it keeps every single row from the left table regardless of whether a match exists on the other side. Say a third user, Rohit, has never placed an order. A LEFT JOIN would still show Rohit in the results, just with a null amount next to his name, rather than dropping him entirely.

SELECT
users.name,
orders.amount
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

RIGHT JOIN is essentially the mirror image of that, keeping every row from the right table instead, though in practice LEFT JOIN shows up far more often. FULL JOIN goes further still, returning everything from both tables, matched or not.

A practical example, showing every product in a catalog and including order info wherever it exists, looks like this.

SELECT
products.name,
orders.quantity
FROM products
LEFT JOIN orders
ON products.id = orders.product_id;
Enter fullscreen mode Exit fullscreen mode

JOINs matter because real applications genuinely spread their data across many tables, users, orders, products, payments, reviews, and so on, and stuffing all of that into one giant table would cause far more problems than it solves.

Why data gets split apart in the first place: normalization

Normalization is really just the practice of organizing your database properly so duplicate data doesn't pile up. Picture a poorly designed orders table that tries to hold everything at once, order id, customer name, customer email, product name, product price, all crammed into one place. If Rahul places a hundred orders, his name and email get repeated a hundred times over, which is wasteful and genuinely fragile.

A properly normalized design splits that apart instead, a users table holding id, name, and email, a products table holding id, name, and price, and an orders table that simply references both through user_id and product_id. Now duplicate data is minimal.

Normalizing your schema this way cuts down on repeated data significantly, makes updates far simpler (changing a user's email means updating one row in one table instead of a hundred scattered copies), and keeps a single, reliable source of truth for every piece of information.

That said, normalization isn't a rule to follow blindly everywhere. Real systems balance it against performance needs, and sometimes data does get duplicated on purpose for speed, which is called denormalization. An orders table that stores a customer_name field directly, even though that's technically duplicated from the users table, might genuinely help order history load faster. It's a deliberate trade-off, not a mistake, when it's used with intention.

When actually designing a database for something like a food delivery app, the process usually starts with identifying the core entities, users, restaurants, food items, orders, payments, deliveries, then figuring out how they relate to each other (a user places orders, an order pulls from a restaurant's food items), and only then translating that into actual tables. A common beginner mistake is skipping straight to one giant table trying to hold everything, users, orders, products, and payments all mashed together, which causes duplicated data, painfully complex queries, and genuinely risky updates down the line.

Indexes: why some queries are fast and others aren't

At some point, every backend developer runs into the same question. Why is this query slow, and how do you actually make it faster? A big part of the answer is indexes.

An index is essentially a shortcut that helps the database find data quickly, without needing to look through every single row. Think of a massive book. Looking for the word "Database" by checking page 1, then page 2, then page 3, all the way to page 500, would take forever. But if the book has an index at the back pointing straight to page 120, you skip right to it. Database indexes work exactly the same way.

Without an index, if you search a users table by email and there's no index on that column, the database checks every single row one by one, something called a full table scan. That's a non-issue on a table with a hundred rows, but on a table with ten million rows, it turns into a genuinely slow query, higher CPU usage, and a noticeably worse response time.

Adding an index changes that completely.

CREATE INDEX email_index
ON users(email);
Enter fullscreen mode Exit fullscreen mode

Now the database can jump straight to matching rows instead of scanning everything.

Primary keys actually get an index automatically the moment you define them, precisely because they tend to get searched constantly.

CREATE TABLE users(
  id SERIAL PRIMARY KEY
);
Enter fullscreen mode Exit fullscreen mode

A query like SELECT * FROM users WHERE id=10; needs to be fast, and that automatic index is exactly why it usually is.

Unique constraints work similarly. Marking a column like email as UNIQUE makes PostgreSQL build a unique index behind the scenes, which does double duty, speeding up searches and preventing duplicate values at the same time. And you can create ordinary indexes yourself wherever a column gets searched frequently, like an index on email for a users table that gets looked up by email a lot.

Sometimes a query depends on more than one column at once. Say you're filtering orders by both user_id and status together.

SELECT *
FROM orders
WHERE user_id=10
AND status='completed';
Enter fullscreen mode Exit fullscreen mode

For that, a composite index covering both columns together makes sense.

CREATE INDEX order_search_idx
ON orders(user_id, status);
Enter fullscreen mode Exit fullscreen mode

The order of columns in a composite index actually matters here too. An index built as (user_id, status) helps a query filtering just on user_id, and helps even more when filtering on both user_id and status together, but it won't help nearly as much for a query that only filters on status alone.

It's tempting to think, why not just index every column and make everything fast? The catch is that indexes cost storage space, and they slow down writes. Every time a new row gets inserted, the database has to update every relevant index alongside inserting the actual data, so indexes genuinely trade off read speed against write speed. They're a clear win for search-heavy applications, but worth using carefully in systems doing heavy, constant data entry.

PostgreSQL also has something called a query planner running behind the scenes, whose whole job is figuring out the fastest way to actually execute a given query, whether that's scanning the full table or using an available index, whichever turns out faster. Running EXPLAIN in front of a query shows you exactly what the planner decided, something like "Index Scan using email_index," confirming the index is actually getting used.

A real-world example worth picturing, an Amazon-style product search across millions of products by name would be painfully slow without an index like this in place.

CREATE INDEX product_name_idx
ON products(name);
Enter fullscreen mode Exit fullscreen mode

It's worth keeping indexes and caching mentally separate too, since they're solving related but distinct problems. An index lives inside the database itself, helping it find rows faster. A cache, something like Redis, typically sits outside the database entirely, storing frequently requested results so the application doesn't even need to hit the database again for the same thing. Beyond indexing, a few other habits genuinely help production performance: selecting only the specific columns you actually need instead of always reaching for SELECT *, paginating large result sets with LIMIT and OFFSET instead of pulling everything at once, pooling database connections instead of creating new ones constantly, and caching data that gets requested repeatedly.

Transactions: how data actually stays safe

Real applications don't just save data, they need to keep it safe, consistent, and correct even when things go wrong halfway through. That's exactly the problem transactions solve.

A transaction is a group of database operations that executes as one single unit, either every operation in it completes, or none of them do.

A bank transfer is the textbook example here. Moving 500 rupees from Rahul's account to Aman's account genuinely involves two separate steps, deducting from Rahul's balance, then adding to Aman's. If the first step succeeds and the second one fails for whatever reason, Rahul's money would just vanish without Aman ever receiving it, which is obviously unacceptable.

A transaction wraps both steps together so either both succeed and get committed, or if anything goes wrong, everything gets rolled back to exactly where it started.

BEGIN;

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

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

COMMIT;
Enter fullscreen mode Exit fullscreen mode

And if something fails partway through, running ROLLBACK undoes everything cleanly, leaving the database exactly as it was before the transaction started.

Understanding transactions properly really comes down to ACID, four properties that define what a reliable transaction actually guarantees.

Atomicity means the transaction completes fully or fails entirely, there's no such thing as a half-finished transaction sitting around, like Rahul losing money while Aman's balance never actually updates.

Consistency means the database always stays in a genuinely valid state before and after any transaction, none of its underlying rules ever get violated in the process.

Isolation deals with multiple things happening at once, say two people trying to buy the last unit of a product in stock at the exact same moment. The database needs to handle that scenario correctly, and isolation is precisely what prevents one transaction from stepping on another mid-operation.

Durability means once a transaction actually commits successfully, that data is genuinely permanent. A payment going through should still be there even if the server crashes and restarts moments later.

It's worth knowing you can technically try to handle something like this at the application level instead, running one operation, then another, in your own code. The problem is obvious once you think it through, if the second operation fails, how do you cleanly undo the first one yourself? That's exactly the gap database-level transactions exist to close.

Modern ORMs make this pretty approachable too. In Prisma, wrapping operations in a transaction looks like this.

await prisma.$transaction([
  prisma.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 500 } }
  }),
  prisma.account.update({
    where: { id: 2 },
    data: { balance: { increment: 500 } }
  })
]);
Enter fullscreen mode Exit fullscreen mode

If either update fails, both roll back together. Drizzle follows the same underlying concept with slightly different syntax.

await db.transaction(async (tx) => {
  await tx.update(accounts)
    .set({ balance: balance - 500 });

  await tx.update(accounts)
    .set({ balance: balance + 500 });
});
Enter fullscreen mode Exit fullscreen mode

Since multiple transactions can genuinely run at the same time, PostgreSQL also defines isolation levels that control exactly how much transactions are allowed to see of each other's in-progress work. Read Uncommitted is the loosest level, technically allowing uncommitted data from other transactions to be read. Read Committed, PostgreSQL's default, only ever shows data that's actually been committed. Repeatable Read guarantees that the same query run twice inside one transaction returns the exact same result both times. And Serializable, the strictest level, makes concurrent transactions behave as though they ran one after another in strict sequence, never actually overlapping.

These levels exist specifically to prevent a handful of classic problems. A dirty read happens when one transaction reads data another transaction changed but hasn't committed yet, and if that first transaction then rolls back, the second one ends up having read data that never actually existed. A non-repeatable read happens when the same query inside one transaction returns different results the second time around, say a balance reading 5000 initially and 4000 moments later, because some other transaction quietly changed it in between. And a phantom read happens when the same query suddenly starts returning entirely new rows that weren't there before, again because of some other transaction's changes landing in between reads.

A concrete e-commerce example ties this all together nicely. Someone clicks "Buy Now," and the backend needs to create the order, reduce inventory, and save a payment record, all as one unit. If the payment step fails, inventory absolutely should not have been reduced. Wrapping the whole sequence, create order, reduce stock, save payment, inside a single transaction with a clean rollback on failure is exactly how that gets handled correctly.

Transactions genuinely matter whenever multiple operations depend on each other, banking transfers, e-commerce orders that touch payment and inventory together, ticket booking that reserves a seat and processes payment together, or wallet systems updating a balance alongside its transaction history. They're not something every tiny query needs though, a plain SELECT * FROM users; has no real need to be wrapped in a transaction. And it's worth keeping transactions short and quick, since long-running transactions can hold database locks for extended periods, quietly hurting performance and blocking other users in the process.

What separates production PostgreSQL from just knowing SQL

Everything covered so far, tables, relations, joins, normalization, indexes, transactions, forms a genuinely solid foundation. But being a production-ready backend developer takes a bit more than just knowing how to write queries. It means actually knowing how to design, secure, and optimize a database properly.

Schema design is really where it all starts, figuring out your database's actual structure. Building a SaaS product means identifying entities like users, organizations, teams, projects, tasks, and payments, then deciding what tables you need and how they relate. A user creating many projects, for instance, becomes a straightforward one-to-many relationship between a users table and a projects table referencing it through user_id.

Choosing the right data types matters more than it might seem at first. PostgreSQL gives you integers for numbers, text for strings, booleans for true or false flags, timestamps for dates and times, and importantly, JSON support through the JSONB type, letting you store genuinely flexible structured data like user settings directly inside a column. That last one's worth remembering, PostgreSQL isn't limited to rigid traditional tables alone, it happily blends in flexible JSON data where it makes sense.

Constraints are the rules that stop bad data from ever getting saved in the first place. NOT NULL makes a field mandatory, so an email column can't end up empty. UNIQUE prevents duplicates, ensuring two users can't share the same email. CHECK lets you enforce custom rules, like requiring age to be at least 18. And foreign key constraints protect relationships directly, refusing to save a user_id that doesn't actually correspond to a real user.

Security deserves real attention too. Passwords should never sit in a database as plain text, ever, since a leak would expose every single one instantly. They should be hashed instead, using something like bcrypt or argon2, so what actually gets stored is unreadable without the original password. SQL injection is another real risk worth guarding against, string-concatenating user input directly into a query is dangerous, and parameterized queries are the safer way to go. Most modern ORMs like Prisma and Drizzle handle this protection for you automatically, which is one more reason they're so widely used.

Migrations matter because production schemas genuinely change over time, adding a phone column to an existing users table, for instance. Manually editing a live production database directly is risky, which is exactly why migrations exist, tracked, versioned changes like ALTER TABLE users ADD COLUMN phone TEXT; that keep a clear history of every structural change made along the way.

Backups are non-negotiable too, protecting against server crashes, accidental deletions, or data corruption, whether through full backups of the entire database or incremental backups capturing just what's changed.

Connection pooling avoids the real cost of spinning up a brand new database connection for every single request, reusing a small set of existing connections instead. And larger applications sometimes split reads and writes entirely, sending writes to a main database and reads to a replica, spreading load and improving overall performance.

Monitoring rounds all of this out, keeping an eye on query time, CPU usage, memory usage, active connections, and slow queries, since production issues tend to show up as patterns in these metrics long before they become outright failures.

A typical real backend request, someone signing up, ties all of this together, the request gets validated, the password gets hashed, the user gets inserted, and a response goes back, with an ORM sitting between your backend code and PostgreSQL the whole way through.

Where all of this leaves you

If you're actually learning PostgreSQL properly, a reasonable order looks something like this. Start with the basics, tables, rows, columns, and the core CRUD operations, select, insert, update, delete. Move into relationships next, primary keys, foreign keys, and the one-to-one, one-to-many, and many-to-many patterns. From there, get comfortable with more advanced SQL, joins, group by, having, subqueries, views, and functions. Then focus on performance, indexes, the query planner, EXPLAIN, and general optimization. And finally, round it out with production concerns, transactions, migrations, backups, security, and replication.

A common modern stack pairs all of this with TypeScript, Node.js, Express or Fastify, and an ORM like Prisma or Drizzle sitting on top of PostgreSQL, with a request flowing from the API through a controller, into a service, through Prisma, and finally down into PostgreSQL itself.

PostgreSQL, at the end of the day, isn't just a database. It's a genuinely complete data management system. A strong backend developer understands how to actually design data properly, how to build relationships that make sense, how to optimize queries when they need it, and how to keep data safe under real conditions. Choosing which database to use matters, sure, but designing it well matters even more. Once these fundamentals are genuinely solid, picking up PostgreSQL, MySQL, MongoDB, or whatever comes next becomes far less intimidating, because a database is really just a tool. The actual skill worth building is understanding a problem deeply enough to design the right data structure for it.

Top comments (0)