🗄️Database basics

One server, many boundaries

A database server (instance/cluster) runs processes that accept connections, hold memory structures (buffer pool, plan cache), and persist data through WAL/redo and data files. Inside it, databases (catalogs) and schemas carve namespaces so teams can share hardware without sharing every object name.

🏗️The Physical Layer: Pages and Buffers

Databases don't read rows; they read 8KB pages. Efficient SQL minimizes the 'Working Set' that must live in memory.

Slotted Pages: How rows survive on disk

A database file is a sequence of fixed-size Pages (usually 8KB). Inside a page, rows aren't just packed end-to-end. They use a Slotted Page structure to handle variable-length data and row deletions without rewriting the whole file.


              graph TD
                subgraph "8KB Page Structure"
                  Header[Page Header: LSN, Checksum, Free Space Pointer]
                  SlotArray[Slot Array: Pointers to Row Offsets]
                  FreeSpace[Free Space]
                  RowN[Row N data]
                  Row1[Row 1 data]
                  Header --> SlotArray
                  SlotArray --> Row1
                  SlotArray --> RowN
                end

Buffer Pool (The Cache)

The database's internal memory manager. It caches pages from disk. A "Buffer Hit" is 100ns; a "Buffer Miss" (disk I/O) is 1-10ms. High Cache Hit Ratio is the primary goal of tuning.

Dirty Pages

When you UPDATE a row, the page is modified in memory (Buffer Pool) first. It becomes "Dirty." The Checkpointer eventually flushes it to disk, but the WAL ensures it's safe immediately.

🧱Storage Hierarchy

From the OS file to the specific field.

Level	Storage Unit	Mechanical Insight
Instance	Process + Memory	Manages the Buffer Pool and Connection Slots.
Database	Filesystem Directory	A collection of files (Heaps, Indices, WAL).
Relation (Table)	Set of Pages	A "Heap" of pages where rows are stored unordered.
Page	8KB Block	The atomic unit of I/O. Reading 1 byte = Reading 8KB.
Row (Tuple)	Header + Data	Includes a "Null Bitmap" and visibility metadata (XMIN/XMAX).

SQL

Inspect databases (PostgreSQL-style catalog)

SELECT datname AS database_name,
       pg_size_pretty(pg_database_size(datname)) AS on_disk
FROM pg_database
WHERE datistemplate = FALSE
ORDER BY pg_database_size(datname) DESC;

✓Illustrative result set

database_name	on_disk
biotech_app	48 GB
warehouse	312 GB

🔐ACID in one paragraph

You will unpack each letter in transactions lessons, here is why it matters on day one.

Transactions bundle changes so they commit as a unit or roll back cleanly. Atomicity stops half-written enrollments; isolation keeps concurrent readers from seeing Frankenstein states; durability is why you still trust data after a power blip. None of that is visible in a single SELECT, but it is why connection pooling, timeouts, and retry logic exist in app code.

On-call cheat sheet

Draw a one-pager for your environment: app → pool → primary URL → read replica URL → backup window. When latency spikes, that map tells you whether to look at connections, locks, disk, or upstream network, before rewriting SQL.

TL;DR

A database is a managed collection of schemas, tables, and security objects on a server instance. Clients connect with a URL, authenticate, and run SQL in a session; the engine handles parsing, planning, execution, WAL/redo, and replication. Know your environment's hierarchy (instance → database → schema → object) so connection strings, grants, and backups target the right scope.