Articles

Consistency

October 31, 2026
Databases Notes
EN

In ACID, consistency means a transaction preserves the invariants that define valid states. The DBMS enforces invariants it knows, such as declared keys and checks; it cannot enforce an unexpressed business rule simply because a transaction commits. Correct application logic and suitable concurrency...

Durability

October 31, 2026
Databases Notes
EN

Durability means that an acknowledged commit survives the failure classes covered by the configured logging, synchronization, replication, and storage assumptions. A successful reply is not a guarantee against simultaneous destruction of all durable copies or against an administrator durably committ...

Atomicity

October 31, 2026
Databases Notes
EN

Atomicity means that the database makes a transaction's effects visible as a committed unit or leaves none of those effects committed. The statements still execute in sequence; atomicity does not mean simultaneous execution, nor does it guarantee that application logic is correct. A statement error ...

Clustered Indexes Heap Tables and Row Locality

October 31, 2026
Databases Notes
EN

A table's primary key does not determine one universal physical layout. This chapter separates the logical key, the physical location of base rows, and the row locator used by secondary indexes. Those decisions affect read locality, key width, and write amplification...

Row Based vs Column Based Databases

October 31, 2026
Databases Notes
EN

A SQL table is a logical relation, not a promise about disk layout. A row store groups many columns of a record together; a column store groups values from the same field across records. This changes the work of reconstructing one row versus scanning a few columns across many rows. Real engines also...

Composite Indexes and Column Order

October 31, 2026
Databases Notes
EN

A composite index orders entries by a tuple of key columns. The leading equalities, range predicates, and requested sort determine which part of that ordering the engine can exploit. The purpose is not to index every filtered column, but to build a useful access path for a specific workload...

Indexing

October 31, 2026
Databases Notes
EN

An index trades extra storage and write maintenance for a potentially cheaper read path. Choose one only after identifying the query's filter, ordering, and columns to return; then compare its plan and measured cost with a scan. This chapter is an overview—the following notes cover individual struct...

Primary Key vs Secondary Key

October 31, 2026
Databases Notes
EN

A key is a logical uniqueness/identity rule; an index is a physical route to rows. Engines often create indexes to enforce keys, but an additional index does not create a new business identifier. This note separates primary and alternate candidate keys from non-unique lookup indexes...

Btree Internals and Page Splits

October 31, 2026
Databases Notes
EN

B-tree-family indexes are commonly used for equality lookup, ranges, and ordered access because they keep keys in a balanced, page-oriented structure. Their cost has two parts: finding an entry and visiting enough leaf or table pages to return all qualifying rows. This chapter follows both search an...

How Tables and Indexes Are Stored on Disk

October 31, 2026
Databases Notes
EN

SQL exposes tables and indexes as logical objects, but the DBMS reads and writes pages, files, and recovery records. This chapter follows a single row lookup through those layers. The key distinction is that rows returned, pages touched, and physical disk reads are different quantities; the cache an...

Index Maintenance Bloat and Online Operations

October 31, 2026
Databases Notes
EN

Indexes have a lifecycle...

Covering Partial and Expression Indexes

October 31, 2026
Databases Notes
EN

A basic index answers...

Specialized Index Structures

October 31, 2026
Databases Notes
EN

B-tree is the default general-purpose index for many relational workloads, but not every predicate is naturally ordered...

Database Pages

October 31, 2026
Databases Notes
EN

SQL presents a table as rows and columns. Storage works with chunks of bytes. In many database engines, a page is a fixed-size unit used to organize data and transfer it between storage and memory. A table or index usually spans many pages...

Query Optimization Techniques

October 31, 2026
Databases Notes
EN

A query can return the correct result and still do far more work than necessary. Optimization means reducing that work while preserving the result the application needs. Start with one measured problem, not a checklist of indexes to add everywhere...

Working with Billion Row Table

October 31, 2026
Databases Notes
EN

A billion rows is not a magic threshold at which one architecture becomes mandatory. A narrow, well-indexed billion-row table with selective queries can be easier to operate than a much smaller table with wide rows, poor access patterns, heavy churn, or global scans. Start from data size, row width...

