DEV Community

Cover image for How Databases Work: From Tables to Query Execution
The Silicon Architect profile image Syed Ahmer Shah
Syed Ahmer Shah for The Silicon Architect

Posted on Originally published at Medium

How Databases Work: From Tables to Query Execution

#ai #webdev #programming #productivity

Introduction: The Library Analogy

Imagine you walk into a massive library with millions of books. You need to find every book written by Stephen King that was published after 2000. If the library had no organizational system, you'd have to physically walk through every shelf, picking up each book to check the author and publication date. That would take forever.

But if the library has a catalog system — an organized, searchable index — you can find what you need in seconds. That catalog system is essentially what a database is. It's not just storing information; it's storing it in a way that makes retrieval incredibly fast and efficient.

Let's dive into how this works, from the ground up.

Part 1: The Foundation — What Databases Actually Are

Before we talk about complex concepts, let's establish what we're dealing with.

A database is an organized collection of structured data. Think of it as a digital filing cabinet where information is arranged in a very specific, deliberate way. But unlike a physical filing cabinet, a database can instantly search through billions of records in milliseconds.

Why Not Just Use Files?

Your computer's file system (the folders and files on your hard drive) could technically store data. Microsoft Excel files, CSV files, JSON files — these all store data. So why do we need databases?

Consider this scenario: You run an e-commerce store with 10 million customers. Every second, hundreds of people are placing orders, updating their profiles, and viewing products.

  • If you stored everything in Excel files, they'd become too large to even open on a normal computer.

  • If two customers try to buy the last item simultaneously, how do you prevent both from getting the same product?

  • If the system crashes while saving, how do you ensure no data is corrupted?

A database management system (DBMS) solves these problems. It's the software that manages how data is stored, retrieved, and modified safely and efficiently. MySQL, PostgreSQL, and MongoDB are all examples of DBMSs.

Relational vs. NoSQL: Two Different Approaches

Relational databases (like PostgreSQL, MySQL) organize data in tables — rows and columns, just like a spreadsheet. Each row represents a single record, and each column represents a property of that record.

NoSQL databases (like MongoDB, Firestore) store data differently — often as documents or key-value pairs. Instead of rigid tables, they're more flexible.

Think of it this way:

  • Relational: A filing system with strictly labeled folders and alphabetically organized documents.

  • NoSQL: A more flexible system where you can store documents however makes sense to you.

For most people starting their journey with databases, relational databases are the standard choice. They're predictable, powerful, and have been refined for decades.

Part 2: Designing a Database — The Blueprint

Let's say you're building that e-commerce platform. Where do you start?

Understanding Relationships

A database isn't just a random pile of tables. Tables are connected in meaningful ways.

In our e-commerce system:

  • A Customer places multiple Orders

  • Each Order contains multiple Products

  • Each Product belongs to a Category

These connections are called relationships, and they're fundamental to database design.

Primary keys uniquely identify each record. For customers, it might be customer_id. Foreign keys create the connection — an Order table would have a customer_id field that references the Customer table.

This is where one-to-many relationships come in. One customer has many orders. To represent many-to-many relationships — where orders have many products and products appear in many orders — you need a junction table. This middle table links them together.

The Art of Normalization

Imagine a spreadsheet where you store everything about orders in one massive table: customer name, address, phone number, order date, product name, product price, and so on. If a customer places 10 orders, their name, address, and phone appear 10 times. That's wasteful and creates problems.

Normalization fixes this. It's the process of breaking data into separate tables and connecting them logically. By separating the Customer table from the Order table, you store each customer's information once. Updates become easier, storage becomes efficient, and data integrity improves.

However, denormalization is sometimes strategically used in the real world. If you frequently query customer names alongside order details, constantly joining two tables might be slow. Copying the customer's name into the order table (denormalization) makes those queries faster — though it introduces redundancy.

The key insight: Good database design is about balance. Structure your data to prevent redundancy and errors, but not so much that querying becomes inefficient.

Part 3: SQL — The Language of Databases

Now that you have a well-designed schema, how do you actually talk to the database?

SQL (Structured Query Language) is the standard language. It's remarkably readable — you can almost understand a query by reading it like English.

The Four Operations: CRUD

Every database interaction falls into four categories:

  • Create: INSERT INTO customers VALUES (1, 'John', 'john@example.com')

  • Read: SELECT * FROM customers WHERE age > 30

  • Update: UPDATE customers SET email = 'newemail@example.com' WHERE customer_id = 1

  • Delete: DELETE FROM customers WHERE customer_id = 1

