Part I — Database Fundamentals and Relational Theory

Chapter 4. Database Architecture and Environment

You have been using a DBMS since Chapter 1 — creating tables, loading data, running queries. This chapter explains what is actually running when you do. Database architecture answers three practical questions: what are the moving parts inside a DBMS server, how do applications reach it over a network, and how do the layers stay decoupled so that storage can change without breaking programs? The ideas here are the vocabulary of everything operational in this book — EXPLAIN plans (Chapter 19), indexes, backups, replication, and connection pooling all refer back to this chapter's pictures.

The chapter's second job is to make two words precise: schema and instance. SQL uses both in a specific way — CREATE SCHEMA, the "current database instance" — and the three-schema architecture explains the deepest promise of the relational approach: data independence, the property that let fifty years of hardware evolve under stable applications.

After studying this chapter you will be able to:

• Name the major components of a DBMS and say what each does.
• Distinguish one-tier, two-tier, and three-tier application architectures with examples.
• Describe client-server database systems and how PostgreSQL and MySQL handle connections.
• Explain schemas and instances as the SQL catalog stores them, and query the catalog.
• Define logical and physical data independence using the three-schema architecture.
• Walk a query through parsing, planning, and execution, and a page through buffer management.
• Sketch PostgreSQL's process-per-connection design and MySQL's pluggable storage engine design.

4.1 Database system architecture

Whatever the brand, a DBMS server is a bundle of cooperating components with the same division of labor. The classic picture:

        SQL  ──────────────►
  ┌──────────────────────────────────────────────────┐
  │                  QUERY PROCESSOR                  │
  │  parser ─► rewriter ─► optimizer ─► executor     │
  └───────────────────────┬──────────────────────────┘
                          │  plan (scan/join operators)
  ┌───────────────────────▼──────────────────────────┐
  │                 STORAGE MANAGER                  │
  │  buffer manager ─► files/pages ─► heap, indexes  │
  └───────────────────────┬──────────────────────────┘
                          │  reads/writes, WAL records
  ┌───────────────────────▼──────────────────────────┐
  │            TRANSACTION MANAGER                   │
  │  lock manager · MVCC · log/recovery              │
  └───────────────────────┬──────────────────────────┘
                          │
                    disk files + WAL

• Query processor. Parses SQL into a tree, rewrites it (expanding views, simplifying conditions), optimizes it into an execution plan — choosing which indexes to use and in what order to join — and executes that plan.
• Storage manager. Turns the plan's reads and writes into operations on pages (fixed-size disk blocks) through the buffer manager, which keeps hot pages in memory; below it, heap files hold table rows and B-tree indexes (Chapter 19) hold search structures.
• Transaction manager. Serializes concurrent users (locks, MVCC — Chapter 18) and makes every change crash-safe by writing a write-ahead log (WAL) before the data pages change (Chapter 21).
• Catalog. The metadata — tables, columns, types, constraints, users — stored as tables in the database itself, so the DBMS can answer questions about its own structure.

The crucial property is the layering: the query processor never touches disk directly, the storage manager never parses SQL, and both consult the catalog. SQL's declaration "give me Fall 2026 sections" hides every one of these layers — that is data abstraction (Chapter 1) implemented as system architecture.

4.2 One-tier, two-tier, and three-tier architectures

Tier counts the independent machines (or processes) between the user and the data.

One-tier: the application, the DBMS, and the data live together. SQLite on a phone, or a desktop app with an embedded database, is one-tier — fast, simple, no network, and every user needs their own copy of the data. It suits single-user work, not sharing.

Two-tier: a client program talks SQL directly to a database server across a network. Your psql or MySQL Workbench session is two-tier: client on your desk, server elsewhere; protocol between them. Classic business applications of the 1990s were two-tier fat clients — powerful, but they put SQL, business rules, and user interface in one program, so every rule change meant redeploying every client.

Three-tier: the standard web shape. A browser (presentation tier) talks HTTP to an application server (application/logic tier), which talks SQL to the database server (data tier):

 [ Browser ]  ──HTTP──►  [ App server:             ]  ──SQL──►  [ DBMS ]
  presentation            business logic + API                data + integrity
                          (Java / Python / PHP)

The university portal is three-tier: the browser renders forms; the application tier checks that you may enroll and that the section has a seat; the database tier enforces the key and capacity constraints that make double-enrollment impossible no matter which application talks to it. The great virtue of the split is separation of concerns: interface changes never touch SQL, security rules live server-side, and the database enforces integrity for every client at once (Chapter 20 relies on exactly this). N-tier extends the idea — added cache tiers, API gateways, microservices — but the three-tier picture remains the backbone.

