How Databases Store and Retrieve Data

The short answer

A database gives an application a controlled way to keep data beyond one request or process. The application sends a query or command. The database interprets it, chooses how to find or change the relevant data, applies integrity and transaction rules, and returns a result.

The details depend on the database. This lesson uses relational systems—especially PostgreSQL—for the internal flow, then contrasts document modeling where useful. It is a durable starting model, not a claim that every database stores bytes or executes queries in the same way.

Why applications need persistence

Variables in a running backend process are temporary. The process can restart, several instances can handle different requests, and memory is limited. An application needs persistent storage when user accounts, orders, messages, or other state must survive beyond one process lifetime.

Persistence alone is not enough. Applications also need to:

• find records without reading everything into application memory;
• prevent invalid or conflicting data;
• coordinate related changes;
• support concurrent callers;
• recover from failures;
• control which operations are allowed.

A database management system provides these services around stored data. Its exact guarantees depend on the product, configuration, data model, and operation.

Database versus database server

The word database is used loosely. It can mean an organized collection of data or a named logical database managed by software. A database server is the software process or system that accepts requests, manages data and metadata, coordinates access, and interacts with storage.

PostgreSQL documents a client/server model in which a server process manages database files, accepts client connections, and performs operations on behalf of clients.PostgreSQL is one implementation of this general boundary.¹ The backend application is a database client even when both processes run on the same machine.

A database is therefore not “just a file,” although files, pages, logs, and other storage structures may exist below the database server. Bypassing the server to edit those structures would also bypass the rules that make them meaningful and consistent.

Data models

A data model defines how information is represented and related. It influences which queries are natural, which constraints the database can enforce, and which changes are easy or expensive.

Relational databases organize data around relations commonly exposed as tables. Document databases organize records as documents grouped into collections. Key-value, graph, time-series, search, and analytical systems use other models.

The model is not merely a file format. It includes operations, constraints, and consistency behavior. “Relational versus document” is a design choice shaped by access patterns and guarantees; neither universally replaces the other.

Relational tables and rows

A relational table has named columns and rows. Columns have declared data types; each row supplies values for the columns. PostgreSQL's table documentation describes a fixed set of named columns with a variable number of rows.Specific relational systems add their own types and storage behavior.²

An illustrative table shape might be:

users
- id
- email
- created_at

A query can select rows whose email matches a value. A relationship can connect an order's user_id to a user's id.

SQL is a language used to define and work with relational data; it is not a database product. PostgreSQL and MySQL are separate database-management systems that support SQL.MySQL's official documentation describes it as a database-management system and explains its SQL role.¹¹

Document collections and documents

A document database stores records that can contain nested fields and arrays. MongoDB groups BSON documents into collections and supports embedding or referencing related data based on the model.MongoDB's data-modeling documentation emphasizes application access patterns and relationships.⁹

Here is an illustrative JSON representation of a user document:

{
  "_id": "user-42",
  "email": "learner@example.com",
  "createdAt": "2026-09-24T10:00:00Z"
}

example.com is reserved by IANA for documentation, so the address is illustrative rather than a real user's contact.IANA maintains the example-domain reservation.¹²

A document can keep related values together, but that does not remove structure. Field names, value types, validation rules, indexes, and application expectations still form a schema, whether the database enforces all of it or not. “NoSQL” does not mean “no structure.”

Schemas and constraints

A schema describes how data is organized. In relational usage, it can refer both to named database namespaces and, more broadly, to tables, columns, types, relationships, and rules. It is not the same as a TypeScript interface, even if application types mirror part of it.

Database constraints protect invariants at the data boundary. Examples include:

• NOT NULL for a required value;
• UNIQUE for values that must not repeat under the constraint's rules;
• primary keys that identify rows;
• foreign keys that require a related row;
• check constraints that enforce a condition.

PostgreSQL documents these constraint types and their data-integrity roles.Constraints complement application validation because every database client encounters the same database rule.³

Constraints do not discover the correct product rules automatically. Designers must choose what the database should enforce and how the application should handle violations.

How an application sends a query

A backend commonly uses a database driver, query builder, or ORM. The driver handles the database protocol and connections. A query builder constructs commands. An ORM maps between application objects and database operations.

These tools are not the database. They create commands, send them to a database server, and translate results or errors back into application values.

For an illustrative relational lookup, the application might issue a parameterized query shaped like:

SELECT id, email, created_at
FROM users
WHERE email = $1;

The bound parameter carries learner@example.com separately from the SQL text. Parameterization is important for safe query construction, but this lesson focuses on how the database finds data once it receives a valid query.

How the database finds matching data

For a relational query, a useful qualified flow is:

This order is conceptual. Engines can pipeline work, parallelize operations, reuse prepared plans, or optimize away steps. A write also needs locking or concurrency control, constraint checks, logging, and durable-change mechanisms specific to the engine.

Sequential scans and indexes

To evaluate WHERE email = $1, a database can inspect table data broadly or use an access structure that narrows the candidates.