Pagination and Large Result Sets

October 31, 2026
Databases Notes
EN

A query is not finished when the database finds matching rows. The system still has to order them, transfer them, allocate client memory, deserialize them, and render/process them...

Execution Plans Statistics and Cardinality

October 31, 2026
Databases Notes
EN

A database optimizer estimates costs from table metadata, statistics, predicates, indexes, join algorithms, and its cost model. It does not execute every possible plan and pick the fastest one...

Partitioning Sharding and Data Locality

October 31, 2026
Databases Notes
EN

Large datasets can be divided so queries touch less data and maintenance works on smaller units. The terms are related but not interchangeable...

Connection Pooling Batching and N Plus One

October 31, 2026
Databases Notes
EN

Database performance is not only query execution time...

Write Performance Vacuum and Bloat

October 31, 2026
Databases Notes
EN

Read performance is only half of database performance...

Accessing Database in Code

October 31, 2026
Databases Notes
EN

Application code sits between users and the database. A request can be slow even when the SQL engine executes its statements quickly...

Benchmarking Load Testing and Capacity

October 31, 2026
Databases Notes
EN

A performance claim without a workload definition is incomplete...

Indexing Strategies

October 31, 2026
Databases Notes
EN

Index access methods suit different operations. This note compares B-tree, bitmap, hash, and full-text indexes using named engines. For composite index design, covering queries, and maintenance, see Database Indexing...

Database Caching

October 31, 2026
Databases Notes
EN

A cache keeps a reusable copy of data or a computed result. Reading the copy can avoid repeating a more expensive operation. The difficulty is knowing whether that copy is still appropriate for the request...

Materialized Views

October 31, 2026
Databases Notes
EN

A materialized view stores the result of a query so readers can reuse work that has already been performed...

Introduction to Orm

October 31, 2026
Databases Notes
EN

An object-relational mapper (ORM) is a library that maps application objects to relational tables and generates SQL for common database operations...

Popular Orm Tools

October 31, 2026
Databases Notes
EN

ORMs solve similar problems, but they expose very different programming models. Some are tightly integrated with a web framework, some implement a language ecosystem standard, and some deliberately stay close to SQL...

Schema Migrations

October 31, 2026
Databases Notes
EN

An ORM maps application code to a database schema, but the schema changes over time. Schema migrations are versioned changes that move the database from one known structure to another...

Relationship Loading and Query Performance

October 31, 2026
Databases Notes
EN

ORM performance problems usually come from the SQL workload generated by application code: too many round trips, too many rows, large joins, missing indexes, or transactions that stay open too long...

Transactions Concurrency and Unit of Work

October 31, 2026
Databases Notes
EN

ORMs make database access convenient, but concurrent requests still operate on a shared database. Two application workers can read the same row, make different decisions, and attempt conflicting writes...

Data Control Language Dcl

October 31, 2026
Databases Notes
EN

DCL describes database privileges, such as GRANT and REVOKE. This chapter uses PostgreSQL, not SQLite: table privileges, role membership, object ownership, and application-user authorization are distinct layers. The goal is to trace effective access, not to assume a single grant is the whole securit...

Intro to Sql

October 31, 2026
Databases Notes
EN

SQL (Structured Query Language) expresses database requests and changes declaratively. Start by reading a result set, then work through filters, joins, groups, missing values, and controlled writes. This chapter uses SQLite so the examples can be run locally; it does not claim every syntax choice wo...

Data Manipulation Language Dml

October 31, 2026
Databases Notes
EN

DML changes rows with INSERT, UPDATE, and DELETE; SELECT reads data and is sometimes classified separately as query language. This chapter treats them together because applications must reason about both the targeted rows and the outcome. A successfully executed statement may affect zero rows, which...

Aggregate Functions

October 31, 2026
Databases Notes
EN

An aggregate reduces qualifying rows to a summary, either once for the whole input or once per GROUP BY key. COUNT(*) counts rows, while aggregates such as AVG(column) generally skip NULL inputs. Before writing a report, identify its grain (one result per what?), which rows qualify, and whether miss...