4.3 Client-server database systems

A client-server database system centralizes the data and the DBMS on a server machine that owns the files; clients connect over a network, send SQL, and receive results. The server is the sole writer — that single point of control is what makes central integrity, backup, and security possible (Chapter 1).

Mechanically, a client-server session is: locate the server (host, port, database, user, password) → the server authenticates → a connection (session) is established → the client sends statements and reads results → the connection closes. Two properties matter:

• The wire protocol is stateless per statement. Each SQL statement is self-contained; the client keeps the session context. This is why a dropped connection loses nothing but open transaction state (Chapter 18).
• Connections cost the server. PostgreSQL forks a backend process per connection; MySQL assigns a thread per connection. Either way, thousands of idle connections consume memory — hence connection pools in application tiers (Chapter 23), which recycle a few dozen real connections among thousands of web requests.

PostgreSQL listens on port 5432 by default, MySQL on 3306. The port numbers are trivia; the concepts — sessions, authentication at connect time, the server as sole owner of the files — are what you will actually use. Chapter 14 walks through real connections with psql and the mysql client.

4.4 Database schemas and instances

Chapter 2 defined the schema (structure) and instance (content) of a relation. SQL uses the words at two more levels, and the catalog ties them together.

• A database in PostgreSQL and MySQL is a named, isolated collection of objects — our university database. A server instance hosts many databases. (Terminology trap: a running PostgreSQL server process is also called an "instance"; MySQL's mysqld likewise. Context disambiguates.)
• A schema inside a database is a namespace for tables, views, and functions. PostgreSQL's default schema is public — that is why student is really public.student (or university.public.student fully qualified). In MySQL, "database" and "schema" are synonyms: CREATE DATABASE and CREATE SCHEMA do the same thing.
• The catalog (or data dictionary) is the metadata: which tables exist, their columns and types, their constraints, their owners. Codd's elegant move was to store the catalog as ordinary tables, queryable with ordinary SELECT. Two interfaces: the standard information_schema (portable across both platforms) and each engine's native catalog — PostgreSQL's pg_catalog (what \d student reads) and MySQL's SHOW commands.

SELECT table_name, table_type
FROM   information_schema.tables
WHERE  table_schema = 'public';

 table_name    | table_type
---------------+------------
 course        | BASE TABLE
 course_section| BASE TABLE
 department    | BASE TABLE
 enrollment    | BASE TABLE
 instructor    | BASE TABLE
 student       | BASE TABLE

Metadata-as-data is not a convenience; it is the foundation of tools. ORMs generate SQL by reading the catalog (Chapter 24), migration tools diff schemas against it, and EXPLAIN is itself a query over planner data. When a DBMS can describe itself, programs can reason about it.

4.5 Logical and physical data independence

The three-schema architecture — the ANSI/SPARC model — distinguishes three descriptions of one database:

 EXTERNAL  level:  views, what each program or user sees
      ▲  external/conceptual mapping (views define it)
 CONCEPTUAL level: the community logical schema — tables, columns, keys
      ▲  conceptual/internal mapping (index and storage choices)
 INTERNAL  level:  files, pages, record layouts, indexes, compression

Logical data independence is the ability to change the conceptual schema without changing external views or programs. Add columns to student and every program selecting named columns still works; split student into two tables and a view named student can keep the old programs running unchanged. Views are the working mechanism.

Physical data independence is the ability to change the internal schema without touching the conceptual one. Add an index on student(gpa), rebuild the table with a different fill factor, move it to another disk: every existing query returns exactly the same rows, only faster or slower. This is the payoff of declarative SQL: programs state what (rows), never how (access paths), so the DBA may retune how at any time (Chapter 19 is entirely about this).

Both independences are why the relational model survived: hardware, file formats, and access methods were reinvented repeatedly between 1970 and today, while the tables of Chapter 2 and the SELECT of Chapter 10 never had to change.

4.6 Database storage and query processing

Storage. Disks and SSDs are addressed in pages — PostgreSQL uses 8 KB blocks, InnoDB 16 KB. A table's rows are slotted into pages of a heap file (unordered by content); a B-tree index is another page-structured file holding key→row-location entries (Chapter 19). No query reads raw bytes: the buffer manager keeps a pool of page frames in memory, satisfies reads from the pool when possible, and writes dirty pages back later — coordinated with the WAL, which records every change before the data page is written, so a crash can be repaired by replay (Chapter 21). The buffer pool is why the second run of your query is faster than the first.

Query processing runs the same pipeline everywhere:

 SQL text ─► parse (syntax) ─► analyze/rewrite (names, views)
        ─► optimize: enumerate plans, cost them, pick one
        ─► execute: iterators pull rows through scan/join/sort operators

The optimizer is cost-based: it uses statistics (how many rows, how many distinct values per column) gathered by ANALYZE to estimate each plan's cost and chooses the cheapest — the plan, not your SQL text, determines the speed, which is why Chapter 19 treats EXPLAIN as an essential skill. The executor runs the plan as a pipeline of operators, each pulling rows from the one below — a design that streams results with low memory. Note what the pipeline does not do: it never stores results back into tables; a plain SELECT touches nothing but buffers, which is why SELECT is safe to run freely.

4.7 Overview of PostgreSQL and MySQL architectures

The two platforms organize the same responsibilities differently, and the differences are themselves instructive.

PostgreSQL is a process-per-connection system. A single postmaster process listens on 5432; for each client connection it forks a backend process that serves that one session alone. Backends share two things: the catalog and data files (through the buffer pool) and the write-ahead log. Background daemons — checkpointer, WAL writer, autovacuum launcher — maintain memory hygiene and (uniquely visible to users) clean up old row versions: PostgreSQL's concurrency scheme (MVCC, Chapter 18) never overwrites a row in place, so an UPDATE writes a new version and leaves the old one until VACUUM reclaims it. Every database contains schemas (public by default) and the catalog lives in the pg_catalog schema.

 PostgreSQL:  postmaster (port 5432)
               ├─ backend proc  ← psql session 1   ┐
               ├─ backend proc  ← JDBC pool conn   ├─ shared buffers + WAL
               └─ ...             ← session N       ┘
               background: checkpointer · walwriter · autovacuum

MySQL (mysqld) is a thread-per-connection system: a connection-handling layer assigns each session a thread, cheaper to create than a process. Above the storage engines sits one shared SQL layer — parser, optimizer, caches — and below it the famous pluggable storage engine API: the same SQL can be served by different physical stores. The default engine InnoDB is transactional (ACID, row-level locking, MVCC), keeps table data in the leaf pages of a B-tree clustered on the primary key, and maintains its own buffer pool, redo and undo logs. MyISAM — non-transactional, table-locked, historically very fast for reads — is the cautionary ancestor; the MEMORY and ARCHIVE engines fill niches. MySQL 8.0 removed the old query cache and moved the data dictionary into InnoDB tables, so information_schema queries are ordinary queries now. In MySQL a "database" is a directory of per-table files (.ibd under InnoDB).

 MySQL:  mysqld
          ├─ connection handler (thread per client, port 3306)
          ├─ SQL layer: parser · optimizer · data dictionary
          └─ storage engine API:  InnoDB (default) · MyISAM · MEMORY · ...

For you as a developer the difference is mostly invisible — until performance and administration chapters, where process vs. thread, VACUUM vs. none, and clustered vs. heap storage produce genuinely different behavior. Chapter 17 compares the platforms point by point; Chapter 14 walks installing and configuring both.

Chapter Summary

• A DBMS server is layered components: query processor, storage manager (buffer, heap, indexes), transaction manager (locks, MVCC, WAL), and a catalog — with the catalog itself stored as tables.
• One-tier embeds the DBMS with the app; two-tier puts a SQL-speaking client against a server; three-tier puts a browser and an application server between users and data — the modern default.
• Client-server systems: one server owns the files; clients open authenticated sessions over a wire protocol (5432 / 3306); connections are real resources, hence pooling.
• SQL schemas are namespaces inside databases; the catalog is queryable metadata (information_schema, pg_catalog, SHOW).
• The three-schema architecture (external/conceptual/internal) defines logical data independence (views absorb conceptual changes) and physical data independence (indexes and layouts change invisibly).
• Storage works in pages through a buffer pool with a write-ahead log; queries run parse → rewrite → cost-based optimize → execute.
• PostgreSQL: postmaster + forked backend per connection, shared buffers, WAL, autovacuum, schemas. MySQL: threads per connection, one SQL layer over pluggable storage engines, InnoDB clustered-index default.

Key Terms

Laboratory Exercises

1. Identify your session. In psql, run \conninfo and SELECT pg_backend_pid();; in the mysql client run SELECT CONNECTION_ID(), @@port;. Note the port each server listens on. Expected results: connection info with host/port 5432 (PostgreSQL) or 3306 (MySQL); a nonzero backend pid / connection id.
2. Query the catalog two ways. In PostgreSQL, run \d student and then the information_schema.tables query of Section 4.4; in MySQL, run SHOW TABLES; and SHOW COLUMNS FROM student;. Confirm both describe the same six tables. Expected result: the six university tables listed by native and standard interfaces on both platforms.
3. Demonstrate physical data independence. Run EXPLAIN SELECT * FROM student WHERE gpa > 3.80;, then CREATE INDEX student_gpa_idx ON student (gpa); and rerun the EXPLAIN. Drop the index afterwards. Expected result: a sequential scan before, an index scan (or bitmap scan) after — and identical query results throughout.
4. Time the buffer pool. Run a counting query over enrollment twice in a row and compare run times with \timing on (psql) or SELECT BENCHMARK-free repeated runs (MySQL: just run twice). Expected result: the second run is faster or equal — cached pages and plan reuse; with only 28 rows the effect is small but visible in repeated trials.
5. Inspect schemas. In PostgreSQL, run SELECT schema_name FROM information_schema.schemata; and note public and pg_catalog; in MySQL, run SHOW DATABASES; and note that database = schema. Expected result: the platform's system schemas listed on both.
6. Draw your environment. Sketch the three-tier diagram with your own labels: browser or client on your machine, which tier is on which host, and the two protocols in use (HTTP/none and the database wire protocol). Expected result: a diagram matching Section 4.2 with your real hostnames and ports.

Review Questions and Exercises

1. Name the four major components of a DBMS and one responsibility of each. Query processor — turns SQL into plans; storage manager — pages, buffer pool, files, indexes; transaction manager — concurrency and recovery; catalog — metadata as tables.
2. Why does a desktop SQLite app not scale to a university registration system? One-tier: each user has a private data copy; no central concurrent access, integrity enforcement, backup, or security.
3. Give one concrete benefit and one concrete cost of moving from two-tier to three-tier. Benefit — business rules centralized server-side, clients become thin, security enforced at one tier; cost — an extra tier to deploy, monitor, and secure.
4. What exactly is a database "session," and what does a connection pool actually reuse? An authenticated client-server connection carrying session state; the pool keeps a small set of live connections open and reuses them across many requests instead of paying connect/authentication cost per request.
5. PostgreSQL "forks a backend per connection" while MySQL uses a thread per connection. Which resource does each choice conserve, and what operational consequence follows? Threads are cheaper to create than processes; PostgreSQL trades per-connection cost for process isolation (one crashed backend does not corrupt the server), and both platforms push large deployments toward pooling.
6. In PostgreSQL, what is the difference between a database, a schema, and the catalog? A database is an isolated object collection; a schema is a namespace inside it (public by default); the catalog is the metadata (pg_catalog schema) describing all of them.
7. Why is storing metadata as ordinary queryable tables architecturally important? Programs and tools can discover structure at runtime — ORMs, migrations, EXPLAIN, and tools like \d are all catalog queries; the DBMS bootstraps itself from its own tables.
8. Define logical and physical data independence, each with a university-schema example. Logical — change the conceptual schema without breaking programs (split student, keep a view named student); physical — change storage without changing the schema (add or drop an index on gpa; same rows, different speed).
9. Which architectural decision makes physical data independence possible? Declarative, access-path-free SQL: programs state results, the optimizer chooses access paths, so paths may change later.
10. What does the buffer manager do, and why is the second execution of a query often faster? It keeps a pool of page frames in RAM, serving repeated reads from memory and deferring writes; the second run hits cached pages (and cached statistics/plans), skipping disk reads.
11. Order the query-processing stages and say what the optimizer's inputs are. Parse → rewrite/analyze → optimize → execute; the optimizer takes the parsed query plus catalog metadata and statistics (row counts, distinct values).
12. Your EXPLAIN shows a sequential scan on student even though an index on gpa exists. Name two plausible reasons. The table is tiny (12 rows) so scanning is cheaper than index traversal; or the condition is not index-usable (e.g., a function on the column). Statistics-based cost choice covers both.

Mini-Project

Map your laboratory onto this chapter's pictures and keep it as a reference diagram. Produce one page containing: (1) the three-tier diagram with your real components — your browser, a small application server you will build in Chapter 24, and your PostgreSQL and MySQL servers — labeled with protocols and ports; (2) the DBMS component diagram annotated with which chapter covers which box (query processor — 19; storage — 19; transactions — 18–21; catalog — 9); and (3) a session log: connect with psql, capture pg_backend_pid() before and after \connect to another database, and explain in two sentences why the pid changed (a new backend was forked for the new session). This artifact becomes the frontispiece of your Part IV laboratory notes.