Getting Smarter: Joins

Here's where SQL becomes powerful. Imagine you want to see every customer's name alongside their orders. That data lives in two separate tables. You need to join them:

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
Enter fullscreen mode Exit fullscreen mode

Different join types answer different questions:

  • INNER JOIN: Only return matches from both tables (customers who have placed at least one order)

  • LEFT JOIN: Return all from the left table, matches from the right (all customers, whether they've ordered or not)

  • RIGHT JOIN: The opposite

  • FULL OUTER JOIN: Everything from both tables

Aggregations and Grouping

What if you want to know how many orders each customer placed? You'd use GROUP BY:

SELECT customers.name, COUNT(orders.order_id) as order_count
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.name
Enter fullscreen mode Exit fullscreen mode

This groups all orders by customer and counts them. Powerful, right?

Part 4: Making Databases Fast — Indexes and Query Execution

Here's the hard truth: As data grows, queries slow down.

You have 100 million customers. A simple query like SELECT * FROM customers WHERE email = 'john@example.com' would normally require checking all 100 million records one by one. That's a full table scan, and it's slow.

This is where indexes save the day.

How Indexes Work

An index is like the back of a textbook. Instead of reading page by page to find mentions of a topic, you flip to the index, find the topic, and jump directly to the relevant pages.

A database index works the same way. If you create an index on the email column, the database builds a data structure (usually a B-tree) that maps email addresses to their corresponding row locations. Now, finding a customer by email is nearly instant — even with 100 million records.

Trade-offs

But indexes aren't free.

  • Storage: An index takes up disk space.

  • Write speed: Every time you insert or update a customer, the database must also update the index on that column.

This is why you don't index everything. You strategically index columns that are frequently searched.

Query Optimization

Most databases include an optimizer — a smart system that figures out the fastest way to execute your query. You can see its plan using EXPLAIN:

EXPLAIN SELECT * FROM customers WHERE email = 'john@example.com'
Enter fullscreen mode Exit fullscreen mode

This shows whether it's using your index (good) or doing a full table scan (bad). If your queries are slow, the optimizer's plan is the first place to look.

Part 5: Transactions — Safety in a Chaotic World

Here's a dangerous scenario: A customer transfers $100 from one account to another.

The database needs to:

  1. Deduct $100 from Account A

  2. Add $100 to Account B

What if the system crashes after step 1 but before step 2? Money disappears.

Transactions prevent this. A transaction is a guarantee: either all steps succeed, or none of them do.

ACID: The Database's Promise

  • Atomicity: The transaction is all-or-nothing.

  • Consistency: The database always stays in a valid state.

  • Isolation: Concurrent transactions don't interfere with each other.

  • Durability: Once committed, data survives crashes.

Locks and Concurrency

When two users modify the same data simultaneously, conflicts arise. Databases use locks to prevent this.

Imagine two customers trying to buy the last item in stock:

  1. Customer A reads: "5 items available"

  2. Customer B reads: "5 items available"

  3. Customer A buys 1: "4 items available"

  4. Customer B buys 1: "4 items available"

Both succeeded, but you only had one item! A lock would prevent this. While Customer A's transaction is active, Customer B would wait for it to complete, see the true inventory count, and act accordingly.

The risk is deadlocks — a circular wait where Transaction A waits for B, and B waits for A. Modern databases detect and resolve these automatically.

Part 6: Real-World Performance Challenges

Understanding databases academically is one thing. Building fast systems is another.

The N+1 Query Problem

Here's a subtle but devastating mistake:

customers = database.query("SELECT * FROM customers")
for customer in customers:
    orders = database.query("SELECT * FROM orders WHERE customer_id = ?", customer.id)
    # process orders
Enter fullscreen mode Exit fullscreen mode

If you have 100,000 customers, this executes 100,001 queries (one to get all customers, then 100,000 to get each customer's orders). This crawls.

The fix? One query with a join, or a more advanced technique like query batching.

Connection Pooling

Every time an application connects to a database, there's overhead. Connection pooling maintains a pool of reusable connections, dramatically improving performance under load.

Pagination and Keyset Pagination

Showing 100,000 rows on a single page is ridiculous. Pagination breaks results into pages. But there are two approaches:

  • Offset pagination (LIMIT 10 OFFSET 200): Simple but slow with large datasets, as the database counts and skips the first 200 rows every time.

  • Keyset pagination: Uses the value of the last row to fetch the next page. Faster at scale, but requires sorted, unique columns.

Database Migrations

Your schema isn't permanent. As your product evolves, you'll add columns, remove fields, or restructure tables. Migrations are scripts that safely transform your schema without losing data.

Caching

Repeatedly querying the same data is wasteful. Caching stores frequently accessed data in memory (using tools like Redis), reducing database load and improving response times.

Part 7: Choosing the Right Database

Not all databases are created equal. Your choice depends on your needs.

Relational Databases

PostgreSQL is incredibly powerful and open-source. It supports complex queries, advanced features (like JSON data types), and scales well. Ideal for most applications.

MySQL is simpler and faster for basic operations, making it popular for web applications.

SQL Server (Microsoft) is feature-rich and widely used in enterprise environments.

NoSQL Databases

MongoDB stores documents (JSON-like objects) instead of rows. Great for unstructured or rapidly changing data, but sacrifices the safety guarantees of relational databases.

Firestore (Google's cloud offering) is serverless and scales automatically, perfect for mobile and web applications where you don't want to manage infrastructure.

The Decision Framework

Ask yourself:

  • Do I need strict consistency? Relational databases are better.

  • Is my data highly structured? Relational.

  • Does my schema change frequently? NoSQL might be better.

  • Do I need complex queries and joins? Relational.

  • Am I building a simple, fast-scaling app? Consider NoSQL.

For our e-commerce example, a relational database like PostgreSQL is the clear choice. The data is structured, relationships are complex, and consistency is critical (you can't have duplicate orders or corrupted inventory counts).

The Mental Model

After understanding all of this, here's what a database fundamentally is:

A database is a disciplined, organized system for storing data in a way that makes retrieval fast, updates safe, and relationships clear.

It prevents data corruption through transactions. It makes queries blazingly fast through indexes. It connects related data through foreign keys. It keeps multiple users from stepping on each other through locks. And it's been refined over decades to handle millions of simultaneous operations reliably.

The complexity you see in real-world databases isn't overengineering — it's the accumulated solution to real problems that arise at scale.

Conclusion: From Theory to Practice

Whether you're building a startup or joining an existing team, databases are the backbone of modern applications. Understanding how they work transforms you from someone who writes queries to someone who understands why their queries are fast or slow.

The journey doesn't end here. Each topic we've covered — normalization, indexing, transaction isolation, optimization — has depth. But with this foundation, you understand the core concepts that everything else builds upon.

Start with PostgreSQL. Design your schema thoughtfully. Write clear queries. Monitor performance. And remember: the best database design is often the simplest one that solves your problem.

References & Sources

  1. C.J. Date. "An Introduction to Database Systems" (11th Edition). Pearson, 2019. — The foundational textbook on relational database theory and design.

  2. Designing Data-Intensive Applications by Martin Kleppmann. O'Reilly Media, 2017. — Essential reading for understanding real-world database challenges, consistency models, and distributed systems.

  3. PostgreSQL Official Documentation. https://www.postgresql.org/docs/ — Comprehensive guide to PostgreSQL features, indexing strategies, and query optimization.

  4. Use The Index, Luke! Markus Winand. — Free online resource specifically about database indexing and query optimization. https://use-the-index-luke.com/

  5. Database Internals by Alex Petrov. O'Reilly Media, 2019. — Deep dive into how databases work internally, covering B-trees, LSM trees, and transaction processing.

  6. ACID Transactions in Distributed Systems. Research papers on MVCC and isolation levels. ACM Transactions on Database Systems. — Academic foundation for understanding transaction handling.

  7. MongoDB Documentation. https://docs.mongodb.com/ — Reference for NoSQL database design patterns and when to use document-based systems.

  8. Google Cloud Firestore Documentation. https://cloud.google.com/firestore/docs — Modern approach to serverless databases and their trade-offs.

  9. Transaction Processing and Recovery in Databases. Ramakrishnan & Gehrke. "Database Management Systems" (3rd Edition). McGraw-Hill, 2003. — Definitive source on how databases handle transactions and ensure durability.

  10. The Anatomy of a Database Query. SQL query optimization techniques as documented by major DBMS vendors including PostgreSQL, MySQL, and SQL Server optimization guides.

Author's Note: This article synthesizes fundamental concepts from decades of database research and industry best practices. The examples and analogies are designed to make these concepts accessible to those encountering databases for the first time, while maintaining technical accuracy grounded in how production systems actually work.

Find me across the web:

Top comments (0)