PostgreSQL calls a pass over table rows a sequential scan. It can be sensible when a query needs a large share of a small table, when no suitable index exists, or when estimates make it cheaper than scattered index access. An index scan can be useful when the indexed condition selects a smaller portion of the data.PostgreSQL's EXPLAIN guide shows sequential, index, and bitmap plans and their estimated costs.⁵

“Scan” is not automatically a bug, and “uses an index” is not automatically fast. The amount and distribution of data, selected columns, ordering, joins, cached pages, database settings, and storage all matter.

What an index is

An index is a maintained data structure that helps the database locate candidate records by indexed values without searching all table or collection data in the same way.

For an email lookup, a suitable index can associate ordered or hashed index entries—depending on index type—with locations or identifiers for matching records. The database follows those references to obtain any additional data it needs.

PostgreSQL's index introduction explains that an index can avoid checking every row and that the planner decides whether to use it.The index exists alongside table data and is maintained as the table changes.⁴ MongoDB similarly documents that, without an appropriate index, it may scan collection documents, while an index can limit inspected data.The exact structures and plan options are product-specific.¹⁰

An index is not best understood as a complete duplicate table. It stores selected key information plus references or engine-specific data, and some index forms can satisfy a query without a separate table lookup. Those details vary.

Why indexes have costs

Indexes consume storage. Inserts, deletes, and changes to indexed values require index maintenance. More indexes can increase write work, memory pressure, backup size, and operational complexity.

An index also helps only when it fits the access pattern. A single-column index may not support a multi-column filter and ordering in the desired way. A low-selectivity value may match so much data that a scan is cheaper. Stale or inaccurate statistics can lead to a poor plan.

The right question is not “does this table have an index?” It is “for this query shape and real data distribution, which plan does the engine choose, and what does that plan cost?”

Memory, cache, pages, and durable storage

Database engines move data through layers. A common relational model groups stored data into fixed-size pages or blocks. PostgreSQL, specifically, organizes tables and indexes as arrays of fixed-size pages, commonly 8 KB by default.That size and layout are PostgreSQL-specific, not a rule for all databases.⁷

Frequently used pages may be available in the database's buffer cache or the operating system's cache. PostgreSQL documents both its shared buffer area and the operating system cache below it.A cache hit can avoid a physical read, though the complete performance path includes more than one cache.⁸

Data being present in memory does not by itself prove it is durably persisted. A database's commit and recovery mechanisms determine when a write is considered durable under its configuration. Likewise, “read from disk” is an oversimplification when operating-system and device caches intervene.

The durable beginner model is: databases manage logical records, but engines perform work over memory and storage structures beneath them.

Creating and updating data

A write goes through more than “save this object.” The database may:

1. parse and plan the command;
2. find affected records;
3. check types and constraints;
4. coordinate with concurrent operations;
5. update table and index structures;
6. record enough information for commit and recovery;
7. return affected values or a status.

The exact order and mechanism vary. An ORM's save method can hide this work but does not remove it.

Application validation and database constraints serve different boundaries. The application can return a helpful message before issuing a command. The database constraint remains the final shared rule for all clients and concurrent requests.

Transactions and atomicity

A transaction groups operations into a unit. Atomicity means its operations do not become partially committed as a successful result: the transaction commits its changes or rolls them back.

Suppose creating an order requires inserting the order and reducing inventory. Without coordinated changes, a failure between the two could leave an order with no inventory update or an inventory reduction with no order.

PostgreSQL documents transactions as all-or-nothing groups whose intermediate states are not visible to other concurrent transactions; COMMIT makes the changes visible, while rollback abandons them.This is the appropriate beginner boundary, not a complete treatment of isolation or crash recovery.⁶

A transaction does not automatically include an external payment service or queue, and it does not automatically prevent every logical race. Boundaries and guarantees must be designed explicitly.

Concurrency at a high level

Several requests can read and change the same data at once. The database needs concurrency control so those operations do not corrupt internal structures and so each transaction receives defined visibility guarantees.

Depending on the system, techniques include locks, multiversion concurrency control, conflict detection, and isolation levels. The application may also use constraints, version columns, or retry logic.

Transactions do not eliminate every concurrency anomaly under every isolation level. A “check inventory, then update” sequence that seems correct for one request may race with another. The deeper topics are isolation, locking, optimistic concurrency, and idempotency.

Query planning

A query describes the result, not every physical step. A relational query planner compares possible execution plans using table statistics and cost estimates. It may choose join order, scan types, sort strategies, and whether to use an index.

PostgreSQL's EXPLAIN displays the plan tree and estimates; EXPLAIN ANALYZE actually executes the query and adds observed timing and row counts.Because ANALYZE executes the statement, use it carefully with writes.⁵

Plans can change as data volume, value distribution, schema, statistics, parameters, or configuration changes. That is why a query that was fast on a small development table can slow as production data grows.

Common misconceptions

Debugging scenario

Knowledge check

What to learn next

Authentication and Sessions is the next lesson in the path; it shows how identity records and session state participate in requests without turning the database into the authentication system itself.