DataSys+ Certification Prep

The exam you are preparing for

On 13 October 2026 CompTIA launched DataSys+ V2, exam code DS0-002, the second version of its certification for people who run databases. This course is built against that exam's published objectives, and every lesson names the objective numbers it covers. Before you study a single query, it pays to know exactly what the test looks like, because its format should shape how you prepare.

Here are the facts as CompTIA publishes them on its DataSys+ V2 page and in the objectives document, read on 2 November 2026. Check the page again before you book, because CompTIA can change any of them.

Two of those numbers deserve a second look. Ninety questions in ninety minutes sounds like a minute each, but performance-based questions take several minutes, so the multiple-choice items have to go faster than that to bank time. And 700 is not 70 percent. CompTIA describes its exams as criterion-referenced: your result is compared with an established standard, a cut score, rather than with other candidates. It classes exam results data as highly confidential, so no published table turns a percentage of correct answers into a scaled score.

The recommended experience is advice, not a gate. Nobody checks your employment history before you test. CompTIA's own frequently asked questions call DataSys+ an intermediate certification and expect you to be comfortable with basic SQL. This course assumes that much. If SELECT, WHERE, GROUP BY and a two-table join are not yet routine for you, work through Databases & SQL (cs-db) first; it is the natural lead-in, and this course does not reteach it at length.

Key idea: DS0-002 gives you at most 90 questions in 90 minutes, mixes multiple choice with performance-based tasks, and reports a scaled score where 700 passes, so practise for speed and for doing, not only for recall.

Two versions in the same window

The first DataSys+ exam, DS0-001, launched on 18 July 2023 and is still on sale while it winds down. CompTIA's DataSys+ page gives three dates: English learning products for V1 retire in March 2027, the English DS0-001 exam retires on 16 April 2027, and translated exams and learning products retire on 11 June 2027. Both versions share the same format and the same passing score. The domains differ.

CompTIA's summary of the change is that V2 widens the coverage of non-relational databases, cloud-hosted data platforms, SQL and programming, modern security practice and business continuity, and adds a whole domain on data integration: ETL and ELT, connectivity, data formats, troubleshooting and emerging AI concepts. This course teaches DS0-002, the version you can still sit after April 2027 and the only English version on offer once V1 retires. If you have a DS0-001 voucher and a test date before 16 April 2027, download the V1 objectives from the V1 page and compare them with the objective numbers listed in each lesson here; much will overlap, but the numbering will not.

Six domains, unevenly weighted

CompTIA tells you what share of the exam each domain represents. Those weights are your first study-planning tool.

No single domain dominates. The two largest, fundamentals and security, sit at 19 percent each, and the smallest, data integration, still carries 13 percent, roughly a dozen questions on a full-length exam. A candidate who skips a domain because it looks small is skipping about one question in eight.

Reading the objectives the way the exam does

The official objectives document for DS0-002, document version 3.0, lists 20 numbered objectives under the six domains. Each objective has bulleted examples beneath it, and the document ends with an acronym list and a sample list of lab hardware and software. Download it from CompTIA (the link is in the Sources) and keep it open while you study. This course paraphrases the objectives rather than copying them.

Read the verb at the start of each objective, because it tells you how deeply the topic is tested.

• Explain asks what something is, why it exists and when it applies. Nine of the 20 objectives use it, including monitoring (3.1), governance and compliance (4.2) and disaster recovery (5.2).
• Compare and contrast asks you to choose between neighbours: a document store or a graph database, a server-side or a client-side script, replication or mirroring. Four objectives use it: 1.1, 1.3, 2.1 and 5.3.
• Given a scenario asks you to apply the idea: develop and run SQL (1.2), implement design and documentation techniques (2.2), data management tasks (3.3), authentication and authorization (4.3) and backups (5.1), use data acquisition methods (6.1) and troubleshoot acquisition problems (6.2). Those seven are where performance-based questions are most at home.

Two warnings sit in the document itself. CompTIA says the bulleted examples are not exhaustive, so a technology that fits an objective can appear even if it is not named. And it states that candidates who prepare with unauthorized materials, the leaked question collections known as brain dumps, will have their certifications revoked and be suspended from future testing. Every practice question in this course is original and written from the objectives.

Key idea: treat the objectives document as your syllabus, read each objective's verb as a depth gauge, and expect anything that reasonably belongs under an objective, named or not.

Performance-based questions, the clock and the rules

CompTIA's page on performance-based questions, or PBQs, lists DataSys+ among the exams that use simulation PBQs. A simulation is an approximation of a tool or environment. Three details from CompTIA's explanation shape your strategy: you can skip a simulation PBQ and return to it later, simulations have a reset button, and partial credit may be given. More than one path can reach a correct answer, and the scoring allows for that.

So when a PBQ appears early, read it, flag it and work the multiple-choice items first, then return with the time you have banked. Never leave a PBQ blank. The best preparation is the one built into every lesson here: writing real SQL, reading real output and making configuration decisions yourself.

You can test at a Pearson VUE test centre or online through Pearson's OnVUE service. Online, your webcam and microphone monitor the whole session, physical notes and scrap paper are not allowed, and you work on a built-in digital whiteboard instead. Since 23 June 2026, candidates aged 17 and under need a parent's or guardian's consent through CompTIA Central before registering, and the consent covers one testing event at a time.

If an attempt goes badly, CompTIA's retake policy sets no waiting period between a first and second attempt but requires at least 14 calendar days before a third or later one, and every attempt is paid for in full. Once you pass, DataSys+ is valid for three years and can be renewed through CompTIA's continuing education program.

Which version, and what this course is

One plain statement belongs here. CompTIA and DataSys+ are trademarks of CompTIA, which does not endorse this course; the course has no connection to CompTIA beyond following its published objectives. No course can promise a pass, and this one does not. What it can do is make sure nothing on the objectives list is new to you on exam day.

How to study so it lasts

In 2013 John Dunlosky and four colleagues reviewed the research on ten common study techniques. Two earned their highest rating, high utility: practice testing and distributed practice, which means spreading study across many sessions instead of cramming. Five were rated low utility, including rereading, highlighting and summarizing. Those are the habits most learners rely on.

For DataSys+ the translation is direct. Use the ten questions at the end of each lesson as a test, not as a check after rereading. Say why each wrong option is wrong, because the distractors are built from real confusions: a neighbouring isolation level, the other backup type, the protocol next door. Retake a lesson's quiz a few days later and again a week after that. Plan on six to eight hours a week, and keep a lab notebook recording each command you run, what it printed and what you think it means.

Key idea: test yourself and space your sessions; rereading and highlighting feel productive but, in the research, do little for what you can recall weeks later.

Building a lab that costs nothing

CompTIA's sample lab list asks for little: a desktop or laptop, a free SQL environment, a language to connect from, a text editor and some sample data. Everything below is free, runs on Windows, macOS or Linux, and needs no account and no payment card.

MongoDB also sells a cloud service, Atlas, which needs an account; no lab here uses it. Throughout the course you will build the database of an imaginary small business, Harbor Outfitters, an online shop selling outdoor gear, in a PostgreSQL database called harbor. Each lab restates the setup it needs, so you can do any lesson on its own. Install the engines with their default settings on your own computer, and keep them off any network you do not control: Lesson 13 explains how database ports are reached and why exposing one is risky.

Your first procedure: one bad value, four engines

A database administrator's first loyalty is to the data, so start by asking what each engine does with a value that does not fit. You will create the same small products table in four engines and feed each the word twelve where a number belongs.

1. SQLite. Run sqlite3 harbor.db, then CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, stock INTEGER); and INSERT INTO products (name, stock) VALUES ('Headlamp', 'twelve');. It succeeds. Now run SELECT name, stock, typeof(stock) FROM products; and the answer is twelve, stored as text.
2. PostgreSQL. In psql run CREATE DATABASE harbor;, connect with \c harbor, create the table with stock integer, and try the same insert. PostgreSQL refuses with an invalid input syntax error and stores nothing. You can ask the question without risking an error: SELECT pg_input_is_valid('twelve', 'integer'); returns false.
3. MariaDB or MySQL. Run CREATE DATABASE harbor; USE harbor;, create the table with stock INT, and insert. The server rejects the row with error 1366, an incorrect integer value.
4. MongoDB. In mongosh run use harbor and then db.products.insertOne({ name: "Headlamp", stock: "twelve" }). MongoDB stores it without complaint.

Four engines, two verdicts. Why? SQLite assigns each column a type affinity, a preference rather than a rule. Its documentation says that when text that is not a well-formed number goes into a column with integer affinity, the value is stored as text. PostgreSQL checks every value against the declared type. MongoDB uses a flexible schema: by default, documents in one collection need not share fields or types.

Now change one input and watch the results flip. In MariaDB or MySQL, run SET SESSION sql_mode = ''; and insert again. This time it succeeds with one warning; SHOW WARNINGS; reports the incorrect integer value, and the row holds 0. The default SQL mode in MySQL 8.4 and in MariaDB includes STRICT_TRANS_TABLES, which turns bad values into errors for transactional tables. Switch it off and the server quietly adjusts the value to the closest valid one, so a typo becomes an empty shelf in your inventory.

The other two engines can be made strict as well. In SQLite, add the word STRICT after the closing parenthesis of CREATE TABLE, a feature available since version 3.37.0; the insert then fails with a message that it cannot store a TEXT value in an INTEGER column. In MongoDB, create a collection with a validator, for example db.createCollection("products_checked", { validator: { $jsonSchema: { required: ["name", "stock"], properties: { stock: { type: "number" } } } } }), and an insert of twelve is rejected because the document fails validation.

Key idea: engines differ in what they accept by default, and a database administrator decides, deliberately, where the rules live: in the column type, in a strict mode, in a validator, or in application code that may not always run.

Common misconceptions

• "700 means I need 70 percent." The score is scaled from 100 to 900 and compared with a cut score; CompTIA keeps results data confidential and publishes no conversion. Aim to know the material, not to hit a guessed percentage.
• "DataSys+ is an SQL exam." SQL is one of 20 objectives. Planning, monitoring, security, backups and integration account for most of the marks, and CompTIA pitches the exam at people with two to three years of administration experience.
• "A database always rejects data of the wrong type." SQLite stores it under its affinity rules unless the table is STRICT, MongoDB accepts any shape unless a validator says otherwise, and the MySQL family converts bad values with only a warning when strict mode is off.
• "If a topic is not listed in the objectives, it will not be tested." The objectives document says its bulleted examples are not exhaustive.

What to carry forward

• DS0-002: at most 90 questions, multiple choice plus performance-based, 90 minutes, a scaled passing score of 700 on 100 to 900, English only, launched 13 October 2026.
• DS0-001 retires in English on 16 April 2027 and in translated versions on 11 June 2027. This course teaches DS0-002.
• Domain weights: Fundamentals 19%, Deployment 17%, Management and Maintenance 18%, Security 19%, Business Continuity 14%, Data Integration 13%.
• Objective verbs set the depth. Seven of the 20 objectives begin "Given a scenario", and those are where you must apply, not just define.
• Simulation PBQs can be skipped and revisited, have a reset button and may earn partial credit.
• Study by testing yourself and spacing sessions. Never use brain dumps.
• Your lab: SQLite, PostgreSQL, MariaDB or MySQL, MongoDB and DBeaver, all free and local. Engines differ in how strictly they police data, and that choice belongs to you.

Sources

1. CompTIA. (n.d.). DataSys+ certification V2 (new version): Exam details, exam objectives summary and frequently asked questions. Retrieved 2 November 2026. comptia.org
2. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0). CompTIA (PDF)
3. CompTIA. (n.d.). DataSys+ certification (version overview and V1 retirement dates) and DataSys+ certification V1 (retiring version). Retrieved 2 November 2026. comptia.org; V1 page
4. CompTIA. (n.d.). Performance-based questions explained. comptia.org
5. CompTIA. (n.d.). Exam development; CompTIA certification retake policy; Online-proctored exam guidelines; Candidate ID policy. exam development; retake policy; online testing; ID policy
6. Dunlosky, J., Rawson, K. A., Marsh, E. J., Nathan, M. J., and Willingham, D. T. (2013). Improving students' learning with effective learning techniques: Promising directions from cognitive and educational psychology. Psychological Science in the Public Interest, 14(1), 4-58. PubMed record
7. SQLite. (n.d.). Datatypes in SQLite and STRICT tables. sqlite.org; STRICT tables
8. PostgreSQL Global Development Group. (2025). Data validity checking functions. In PostgreSQL 18 documentation, section 9.27.7. postgresql.org
9. Oracle. (n.d.). Server SQL modes. In MySQL 8.4 reference manual, section 7.1.11. dev.mysql.com; MariaDB plc. (n.d.). SQL_MODE. mariadb.com
10. MongoDB, Inc. (n.d.). Schema validation and $jsonSchema. MongoDB manual. mongodb.com; $jsonSchema
11. Licences: PostgreSQL Global Development Group, License; MariaDB, Licensing FAQ; MongoDB, Inc., Community edition licensing; DBeaver, About. postgresql.org; mariadb.com; mongodb.com; dbeaver.io

A paper from 1970 and the table it gave us

In June 1970 Edgar F. Codd, a mathematician at IBM's San Jose research laboratory, published "A Relational Model of Data for Large Shared Data Banks" in Communications of the ACM. His proposal was that people using a database should not need to know how the data sat on disk. They should see relations, which we now call tables: rows of facts, each row with the same named columns, linked to other tables by shared values. Fifty-six years later most of the world's orders, payments and bookings still live in tables built on that idea.

A relational database stores each kind of thing in its own table. Harbor Outfitters, the shop you will build throughout this course, keeps customers in one table, products in another and orders in a third. An order row carries a customer_id, and that shared value is what connects the two tables. You ask questions in SQL, joining tables at query time. The structure is declared before any data arrives, a habit called schema-on-write: the database checks every row against the table definition as it is written.

Three strengths follow from that design. Any question can be asked later, including ones nobody planned for, because joins reassemble the facts on demand. Constraints such as primary keys, foreign keys and NOT NULL keep bad data out. And transactions group changes so that they all happen or none do, the ACID guarantees you will meet again in Lesson 12. Databases & SQL (cs-db) teaches keys, joins and normalization in depth; this lesson assumes them.

The point: a relational database trades some flexibility at write time, a fixed schema, for the ability to answer almost any question later with joins, constraints and transactions.

Why "not only SQL"

CompTIA's acronym list expands NoSQL as "not only Structured Query Language", and the expansion matters. NoSQL is not one technology and not an enemy of SQL. It is a loose name for databases that store data in some shape other than related tables, usually because a particular workload fits that shape better.

Three pressures pushed these systems into use. Some workloads need to spread across many servers, and joins across machines are expensive. Some data arrives in shapes that change week to week, such as product attributes that differ for every category. And some questions follow a single access pattern so heavily, look up this key or find the nearest neighbour, that a store built for exactly that pattern beats a general one. The price is usually paid in ad hoc querying, in joins, or in consistency, and you should be able to say which price each family pays.

Seven non-relational families, side by side

The objectives name seven NoSQL types. Read this table across a row to see what each family stores and what it is good at, then down a column to compare them.

MongoDB's manual describes its records as BSON documents, a binary form of JSON with more data types, each in a standard collection carrying a unique _id field, up to 16 mebibytes each. Cassandra's documentation describes a partitioned wide-column storage model with eventually consistent semantics. Neo4j stores nodes, relationships and properties instead of tables, so a query walks stored relationships rather than computing joins. The pgvector extension adds a vector column type to PostgreSQL and finds nearest neighbours by distance, exactly or approximately with an index. MongoDB's manual defines time series data as a sequence of data points where insight comes from changes over time, and stores readings from the same source near each other.

Four confusions the exam likes

Wide-column is not the same as columnar. "Column-oriented" covers two different ideas. A wide-column store such as Cassandra keeps rows, but each partition can hold many columns and rows are spread across servers by a partition key. A columnar store keeps each column's values together on disk. Microsoft's documentation for SQL Server columnstore indexes says this column-wise format can give up to ten times the query performance and up to ten times the compression for data-warehouse fact tables. One is about distributing writes; the other is about scanning a few columns of billions of rows.

A document store is not a key-value store with longer values. Both find a record by its identifier, but a document database can also index and query fields inside the document. A key-value store treats the value as opaque: if you want carts over 100 dollars, you must keep a second structure yourself.

A graph database is not a relational database with foreign keys. You can store relationships in tables, and for one or two hops that works well. But "customers who share a device with someone who shares an address with a flagged account" is four joins, and the cost grows with each hop. A graph database stores the links themselves, so following one is cheap.

Object-oriented is not object-relational. An object-oriented database stores program objects directly, references included. PostgreSQL calls itself an object-relational database because it adds object ideas, such as user-defined types and table inheritance, to an engine that is still relational and still speaks SQL.

The point: each NoSQL family is shaped around one access pattern, key lookup, nested documents, partitions, relationships, similarity, time or objects, and the exam expects you to match a scenario to that pattern.

Structured, semistructured and unstructured data

The second half of objective 1.1 classifies the data itself, not the database. The test is where the structure lives.

Semistructured data is self-describing. The JSON text {"capacity": 2, "weight_g": 1450} tells you its own field names, and the next record is free to have different ones. That is why it suits a document store, and why reading it needs care: the structure is applied when you read, a habit called schema-on-read, so a program must cope with fields that are missing or typed differently.

Unstructured data still has some structure, a JPEG has a header and an email has a subject line, but its content is not organized into fields a query can use. The usual pattern is to keep the file in file or object storage and keep facts about it, such as its location, size, owner and a checksum, in a table. When the file itself must sit inside the database, it goes in a binary large object, or BLOB, column; PostgreSQL's equivalent type is called bytea.

Semistructured data inside relational engines

The line between relational and document databases has blurred, because the relational engines learned to hold JSON. Each does it differently, and the differences matter when you choose a column type.

• PostgreSQL has two types. Its documentation says json stores an exact copy of the input text and must reparse it on every use, while jsonb stores a decomposed binary form that is slightly slower to write but significantly faster to process and can be indexed. jsonb does not keep whitespace or key order, and keeps only the last of any duplicate keys. The documentation recommends jsonb for most applications. Its containment operator asks whether one document contains another: specs @> '{"waterproof": true}'.
• MySQL has a JSON type that validates documents automatically, so an invalid document produces an error, and converts them to an internal format for quick access to their elements.
• SQLite stores JSON as ordinary text and supplies functions to read it: json_extract(specs, '$.weight_g'), or the shorter operator specs ->> '$.weight_g', and json_valid() to check a document before you trust it.

So a relational database can hold a column of varied product attributes while keeping orders strictly typed. That hybrid is often the right answer for a small shop: one engine to run, back up and secure. A dedicated document database earns its place when most of the data is document-shaped, or when it must scale across many servers.

Choosing for Harbor Outfitters

Work through Harbor's needs one at a time and the families sort themselves out.

1. Orders and payments must never be half-written and must answer unplanned questions from the accountant: relational.
2. Shopping carts are read and written by session ID on every page view and thrown away after a day: key-value, or simply a table if traffic is modest.
3. Product specifications differ for tents, boots and stoves: a jsonb column in PostgreSQL, or a document store if the catalogue grows huge.
4. Website response times arrive every second from every server and are queried by time window: time series.
5. Search that finds "warm waterproof jacket for a child" when the description says "insulated kids' rain shell": vector similarity over embeddings.
6. "Customers who bought this also bought" across several hops of purchases: a graph, or for a shop this size, a well-indexed join.

Using several engines for different jobs is called polyglot persistence. Every extra engine is another system to patch, monitor, back up and secure, and the rest of this course will show you what each of those jobs costs. A database administrator's default is the smallest number of engines that meets the requirements.

The point: classify the data first, structured, semistructured or unstructured, then pick the store whose access pattern matches the questions you will ask of it.

Common misconceptions

• "NoSQL means the database cannot use SQL." The name is read as "not only SQL", and several NoSQL systems offer SQL-like languages, such as Cassandra's CQL. What they drop is the relational table model, not necessarily the query syntax.
• "Column-oriented always means Cassandra." Wide-column stores and columnar analytic storage share a word and little else. Read the scenario: distributed writes point to wide-column; scanning a few columns of a huge table points to columnar.
• "JSON in a relational database is unstructured data." JSON is semistructured: its keys describe its values. Unstructured data, such as images and audio, carries no field-level structure a query can use.
• "A vector database replaces a relational one." A vector index answers "what is similar?" It does not enforce foreign keys or keep a ledger. Most systems that use vectors keep the authoritative records elsewhere.

Recap

• Relational databases store related tables with a declared schema, join them at query time, and protect data with constraints and transactions.
• NoSQL means not only SQL: document, key-value, column-oriented, graph, vector, time series and object-oriented stores, each built around an access pattern.
• Column-oriented has two meanings: wide-column (Cassandra) for distributed writes, and columnar (columnstore) for analytic scans.
• Structured data has an external schema, semistructured data carries its own keys or tags, unstructured data has no field-level structure.
• Relational engines hold semistructured data too: PostgreSQL jsonb, MySQL's validated JSON type, SQLite's JSON functions. Large binary files go in BLOB or bytea columns, or better, in file storage with metadata in a table.
• Choose the fewest engines that meet the requirements; each one adds patching, monitoring, backup and security work.

Sources

1. Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. DOI 10.1145/362384.362685.
2. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 1.1 and acronym list. CompTIA (PDF)
3. MongoDB, Inc. (n.d.). Documents and Time series. MongoDB manual. mongodb.com; time series
4. Apache Software Foundation. (n.d.). Overview. Apache Cassandra documentation. cassandra.apache.org
5. Microsoft. (2026). Columnstore indexes: Overview. SQL Server documentation. learn.microsoft.com
6. Neo4j, Inc. (n.d.). What is a graph database? Neo4j getting started guide. neo4j.com
7. pgvector contributors. (n.d.). pgvector: Open-source vector similarity search for Postgres. github.com
8. PostgreSQL Global Development Group. (2025). JSON types. In PostgreSQL 18 documentation, section 8.14; and About PostgreSQL. postgresql.org; about
9. Oracle. (n.d.). The JSON data type. In MySQL 8.4 reference manual, section 13.5. dev.mysql.com; SQLite. (n.d.). JSON functions and operators. sqlite.org
10. Redis Ltd. (n.d.). Redis data types. redis.io
11. Abiteboul, S., Buneman, P., and Suciu, D. (2000). Data on the Web: From relations to semistructured data and XML. Morgan Kaufmann.

One language, five jobs

MySQL's reference manual files SELECT under "Data Manipulation Statements", next to INSERT and DELETE. CompTIA's objectives give it a category of its own, Data Query Language. Neither is wrong. The subtypes of SQL are a teaching convention for sorting statements by what they do, and vendors draw the lines in slightly different places. The formal standard, ISO/IEC 9075, most recently revised as SQL:2023, defines the language; the familiar three-letter labels are how practitioners talk about it.

The exam expects you to recognize each label, know which statements belong to it, and, more usefully, know how each kind behaves when something goes wrong. That last part is what this lesson spends most of its time on.

Step 1: define the structures with DDL

Start Harbor Outfitters' core tables in PostgreSQL. Connect to the harbor database from Lesson 1 and run:

CREATE TABLE customers (customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE, full_name text NOT NULL, created_at timestamptz NOT NULL DEFAULT now());

CREATE TABLE products (product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, sku text NOT NULL UNIQUE, name text NOT NULL, category text NOT NULL, price numeric(8,2) NOT NULL CHECK (price >= 0));

Every clause is a decision about the data. GENERATED ALWAYS AS IDENTITY makes the database number the rows. UNIQUE on email stops two accounts sharing one address. numeric(8,2) stores money exactly, up to 999,999.99; a floating-point type would not, and SQLite shows why: its NUMERIC columns store 189 multiplied by 1.05 as 198.45000000000002. The CHECK constraint refuses a negative price. If the shop later needs a weight for each product, that is DDL too: ALTER TABLE products ADD COLUMN weight_g integer;.

DDL is where the engines disagree most sharply about transactions. MySQL's manual lists CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE and CREATE INDEX among the statements that implicitly end any open transaction, as if you had typed COMMIT first. PostgreSQL runs most DDL inside the transaction, so BEGIN; CREATE TABLE promo_codes (code text); ROLLBACK; leaves no table behind. A few PostgreSQL commands opt out: the manual says CREATE DATABASE cannot be executed inside a transaction block at all.

Why this matters: on MySQL or MariaDB, a CREATE INDEX in the middle of your transaction silently commits everything before it, so a later ROLLBACK cannot undo those changes.

Step 2: create, read, update and delete with DML and DQL

CRUD names the four things an application does to data, and each maps to one statement.

Real work often mixes create and update. A supplier sends a price list in which some SKUs are new and some already exist. Each engine has an upsert for that: PostgreSQL and SQLite accept INSERT ... ON CONFLICT (sku) DO UPDATE SET price = excluded.price, MySQL and MariaDB use INSERT ... ON DUPLICATE KEY UPDATE, and PostgreSQL and SQL Server also support the standard MERGE statement, which matches source rows to target rows and inserts, updates or deletes accordingly.

Removing every row has two routes, and they are not interchangeable. DELETE without a WHERE clause is DML: it removes rows one at a time and fires any row-level delete triggers. TRUNCATE is classed as DDL. PostgreSQL's manual says it has the same effect as an unqualified DELETE but is faster because it does not scan the table, reclaims disk space immediately, does not fire ON DELETE triggers, and can restart the table's identity numbering with RESTART IDENTITY. PostgreSQL rolls a TRUNCATE back if the surrounding transaction fails; MySQL commits implicitly around it.

Step 3: wrap changes in a transaction with TCL

Harbor wants tents 5 percent dearer. Someone also proposes a 50 percent rise on boots, which turns out to be a typo. A transaction with a savepoint lets you keep one change and drop the other.

1. BEGIN; starts a transaction. Nothing after it is permanent until COMMIT.
2. UPDATE products SET price = price * 1.05 WHERE category = 'tents';
3. SAVEPOINT before_boots; marks a point you can return to.
4. UPDATE products SET price = price * 1.50 WHERE category = 'boots'; is the mistake.
5. ROLLBACK TO SAVEPOINT before_boots; undoes everything after the savepoint and keeps the tent change.
6. COMMIT; makes the tent change permanent.

Run SELECT sku, price FROM products ORDER BY sku; and the tents are dearer while the boots are unchanged. A plain ROLLBACK at step 5 would have discarded both updates.

Now change one input and watch the result flip. Skip step 1 and run the boots update on its own. psql's AUTOCOMMIT setting is on by default, and the psql manual says that in this mode each SQL command is committed automatically upon successful completion. MySQL behaves the same way: each new connection starts with autocommit enabled. Your ROLLBACK now has nothing to roll back; PostgreSQL's manual says that ROLLBACK outside a transaction block emits a warning and otherwise has no effect. The boots are 50 percent dearer, and only a backup or a second UPDATE will fix them.

That is why careful administrators type BEGIN before any change they are not certain of, and why the MySQL client offers a safe-updates mode that rejects UPDATE and DELETE statements with neither a key in the WHERE clause nor a LIMIT.

Why this matters: a transaction is your undo button, but only if you press BEGIN first; in autocommit mode every successful statement is already permanent.

Step 4: think in sets

SQL is set-based: a statement describes which rows you want and what should happen to them, and the database decides how. The 5 percent tent rise above is one statement however many tents there are. The row-by-row alternative, a script that fetches each tent and sends a separate UPDATE for it, makes one round trip per row, holds locks longer and gives the optimizer nothing to optimize. Practitioners have a rude name for it, RBAR, row by agonizing row.

Set operations make the idea explicit. Each combines the results of two queries with matching columns.

Thinking in sets also means thinking about NULL, which stands for an unknown value. A comparison with NULL is neither true nor false but unknown, and a WHERE clause keeps only rows where the condition is true. So WHERE discount <> 10 silently skips every row whose discount is NULL. Lesson 4 shows the nastiest consequence of this, a NOT IN subquery that returns nothing at all.

Common misconceptions

• "SELECT is DML." In MySQL's manual it is filed there; in CompTIA's objectives it is DQL. On the exam, use CompTIA's categories: SELECT is the query language, and it changes nothing.
• "DDL can always be rolled back." MySQL and MariaDB implicitly commit around most DDL, and even PostgreSQL refuses to run some commands, such as CREATE DATABASE, inside a transaction block.
• "TRUNCATE is just a faster DELETE." It is faster, but it is DDL, it skips row-level delete triggers, cannot take a WHERE clause, and on MySQL ends your transaction.
• "ROLLBACK undoes my last statement." ROLLBACK undoes the open transaction. With autocommit on and no BEGIN, there is no open transaction, and nothing is undone.

What you now know

• DDL defines structures, DML changes rows, DQL reads them, TCL controls transactions and DCL controls permissions. Vendors file statements slightly differently; the exam uses CompTIA's labels.
• CRUD maps to INSERT, SELECT, UPDATE and DELETE; upserts combine create and update through ON CONFLICT, ON DUPLICATE KEY UPDATE or MERGE.
• TRUNCATE empties a table quickly without row triggers; DELETE removes rows selectively and fires them.
• BEGIN, SAVEPOINT, ROLLBACK TO SAVEPOINT and COMMIT let you keep some changes and discard others. Autocommit makes each statement permanent the moment it succeeds.
• MySQL and MariaDB commit implicitly around most DDL; PostgreSQL and SQLite can roll most DDL back.
• Write set-based statements; use UNION, INTERSECT and EXCEPT to combine results; remember that comparisons with NULL are unknown, not false.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 1.2 and acronym list. CompTIA (PDF)
2. Oracle. (n.d.). Data manipulation statements; Statements that cause an implicit commit; autocommit, commit and rollback; mysql client tips (safe-updates mode). In MySQL 8.4 reference manual. data manipulation statements; implicit commit; autocommit; safe updates
3. PostgreSQL Global Development Group. (2025). TRUNCATE; ROLLBACK; SAVEPOINT; CREATE DATABASE; MERGE. In PostgreSQL 18 documentation. TRUNCATE; ROLLBACK; SAVEPOINT; CREATE DATABASE; MERGE
4. PostgreSQL Global Development Group. (2025). psql (variable AUTOCOMMIT); SQL conformance; Combining queries (UNION, INTERSECT, EXCEPT). In PostgreSQL 18 documentation. psql; SQL conformance; combining queries
5. SQLite. (n.d.). UPSERT. sqlite.org
6. International Organization for Standardization and International Electrotechnical Commission. (2023). ISO/IEC 9075-1:2023, Information technology: Database languages SQL, Part 1: Framework (SQL/Framework). ISO.

Three questions on a Monday morning

Harbor Outfitters' owner sends three questions before the shop opens. Which customers have never placed an order? What was each customer's biggest order? And how did each month's revenue compare with the month before? Each sounds simple. Each needs a different tool from the query language, and each has a plausible wrong answer that runs without any error at all. This lesson works all three on a small data set you can type in yourself.

A fifth customer, Eli (customer 5), has an account but no orders. The customers table also records who referred whom: Ben and Chen were referred by Ana, and Dara by Ben. Every result quoted below came from running these queries; PostgreSQL, MySQL 8, MariaDB and SQLite all support the features used.

Joins: getting the rows side by side

A join pairs rows from two tables on a condition. Databases & SQL (cs-db) teaches the mechanics; here is the summary you need for the exam.

The first question, customers who never ordered, is a LEFT JOIN that keeps the unmatched rows: SELECT c.full_name, COUNT(o.order_id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.full_name;. Eli appears with a count of 0. Counting o.order_id rather than * matters: COUNT(*) would count Eli's single all-NULL row as 1.

Now the first trap. The owner refines the question: count only orders over 100. Put that condition in the WHERE clause and Ben, Dara and Eli vanish from the result, leaving Ana with 2 and Chen with 1. The WHERE clause runs after the join and throws away every row whose o.total is NULL or 100 or less, so the outer join has quietly become an inner join. Put the condition in the ON clause instead, ON o.customer_id = c.customer_id AND o.total > 100, and all five customers come back, three of them with 0.

The second trap is fan-out. Join each order to its order_items and also to its payments, then SUM(o.total), and an order with three items and two payments contributes its total six times. Nothing errors; the number is simply wrong. Aggregate each child table in its own subquery first, then join the summaries.

What matters here: conditions on the optional side of an outer join belong in ON, not WHERE, and joining a parent to two child tables multiplies rows, so aggregate before you join.

Nested queries: a question inside a question

A nested query, or subquery, is a SELECT inside another statement. Where it sits decides what it may return.

• Scalar subquery: returns one value. SELECT order_id, total FROM orders WHERE total > (SELECT AVG(total) FROM orders); The average is 112.50, so orders 101, 103 and 108 come back.
• IN and EXISTS: test membership. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total > 100) returns Ana and Chen. PostgreSQL's manual notes that EXISTS depends only on whether any rows come back, not on their contents, which is why the convention is SELECT 1.
• Correlated subquery: refers to the outer row, as the EXISTS above does with c.customer_id, so it is evaluated, logically, once per outer row.
• Derived table: a subquery in the FROM clause that acts as a temporary table, the usual cure for fan-out.

Here is the nastiest trap in the language. The never-ordered question can also be written SELECT full_name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);, and it correctly returns Eli. Then a guest checkout writes order 109 with a NULL customer_id. Run the same query again and it returns no rows at all. PostgreSQL's manual explains why: if there is no equal right-hand value and at least one right-hand row is NULL, NOT IN yields NULL, not true. Eli's id is not equal to 1, 2, 3 or 4, but it might be equal to the unknown value, so the database cannot say it is absent. The fix is WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id), which returns Eli whatever NULLs the orders table holds.

Common table expressions: naming the steps

A common table expression, or CTE, is a named subquery written before the main query with WITH. PostgreSQL's manual describes CTEs as temporary tables that exist for one query. Their first value is readability: a long query becomes a sequence of named steps you can test one at a time.

Their second value is recursion. The referral chain, who did Ana bring in, and who did they bring in, cannot be answered by a fixed number of joins, because nobody knows how deep it goes. A recursive CTE starts from Ana and repeatedly joins the result to customers:

WITH RECURSIVE chain (customer_id, full_name, depth) AS (SELECT customer_id, full_name, 0 FROM customers WHERE customer_id = 1 UNION ALL SELECT c.customer_id, c.full_name, chain.depth + 1 FROM customers c JOIN chain ON c.referred_by = chain.customer_id) SELECT * FROM chain;

The result is Ana at depth 0, Ben and Chen at depth 1 and Dara at depth 2. The same pattern walks product categories, organization charts and bills of materials.

Window functions: calculations that keep the rows

The second question, each customer's biggest order, defeats GROUP BY. MAX(total) per customer gives the amount but loses which order it was, because GROUP BY collapses each customer's rows into one. A window function computes across a set of related rows while, in the words of PostgreSQL's tutorial, the rows keep their separate identities.

The OVER clause defines the window. PARTITION BY splits rows into groups, ORDER BY sorts within each group, and an optional frame, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, limits which neighbours count.

So the biggest-order question becomes two steps: number each customer's orders by total, largest first, then keep number 1.

WITH ranked AS (SELECT order_id, customer_id, total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn FROM orders) SELECT customer_id, order_id, total FROM ranked WHERE rn = 1;

The answer: Ana 103 (310), Ben 105 (60), Chen 108 (150), Dara 106 (95). Why the CTE? Because WHERE ROW_NUMBER() OVER (...) <= 3 is an error; SQLite reports a misuse of window function. PostgreSQL's tutorial gives the reason: window functions are allowed only in the SELECT list and ORDER BY, because they logically run after WHERE, GROUP BY and HAVING. To filter on one, compute it in a subquery or CTE first.

The third question needs LAG over monthly totals:

WITH monthly AS (SELECT substr(ordered_on, 1, 7) AS month, SUM(total) AS revenue FROM orders GROUP BY 1) SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change FROM monthly;

The substr function suits SQLite's text dates; in PostgreSQL write date_trunc('month', ordered_on) on a date column. A running total per customer is one more window: SUM(total) OVER (PARTITION BY customer_id ORDER BY ordered_on) gives Ana 120, then 430, then 470.

What matters here: GROUP BY collapses rows and window functions do not, so use a window when you need a group's figure beside each row, and a CTE when you need to filter on it.

The order the database thinks in

Most of these traps come from one fact: a query is written in one order and logically evaluated in another.

The optimizer may physically do things in another order, but it must return the result this logical order defines.

Common misconceptions

• "A LEFT JOIN always keeps every row of the left table." Only until a WHERE condition on the right table discards the NULL-extended rows. Put such conditions in ON.
• "NOT IN and NOT EXISTS are interchangeable." They agree until the subquery returns a NULL; then NOT IN returns nothing. Prefer NOT EXISTS.
• "Window functions are just GROUP BY with extra syntax." GROUP BY returns one row per group. A window function returns every row, with the group calculation alongside.
• "RANK and ROW_NUMBER give the same answer." Only when there are no ties. With ties, RANK repeats a number and skips the next, DENSE_RANK repeats without skipping, and ROW_NUMBER never repeats.

The short version

• INNER keeps matches; LEFT, RIGHT and FULL keep unmatched rows from one or both sides; CROSS gives every combination; a self join compares rows of one table.
• Filters on the optional side of an outer join go in ON. Joining to two child tables fans out; aggregate first.
• Subqueries can be scalar, IN or EXISTS tests, correlated, or derived tables. NOT IN with a NULL in the list returns no rows; NOT EXISTS is safe.
• CTEs name the steps of a query, and WITH RECURSIVE walks hierarchies of unknown depth.
• Window functions keep the rows: ROW_NUMBER, RANK and DENSE_RANK for rankings, LAG and LEAD for neighbours, SUM OVER for running totals. Filter on them from an outer query.
• Logical order: FROM, WHERE, GROUP BY, HAVING, windows, SELECT, ORDER BY, LIMIT.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 1.2. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). Window functions (tutorial, section 3.5) and Window functions (section 9.22). In PostgreSQL 18 documentation. tutorial; reference
3. PostgreSQL Global Development Group. (2025). Subquery expressions (section 9.24) and WITH queries (common table expressions) (section 7.8). In PostgreSQL 18 documentation. subqueries; WITH queries
4. SQLite. (n.d.). Window functions. sqlite.org
5. Oracle. (n.d.). Window functions. In MySQL 8.4 reference manual, section 14.20. dev.mysql.com

The oversold tent

Picture Harbor Outfitters' spring sale. The Ridge 2 tent is down to one in stock, and two customers press Buy within the same second. The web application, written the obvious way, does three things for each: read on_hand, check that it is at least 1, and write on_hand minus 1. Both requests read 1, both pass the check, and both write 0. Two tents sold, one tent on the shelf, and one customer about to receive an apology.

Nothing in that story is a bug in the database. The rule "never sell stock you do not have" lived in application code, split across three round trips, and two copies of the code interleaved. The usual fix is to move the rule into the database, where it can run as one unit of work. This lesson follows that move through the three kinds of code a database can hold: functions, stored procedures and triggers, the SQL programming that objective 1.2 lists.

Functions: a value you can use in a query

Start small. Harbor's reports keep recalculating an order's total from its lines, and every analyst writes the sum slightly differently. A function puts the calculation in one place. In PostgreSQL:

CREATE FUNCTION order_total(p_order_id integer) RETURNS numeric LANGUAGE sql STABLE AS $$ SELECT COALESCE(SUM(quantity * unit_price), 0) FROM order_items WHERE order_id = p_order_id; $$;

Now SELECT order_id, order_total(order_id) FROM orders; gives every order with its total. That is the defining feature of a function: it returns a value and is called as part of a query or a DML statement. The text between the pairs of dollar signs is the body, a quoting style PostgreSQL uses so the body's own quote marks need no escaping. STABLE tells the planner the function does not modify the database and returns the same result for the same arguments within one statement, which lets it optimize calls.

COALESCE matters too. An order with no lines would otherwise total NULL, and a NULL total quietly drops out of later sums. Functions are the right home for that kind of carefully considered detail: write it once, test it once, use it everywhere.

Stored procedures: a unit of work you CALL

Placing an order changes several tables, so it needs more than a value. A stored procedure is a named routine that performs actions and is invoked on its own. Here is Harbor's, written in PL/pgSQL, PostgreSQL's procedural language:

CREATE PROCEDURE place_order(p_customer_id integer, p_product_id integer, p_qty integer) LANGUAGE plpgsql AS $$ DECLARE v_order_id integer; v_price numeric(8,2); BEGIN UPDATE inventory SET on_hand = on_hand - p_qty WHERE product_id = p_product_id AND on_hand >= p_qty; IF NOT FOUND THEN RAISE EXCEPTION 'not enough stock for product %', p_product_id; END IF; SELECT price INTO v_price FROM products WHERE product_id = p_product_id; INSERT INTO orders (customer_id) VALUES (p_customer_id) RETURNING order_id INTO v_order_id; INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (v_order_id, p_product_id, p_qty, v_price); END; $$;

The application now sends one statement: CALL place_order(42, 7, 1);. Read the procedure in order and the oversold tent cannot happen.

1. The stock check and the decrement are one UPDATE with the condition on_hand >= p_qty in its WHERE clause. The database locks the row it changes, so a second buyer's UPDATE waits, then finds on_hand is 0 and matches no row.
2. FOUND is false when the UPDATE matched nothing, and RAISE EXCEPTION aborts the procedure.
3. Because the CALL runs inside a transaction, an exception rolls back everything the procedure did. No half-written order is left behind.

PostgreSQL's manual lists what separates the two kinds of routine. Procedures are created with CREATE PROCEDURE and have no RETURNS clause, though they can pass data back through output parameters. A function is called inside a query or DML command; a procedure is called in isolation with CALL. And a procedure may commit or roll back transactions as it runs, provided the CALL is not itself inside an explicit transaction block. A function cannot. That last difference is why long maintenance jobs, such as archiving old orders in batches of 10,000 with a COMMIT after each batch, are written as procedures.

The other engines follow the same idea with their own syntax. MySQL and MariaDB use CREATE PROCEDURE and CALL; in the mysql command-line client you first change the statement delimiter with the DELIMITER command so that the semicolons inside the body do not end the CREATE statement early. SQL Server runs procedures with EXEC.

Remember: a function returns a value and lives inside queries; a procedure performs a unit of work, is invoked with CALL, and in PostgreSQL can control transactions, which a function cannot.

Triggers: code that runs because something happened

Harbor's accountant now wants a record of every price change: which product, old price, new price, when. Nobody wants to rely on every developer remembering to write that record. A trigger is code the database runs automatically when a specified operation happens on a table. In PostgreSQL a trigger calls a trigger function:

CREATE FUNCTION log_price_change() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NEW.price IS DISTINCT FROM OLD.price THEN INSERT INTO price_history (product_id, old_price, new_price, changed_at) VALUES (OLD.product_id, OLD.price, NEW.price, now()); END IF; RETURN NEW; END; $$;

CREATE TRIGGER products_price_audit AFTER UPDATE OF price ON products FOR EACH ROW EXECUTE FUNCTION log_price_change();

OLD holds the row before the change and NEW the row after it. IS DISTINCT FROM is used instead of <> because it treats two NULLs as equal and a NULL against a value as different, so a price set for the first time is still recorded.

Three choices define every trigger.

Engines differ here more than anywhere else in SQL programming, and the differences produce exam scenarios.

• MySQL and MariaDB have row-level triggers only: the syntax requires FOR EACH ROW, with BEFORE or AFTER timing on INSERT, UPDATE or DELETE. MySQL's manual adds a sharp edge: cascaded foreign key actions do not activate triggers. Delete an order, let ON DELETE CASCADE remove its lines, and a delete trigger on order_items never hears about it.
• SQL Server DML triggers fire once per statement, whether or not any rows were affected, and see the changed rows through two logical tables named inserted and deleted. A trigger written as if exactly one row changed, for example by reading one value from inserted into a variable, breaks the first time someone inserts 500 rows in one statement. Microsoft's documentation also notes that TRUNCATE TABLE does not activate a trigger.

When the trigger bites back

Six months later Harbor imports a new supplier price list: one UPDATE touching 200,000 product rows. The row-level audit trigger fires 200,000 times and writes 200,000 history rows, inside the same transaction, holding locks the whole time. The import that took seconds in testing takes many minutes in production, because the test database had no trigger.

That is the trade every trigger makes. It guarantees a rule runs whatever application touches the table, and it hides that work from anyone reading the application code. Triggers that write to tables with their own triggers form chains; SQL Server allows nesting up to 32 levels before it cancels the chain. Debugging an unexpected change three triggers deep is slow work.

The fixes follow from the table above. Where the rule can see all affected rows at once, a statement-level trigger with a transition table writes the history in one set-based INSERT. Where the work belongs to a known business action, a procedure makes it explicit. And bulk loads deserve a written procedure of their own that states, in the change record, whether triggers stay on.

Choosing where the logic lives

Database code has to be versioned and reviewed like any other code. Lesson 16 shows how change management and version control apply to it.

Common misconceptions

• "Checking stock in the application and then updating is safe if the code is correct." Correct code can still interleave with another copy of itself. Put the check and the change in one statement or one locked transaction.
• "Functions and procedures are the same thing with different keywords." In PostgreSQL a function returns a value inside a query and cannot control transactions; a procedure is called with CALL and can.
• "A trigger fires once per row everywhere." MySQL triggers are row-level only, but SQL Server DML triggers fire once per statement, and PostgreSQL lets you choose.
• "A delete trigger catches every deleted row." Not TRUNCATE in PostgreSQL or SQL Server, and not cascaded foreign key deletes in MySQL.

Putting it together

• Rules split across application round trips can interleave; moving them into one conditional statement inside a procedure makes them atomic.
• Functions return values for use in queries. Procedures perform units of work, are invoked with CALL, and in PostgreSQL may commit or roll back as they go.
• Triggers run automatically on INSERT, UPDATE, DELETE or TRUNCATE, BEFORE, AFTER or INSTEAD OF the operation, per row or per statement, with OLD and NEW rows or transition tables.
• MySQL triggers are row-only and ignore cascaded foreign key actions; SQL Server triggers are per statement and use the inserted and deleted tables.
• Every trigger is hidden work: measure it on bulk changes and prefer set-based statement-level designs.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 1.2. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). User-defined procedures (section 36.4) and Overview of trigger behavior (section 37.1). In PostgreSQL 18 documentation. procedures; triggers
3. PostgreSQL Global Development Group. (2025). CREATE FUNCTION, CREATE PROCEDURE and Trigger functions in PL/pgSQL. In PostgreSQL 18 documentation. CREATE FUNCTION; PL/pgSQL triggers
4. Oracle. (n.d.). CREATE TRIGGER statement and Defining stored programs. In MySQL 8.4 reference manual. CREATE TRIGGER; defining stored programs
5. Microsoft. (2025). CREATE TRIGGER (Transact-SQL). SQL Server documentation. learn.microsoft.com

A specification from 1999

On 12 April 1999 the Python community published PEP 249, the Python Database API Specification 2.0. It is a short document, and it still governs almost every Python database driver: you call connect() to get a connection, ask the connection for a cursor, pass SQL to execute(), fetch the rows, and commit(). Write a script against that interface for SQLite and most of it carries over to PostgreSQL or MySQL. One detail does not, and PEP 249 says so openly: each driver declares a paramstyle, the way it marks the places where values go. Python's built-in sqlite3 module reports qmark, so it uses a question mark; the popular PostgreSQL drivers use %s.

That small specification captures the theme of this lesson, objective 1.3. Database scripts differ in where they run and in what language they are written, and each choice has consequences you can predict.

Where a script runs: server side or client side

The objectives frame the first comparison as purpose and runtime location. A server-side script runs on the database server or inside the database engine itself: a stored procedure, a job run by the database's own scheduler, or a shell script on the database host. A client-side script runs somewhere else, an administrator's laptop, an application server, an integration tool, and talks to the database across the network.

A common exam scenario puts these in tension. An analyst's script loops over 50,000 customer IDs and sends one query per ID from a laptop over a VPN. Each query is fast; the round trips are not. The fix is either one set-based statement, as Lesson 3 argued, or moving the loop to the server, as Lesson 5 did with procedures.

On the web the same words carry an extra warning. PHP, which appears in CompTIA's acronym list, runs server side on a web server and can hold database credentials safely there. JavaScript running in a visitor's browser is client side in the strictest sense, and a browser must never connect to a database directly, because anything shipped to a browser, including a password, is readable by its user.

In short: server-side scripts run next to the data and save round trips at the cost of server resources and privileged access; client-side scripts spare the server but pay for every trip across the network and must carry credentials.

Four languages, compared

Perl's DBI documentation describes it as the standard database interface module for Perl, a set of methods and conventions that give a consistent interface independent of the actual database. Python's DB-API plays the same role. Both separate the language from the engine, so moving a script from one database to another mostly means changing the connection string and the driver.

One task, four scripts

Harbor's accountant wants a CSV file of orders over 100 dollars. Here is the same task in each language, so you can recognize them on sight.

Python, against SQLite, using a placeholder for the threshold:

import csv, sqlite3, sys
con = sqlite3.connect("harbor.db")
rows = con.execute("SELECT order_id, customer_id, total FROM orders WHERE total > ?", (100,))
w = csv.writer(sys.stdout); w.writerow(["order_id", "customer_id", "total"]); w.writerows(rows)

Python's sqlite3 documentation is blunt about the alternative: building the SQL with string formatting makes it vulnerable to SQL injection, so always use placeholders. Lesson 23 shows the attack.

Bash, calling psql in CSV mode:

#!/usr/bin/env bash
set -euo pipefail
psql -d harbor -v ON_ERROR_STOP=1 --csv -c "SELECT order_id, customer_id, total FROM orders WHERE total > 100" > big_orders.csv

PowerShell, turning psql's CSV into objects so it can filter and re-export them:

psql -d harbor --csv -c "SELECT order_id, customer_id, total FROM orders" | ConvertFrom-Csv | Where-Object { [decimal]$_.total -gt 100 } | Export-Csv big_orders.csv -NoTypeInformation

Against SQL Server the first stage would be Invoke-Sqlcmd, which Microsoft describes as running a script containing statements supported by the SQLCMD utility. The -NoTypeInformation switch removes a type header that Windows PowerShell 5.1 adds; PowerShell 6 and later omit it by default.

Perl, with DBI and a placeholder:

use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=harbor.db", "", "", { RaiseError => 1 });
my $sth = $dbh->prepare("SELECT order_id, total FROM orders WHERE total > ?");
$sth->execute(100);
while (my @row = $sth->fetchrow_array) { print join(",", @row), "\n"; }

Notice what the four share: a connection, SQL with the value kept separate, rows coming back, a file going out. The languages differ in syntax far more than in shape.

Command-line scripting that fails loudly

A scheduled script has no one watching it, so its most important property is how it fails. Two defaults work against you.

• psql keeps going after an error. Its manual says command processing continues after an error by default; set the variable ON_ERROR_STOP to on and processing stops immediately. With it set, psql exits with status 3 when a script statement fails, rather than 0. A cron job that checks the exit status can then tell success from failure.
• Bash ignores most failures too. The bash manual's set options fix that: -e exits when a pipeline fails, -u treats unset variables as an error, and pipefail makes a pipeline fail if any command in it fails, not only the last. Without pipefail, pg_dump harbor | gzip > harbor.sql.gz reports success even when pg_dump died, because gzip, the last command, succeeded on an empty stream.

Good scripts are also idempotent where they can be: running one twice should not double-insert. CREATE TABLE IF NOT EXISTS, upserts and date-stamped output files all help.

In short: a script that cannot fail loudly will eventually fail silently, so set ON_ERROR_STOP in psql, set -euo pipefail in bash, and check exit codes in whatever schedules the job.

Keeping credentials out of the script

A password typed on the command line can be visible to other users through the process list, and a password written into a script travels wherever the script is copied, including into version control. Each engine offers a better home.

• PostgreSQL: a .pgpass file in the user's home directory supplies passwords to psql and other libpq programs. On Unix the manual requires that the file disallow any access to group or world, for example with chmod 0600; otherwise it is ignored.
• MySQL and MariaDB: option files such as a per-user .my.cnf, readable only by its owner, hold the client's connection settings.
• Windows and SQL Server: integrated Windows authentication avoids a stored database password altogether.

Checking scripts for stored credentials is part of the code auditing that objective 4.1 lists, and Lesson 20 returns to it.

IDE scripting

An integrated development environment wraps the same SQL in tools that make it easier to write and check. CompTIA's sample lab list names DBeaver and SQL Server Management Studio (SSMS), which Microsoft describes as an integrated environment for managing SQL infrastructure, alongside editors such as Visual Studio Code. What they add:

• Autocompletion of table and column names from the live schema, which catches typos before the server does.
• Result grids you can sort, filter and export, and several result sets side by side.
• Graphical execution plans, which Lesson 17 uses for tuning.
• Schema browsers and entity relationship diagrams generated from the database, which Lesson 11 uses.
• Running a script with a confirmation prompt, a transaction toggle, or against several connections.

The trade-off is that what you click is harder to repeat than what you script. A sensible habit is to explore in the IDE and then save the final SQL as a file that a command-line job can run unattended.

Common misconceptions

• "Server-side scripts are always faster." They save round trips, but they consume the shared server's CPU and memory. A heavy report may be better run on a client against a replica.
• "A script that printed no errors succeeded." psql and bash both continue past many errors by default. Check exit codes and set the options that stop on failure.
• "Using a placeholder is just tidier formatting." A placeholder sends the value separately from the SQL text, which is what defeats SQL injection.
• "Perl and bash are obsolete, so the exam will not ask about them." They are named in the objectives, and plenty of production database hosts still run them nightly.

Where this leaves us

• Server-side scripts run next to the data and save round trips; client-side scripts run elsewhere, cost a round trip per query and need a stored credential.
• PowerShell passes objects and suits Windows and SQL Server; Python's DB-API covers almost every engine; bash glues command-line tools together; Perl's DBI is the database-independent layer in older scripts.
• Keep values out of SQL text with placeholders; their style depends on the driver.
• Scheduled scripts must fail loudly: ON_ERROR_STOP for psql, set -euo pipefail for bash, and checked exit codes.
• Credentials belong in protected files such as .pgpass or option files, or in integrated authentication, never in the script.
• IDEs add autocompletion, grids, plans and diagrams; save the final SQL as a repeatable script.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 1.3, acronym list and hardware and software list. CompTIA (PDF)
2. Lemburg, M.-A. (1999). PEP 249: Python database API specification v2.0. Python Software Foundation. peps.python.org
3. Python Software Foundation. (n.d.). sqlite3: DB-API 2.0 interface for SQLite databases. Python 3 documentation. docs.python.org
4. PostgreSQL Global Development Group. (2025). psql and The password file. In PostgreSQL 18 documentation. psql; .pgpass
5. Microsoft. (n.d.). Invoke-Sqlcmd (SqlServer module); Export-Csv; What is SQL Server Management Studio? Invoke-Sqlcmd; Export-Csv; SSMS
6. Bunce, T., and contributors. (n.d.). DBI: Database independent interface for Perl. dbi.perl.org
7. Free Software Foundation and Kerrisk, M. (n.d.). bash(1), the set builtin. Linux manual pages. man7.org

"The database is slow"

Harbor Outfitters' developers ship a new page that shows a customer's last 100 orders with the items in each. It takes four seconds to load. The ticket they send the database administrator is short: the database is slow, please give it more CPU. It is a reasonable guess. The server holds the data, the page waits on the server, so the server must be the bottleneck.

The monitoring graph says otherwise. The database server's CPU sits below 10 percent while the page loads. Something is making the page wait without making the server work hard. This lesson traces that something, and in doing so walks the four steps objective 1.4 lists for measuring the impact of programming on a database: review the SQL that the code generated, confirm that the code is valid, determine its impact on the server, and remediate.

Step 1: look at the SQL the code actually sent

Harbor's application does not contain the word SELECT. It uses an object-relational mapper, or ORM: a library that maps classes in the program to tables, objects to rows and object references to foreign keys, and writes the SQL itself. The developer writes something like customer.orders and the ORM decides what query that means. So the first job is to see the SQL, and every tool in the objectives has a way to show it.

The database can tell you the same story from its side, which matters when you cannot change the application's logging. PostgreSQL's log_min_duration_statement setting logs statements that run longer than a threshold, and the pg_stat_statements extension, in the words of its documentation, tracks planning and execution statistics of all SQL statements executed by a server. MySQL has its slow query log, and SQL Server its Query Store, which Microsoft describes as giving insight into query plan choice and performance.

Harbor's log for one page view shows 101 statements. The first fetches the customer's 100 orders. Then come 100 near-identical queries, each fetching the items of one order: SELECT ... FROM order_items WHERE order_id = 5001, then 5002, then 5003. Each takes about a millisecond on the server. Each also pays a network round trip and the ORM's own overhead. That is where the four seconds went.

Why the ORM did that: lazy loading and N+1

The pattern has a name, the N+1 select problem: one query to load N parent objects, then one more query per parent to load its children. It comes from lazy loading, a sensible default in which an ORM loads related objects only when the code first touches them. SQLAlchemy's documentation says lazy loading is the default loading style for relationships, and names the N plus one problem as its cost. Hibernate's introduction is blunter, calling N+1 selects "the most common cause of poorly-performing data access code in Java programs".

The ORMs differ in their defaults, which is worth knowing when you read a scenario. Entity Framework Core does not lazy load unless a developer opts in, through its proxies package and navigation properties that can be overridden. Without that, an unloaded navigation property stays empty, which surprises developers in a different way. Ebean advertises that it avoids N+1 automatically through a load context that batches lazy loads. None of these defaults removes the need to read the SQL.

The upshot: an idle database server and a slow page together point to many small queries, and the cure starts with reading the SQL the ORM generated, not with buying a bigger server.

Step 2: confirm the code is valid

Valid means more than syntactically correct. The generated SQL must return the right rows, and an ORM can produce SQL that runs perfectly and answers the wrong question. Check three things before you tune anything.

• Does it return what the code intended? A filter written as status != 'cancelled' becomes WHERE status <> 'cancelled', which, as Lesson 3 showed, silently drops every row whose status is NULL. Compare the row count with a hand-written query you trust.
• Is the order defined? A page of results generated with LIMIT and OFFSET but no ORDER BY can return different rows on each request, so customers see orders repeated or skipped as they page.
• Does a join duplicate rows? Eager loading through joins can repeat each parent row once per child. ORMs usually de-duplicate the objects, but a count or sum written over that SQL will be wrong.

Step 3: determine the impact on the database server

Impact is not the duration of one statement. A query that takes 2 milliseconds but runs 40,000 times an hour costs the server 80 seconds an hour; a report that takes 30 seconds once a day costs 30 seconds a day. pg_stat_statements makes the comparison easy because it records, for each normalized statement, the number of calls and the total and mean execution time. Sort by total time, not by mean, to find what the server actually spends its effort on.

Then ask what else the query consumes. EXPLAIN ANALYZE, which Lesson 17 covers in depth, shows whether it scans whole tables. Locks held during long ORM transactions block other sessions, which Lesson 15 covers. And some costs fall on the network rather than the CPU. Entity Framework Core's documentation describes cartesian explosion: when one query eagerly loads two sibling collections through JOINs, every combination of rows comes back, and the documentation warns this can transfer huge amounts of data to the client. An order with 20 items and 10 shipment events returns 200 rows to build one order.

Step 4: remediate, then measure again

For Harbor's page, the remedy is one option on the query. With SQLAlchemy's selectinload the 101 statements become two: the orders, then all their items in a single query with WHERE order_id IN (...). A joinedload would make it one, at the price of repeating each order's columns once per item. Then measure again. A remediation is not finished until the log and pg_stat_statements show the change.

The upshot: remedies live mostly in the application, eager loading, projections and batching, so the administrator's contribution is evidence: the SQL, the call counts and the total time.

dbt: SQL generated from templates

dbt is not an object-relational mapper; it builds analytic tables inside a data warehouse from SQL files that use Jinja templates and macros. The objectives list it because the same discipline applies: the SQL that runs is not the SQL that was written. dbt compile generates executable SQL from models, tests and analyses and places it in the target/ directory, where you review it like any other generated code.

dbt also decides how each result is stored. Its documentation calls these materializations, strategies for persisting models in a warehouse, with five built in, including view, table and incremental. The choice moves load around: a model materialized as a view recomputes on every query that reads it, a table is rebuilt in full on each run, and an incremental model processes only new rows. When a warehouse slows down every morning, the dbt run and its materializations are among the first things to read.

Common misconceptions

• "The ORM writes efficient SQL, so the database must be the problem." The ORM writes correct SQL for what the code asked, and lazy loading asks one row at a time. Read the log first.
• "The slowest query is the one to fix." Total time is calls multiplied by mean time. A fast query run constantly often costs more than a slow one run daily.
• "Eager loading is always better." Joined eager loading of several collections causes cartesian explosion, and eagerly loading data the page never shows wastes work. Load what the page uses.
• "dbt is an ORM." dbt compiles templated SQL into warehouse tables and views. It generates SQL, which is why the objectives list it, but it maps no objects.

Pulling it together

• ORMs map classes to tables and generate SQL: Hibernate and Ebean for Java, Entity Framework for .NET, SQLAlchemy for Python. dbt generates SQL from templates for warehouses.
• Measure impact in four steps: review the generated SQL, confirm it returns the right rows, determine its load on the server, remediate and measure again.
• See the SQL with ORM logging (show_sql, LogTo, ToQueryString, echo, dbt compile) or from the server (slow logs, pg_stat_statements, Query Store).
• N+1 selects come from lazy loading; fix them with eager loading. Sibling joins cause cartesian explosion; split the queries. Fetch only the columns you need.
• Rank queries by total time, calls times mean, not by the slowest single run.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 1.4. CompTIA (PDF)
2. SQLAlchemy authors. (n.d.). Relationship loading techniques and Engine configuration. SQLAlchemy 2.0 documentation. loading techniques; engines
3. Red Hat and the Hibernate team. (n.d.). An introduction to Hibernate 6. docs.hibernate.org
4. Microsoft. (n.d.). Single vs. split queries; Eager loading of related data; Lazy loading of related data; Simple logging; What's new in EF Core 5.0 (ToQueryString). Entity Framework Core documentation. split queries; eager loading; lazy loading; logging; EF Core 5.0
5. Ebean. (n.d.). Ebean ORM for Java and Kotlin and Query: fetch. ebean.io; fetch
6. dbt Labs. (n.d.). About dbt compile command and Materializations. dbt Developer Hub. compile; materializations
7. PostgreSQL Global Development Group. (2025). pg_stat_statements and Error reporting and logging (log_min_duration_statement). In PostgreSQL 18 documentation. pg_stat_statements; logging
8. Microsoft. (n.d.). Monitor performance by using the Query Store. SQL Server documentation. learn.microsoft.com

The wholesale question

Harbor Outfitters has signed its first wholesale partners: 40 independent outdoor shops that will order stock through a new portal. Their draft contract asks for three things in writing. The portal must be available 99.9 percent of each month. An order must be confirmed within two seconds. And a critical fault must get a response within 30 minutes, at any hour. The owner forwards the draft with one line: can our database do this, and what do we need?

Nobody can answer that by choosing a product. It is answered the way objective 2.1 lays out: gather the requirements, compare them with what exists, project the resources the new workload will need, and write down service levels that can be measured. This lesson works Harbor's question through those steps.

Gap analysis: where we are against where we must be

A gap analysis lists each requirement, the current state, and the difference between them. It turns a vague worry into a work list.

Notice that half the gaps are not about hardware at all. Gap analysis is valuable precisely because it surfaces process gaps, such as on-call cover and retention rules, alongside technical ones.

Worth holding on to: a gap analysis sets each requirement beside the current state and names the action that closes the difference, and its most useful findings are often about process rather than hardware.

Resource projection: sizing for three years

Resource projection estimates what the database will need over its planned life, usually three years, from measurements rather than guesses. Storage comes first.

1. Measure the bytes per row you already have. PostgreSQL's pg_total_relation_size computes the total disk space a table uses, including all its indexes and its TOAST data, the out-of-line storage for large values. Divide it by the row count: SELECT pg_total_relation_size('order_items') / count(*) FROM order_items;. Suppose the answer is 180 bytes per order line, indexes included.
2. Project the rows. 40 shops placing about 30 orders a day, each with about 12 lines, adds 40 times 30 times 365 times 12, about 5.3 million order lines a year.
3. Multiply. 5.3 million rows at 180 bytes is about 950 MB a year, roughly 2.9 GB over three years for this table.
4. Add what is not table data. Write-ahead log files, temporary space for sorts, room for index rebuilds, and local backup copies can each rival the data itself. A common planning margin is to double the projection.
5. Re-measure every quarter and compare the projection with reality, because the first estimate is always wrong in some direction.

Memory and processing follow the same logic. PostgreSQL's manual suggests that on a dedicated server with 1 GB or more of RAM, a reasonable starting value for shared_buffers, its main data cache, is 25 percent of system memory. The aim is to keep the frequently used part of the data, the working set, in memory. CPU is projected from peak queries per second and the cost of the heaviest queries, measured in testing (Lesson 10).

Storage: size, speed and type

The objectives break storage considerations into size, speed and type. Size you have just projected. Speed has three measures, and different workloads lean on different ones.

Type covers both the medium and how it attaches. Spinning hard drives offer cheap capacity but slow random access; solid-state drives, especially NVMe models attached directly to the PCIe bus, offer far higher IOPS and lower latency. Direct-attached storage (DAS) sits in the server; network-attached storage (NAS) shares files over the network; a storage area network (SAN) presents block storage over a dedicated network. Cloud providers sell block storage in tiers priced by capacity, IOPS and throughput. For Harbor's checkout, latency and IOPS matter far more than raw capacity: the whole database fits on one modest SSD, but every commit waits for a log write.

Users: how many, and what kind

The number of users drives concurrency, and concurrency drives connections. PostgreSQL's max_connections setting defaults to typically 100, and the server sizes some of its resources directly from that value. A web tier of 30 application processes each holding a pool of 20 connections asks for 600, so the plan must either raise the limit, with the memory that costs, or put a connection pooler in front of the database.

The type of users matters as much as their number, because each type brings a different workload and needs different permissions.

• Customers and wholesale buyers, through the applications: short transactions, strict response times.
• Analysts: long read-only queries that scan large tables. Mixed with checkouts on one server, they cause exactly the month-end slowdown in the gap analysis.
• Administrators: few in number, powerful privileges, and the need to be audited.
• Service accounts for integrations and backups: no human behind them, so their credentials and privileges need particular care (Lesson 22).

On premises, cloud or hybrid

The last system specification is where the database will live. On premises, Harbor buys and runs the hardware: full control and predictable cost, but capacity must be bought ahead of need and every failure is Harbor's to fix. In the cloud, capacity is rented, scales on demand and arrives with managed backups and failover, at a running cost and with less control over the platform. Hybrid combines the two, for example keeping the primary database on premises while a disaster-recovery copy runs in the cloud. Lesson 9 compares the cloud service models in detail.

The service-level agreement

A service-level agreement (SLA) is the written promise between the people who run a service and the people who use it. The objectives name its three parts: key performance indicators, escalation procedures and reporting. NIST's work on cloud service metrics makes the underlying point: a promise is only as good as the definition of the metric behind it.

KPIs must be measurable and defined precisely. "Fast" is not a KPI; "95 percent of order confirmations complete within 2 seconds, measured at the application, each calendar month" is. Averages hide the slow tail, which is why percentiles are preferred. Availability needs equally careful arithmetic.

Each extra nine cuts the allowance tenfold. Harbor's hour of monthly patching already breaks 99.9 percent, which is why the gap analysis demanded a standby server. The SLA must also say whether planned maintenance counts as downtime; many agreements exclude announced maintenance windows, and the wholesale shops will want to know.

Escalation procedures define who is told, how fast, and who comes next if nobody responds.

Reporting closes the loop: a monthly report of measured availability and response percentiles against target, every incident with its cause and duration, any breaches and the credits or remedies they trigger, and trends that predict the next breach, such as storage growing faster than projected.

Worth holding on to: an SLA is only enforceable when every KPI has a precise definition and measurement method, every severity has a response time and an escalation path, and someone reports the results each period.

Common misconceptions

• "Sizing means estimating the data." Data is only part of it. Indexes, write-ahead logs, temporary space and local backups can double the figure, and memory, IOPS and connections need projections too.
• "99.9 percent is close enough to 100 percent." It allows 43 minutes of downtime a month, and a single monthly patch window can use all of it.
• "An average response time is a good KPI." Averages hide the slow tail that customers notice. SLAs use percentiles such as the 95th or 99th.
• "More users simply means a bigger server." The type of user matters as much: one analyst's month-end report can slow every customer's checkout.

Summing up

• Gap analysis compares requirements with the current state and names the actions, technical and procedural, that close each gap.
• Resource projection measures bytes per row and current load, multiplies by expected growth over the planning horizon, and adds margin for indexes, logs, temporary space and backups.
• Storage speed is IOPS, throughput and latency; OLTP leans on IOPS and latency, analytics on throughput. Storage type covers HDD, SSD and NVMe, and DAS, NAS, SAN or cloud block storage.
• The number of users sets concurrency and connections; their type sets workload and permissions.
• An SLA needs precisely defined KPIs, escalation procedures with response times, and regular reporting. Each extra nine of availability cuts the downtime allowance tenfold.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 2.1 and acronym list. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). Database object management functions (pg_total_relation_size); Resource consumption (shared_buffers); Connections and authentication (max_connections). In PostgreSQL 18 documentation. size functions; resource consumption; connections
3. National Institute of Standards and Technology. (2018). Cloud computing service metrics description (NIST SP 500-307). nist.gov
4. Mell, P., and Grance, T. (2011). The NIST definition of cloud computing (NIST SP 800-145). National Institute of Standards and Technology. csrc.nist.gov

Seven pages that named the cloud

In September 2011 the US National Institute of Standards and Technology published Special Publication 800-145, The NIST Definition of Cloud Computing by Peter Mell and Timothy Grance. Its definitions fit on two pages, and the industry still uses them. The document defines three service models, software, platform and infrastructure as a service, and four deployment models: private, community, public and hybrid. Each service model is defined by what the consumer does and does not manage or control.

That framing, who controls what, is the most useful way to compare places a database can run, which is the second half of objective 2.1. It turns marketing names into a table of responsibilities.

Who does what, by hosting model

Read the columns against NIST's definitions. In infrastructure as a service, the consumer provisions processing, storage and networks and can run arbitrary software, keeping control over operating systems, storage and deployed applications. Harbor renting a virtual machine and installing PostgreSQL on it is IaaS: the provider keeps the hardware running, and Harbor patches everything above it. In platform as a service, NIST says the consumer does not manage the operating system or storage but controls the deployed applications. In software as a service, the consumer uses the provider's application and at most adjusts limited user-specific settings.

Database as a service (DBaaS) is a platform service specialized for databases, and the objectives list it separately. Amazon's documentation for its Relational Database Service is typical: RDS manages backups, software patching, automatic failure detection and recovery, while the customer remains responsible for query tuning. The schema, the indexes, the users and the data are still the customer's job, so a DBaaS database still needs a database administrator, just one who never installs the engine.

SaaS sits at the far end. A cloud CRM or accounting package has a database inside it, but the customer never sees it. Harbor would reach that data only through the application's reports, exports and API, which is why SaaS data shows up again in Lesson 27 as a source for integration.

So what?: moving up from IaaS to DBaaS to SaaS hands more layers to the provider, but the data, its permissions and, until SaaS, the query tuning always stay with you.

Computational persistence: data that outlives its compute

Cloud computing treats compute as disposable. Virtual machines are stopped, resized and replaced; containers are deleted and recreated on every deployment. A database cannot be disposable, so its files must live somewhere with a longer life than the process that reads them. The objectives call this computational persistence, and it explains several cloud traps.

• Virtual machine disks. Amazon EC2's instance store is fast local storage, but AWS's documentation says its data does not persist if the instance is stopped, hibernated or terminated. Database files belong on persistent block storage that survives the instance.
• Containers. A container's writable layer disappears with the container. Docker's documentation calls volumes the preferred mechanism for persisting data generated and used by containers. Start PostgreSQL in a container without a volume, delete the container, and the database is gone.
• Kubernetes. Its documentation describes a PersistentVolume as storage with a lifecycle independent of any individual Pod that uses it, which is exactly the property a database needs.
• Managed and serverless databases. Many separate compute from storage, so compute can scale or pause while the stored data persists. You pay for the two separately and must know which one an outage affects.

So what?: before trusting a cloud database, ask where its data files and logs physically live and what happens to them when the compute stops; if the answer is "with the instance", it is not a database you can keep.

The phases of deployment

The objectives list the phases of a deployment under installation and configuration. In order, for Harbor's new wholesale server:

Importing quickly

PostgreSQL's manual has a section on populating a database, and its advice reverses the order most people use. COPY moves data between tables and files and loads all the rows in one command instead of a series of INSERTs. For a freshly created table, the manual says the fastest method is to create the table, bulk load it with COPY, then create the indexes. Building an index once over 20 million rows is far cheaper than updating it 20 million times. The same section recommends removing foreign key constraints during the load and running ANALYZE afterwards, so the planner has statistics for the new data (Lesson 17).

Each engine has its bulk path: COPY or psql's client-side copy command and pg_restore for PostgreSQL, LOAD DATA and mysqlimport for MySQL and MariaDB, and mongoimport, which reads Extended JSON, CSV or TSV files, for MongoDB.

Upgrading: minor and major are different jobs

PostgreSQL's documentation draws the line clearly. Minor releases, such as 17.4 to 17.6, never change the internal storage format and are compatible with earlier and later minor releases of the same major version; to update, you simply replace the executables while the server is down and restart it. Major releases, such as 16 to 18, may change the data storage format, and the documentation gives three routes.

Whatever the method, a major upgrade is rehearsed on a copy first, tested with the application (Lesson 10), and scheduled with a written rollback plan.

Common misconceptions

• "A managed database needs no administrator." The provider patches and backs it up, but schema design, indexes, query tuning, users and the data remain the customer's work.
• "A cloud VM's disk is always persistent." Instance store volumes lose their data when the instance stops, hibernates or terminates. Check which kind of storage holds the database files.
• "Containers are fine for databases out of the box." Only with a persistent volume. Without one, deleting the container deletes the database.
• "An upgrade is an upgrade." A PostgreSQL minor upgrade swaps executables; a major upgrade needs dump and restore, pg_upgrade or logical replication, and a rehearsal.

Looking back

• NIST SP 800-145 defines IaaS, PaaS and SaaS by what the consumer controls; DBaaS is a platform service in which the provider runs the engine, patching, backups and failover.
• In every model short of SaaS, schema, tuning, users and data stay with you.
• Computational persistence means data files outlive compute: persistent block storage, container volumes, Kubernetes PersistentVolumes, separated storage in managed services.
• Deployment phases: prerequisites, provisioning, installation and configuration, importing, upgrading, modifying.
• Bulk loads: create the table, COPY the data, then build indexes and run ANALYZE.
• Minor upgrades replace executables; major upgrades use dump and restore, pg_upgrade or logical replication.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 2.1. CompTIA (PDF)
2. Mell, P., and Grance, T. (2011). The NIST definition of cloud computing (NIST SP 800-145). National Institute of Standards and Technology. csrc.nist.gov
3. Amazon Web Services. (n.d.). What is Amazon Relational Database Service? and Data persistence for Amazon EC2 instance store volumes. Amazon RDS; instance store
4. Docker Inc. (n.d.). Volumes. Docker documentation. docs.docker.com; The Kubernetes Authors. (n.d.). Persistent volumes. kubernetes.io
5. PostgreSQL Global Development Group. (2025). Upgrading a PostgreSQL cluster (section 18.6); Populating a database (section 14.4); COPY. In PostgreSQL 18 documentation. upgrading; populating; COPY
6. MongoDB, Inc. (n.d.). mongoimport. MongoDB Database Tools documentation. mongodb.com

The counts match. Is it ready?

Harbor's migration from its old MariaDB server to the new PostgreSQL server finishes early on a Sunday morning. The script reports 1,204,377 order lines read and 1,204,377 written. The counts match exactly. The wholesale portal goes live on Monday. Is the new database ready?

Matching counts prove only that the right number of rows arrived. They say nothing about whether the values are right, whether rows still connect to their parents, whether the application can talk to the new schema, or whether the server survives Monday's traffic. Objective 2.1 splits the remaining work into testing, which asks whether the system behaves, and validation, which asks whether the data is right. This lesson walks through both as one procedure, then changes a single input to show how a migration can pass every check but one.

Step 1: quality-check the columns and tables

A database quality check compares the structure and contents of each table with what was expected. Build a small comparison for every migrated table, run on both sides.

Step 2: test schema compatibility

The application was written against the old schema, so the new one must accept every statement it sends. The fastest test is the application's own test suite pointed at the new database, plus a list of engine differences to check by hand. Harbor meets one at once: its reporting tool sends SELECT * FROM "Orders" with the name in double quotes. PostgreSQL's manual explains the failure: quoting an identifier makes it case-sensitive, whereas unquoted names are always folded to lower case. The table was created unquoted, so it is orders, and "Orders" does not exist. Other common incompatibilities include boolean columns stored as small integers, different date functions, different upsert syntax (Lesson 3) and reserved words used as column names.

Step 3: validate the data mapping

A data mapping is the documented rule for each target column: which source column feeds it and what transformation applies. Harbor's mapping says the old price_cents integer becomes unit_price numeric(8,2) divided by 100, status codes 1 to 4 become words, and timestamps move to timestamptz. Validation checks each rule with a query, for example that no unit_price is above the most expensive product, which would reveal a missing division.

Step 4: check the data values

Value checks test domains, the ranges and formats a column must respect: no negative quantities, every email containing an @, every status in the allowed list, no order dated in the future. Write each as a query that should return zero rows, so the whole set can run as a script after every load: SELECT order_id FROM orders WHERE status NOT IN ('new', 'paid', 'shipped', 'cancelled');.

Step 5: check referential integrity

Fast loads often drop foreign key constraints, as Lesson 9 recommended, which means nothing stopped an orphan from arriving. Look for children whose parent is missing:

SELECT count(*) FROM order_items oi LEFT JOIN orders o ON o.order_id = oi.order_id WHERE o.order_id IS NULL;

The answer must be 0. Then restore the constraint. PostgreSQL lets you split the work: add the foreign key with NOT VALID, which enforces it for new rows at once, then run VALIDATE CONSTRAINT later. The manual notes that the database will not assume the constraint holds for all rows until it has been validated.

The core of it: testing asks whether the system behaves and validation asks whether the data is right, and a migration needs both, because perfect row counts can sit on top of wrong values and orphaned rows.

Step 6: analyze the indexes

Index analysis confirms that the indexes the workload needs exist, and that no useless ones remain. Three questions cover most of it.

• Is every foreign key column indexed? PostgreSQL's manual is explicit that declaring a foreign key does not automatically create an index on the referencing columns, although it is often a good idea, because deleting or updating a parent row must otherwise scan the child table. Primary keys, unique constraints and exclusion constraints, by contrast, do create indexes.
• Do the key queries use the indexes? Run EXPLAIN on the ten most important queries and compare the plans with the old system's (Lesson 17).
• Are any indexes unused? After a period of normal traffic, the statistics view pg_stat_user_indexes shows idx_scan, the number of scans that used each index. An index still at zero costs write time and disk for nothing and is a candidate for removal.

Step 7: stress test the database and the application

The objectives name two targets for stress testing, and they find different problems.

A stress test pushes past the expected peak, deliberately, to find where and how the system breaks: Harbor tests at two and three times the wholesale forecast and records the load at which the 95th-percentile confirmation time crosses the SLA's two seconds.

Step 8: regression testing

Regression testing re-runs checks that passed before a change to prove they still pass after it. Before the migration Harbor saved the output of 40 business queries: monthly revenue, stock by warehouse, top customers. After it, the same queries run on the new server, and each pair of results is compared in both directions with EXCEPT. Any row present on one side only is a regression. The same suite runs again after every later upgrade and schema change, which is what makes it worth building.

Change one input: the midnight orders

Harbor's migration passes steps 1 to 7. Row counts match, the grand total of all order values matches to the cent, there are no orphans, and pgbench is comfortable. Then regression testing flags one report: monthly revenue. Yearly totals agree, but March is 412 dollars higher in the new system and February 412 dollars lower.

The cause is in the data mapping. The old ordered_at column was a MariaDB DATETIME, which stores a date and time with no time zone; MySQL's manual notes that only TIMESTAMP values are converted to and from UTC, not DATETIME. Harbor's server ran on local time, but the migration loaded the values into timestamptz as if they were UTC. Every order is now shifted by the time-zone offset, and the handful placed in the last hours of February crossed into March. Totals at the grain of a year cannot see the error; totals at the grain of a month can.

The lesson generalizes. Compare aggregates at several grains, year, month, day and customer, because each grain catches errors the others hide. And write the time zone of every timestamp into the data mapping before the load, not after the report.

The core of it: a check only catches errors at the level of detail it measures, so validate totals at several grains and test every mapping rule, especially units, codes and time zones.

Common misconceptions

• "If the row counts match, the migration worked." Counts miss wrong values, broken mappings, orphans and type changes.
• "Stress testing and regression testing are the same thing at different volumes." Stress testing finds the breaking point under load; regression testing proves that what worked before still works.
• "Foreign keys get indexes automatically." Primary keys and unique constraints do. PostgreSQL does not index the referencing columns of a foreign key; validation must check them.
• "Testing the database is enough." Many failures appear only through the application: pool exhaustion, ORM query patterns, timeouts.

What to remember

• Quality checks: row counts, types, NULL counts, ranges, distinct counts and sums per table, on both sides.
• Schema compatibility: run the application's tests against the new schema; watch identifier case, types and syntax differences.
• Validation: test every data-mapping rule, check value domains, find orphans before restoring constraints, and analyze indexes, including foreign key columns and unused indexes.
• Stress testing pushes the database (pgbench) and the application (a load generator) past peak; regression testing re-runs saved results after every change.
• Compare aggregates at several grains; a year-level match can hide a month-level error.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 2.1. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). Lexical structure (identifiers); Constraints (foreign keys); ALTER TABLE (NOT VALID and VALIDATE CONSTRAINT). In PostgreSQL 18 documentation. identifiers; constraints; ALTER TABLE
3. PostgreSQL Global Development Group. (2025). pgbench and The cumulative statistics system (pg_stat_user_indexes). In PostgreSQL 18 documentation. pgbench; statistics views
4. Oracle. (n.d.). The DATE, DATETIME, and TIMESTAMP types. In MySQL 8.4 reference manual, section 13.2.2. dev.mysql.com
5. Apache Software Foundation. (n.d.). Apache JMeter. jmeter.apache.org

From a whiteboard to a table

Harbor's wholesale manager explains the new business in one meeting, standing at a whiteboard. Each partner shop has an account and one or more buyers who can place orders. Every shop gets a price list, and some get special prices on certain products. An order belongs to one shop, is placed by one buyer, and has many lines. By the end of the meeting the whiteboard holds boxes, arrows and a few prices circled in red.

That drawing is the start of a schema, but it is not yet anything a database can store. Objective 2.2 asks you to implement design and documentation techniques, so this lesson follows Harbor's whiteboard through two levels of design, logical and physical, and into the two documents every database should have: an entity relationship diagram and a data dictionary. Then a second request, from the analysts, shows why the same data is sometimes stored in a completely different shape. Databases & SQL (cs-db) teaches ER modelling and normalization from the beginning; this lesson builds on them.

Three levels of a schema

The objectives name the logical and physical levels. The test for which level a detail belongs to is simple: would it change if Harbor moved from PostgreSQL to SQL Server? A data type, an index, a partition or a storage location would change, so it is physical. "An order belongs to exactly one shop" would not, so it is logical.

Bottom line: the logical schema records what the data means and how it relates, independent of any product; the physical schema records how one engine will store and index it.

Drawing it: entity relationship diagrams and UML

An entity relationship diagram (ERD) draws entities as boxes and relationships as lines, with symbols at each end of a line showing cardinality, how many, and optionality, whether any are required. The most common style in industry is crow's foot notation.

Read a line from each end. Between Shop and Order, two bars at the Shop end and a circle with a crow's foot at the Order end say that each order belongs to exactly one shop, and each shop has zero or many orders. A many-to-many relationship, such as shops and the products with special prices, cannot be stored directly; the logical model resolves it into an associative entity, here ShopPrice, with one row per shop and product.

Some teams draw the same model as a UML class diagram. The Unified Modeling Language, maintained by the Object Management Group and currently at version 2.5.1, shows classes with attributes and associations with multiplicities such as 1 and 0..*. It suits teams whose developers already use UML; CompTIA lists UML tools in its sample lab software. Either notation works if everyone reading it knows the conventions.

Diagrams can also be produced from a live database. DBeaver, for instance, draws diagrams for existing tables and schemas, which is a quick way to check that the physical schema still matches the design.

From logical to physical

Turning the logical model into Harbor's PostgreSQL tables means making engine decisions for every attribute and relationship.

• Keys. The logical key of a shop might be its trade registration number, but the physical table uses a surrogate integer identity as its primary key and keeps the registration number with a UNIQUE constraint, because external identifiers change.
• Types. Money becomes numeric(10,2), dates become date or timestamptz, and short codes get CHECK constraints listing their allowed values.
• Relationships. Each one-to-many line becomes a foreign key, and, as Lesson 10 noted, PostgreSQL does not index the referencing column for you, so the physical design adds those indexes.
• Storage. Large tables may be partitioned (Lesson 17); some columns may be stored compressed or in a separate tablespace.

The data dictionary

A data dictionary describes every table and column in words a new colleague, an auditor or an integration partner can use. A useful entry records:

The common failure is a dictionary kept in a spreadsheet that drifts away from the real schema within months. The cure is to store the descriptions in the database and generate the document from its catalog. PostgreSQL's COMMENT command stores, replaces or removes the comment on a database object: COMMENT ON COLUMN orders.status IS 'Life-cycle state: new, paid, shipped, cancelled';. The information schema, which PostgreSQL's manual describes as a set of views defined in the SQL standard and therefore portable and stable, supplies the rest:

SELECT c.table_name, c.column_name, c.data_type, c.is_nullable, col_description((c.table_schema || '.' || c.table_name)::regclass, c.ordinal_position) AS description FROM information_schema.columns c WHERE c.table_schema = 'public' ORDER BY c.table_name, c.ordinal_position;

Run that query in each release and the dictionary can never fall behind the schema it describes.

Bottom line: generate the data dictionary from the database's catalog and stored comments, so documentation and schema change together.

OLTP and OLAP: two shapes for two jobs

A month after the wholesale launch the analysts ask for five years of sales by shop, region, product category and month, refreshed daily. Run against the order tables, their queries join six tables, scan millions of rows and slow down checkout. The order tables were designed for a different job.

The usual OLAP design is the star schema, from Ralph Kimball's dimensional modelling. A central fact table records events at a declared grain, here one row per order line, with numeric measures such as quantity and revenue and a foreign key to each dimension. Around it sit dimension tables, wide and descriptive: date (with month, quarter, holiday flags), product (with category and brand), shop (with region). Microsoft's guidance for Power BI puts it plainly: fact tables store observations or events, and dimension tables describe the business entities you model. A question such as revenue by region and month becomes one fact table joined to two dimensions, filtered and grouped.

The duplication is deliberate. Region is repeated on every shop row instead of living in its own table, because analysts query far more often than shops move region. A snowflake schema normalizes some dimensions back out, saving space at the price of extra joins. The OLTP system remains the source of truth, and a scheduled load, the subject of Lesson 27, copies its changes into the star.

Common misconceptions

• "The logical schema is just the physical schema without indexes." The logical schema is independent of any product: entities, attributes, keys and relationships. Types, indexes, partitions and storage belong to the physical design.
• "A data dictionary is a one-time document." A dictionary that is not regenerated with each release is wrong within months. Store descriptions with COMMENT and generate it from the catalog.
• "Denormalization is a design mistake." In OLTP it invites update anomalies; in OLAP it is the point, trading storage and update cost for simpler, faster analytic queries.
• "Crow's foot notation and UML say different things." They are two notations for the same model. Both show cardinality and optionality.

The takeaway

• Conceptual, logical and physical schemas move from business language to product-independent model to engine-specific DDL.
• ERDs in crow's foot notation show cardinality and optionality at each end of a line; UML class diagrams show the same with multiplicities. Many-to-many relationships become associative entities.
• Physical design chooses keys, types, constraints, indexes (including on foreign keys) and storage.
• A data dictionary gives each column's type, meaning, allowed values, source, owner and sensitivity; generate it from COMMENT and the information schema.
• OLTP systems are normalized for many short transactions; OLAP systems use star schemas, facts at a declared grain surrounded by descriptive dimensions, for fast analysis.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 2.2 and hardware and software list. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). COMMENT and The information schema (chapter 35). In PostgreSQL 18 documentation. COMMENT; information schema
3. Object Management Group. (2017). Unified Modeling Language specification, version 2.5.1. omg.org
4. Microsoft. (n.d.). Understand star schema and the importance for Power BI. learn.microsoft.com
5. DBeaver. (n.d.). ER diagrams. DBeaver documentation. dbeaver.com
6. Kimball, R., and Ross, M. (2013). The data warehouse toolkit: The definitive guide to dimensional modeling (3rd ed.). Wiley.

Two acronyms, twenty-five years apart

In 1983 Theo Härder and Andreas Reuter, writing in ACM Computing Surveys about how databases recover from failure, gave the guarantees of a transaction a four-letter name: atomicity, consistency, isolation and durability, ACID. In 2008 Dan Pritchett, an engineer at eBay, published "BASE: An Acid Alternative" in ACM Queue, arguing that very large distributed systems should relax those guarantees in exchange for availability and scale. CompTIA's acronym list expands his acronym as basically available, soft state and eventually consistent.

Both positions have serious engineering behind them, and objective 2.2 asks you to know both. At Harbor Outfitters the argument arrives as a real decision. The web team wants to move the product catalogue, carts and page-view counters to a distributed store that stays fast and available across regions. The finance lead wants every order, payment and stock movement to stay exactly where it is, in one ACID database. This lesson sets out each case on its own evidence, then shows what settles it.

ACID, letter by letter

PostgreSQL's manual states the central idea of its write-ahead log, or WAL: changes to the data files must be written only after those changes have been logged, with the log records flushed to permanent storage. That one rule delivers both atomicity and durability. After a crash, the server replays the log for committed transactions and ignores the work of uncommitted ones.

Key idea: ACID makes each transaction all-or-nothing, constraint-preserving, isolated and permanent once committed, and the write-ahead log is what makes the first and last of those survive a crash.

BASE, letter by letter

BASE describes systems that keep accepting reads and writes even when parts of the system cannot talk to each other.

• Basically available: the system answers requests even during failures, possibly with stale or partial data.
• Soft state: the state of the system may change over time without new input, as replicas exchange updates.
• Eventually consistent: if new updates stop, all replicas converge on the same value, but at any given moment two readers may see different values.

Apache Cassandra's documentation describes its storage model as partitioned wide-column with eventually consistent semantics. It also offers tunable consistency: for each operation, the client chooses a consistency level such as ONE, QUORUM or ALL, the number of replicas that must respond. With a replication factor of 3, writing and reading at QUORUM (2 of 3) guarantees that every read overlaps at least one replica that took the latest acknowledged write. Writing and reading at ONE is faster and more available, and may return stale data.

The line between the camps has blurred. MongoDB's documentation says its multi-document transactions provide atomicity, consistency, isolation and durability according to the configured read and write concern settings. Its default write concern is majority: an acknowledgment that a majority of voting members have durably written the change. The question is rarely which product, and more often which setting, for which data.

The case for ACID at Harbor

The finance lead's evidence is concrete. Lesson 5's oversold tent happened because a check and an update were not one atomic unit. A payment recorded without its order, or an order without its payment, has to be found and repaired by a person, and the accountant reconciles every one. Stock that is briefly wrong in two regions can be sold twice. Auditors expect the books to balance at every moment, not eventually. For data where a single wrong value costs money or trust, the latency of a single strongly consistent database is a small price.

The case for BASE at Harbor

The web team's evidence is just as concrete. The catalogue is read thousands of times for every write, from customers in several countries, and a product description that is thirty seconds out of date harms nobody. A shopping cart that refuses to accept an item because a replica is unreachable loses a sale; one that accepts it and reconciles later does not. Page-view counters can be approximate. For data like this, insisting on strong consistency buys nothing and costs availability.

What settles it

Eric Brewer's CAP theorem frames the trade. A distributed system facing a network partition must choose between consistency, every read sees the latest write, and availability, every request gets an answer. In his 2012 retrospective, "CAP Twelve Years Later", Brewer stressed that the choice only bites during partitions and can be made separately for different operations and data. That points to the answer Harbor adopts: classify each data set by the cost of a stale or lost write.

Note the cart row. The common, robust pattern is BASE where the customer browses and ACID at the moment money and stock change hands.

Key idea: choose ACID or BASE per data set, not per company, by asking what a stale or lost write would cost; distributed systems that let you tune consistency per operation make that choice explicit.

Writing it down: standard operating procedures

Decisions like these only hold if the people running the system follow the same steps every time. A standard operating procedure (SOP) is the written, step-by-step instruction for a routine task. The US Environmental Protection Agency's guidance on preparing SOPs, written for quality systems, makes the case that holds for databases too: written procedures make routine work consistent and checkable, whoever does it. A good database SOP contains its purpose and scope, the roles allowed to perform it, prerequisites, numbered steps, verification checks after each risky step, a rollback, the records to keep, and a review date.

The objectives name three kinds of SOP documentation.

SOPs also carry the consistency decisions above. Harbor's maintenance SOP for the catalogue cluster states the consistency level each service must use, and its incident SOP says who may lower it during an outage and how the change is recorded and reversed.

Common misconceptions

• "The C in ACID and the C in CAP are the same." ACID's consistency means constraints and invariants hold after each transaction. CAP's consistency means every read sees the most recent write across replicas.
• "NoSQL databases cannot do transactions." MongoDB supports multi-document ACID transactions, and Cassandra lets each operation choose a stronger consistency level. The trade-off is chosen, not fixed.
• "Eventually consistent means consistent after a nightly batch." It means replicas converge once updates stop, usually within moments; there is simply no guarantee at any given instant.
• "An SOP is a policy." A policy says what must happen; an SOP says exactly how, step by step, with checks and rollback.

Recap

• ACID: atomicity, consistency, isolation, durability. Write-ahead logging delivers atomicity and durability; constraints deliver consistency; isolation levels and locks deliver isolation.
• BASE: basically available, soft state, eventually consistent. Replicas converge once updates stop.
• Tunable consistency lets each operation choose, for example QUORUM reads and writes with three replicas for read-your-latest-write behaviour.
• CAP: during a partition, choose consistency or availability; the choice can differ by operation and data.
• Choose per data set by the cost of a stale or lost write: ACID for money and stock, BASE for catalogue views and counters.
• SOPs are step-by-step instructions with checks and rollback, written for organizational compliance, maintenance and third-party compliance.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 2.2 and acronym list. CompTIA (PDF)
2. Härder, T., and Reuter, A. (1983). Principles of transaction-oriented database recovery. ACM Computing Surveys, 15(4), 287-317. DOI 10.1145/289.291.
3. Pritchett, D. (2008). BASE: An Acid alternative. ACM Queue, 6(3), 48-55. DOI 10.1145/1394127.1394128.
4. Brewer, E. (2012). CAP twelve years later: How the "rules" have changed. Computer, 45(2), 23-29. DOI 10.1109/MC.2012.37.
5. PostgreSQL Global Development Group. (2025). Write-ahead logging (WAL) (section 28.3). In PostgreSQL 18 documentation. postgresql.org
6. Apache Software Foundation. (n.d.). Dynamo (tunable consistency and consistency levels). Apache Cassandra documentation. cassandra.apache.org
7. MongoDB, Inc. (n.d.). Transactions and Write concern. MongoDB manual. transactions; write concern
8. US Environmental Protection Agency. (2001). Guidance for preparing standard operating procedures (EPA QA/G-6). epa.gov

"The database is down"

The new wholesale portal's application server cannot connect to Harbor's PostgreSQL server. The developer's message says the database is down. It is the most natural guess and usually the wrong one. Log in to the database server itself and psql connects instantly; the server is running, healthy and idle. Something between the application and the database is failing, and objective 2.3 is about exactly that space: client/server architecture, DNS, addressing, ports and protocols, firewalls and perimeter networks, load balancing, and multizone regions.

A client connection succeeds only when five things line up, in order. Debugging means checking them in that order, because each check is meaningless until the one before it passes.

The point: a connection failure is a layer problem, not a verdict on the server; check name, path, listener, authentication and encryption in that order and the first failing layer tells you where to look.

Layer 1: the name, and the address behind it

Clients should find a database by a DNS name, never a hard-coded IP address, because addresses change and names can be repointed. Two details cause most name problems. First, a server whose address comes from DHCP, dynamic addressing, can return from a power cut with a different address; database servers should have static addresses or DHCP reservations, with clients using the name. Second, DNS answers are cached for their time to live (TTL). After a failover repoints db.harbor.internal to the standby, clients that cached the old answer keep connecting to the old primary until the TTL expires, which is why failover records use short TTLs.

Layer 2: the path and the port

Each engine listens on a well-known default port, and firewalls must allow it from the right sources only.

A timeout, rather than a refusal, usually means a firewall silently dropped the packets. There are often two: the host firewall on the database server and a network firewall between segments. NIST's guidelines on firewall policy recommend denying traffic by default and allowing only what is required, which for a database means: the database port, from the application servers' addresses, and nothing else.

Network placement follows the same logic. A perimeter network, often called a DMZ, holds the systems that must face the internet, such as Harbor's web servers. The database does not belong there. It sits in an internal segment that only the application tier can reach, so an attacker who compromises a web server still faces a second firewall. Administrators reach it through a hardened jump host or VPN, not from the internet.

Layer 3: is anything listening?

Harbor's case resolves here. Test-NetConnection shows port 5432 refusing connections, and on the server the reason is a default. PostgreSQL's listen_addresses setting defaults to localhost, which, in the manual's words, allows only local TCP/IP loopback connections. A fresh install accepts nobody from the network until an administrator sets listen_addresses to the server's internal address and restarts it. That default is deliberate: a new database should not be reachable before it is secured.

pg_isready is the quick check from any client, and its exit status is precise: 0 when the server is accepting connections, 1 when it is rejecting them, for example during startup, 2 when there was no response to the connection attempt, and 3 when no attempt was made, for example because of invalid parameters.

Layer 4: will the server let you in?

Once the listener answers, PostgreSQL decides who may connect using client authentication rules that, as its manual says, are controlled by a configuration file traditionally named pg_hba.conf. Each line matches a connection type, database, user and client address range to an authentication method. A connection from an address no line matches fails with an error saying there is no pg_hba.conf entry for that host. That is good news when debugging: the network works, and the fix is one carefully scoped rule, such as the application's user, from the application subnet, to the harbor database, with password authentication over TLS.

Load balancing: more than one server behind one name

Once Harbor adds read replicas, connections need routing. Load balancing spreads work across servers, but a database adds a constraint that web servers do not have: writes must go to the primary, while reads may go to replicas that are slightly behind.

• A proxy or pooler in front of the servers, such as HAProxy, PgBouncer or ProxySQL, can route connections and health-check each server.
• The client library can do it. PostgreSQL's libpq accepts several hosts in one connection string and tries each host in turn until a connection succeeds. Adding target_session_attrs=read-write makes it accept only a server that allows read-write sessions, the primary, so a failover needs no configuration change; a load_balance_hosts option can spread connections across hosts, for example across standbys only.
• MongoDB drivers discover the replica set themselves, send writes to the primary, and use a read preference, which MongoDB describes as how clients route read operations to the members of a replica set.

Regions and zones

Cloud providers group data centres into regions, and divide each region into zones that are physically separate. AWS describes Availability Zones as multiple, isolated locations within each Region. IBM's documentation uses the term the objectives use, a multizone region (MZR), composed of three or more data centres in multiple zones with separate power, cooling and network connectivity.

For a database, zones matter in two ways. Placing the primary and its standby in different zones protects against losing a whole data centre, at the cost of a little extra latency on every replicated write. And clients should connect to the database in their own zone when they can, because cross-zone traffic adds latency and, at many providers, cost. Lesson 26 returns to zones when it compares high-availability designs; a whole-region outage needs a copy in a second region.

Common misconceptions

• "If the application cannot connect, the database is down." Usually the server is fine and a layer between them, name, firewall, listener or authentication rule, is not.
• "A refused connection and a timeout mean the same thing." Refused means something answered and said no, often nothing listening on that address. A timeout usually means a firewall dropped the traffic.
• "The database belongs in the DMZ with the web servers, for speed." The perimeter network is for internet-facing systems. The database belongs behind a second firewall, reachable only from the application tier.
• "A load balancer can send any query to any replica." Writes must reach the primary, and reads from replicas may be slightly stale.

What to carry forward

• Debug connections in order: name, path, listener, authentication, encryption.
• Use DNS names with short TTLs for failover, and static addresses or DHCP reservations for database servers.
• Default ports: PostgreSQL 5432, MySQL and MariaDB 3306, SQL Server 1433 (Browser UDP 1434), MongoDB 27017.
• Deny by default; allow the database port only from the application tier; keep the database out of the perimeter network.
• PostgreSQL listens only on localhost by default and admits clients only through pg_hba.conf rules; pg_isready's exit codes separate refusal from silence.
• Load balancing routes writes to the primary and reads to replicas, through a proxy, libpq's multi-host connection strings or MongoDB's read preference.
• Zones in a multizone region are physically separate; a primary and standby in different zones survive the loss of one.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 2.3. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). Connections and authentication (listen_addresses, port); The pg_hba.conf file; pg_isready; Database connection control functions (multiple hosts, target_session_attrs). In PostgreSQL 18 documentation. connections; pg_hba.conf; pg_isready; libpq
3. Microsoft. (n.d.). Configure the Windows Firewall to allow SQL Server access. learn.microsoft.com
4. Oracle. (n.d.). Connecting to the MySQL server using command options (default port 3306). In MySQL 8.4 reference manual, section 6.2.4. dev.mysql.com
5. MongoDB, Inc. (n.d.). Default MongoDB port and Read preference. MongoDB manual. ports; read preference
6. Scarfone, K., and Hoffman, P. (2009). Guidelines on firewalls and firewall policy (NIST SP 800-41 Rev. 1). National Institute of Standards and Technology. csrc.nist.gov
7. Amazon Web Services. (n.d.). Regions and Zones. Amazon EC2 user guide. docs.aws.amazon.com; IBM. (n.d.). Locations. IBM Cloud documentation. cloud.ibm.com

One row per process

PostgreSQL keeps a view called pg_stat_activity with, in its manual's words, one row per server process, showing information related to the current activity of that process, such as state and current query. Run SELECT pid, usename, client_addr, state, now() - xact_start AS in_transaction, left(query, 60) FROM pg_stat_activity; on Harbor's server at noon and you see the shop as the database sees it. There are 38 sessions from the web tier, two analysts and a backup process. There is also one session from a laptop that has sat in an open transaction since 9:14.

That last row is the kind of thing monitoring exists to catch. Objective 3.1 asks you to explain the purpose of monitoring and reporting, and lists what to watch in four groups, plus deadlocks, which Lesson 15 covers. This lesson compares the groups, then turns measurements into baselines, alerts and reports.

The four groups, side by side

System alerts: growth, jobs and backups

Growth alerts work best as forecasts. A threshold at 90 percent full gives no warning for a disk filling at 5 percent a day, and plenty for one filling at 1 percent a month. Record pg_database_size and free space daily, fit the trend, and alert on days until full. That also feeds the resource projection from Lesson 8, which is only as good as the measurements that later test it.

Jobs fail silently unless something checks them. Think of a scheduled export that exits with an error, a maintenance task that never started, or a backup that wrote a zero-byte file. Each needs an alert on failure and, just as important, an alert on the absence of success. "No successful backup in the last 26 hours" catches the job that was accidentally deleted, which an alert on failures never will. Lesson 24 adds restore testing, because a backup that succeeded but cannot be restored is the worst kind of silent failure.

System health: usage, throughput, configuration and logs

Health is judged against a baseline: a record of normal behaviour, kept by hour of day and day of week. Harbor at 10:00 on a Monday and at 03:00 on a Sunday are different systems. Daily usage, throughput and response time all need one. Throughput comes straight from the statistics views: pg_stat_database's xact_commit counts the transactions committed in each database, so sampling it every minute gives commits per second. A sudden drop is often the first sign that the application, not the database, has a problem.

The baseline configuration is the recorded set of settings the server should be running. The pg_settings view provides access to the server's run-time parameters, so a scheduled comparison of pg_settings with the recorded baseline catches drift, such as an emergency change during an incident that nobody reverted.

Log files carry what the counters cannot: errors, crashes, slow statements and security events. PostgreSQL can log connection attempts and authentication details with log_connections, and the end of each session with log_disconnections. It can also log statements slower than a threshold with log_min_duration_statement, and, with log_lock_waits, any session that waits longer than deadlock_timeout for a lock. Reviewing those logs regularly is a maintenance task in its own right (Lesson 16).

Why this matters: a number on a dashboard means nothing without a baseline; compare every measure with its normal value for that hour and day, and record the expected configuration so drift is visible.

Resource utilization: the operating system's view

The database runs on an operating system, and many database problems are resource problems underneath. Watch four resources and know what each symptom suggests.

• CPU: sustained high use from database processes usually means expensive queries; check pg_stat_statements (Lesson 7). High CPU with low throughput suggests lock spinning or a bad plan.
• Memory: the danger sign is swapping, when the OS moves memory pages to disk. A database that swaps slows dramatically; reduce memory settings or add RAM.
• Disk IOPS and latency: IOPS near the volume's limit, or latency per operation climbing above baseline, means storage is the bottleneck, whatever the free space says.
• Disk space: includes the write-ahead log and temporary files, which can grow suddenly during large sorts or when a replica stops consuming the log.

Data metering, the last item in the objectives' list, measures how much of a resource each consumer uses, over time. In the cloud it is how you are billed: NIST's definition of cloud computing lists measured service as an essential characteristic, in which resource use is metered, monitored, controlled and reported. Inside an organization, metering by application or tenant shows which team's workload consumes the storage, the IOPS or the data transfer, which turns a capacity argument into a conversation with numbers.

Connections and sessions

Count concurrent connections against the limit: in PostgreSQL, the rows in pg_stat_activity against max_connections; in MySQL, Max_used_connections, which the manual defines as the maximum number of connections in use simultaneously since the server started. When the limit is reached, new connections are refused, and to customers that looks like an outage.

Session state matters as much as the count. A session idle in transaction, like the laptop at 9:14, has opened a transaction and stopped. PostgreSQL's manual warns that such sessions can hold locks, and that even without locks an open transaction prevents vacuuming away dead row versions, so it can contribute to table bloat. Alert on any transaction open longer than a few minutes, and consider the idle_in_transaction_session_timeout setting.

Failed and attempted connections are both an operations signal and a security signal. MySQL's Aborted_connects counts failed attempts to connect to the server; PostgreSQL's connection logging records each attempt and its authentication. A handful of failures a day is a mistyped password. Thousands an hour is either a misconfigured client after a password change or someone guessing passwords, and both need action today.

Alerts people act on, and reports that tell a story

An alert should be actionable: if nobody needs to do anything, it belongs in a report, not a page at 03:00. Use two levels, warning and critical; route critical alerts through the escalation procedure in the SLA (Lesson 8); and review alert history monthly to remove the ones that only create noise. Teams that receive dozens of meaningless alerts learn to ignore all of them, including the one that matters.

Reports summarize what alerts do not. The daily health summary covers backups, failed jobs and errors in the log. The weekly capacity report covers growth and forecasts, connection peaks and the top queries by total time, and the monthly report covers the SLA. Reports are also where slow trends show up before they become incidents.

Common misconceptions

• "An alert at 90 percent disk is enough." At fast growth it fires too late; at slow growth too early. Forecast days until full.
• "If no backup failed, the backups are fine." A deleted or disabled job never fails. Alert on the age of the last successful backup.
• "Idle sessions cost nothing." Idle is fine; idle in transaction can hold locks and blocks cleanup of dead rows.
• "Failed logins are just users mistyping passwords." A sudden spike can be an attack or a broken integration. Both need investigation.

What you now know

• System alerts cover storage growth and limits, job completion or failure, and backups, including the age of the last success.
• System health covers daily usage, throughput such as commits per second, the baseline configuration in pg_settings, and the logs.
• Resource utilization covers CPU, memory and swapping, disk IOPS, latency and space, and data metering of who uses what.
• Connections and sessions cover concurrency against limits, idle-in-transaction sessions, and failed or attempted connections.
• Baselines by hour and day make anomalies visible; alerts must be actionable and routed by severity; reports carry the trends.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 3.1. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). The cumulative statistics system (pg_stat_activity, pg_stat_database); Error reporting and logging; Client connection defaults (idle_in_transaction_session_timeout); pg_settings. In PostgreSQL 18 documentation. statistics; logging; client defaults; pg_settings
3. Oracle. (n.d.). Server status variables (Aborted_connects, Max_used_connections). In MySQL 8.4 reference manual. dev.mysql.com
4. Mell, P., and Grance, T. (2011). The NIST definition of cloud computing (NIST SP 800-145), essential characteristic: measured service. csrc.nist.gov

Two messages in the log

During Harbor's spring sale the PostgreSQL log fills with a message nobody has seen before: an error saying a deadlock was detected, followed by details of two processes, each waiting for a lock the other holds. Customers see a checkout error, press Buy again, and the second attempt works. The same afternoon an analyst reports that a stock report showed a total that never existed at any moment. Both problems come from the same place: many transactions working on the same rows at once.

The objectives place deadlock monitoring under 3.1 and isolation levels under the data management tasks of 3.3. They belong together, because the isolation level decides what a transaction may see of others' work, and locks are how the engine enforces it. Start with the deadlock.

How a deadlock forms

Two customers each buy two items, a stove (product 7) and a lantern (product 9). The application updates inventory one product at a time, in the order the items sit in each basket.

At time 4 each session waits for the other, and neither can ever proceed. That cycle of waits is a deadlock. No amount of patience resolves it; one transaction must give way.

How engines detect and resolve it

• PostgreSQL waits for deadlock_timeout, one second by default, before checking whether a lock wait is part of a cycle. Its manual says it then resolves the deadlock by aborting one of the transactions involved, allowing the others to complete.
• MySQL's InnoDB detects deadlocks automatically and rolls back a transaction to break them, trying to pick small transactions, measured by rows inserted, updated or deleted.
• SQL Server chooses a deadlock victim, rolls back its transaction, releases its locks and returns error 1205 to the application.

In every case the victim's whole transaction is rolled back, and the application must retry it. That is why Harbor's customers succeeded on their second press: the retry ran after the other transaction had finished.

Monitoring deadlocks and blocking

Deadlocks are monitored for their rate and their pattern. In PostgreSQL, the deadlocks column of pg_stat_database counts the deadlocks detected in each database, so a scheduled sample gives a rate per hour to compare with the baseline. Setting log_lock_waits writes a log message whenever a session waits longer than deadlock_timeout for a lock, which reveals blocking before it becomes a deadlock. For a live view, the pg_locks view and the function pg_blocking_pids(), which returns the process IDs blocking a given session, show who waits for whom. In MySQL, the InnoDB monitor output from SHOW ENGINE INNODB STATUS includes a LATEST DETECTED DEADLOCK section with both transactions' statements. SQL Server applications see error 1205, and the server records deadlock details that administrators can retrieve as a deadlock graph.

The pattern matters more than any single event. The same two statements deadlocking every day point to a code path that locks rows in inconsistent order.

Preventing deadlocks

PostgreSQL's manual gives the main defence directly: make sure all applications acquire locks on multiple objects in a consistent order. For Harbor, that means updating inventory rows in ascending product_id order, whatever order they sit in the basket. A clean way is to lock them first: SELECT product_id FROM inventory WHERE product_id IN (7, 9) ORDER BY product_id FOR UPDATE;, then update. Both sessions now request row 7 first, so one simply waits for the other and no cycle can form.

Three habits reduce the rest: keep transactions short, so locks are held briefly; index the columns in UPDATE and DELETE conditions, so statements lock only the rows they need; and write retry logic for the deadlocks that remain, because in a busy system some always will.

What matters here: a deadlock is a cycle of lock waits that the engine breaks by rolling back a victim; monitor the rate, read the pattern, and prevent recurrences by locking in a consistent order and keeping transactions short.

Isolation levels: what each transaction may see

The analyst's impossible stock total is an isolation problem. The SQL standard defines four isolation levels by which of these phenomena they allow.

• Dirty read: reading another transaction's uncommitted change, which may later be rolled back.
• Nonrepeatable read: re-reading a row in the same transaction and finding it changed by another transaction that committed in between.
• Phantom read: re-running a query and finding new rows that another transaction inserted and committed.
• Serialization anomaly: a set of concurrent transactions, each committed, whose combined result matches no possible order of running them one at a time.

The table follows PostgreSQL's manual, which also says that although you can request all four levels, PostgreSQL implements only three: its read uncommitted behaves like read committed. Read committed is PostgreSQL's default. InnoDB's default is repeatable read, and SQL Server's is read committed. PostgreSQL's multiversion concurrency control means, in its manual's words, that reading never blocks writing and writing never blocks reading; each statement or transaction sees a consistent snapshot.

The analyst's report summed stock across several queries in one read committed transaction. Each query took a fresh snapshot, so sales that committed between the queries were counted in some sums and not others. Running the report at repeatable read gives every query the same snapshot, and the total becomes a true picture of one moment.

Stronger isolation has a price. At repeatable read and serializable, PostgreSQL may cancel a transaction with a serialization failure rather than allow an anomaly, and the manual says applications using these levels must be prepared to retry transactions. Choose the level per transaction: read committed for most short updates, repeatable read for multi-query reports, serializable where business rules span many rows and must never be broken.

The lost update

One more concurrency error is common enough to name. Two sessions read on_hand = 5, each subtracts 1 in application code, and both write 4. One sale has vanished from the count. Under read committed nothing stops this, because each write is valid on its own. Three fixes work: make the change atomic in one statement (UPDATE inventory SET on_hand = on_hand - 1 WHERE product_id = 7), lock the row before reading it with SELECT ... FOR UPDATE, or run at repeatable read, where PostgreSQL refuses the second write with a serialization failure for the application to retry.

Isolation at the table level: locks and hints

Isolation usually works row by row, but some operations lock whole tables. Many forms of ALTER TABLE, and DROP TABLE and TRUNCATE, take PostgreSQL's strongest lock, ACCESS EXCLUSIVE, which the manual says guarantees the holder is the only transaction accessing the table in any way. It is also the default mode of an explicit LOCK TABLE. If a long report holds an ordinary read lock, the ALTER waits, and every new query on that table, even simple SELECTs, queues behind the waiting ALTER. Setting lock_timeout before schema changes makes them give up quickly instead of stalling the shop; Lesson 16 covers scheduling such changes.

SQL Server goes further and lets a query set isolation for a single table reference through table hints. NOLOCK, which Microsoft documents as equivalent to READUNCOMMITTED, allows dirty reads on that table: faster, at the cost of reading changes that other transactions may roll back. Seeing NOLOCK throughout a reporting codebase is a reason to ask whether anyone has checked the numbers.

Common misconceptions

• "A deadlock means the database is broken." Deadlocks are a normal consequence of concurrency. The engine resolves each one; your job is to watch the rate and fix inconsistent lock ordering.
• "Raising deadlock_timeout prevents deadlocks." It only delays detection, so victims wait longer before being rolled back.
• "READ UNCOMMITTED in PostgreSQL gives dirty reads." PostgreSQL treats it as read committed. In SQL Server, NOLOCK really does allow dirty reads.
• "Serializable means transactions run one at a time." They run concurrently; the engine guarantees the result matches some serial order, and cancels transactions with serialization failures when it cannot.

The short version

• A deadlock is a cycle of lock waits. PostgreSQL checks after deadlock_timeout (1 second by default) and aborts one transaction; InnoDB rolls back small transactions; SQL Server returns error 1205 to its victim.
• Monitor deadlock counts, log lock waits, and use pg_locks and pg_blocking_pids; in MySQL read the LATEST DETECTED DEADLOCK section.
• Prevent deadlocks by locking in a consistent order, keeping transactions short, indexing update conditions and retrying victims.
• Isolation levels allow or forbid dirty reads, nonrepeatable reads, phantoms and serialization anomalies. Defaults: PostgreSQL read committed, InnoDB repeatable read, SQL Server read committed.
• Stronger levels require retry logic. Lost updates are prevented by atomic updates, FOR UPDATE locks or stricter isolation.
• Table-level locks from DDL block every query on the table; SQL Server table hints such as NOLOCK change isolation for one table reference.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objectives 3.1 and 3.3. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). Transaction isolation (section 13.2); Explicit locking (section 13.3); Introduction to MVCC; Lock management (deadlock_timeout). In PostgreSQL 18 documentation. isolation; locking; MVCC; deadlock_timeout
3. Oracle. (n.d.). Deadlock detection; Transaction isolation levels. In MySQL 8.4 reference manual. deadlock detection; isolation levels
4. Microsoft. (n.d.). Deadlocks guide; Table hints (Transact-SQL); SET TRANSACTION ISOLATION LEVEL (Transact-SQL). SQL Server documentation. deadlocks; table hints; isolation level

A month of maintenance

PostgreSQL's versioning policy promises that each supported major version receives bug fixes and, when needed, security fixes in a minor release at least once every three months, and each major version is supported for five years. So a database administrator's calendar has a rhythm before any incident happens. This lesson follows one month of that rhythm at Harbor Outfitters, week by week, through the maintenance processes objective 3.2 lists: patch management, integrity checks, periodic review of logs, and change management.

Week 1: a patch arrives

On the first Thursday, a new minor release is announced with a fix for a security vulnerability. NIST's guide to enterprise patch management, which treats patching as preventive maintenance, describes the process as identifying, prioritizing, acquiring, installing and verifying the installation of patches, updates and upgrades. For Harbor's database, each verb becomes a step.

1. Identify. Keep an inventory of every database server, its engine and version, and its operating system, and subscribe to each vendor's security announcements.
2. Prioritize. Read the release notes. A remotely exploitable flaw in a server reachable from the application tier is urgent; a fix for a feature Harbor does not use can wait for the routine window.
3. Acquire and test. Install the release on the staging server first and run the regression suite from Lesson 10.
4. Install. Patch the standby first, verify it, switch over to it, then patch the former primary. The application sees one short switchover instead of an outage, which keeps the 99.9 percent promise from Lesson 8.
5. Verify and record. Confirm the new version with SELECT version(), check the logs and replication, and record the change.

The operating system beneath the database needs the same cycle; an unpatched kernel or OpenSSL library undermines a fully patched database.

Remember: patch management is a cycle, identify, prioritize, test, install and verify, and rolling a patch through the standby first turns a patch window into a brief switchover.

Week 2: checking that the data is still the data

Storage fails quietly. A failing disk controller or a firmware bug can change bytes on disk without any error at the moment it happens. The damage surfaces weeks later when a query reads the page, or not until a restore. Integrity checks look for it on purpose.

Integrity checks read everything, so they are heavy. Schedule them in quiet hours, or run them on a restored copy of last night's backup, which also proves the backup restores. When a check finds corruption, the safe response is to restore the damaged objects from a known-good backup and investigate the hardware, not to delete the damaged rows and carry on.

Week 3: reading the logs on purpose

NIST's guide to computer security log management makes the point that logs are useful only if someone reviews them. Alerts (Lesson 14) catch the known emergencies; a periodic human review catches the patterns nobody wrote an alert for. Harbor's administrator spends an hour each week on the server log, looking for:

• Errors and worse: ERROR, FATAL and PANIC entries, and any new kind of error that was not there last week.
• Authentication failures: repeated failed logins, especially for administrative accounts or from unfamiliar addresses.
• Slow statements logged by log_min_duration_statement, to feed tuning (Lesson 17).
• Lock waits and deadlocks logged by log_lock_waits (Lesson 15).
• Storage and operating system warnings: checksum failures, I/O errors, disk-full errors.

Logs also need managing: rotation so files stay a usable size, retention long enough to investigate an incident found weeks later, and protection so an intruder cannot erase the evidence. Lesson 20 returns to logs as audit evidence.

Week 4: a change request

On the last Monday a developer asks for a new column and an index on the orders table, today, during business hours, to support a new wholesale report. This is where change management earns its keep. Its parts, as the objectives list them:

The review catches a real risk. A plain CREATE INDEX on a busy table blocks writes until it finishes. PostgreSQL's manual says a standard index build locks out writes, but not reads. CREATE INDEX CONCURRENTLY builds the index without taking locks that prevent concurrent inserts, updates or deletes, at the cost of a slower build and some caveats. Adding a column with no default is quick, but the ALTER still needs a brief ACCESS EXCLUSIVE lock, so the migration sets lock_timeout and retries rather than queueing every query behind it (Lesson 15). The change goes out on Tuesday evening, through the pipeline, with a rollback script that drops the index and column.

Remember: database changes are code: review them, version them, test them automatically against refreshed data, and release them on a schedule with a rollback ready.

Common misconceptions

• "Minor releases are optional extras." They carry the security fixes. Skipping them leaves known vulnerabilities in place.
• "If the database starts, the data is fine." Silent corruption is found only by reading the data: checksums on read, amcheck, DBCC CHECKDB or validate.
• "Logs are for after an incident." Regular review finds failed-login patterns, new errors and slow queries before they become incidents.
• "A one-line schema change does not need change management." One line can lock the busiest table in the shop. Small changes are the ones most often run without a rollback plan.

Putting it together

• Patch management: identify, prioritize, acquire and test, install standby first, verify and record; patch the OS too.
• Integrity checks: PostgreSQL data checksums (on by default in current initdb) and amcheck, SQL Server DBCC CHECKDB, MongoDB validate; schedule them in quiet hours or on restored backups; restore, do not paper over, when corruption appears.
• Log review: a weekly human pass over errors, authentication failures, slow statements, lock waits and storage warnings, with rotation, retention and protection.
• Change management: approval by someone other than the author, release schedules, migrations in version control, CI/CD testing, regular database refreshes for staging, and a rollback for every change.
• Use operations that do not block: CREATE INDEX CONCURRENTLY, lock_timeout on DDL.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 3.2. CompTIA (PDF)
2. PostgreSQL Global Development Group. (n.d.). Versioning policy. postgresql.org
3. Souppaya, M., and Scarfone, K. (2022). Guide to enterprise patch management planning: Preventive maintenance for technology (NIST SP 800-40 Rev. 4). National Institute of Standards and Technology. csrc.nist.gov
4. PostgreSQL Global Development Group. (2025). initdb (data checksums); amcheck; CREATE INDEX (building indexes concurrently). In PostgreSQL 18 documentation. initdb; amcheck; CREATE INDEX
5. Microsoft. (n.d.). DBCC CHECKDB (Transact-SQL). SQL Server documentation. learn.microsoft.com; MongoDB, Inc. (n.d.). validate. mongodb.com
6. Kent, K., and Souppaya, M. (2006). Guide to computer security log management (NIST SP 800-92). National Institute of Standards and Technology. csrc.nist.gov

The report that took four minutes

Harbor's new wholesale dashboard has one panel that takes four minutes to load: each shop's orders for the current quarter. The data is not large by industry standards, a few million order rows, so the time is going somewhere it should not. Tuning is the procedure for finding where, and objective 3.2 names its main tools: index optimization, query optimization and attention to transaction volumes, plus partitioning. Objective 3.3 adds the hands-on tasks of creating indexes and creating statistics. This lesson works the procedure end to end, then changes one input to show a case where the slow-looking plan is the right one.

Step 1: find the query that costs the most

Start from evidence, not guesses. Lesson 7 showed how pg_stat_statements ranks statements by total execution time; Lesson 14 showed how slow statements reach the log through log_min_duration_statement. The dashboard's query tops both lists:

SELECT order_id, ordered_on, total FROM orders WHERE customer_id = 42 AND ordered_on >= '2026-10-01' ORDER BY ordered_on;

Step 2: read the plan

An execution plan is the optimizer's chosen strategy: which tables to read, how (a full scan or an index), in which order to join them, and how to sort. PostgreSQL shows it with EXPLAIN. EXPLAIN ANALYZE goes further, and its manual warns that the statement is actually executed, not only planned; to examine an UPDATE or DELETE safely, wrap it in BEGIN and ROLLBACK. Things to look for in the output:

• Scan types: a sequential scan reads the whole table; an index scan or bitmap scan reads only matching parts.
• Estimated against actual rows: a large gap means the planner was working from wrong statistics, and every later choice may be wrong too.
• Join methods and sorts: a nested loop over thousands of rows, or a sort that spills to disk, are common culprits.

You can see the same ideas in SQLite's EXPLAIN QUERY PLAN, which is quick to try. On a 200,000-row copy of the orders table, these are the plans SQLite reported for the dashboard's filter:

Step 3: index optimization

PostgreSQL's CREATE INDEX makes B-tree indexes by default, which fit the most common situations: equality and range searches and sorting. The skill is choosing which columns, in which order, and which special forms.

• Composite indexes: PostgreSQL's manual says a multicolumn B-tree index can serve conditions on any subset of its columns but is most efficient when there are constraints on the leading (leftmost) columns. Put equality columns first and the range column last: CREATE INDEX idx_orders_cust_date ON orders (customer_id, ordered_on);.
• Covering indexes: adding the selected columns with INCLUDE, ... ON orders (customer_id, ordered_on) INCLUDE (total), lets the query be answered from the index alone, an index-only scan.
• Expression indexes: a condition on lower(email) cannot use an index on email, but an index on the expression itself, CREATE INDEX ON customers (lower(email));, matches it.
• Partial indexes: when queries look only for a small subset, such as the few pending orders among millions shipped, CREATE INDEX ON orders (ordered_on) WHERE status = 'pending'; indexes just that subset, so it stays small.

Every index has a price: each INSERT, UPDATE and DELETE must maintain it, and it takes disk and memory. Index optimization therefore removes as well as adds. Lesson 10's check for indexes whose idx_scan stays at zero finds candidates; confirm that no monthly job needs them, then drop them. On a busy table, build or rebuild with CONCURRENTLY (Lesson 16).

In short: index for the conditions your expensive queries actually use, equality columns before range columns, and pay for every index only if a query uses it.

Step 4: query optimization

Some queries cannot use any index as written. A condition is index-friendly, often called sargable, when it compares the bare column with a value. Wrapping the column in a function or arithmetic, as in customer_id + 0 = 42 above, or WHERE date_trunc('month', ordered_on) = '2026-10-01', forces a scan; rewrite the second as a range, WHERE ordered_on >= '2026-10-01' AND ordered_on < '2026-11-01'. Other rewrites that pay off: select only the columns you need, use EXISTS for existence tests (Lesson 4), replace row-by-row loops with set-based statements (Lesson 3), and pair LIMIT with an ORDER BY an index can supply.

Step 5: statistics

The planner chooses plans from statistics about the data: row counts, common values and their frequencies, the distribution of values. PostgreSQL's manual says these statistics are gathered by the ANALYZE command, and that the autovacuum daemon, if enabled, issues ANALYZE automatically whenever a table's content has changed sufficiently. After a large load, run ANALYZE yourself rather than waiting.

Ordinary statistics treat each column independently, which misleads the planner when columns are correlated. A city fixes its postal codes almost completely, so a filter on both matches far more rows than multiplying their separate selectivities suggests. CREATE STATISTICS creates an extended statistics object; its kinds are ndistinct, functional dependencies and most-common-values lists. CREATE STATISTICS addr_dep (dependencies) ON city, postal_code FROM customers; followed by ANALYZE gives the planner the correlation.

Change one input: when the scan is right

With the composite index in place, the dashboard is fast for nearly every shop. Then the largest wholesale partner, which places 40 percent of all orders, opens it, and EXPLAIN shows a sequential scan. It looks like a regression. It is the planner doing its job: when a condition matches a large fraction of the table, reading the table straight through costs less than jumping between index and table for almost half its rows. The statistics told the planner how common that customer_id is, and it chose differently. Forcing the index here would make the query slower. The fix for that shop, if one is needed, is different: a covering index, a summary table, or the OLAP store from Lesson 11.

Transaction volumes

How work is grouped into transactions affects throughput as much as indexes do. A nightly import that commits after every row pays the cost of a durable commit, the log flush from Lesson 12, millions of times. One giant transaction for 20 million rows has the opposite problems: locks and old row versions held for hours, and all the work lost on any failure. Batches of a few thousand rows per commit, or a single COPY (Lesson 9), sit between the two. Watch transaction volume against the baseline (Lesson 14), and give large batch jobs their own window away from peak traffic.

Partitioning

Partitioning splits one large table into smaller ones, partitions, by a key: a range of dates, a list of regions, or a hash of an id. PostgreSQL's declarative partitioning gives two main benefits. Partition pruning lets the planner skip partitions that cannot contain matching rows, so a query on October's orders reads only October's partition. And, as the manual puts it, dropping an individual partition with DROP TABLE, or detaching it with ALTER TABLE DETACH PARTITION, is far faster than a bulk DELETE, so a seven-year retention rule becomes one quick statement a month instead of an hours-long delete.

Partitioning is not free. Too many partitions slow planning, and queries that do not filter on the partition key gain nothing. Choose the key from how data is queried and retired, which for orders is almost always time.

Common misconceptions

• "A sequential scan always means a missing index." When a condition matches a large share of the table, a scan is the cheaper plan, and the planner chooses it on purpose.
• "More indexes always mean faster queries." Each index slows every write and uses space. Unused indexes are pure cost.
• "EXPLAIN ANALYZE is read-only." It executes the statement. Wrap data changes in BEGIN and ROLLBACK when you analyze them.
• "Partitioning makes every query faster." Only queries that filter on the partition key benefit from pruning; others may get slower.

Where this leaves us

• Tune from evidence: rank queries by total time, then read their plans with EXPLAIN and EXPLAIN ANALYZE, watching scan types and estimated against actual rows.
• B-tree is the default index. Order composite columns with equality first and ranges last; use covering, expression and partial indexes where they fit; drop unused ones.
• Write sargable conditions: no functions or arithmetic on indexed columns; ranges instead of truncations.
• Keep statistics fresh with ANALYZE and autovacuum; add CREATE STATISTICS for correlated columns.
• Batch transactions sensibly: neither one commit per row nor one commit for millions.
• Partition by how data is queried and retired; pruning skips partitions, and dropping a partition beats a bulk DELETE.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objectives 3.2 and 3.3. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). EXPLAIN; Index types; Multicolumn indexes. In PostgreSQL 18 documentation. EXPLAIN; index types; multicolumn indexes
3. PostgreSQL Global Development Group. (2025). CREATE STATISTICS; Routine vacuuming (updating planner statistics); Table partitioning. In PostgreSQL 18 documentation. CREATE STATISTICS; vacuuming; partitioning
4. SQLite. (n.d.). EXPLAIN QUERY PLAN. sqlite.org

The spreadsheet that became a table

Harbor's first big wholesale partner sends its order history as a spreadsheet, and a developer imports it as one wide table called wholesale_orders. Each row holds an order number, the shop's name, address and region, a products column such as 7,9,12, a matching quantities column such as 2,1,4, and an order total. The import ran without an error, and the developer's conclusion is the plausible one: the data is in, so the job is done.

Objective 3.3 asks you to implement data management tasks in scenarios like this one: defining and modifying data, appending columns, creating tables, relationships, views and indexes, computed columns, and normalizing or denormalizing. Trace what happens to this table over its first month and you meet the reason for each of them.

Where the wide table fails

• Update anomaly. The shop moves. Its address appears on 900 rows; an update with a slightly wrong WHERE clause changes 850 of them. Now the shop has two addresses, and nothing says which is right.
• Insertion anomaly. A new partner signs up but has not ordered yet. There is nowhere to store the shop without inventing an order.
• Deletion anomaly. A cancelled first order is deleted, and the shop's only address goes with it.
• Unqueryable lists. "Which orders included product 9?" needs string matching on 7,9,12, which cannot use an index and will happily match product 19 or 90.
• Drifting totals. The stored order_total was typed in the spreadsheet; when a quantity is corrected, the total is not.

Every one of these comes from storing a fact in more than one place, or several facts in one place.

Defining the data: normalize to third normal form

Normalization is the procedure that removes those anomalies by giving each fact one home. Databases & SQL (cs-db) teaches it step by step; here are the three normal forms you need, applied to the wide table.

The result is four tables, shops, orders, order_lines and products, each defined with types and constraints that state the rules: NOT NULL where a value is required, CHECK (quantity > 0), UNIQUE on a shop's trade registration number, and DEFAULT values where they make sense.

Creating relationships

Relationships become foreign keys, and each foreign key needs a decision about what happens when the referenced row is deleted. PostgreSQL's manual describes CASCADE as automatically deleting the rows that reference a deleted row.

A many-to-many relationship, such as shops and the products they have special prices for, needs a junction table with a foreign key to each side. And check that the engine enforces what you declared: SQLite's documentation notes that foreign key constraints are disabled by default and must be switched on for each connection with PRAGMA foreign_keys = ON. Without it, an order line can point at a product that does not exist and SQLite will not object.

The upshot: normalization gives each fact one home, and foreign keys with deliberate delete actions keep the homes connected; check that the engine actually enforces them.

Modifying data and appending columns

Moving the data from the wide table into the new design is itself a data management task. Insert the distinct shops first, then orders, then lines, each with INSERT ... SELECT. Run each step in a transaction, compare row counts and totals before committing (Lesson 10), and keep the wide table until the checks pass.

A month later the partner wants a gift note on each order. Appending a column is DDL, and on a large table its cost depends on how you write it. PostgreSQL's manual explains that when a column is added with a non-volatile DEFAULT, the default is evaluated once and stored in the table's metadata, where it is returned for existing rows, so the table is not rewritten. A nullable column with no default is equally quick. Adding NOT NULL is a separate step after any backfill, and every ALTER still needs a brief lock, so set lock_timeout (Lessons 15 and 16).

Computed columns

The drifting order_total suggests the cure for derived values: let the database compute them. A generated column is defined by an expression over other columns of the same row:

CREATE TABLE order_lines (order_id integer NOT NULL REFERENCES orders, product_id integer NOT NULL REFERENCES products, quantity integer NOT NULL CHECK (quantity > 0), unit_price numeric(10,2) NOT NULL, line_total numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL, PRIMARY KEY (order_id, product_id));

PostgreSQL's documentation describes two kinds. A virtual generated column occupies no storage and is computed when it is read, like a view; a stored one is computed when the row is written and kept on disk, like a materialized view that is always up to date. In PostgreSQL 18 a generated column is virtual unless you say STORED. Either way, nobody can write a wrong value into it: SQLite, for example, rejects an INSERT that names a generated column with an error saying it cannot insert into that column. Change the quantity and the total follows.

Note what is not a computed column. unit_price on the order line looks like a copy of products.price, but it records the price at the moment of sale, a different fact that must not change when the catalogue price does.

Views and materialized views

A view is a stored query that behaves like a table. PostgreSQL's manual is explicit that a view is not physically materialized: the query runs each time the view is read. Views simplify (analysts query shop_order_summary instead of a four-table join) and protect (grant access to a view that omits sensitive columns, as Lesson 22 does).

A materialized view, in the manual's words, persists the results in a table-like form. It is fast to read and as fresh as its last REFRESH MATERIALIZED VIEW. Plain refresh locks out readers while it runs; REFRESH ... CONCURRENTLY lets queries continue, but is only allowed when the materialized view has at least one suitable UNIQUE index covering all rows.

When to denormalize

Normalization optimizes for correct writes; some workloads are dominated by reads. Denormalization deliberately stores a fact twice to make reads cheaper, and it is sound when two conditions hold: a measured read problem that normalization causes, and a mechanism that keeps the copy consistent. Harbor's examples: the star schema for analysts (Lesson 11), refreshed nightly; a materialized view of monthly revenue per shop; a stored generated column that indexes a computed value. Unsound denormalization is the original wide table: duplication with no mechanism at all.

The upshot: denormalize only for a measured read benefit, and only with a mechanism, refresh, generated column or trigger, that keeps every copy in step.

Common misconceptions

• "If the import succeeded, the design is fine." A table can accept every row and still guarantee update, insertion and deletion anomalies.
• "A comma-separated list in one column saves space and joins." It breaks first normal form, defeats indexes and makes simple questions error-prone.
• "A view stores a copy of the data." An ordinary view runs its query on every read; only a materialized view stores results.
• "Any duplicated value is a normalization error." A price recorded at the time of sale is a separate historical fact, not a copy of the current catalogue price.

Pulling it together

• Wide, repeated data causes update, insertion and deletion anomalies; 1NF removes lists, 2NF partial dependencies, 3NF transitive dependencies.
• Define tables with types and constraints; create relationships as foreign keys with deliberate RESTRICT, CASCADE or SET NULL actions; make sure enforcement is on.
• Modify data in verified transactions; append columns cheaply with nullable columns or non-volatile defaults, then add NOT NULL.
• Generated columns compute row-level values, virtual by default in PostgreSQL 18; views simplify and restrict; materialized views store expensive results until refreshed.
• Denormalize only for a measured read benefit and with a mechanism that keeps copies consistent.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 3.3. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). Constraints (foreign keys); Generated columns; ALTER TABLE. In PostgreSQL 18 documentation. constraints; generated columns; ALTER TABLE
3. PostgreSQL Global Development Group. (2025). CREATE VIEW; Materialized views; REFRESH MATERIALIZED VIEW. In PostgreSQL 18 documentation. CREATE VIEW; materialized views; REFRESH
4. SQLite. (n.d.). SQLite foreign key support and Generated columns. foreign keys; generated columns
5. Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. DOI 10.1145/362384.362685.

One record, many places

A buyer at one of Harbor's wholesale shops types the shop's delivery address and tax number into the portal. Before the order ships, that record will travel from the browser to Harbor's web server and on to the database. It will then sit on the database's disks, go into a nightly backup, out to a cloud storage bucket, and into a report on an analyst's laptop. At every step someone could read it who should not: a stranger on the network, a thief with a stolen disk, a curious administrator, a hacker in the cloud account.

Objective 4.1 sorts the defences by the state the data is in: in motion, at rest and, newest, in use. It also asks about the keys, because encryption is only as strong as the protection of its keys. This lesson follows the record through each state.

In motion: TLS, and why "encrypted" is not enough

Data crossing a network is protected with Transport Layer Security, whose current version, 1.3, is specified in RFC 8446. TLS gives two things: encryption, so an eavesdropper sees nothing useful, and, if the client checks the server's certificate, authentication of the server, so an attacker cannot pretend to be the database.

The second half is easy to switch off by accident. PostgreSQL's client library chooses its behaviour with a setting called sslmode. Its documentation is blunt about the default value, prefer: it is provided only for backward compatibility and is not recommended in secure deployments, because it quietly falls back to an unencrypted connection. The require setting insists on encryption but does not check who answered. verify-ca checks that a trusted authority signed the server's certificate, and verify-full also checks that the host name matches the certificate; the documentation recommends verify-full in most security-sensitive environments. A man-in-the-middle can satisfy require. It cannot satisfy verify-full without a certificate for Harbor's database name from an authority Harbor trusts.

Client-side encryption: when the server must never see plaintext

TLS protects the record on the wire, but the server decrypts it on arrival. Sometimes that is not acceptable: Harbor's hosting contract may say the provider's administrators must never see tax numbers. PostgreSQL's documentation states the principle: if the system administrator for the server's machine cannot be trusted, the client must encrypt the data, so unencrypted data never appears on the database server. SQL Server builds this in as Always Encrypted, which Microsoft describes as encrypting sensitive data within client applications so that the encryption keys are never exposed to the Database Engine.

The price is function. A database that cannot read a value cannot easily search, sort or index it, so client-side encryption is kept for the few columns that justify it.

Worth holding on to: in-transit encryption protects the wire and must verify the server to stop impersonation; client-side encryption protects data from the server itself, at the cost of the server's ability to work with it.

At rest: disks, files and columns

Data at rest is protected against someone who obtains the storage: a stolen laptop, a discarded disk, a copied backup. There are three common layers, and each defends against a different thief.

Microsoft describes transparent data encryption as encrypting the data files, which it calls encrypting data at rest, using a database encryption key stored in the user database. "Transparent" means applications need no changes: data is decrypted as it is read into memory. PostgreSQL's core has no TDE; its documentation lists storage encryption at the file-system or block level, and the pgcrypto module, which allows certain fields to be stored encrypted. Whatever the layer, backups need the same protection as the live database, or the backup becomes the easiest copy to steal.

Keys: the real secret

Encryption turns a large secret, the data, into a small one, the key. Systems therefore use two tiers of keys, an arrangement called envelope encryption.

• A data encryption key (DEK) encrypts the data itself; SQL Server's TDE calls its version the database encryption key.
• A key encryption key (KEK) encrypts the DEKs. NIST defines it as a key used to encrypt or decrypt other keys to protect their confidentiality.

Cloud key services work the same way: AWS's documentation describes its key management service generating data keys and giving you an encrypted copy of each data key that you can safely store with the data. The design has two benefits. The KEK never leaves its protected home, and rotating the KEK only means re-encrypting small DEKs, not terabytes of data. It also explains two failures in Lesson 29: lose the KEK and every DEK, and so every byte, is unreadable; let a KEK expire or lose permission to it, and data loads fail with decryption errors.

Where do the KEKs live? One home is a hardware security module (HSM), tamper-resistant hardware that stores and uses keys without letting them out. HSMs are typically validated by NIST's Cryptographic Module Validation Program, which since 22 September 2020 has validated modules to FIPS 140-3. The other home is a key management service (KMS), often backed by HSMs, that controls key use through permissions and logs every use. The objectives and the acronym list name the ownership models.

Worth holding on to: data keys encrypt data and key encryption keys encrypt data keys. Whoever creates and holds the KEK, in the provider's KMS or in the customer's own HSM, decides who can ultimately read the data.

In use: confidential computing

Even perfectly encrypted data must be decrypted to be processed, so for the moments it sits in memory it is exposed to anyone who controls the machine: a privileged insider, a compromised hypervisor. Confidential computing addresses that last gap. The Confidential Computing Consortium, part of the Linux Foundation, defines it as protecting data in use by performing computation in a hardware-based, attested Trusted Execution Environment. The processor keeps the TEE's memory encrypted and isolated from the host, and attestation lets the data owner verify what code is running inside before releasing keys to it. For Harbor, that could let analytics run on cloud hardware without the provider being able to read the data even while it is processed.

Common misconceptions

• "sslmode=require makes the connection secure." It encrypts, but does not check the server's identity. Use verify-full to stop impersonation.
• "TDE protects data from the DBA." TDE decrypts data for anyone who can query it. It protects files and backups, not query results.
• "Encryption at rest and in transit means the data is always encrypted." It is decrypted in memory to be processed, which confidential computing addresses.
• "BYOK means the provider can never see the key." With BYOK the key material is imported into the provider's KMS. Exclusive customer control is KYOK.

Summing up

• In motion: TLS (version 1.3 in RFC 8446), with server verification; in PostgreSQL use sslmode=verify-full, not the prefer default.
• Client-side encryption, such as Always Encrypted, keeps plaintext and keys away from the server, at the cost of searching and sorting.
• At rest: disk or block encryption against stolen hardware, TDE against copied files and backups, column encryption against readers without the key.
• Envelope encryption: DEKs encrypt data, KEKs encrypt DEKs; keep KEKs in an HSM or KMS.
• Ownership: provider-managed, CMK, BYOK (imported) and KYOK (exclusive customer control).
• Confidential computing protects data in use inside attested, hardware-based TEEs.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 4.1 and acronym list. CompTIA (PDF)
2. Rescorla, E. (2018). The Transport Layer Security (TLS) protocol version 1.3 (RFC 8446). RFC Editor. rfc-editor.org
3. PostgreSQL Global Development Group. (2025). SSL support (libpq) and Encryption options. In PostgreSQL 18 documentation. libpq SSL; encryption options
4. Microsoft. (n.d.). Transparent data encryption (TDE); Always Encrypted; What is confidential computing? TDE; Always Encrypted; confidential computing
5. National Institute of Standards and Technology. (n.d.). Key encryption key (glossary) and Cryptographic Module Validation Program. glossary; CMVP
6. Amazon Web Services. (n.d.). Data keys and Importing key material for AWS KMS keys. AWS Key Management Service developer guide. data keys; importing keys
7. IBM. (n.d.). Hyper Protect Crypto Services overview (Keep Your Own Key). IBM Cloud documentation. cloud.ibm.com

The refresh that copied too much

Lesson 16 set up a Sunday refresh that copies production into staging so testers work with realistic data. The first refresh does exactly what was asked: every customer's name, email, phone number, delivery address and tax number now sits on a staging server that twelve contractors can query. Nothing was stolen, yet the shop has just multiplied the number of places a breach could happen.

Objective 4.1 lists the techniques that fix this, masking, anonymization, suppression and destruction, alongside two kinds of audit: security audits of accounts, connections and evidence, and code audits of SQL and stored credentials. This lesson works them as one procedure for Harbor, then applies the same thinking to retiring old hardware.

Step 1: classify the columns

Start with the data dictionary from Lesson 11, which should already record each column's sensitivity. For the refresh, list every column that identifies or describes a person and decide what testers actually need from it.

Step 2: understand the techniques

• Masking replaces real values with realistic fakes. Static masking changes the stored copy, as in the staging refresh. Dynamic masking leaves the stored value intact and hides it at query time from users who lack permission; Microsoft describes SQL Server's dynamic data masking as limiting sensitive data exposure by masking it to nonprivileged users. Harbor's support staff, who need only the last four digits of a phone number, are the classic case.
• De-identification is NIST's general term for removing the association between identifying data and the person it describes. Within it, pseudonymization replaces identifiers with consistent substitutes, such as customer 1042 becoming token K7F2, so records still link to each other; whoever holds the mapping can re-identify them. Anonymization aims to make re-identification impossible, including by combining quasi-identifiers like postal code, birth date and gender, and is much harder to achieve than it sounds.
• Suppression withholds values entirely: a column set to NULL, or a cell in a report hidden when it describes too few people. If one postal code in a sales report contains a single wholesale customer, publishing that cell publishes that customer's figures, so reports suppress cells below a minimum count.

So what?: pick the technique by what the user of the data actually needs; pseudonymized data can still be traced back to people, so it must be protected like the original.

Step 3: verify the result

Masking scripts fail quietly. After each refresh, run checks that should return zero rows: no email in staging that also appears in production, no tax numbers, no real postal codes in full. Check also the places a copy hides: views, materialized views, audit tables and logs that might have been refreshed with the data.

Destroying data: deleting is not destroying

When Harbor retires its old database server, deleting the database is not enough. Deleted rows linger in data files until space is reused; copies sit in write-ahead logs, replicas and backups; and storage hardware keeps data in places the operating system cannot address. NIST's media sanitization guidelines, revised as SP 800-88 Revision 2 in September 2025, define three levels.

Destruction also has a data side. Retention policies (Lesson 21) say when records must go, and every copy must go with them: production, replicas, backups past their retention, exports and staging. When data has been encrypted throughout its life, destroying every copy of its keys leaves the remaining ciphertext unreadable, which is one reason key management (Lesson 19) belongs in the destruction plan.

The security audit

A security audit checks that the controls are doing what the policies say. The objectives name three targets.

• Expired accounts. List every login role and when it was last needed. In PostgreSQL, SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolcanlogin; shows each login and its expiry; the VALID UNTIL clause sets a date after which a role's password is no longer valid. Accounts of people who have left, and service accounts for systems that no longer exist, are disabled and then removed, and the leaver procedure that missed them is fixed.
• Connection requests. Review who connects, from where and how often: connection logs, failed logins (Lesson 14), connections from unexpected addresses or at unusual hours, and accounts used from more than one place at once.
• Forensics. When an audit or an alert suggests a breach, evidence must be preserved before anything is fixed. NIST's guide to integrating forensic techniques into incident response gives the order of work: collection, examination, analysis and reporting. Copy logs and disk images, record who handled them and when, and do not restore the server over the evidence.

The code audit

Code audits look for two things in everything that talks to the database: application code, scripts, stored procedures and configuration.

• SQL code. Any query built by pasting input into SQL text is a candidate for SQL injection (Lesson 23). The fix OWASP lists first is prepared statements with parameterized queries. Inside the database, dynamic SQL in procedures needs the same care: PostgreSQL's documentation shows dynamic statements built safely with the format function and values passed with USING. Audits also flag procedures that run with elevated privileges and queries that select far more than they need.
• Credential storage checks. Search code, configuration files and the history of every repository for passwords and keys; a secret deleted in a later commit is still in the history. Check how the database stores passwords: PostgreSQL supports SCRAM-SHA-256 authentication, and its documentation states that support for MD5-encrypted passwords is deprecated and will be removed. Any credential found exposed is rotated, not just removed, and moved to a secret store or protected file (Lesson 6).

So what?: audits turn policy into evidence: who still has access, who is connecting, what the code does with input, and where the secrets live.

Common misconceptions

• "Test environments do not need protection because they are not production." A staging copy of real data carries the same risk with usually weaker controls. Mask on the way in.
• "Pseudonymized data is anonymous." Anyone with the mapping, or enough auxiliary data, can re-identify it. Treat it as personal data.
• "DELETE or DROP destroys the data." Copies remain in data files, logs, replicas and backups, and on the media. Destruction needs a sanitization method and every copy.
• "Deleting a leaked password from the repository fixes the leak." It remains in the history and in every clone. Rotate it.

Looking back

• Classify columns, then choose per column: static masking for copies, dynamic masking for restricted viewers, pseudonymization when records must still link, anonymization when they must not be traceable, suppression when the value or small cell must not appear.
• Verify masking after every refresh, including views, logs and audit tables.
• NIST SP 800-88 Rev. 2 sanitization: clear, purge, destroy. Deleting rows is not destroying data; every copy and medium counts.
• Security audits: expired accounts (VALID UNTIL, leavers), connection requests, and evidence preserved for forensics in collection, examination, analysis and reporting order.
• Code audits: parameterized SQL, safe dynamic SQL, and credential storage checks that end in rotation.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 4.1. CompTIA (PDF)
2. Garfinkel, S., Guttman, B., Near, J., Dajani, A., and Singer, P. (2023). De-identifying government datasets: Techniques and governance (NIST SP 800-188). National Institute of Standards and Technology. csrc.nist.gov
3. National Institute of Standards and Technology. (2025). Guidelines for media sanitization (NIST SP 800-88 Rev. 2), and glossary entries for clear, purge and destroy. csrc.nist.gov; glossary
4. Kent, K., Chevalier, S., Grance, T., and Dang, H. (2006). Guide to integrating forensic techniques into incident response (NIST SP 800-86). National Institute of Standards and Technology. csrc.nist.gov
5. Microsoft. (n.d.). Dynamic data masking. SQL Server documentation. learn.microsoft.com
6. PostgreSQL Global Development Group. (2025). CREATE ROLE (VALID UNTIL); Password authentication; Basic statements (executing dynamic commands). In PostgreSQL 18 documentation. CREATE ROLE; passwords; dynamic SQL
7. OWASP Foundation. (n.d.). SQL injection prevention cheat sheet. owasp.org

A shop in Ohio, a customer in Lyon

Regulation (EU) 2016/679, the General Data Protection Regulation, has applied since 25 May 2018. Harbor Outfitters has no office in Europe, so its owner assumed the GDPR was someone else's concern. Then the wholesale portal signed a partner in Lyon, and the web shop started shipping to French hikers. Article 3 of the GDPR answers the owner's assumption directly. The Regulation reaches controllers outside the Union when they process the personal data of people in the Union in connection with offering them goods or services.

Objective 4.2 asks you to explain the purpose of governance and regulatory compliance. In practice that means knowing which rulebooks apply to which data, what each demands of a database, and how a database administrator turns those demands into retention rules, storage locations and controls. This lesson compares the rulebooks the objectives name.

The vocabulary: PII and PHI

Personally identifiable information (PII). NIST's glossary defines it as information that can be used to distinguish or trace an individual's identity, such as a name, social security number or biometric record. That can happen alone or in combination with other information linked or linkable to the person, such as date and place of birth. The second half matters: a postal code and a birth date are not identifying alone, but together they often are.

Protected health information (PHI). CompTIA's list expands PHI as personal health information; the US regulation that governs it, HIPAA, calls it protected health information. The definitions in 45 CFR 160.103 make PHI individually identifiable health information, created or received by a health care provider, health plan, employer or clearinghouse, whether transmitted or maintained electronically or in any other form. Harbor holds no PHI. A clinic's appointment database is full of it.

The GDPR uses a wider term, personal data: any information relating to an identified or identifiable natural person. Its definition of pseudonymisation, data that can no longer be attributed to a person without additional information kept separately, explains Lesson 20's warning: pseudonymised data is still personal data. Recital 26 adds that the Regulation does not concern anonymous information, which is why genuine anonymization takes data out of scope and pseudonymization does not.

The rulebooks compared

Two details deserve attention. The GDPR requires a controller to notify the supervisory authority of a personal data breach without undue delay. Where feasible, that means not later than 72 hours after becoming aware of it, unless the breach is unlikely to risk people's rights. And PCI DSS, now in version 4.0.1, published on 11 June 2024, defines its scope by the data. The Council's page says it applies to entities that store, process or transmit cardholder data or sensitive authentication data. That gives a database administrator the most powerful compliance tool there is: do not store the data. Harbor's payment provider tokenizes cards, so Harbor's database holds only tokens and stays out of the cardholder data environment.

The core of it: identify which regulated data a database holds, PII, personal data, PHI or card data, because the data, not the company's location, decides which rulebooks apply.

Data retention: both floors and ceilings

A retention policy says how long each kind of record is kept and what happens at the end. It must satisfy rules that push in opposite directions.

• Minimums. The US Internal Revenue Service tells businesses to keep records for 3 years in most situations and for 7 years in some, such as claims for losses from worthless securities or bad debts. Contracts and other laws add their own floors.
• Maximums. The GDPR's storage limitation principle requires personal data to be kept in a form that permits identification for no longer than necessary for its purposes.
• Exceptions. A legal hold suspends deletion for records relevant to litigation or investigation, whatever the schedule says.

Harbor's policy reconciles them per record type: order and invoice records seven years for tax, marketing preferences until consent is withdrawn, web logs ninety days. When a French customer asks for erasure under Article 17, the right to erasure or right to be forgotten, Harbor deletes or anonymizes the marketing profile but keeps invoices the tax rules require, recording why. Implementation brings in earlier lessons: monthly partitions make expiry a quick DROP (Lesson 17), and deletion must reach replicas, exports and, as backups age out, the backups too (Lesson 20).

Sovereignty and residency

Two terms describe where data lives and whose law follows it, and they are easy to confuse.

The GDPR links the two through its rules on transfers: Article 44 allows personal data to be transferred to a third country only if the conditions laid down in its transfer chapter are met. Residency compliance then becomes a set of database decisions. Which cloud region holds the primary? Where do replicas and backups go, given that a disaster-recovery copy in another country is a transfer? And which administrators, in which countries, can read the data?

Data loss prevention

NIST defines data loss prevention (DLP) as a system's ability to identify, monitor and protect data in use, in motion and at rest. DLP tools look at content and context: an email attachment full of customer addresses heading to a personal account, a bulk export copied to removable media, a query result uploaded to an unknown website. For DLP to recognize sensitive data, the data must be classified first, which is one more reason the data dictionary records sensitivity (Lesson 11) and why data classification opens objective 6.1 (Lesson 27). At the database itself, DLP thinking means watching for unusual bulk reads and exports, and limiting who can run them (Lesson 22).

Governance: who decides

Regulations say what must happen; governance decides who makes sure it does. A data owner, usually a business manager, decides who may use a data set and how long it is kept. A data steward maintains its definitions and quality. The database administrator is the custodian who implements the controls. Policies set the rules, SOPs describe the steps (Lesson 12), controls enforce them, and audits produce the evidence (Lesson 20). When a regulator or auditor asks how Harbor meets storage limitation, the answer is that chain, written down.

Common misconceptions

• "The GDPR only applies to companies in Europe." Article 3 extends it to controllers outside the Union that offer goods or services to people in it.
• "Pseudonymized data is outside the GDPR." It is still personal data; only truly anonymous information falls outside.
• "Retention means keeping everything as long as possible." Laws set maximums as well as minimums. Keeping personal data longer than needed is itself a breach of storage limitation.
• "PCI DSS is a law." It is an industry standard enforced through contracts with card brands and banks; the cheapest way to comply for a database is not to store card data at all.

What to remember

• PII: information that can distinguish or trace an identity, alone or combined (NIST). PHI: individually identifiable health information held by covered entities (45 CFR 160.103). Personal data (GDPR): any information relating to an identifiable person.
• GDPR: applies to offering goods or services to people in the EU; storage limitation; right to erasure; breach notification within 72 hours where required; fines up to 20 million euros or 4 percent of turnover.
• PCI DSS 4.0.1 covers entities that store, process or transmit card data; tokenization keeps card data, and the database, out of scope.
• Retention policies balance minimums, maximums and legal holds, and must reach every copy.
• Residency is where data is stored; sovereignty is whose law governs it; transfers and replicas across borders must be checked.
• DLP identifies, monitors and protects data in use, in motion and at rest, and depends on classification.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 4.2 and acronym list. CompTIA (PDF)
2. European Parliament and Council of the European Union. (2016). Regulation (EU) 2016/679 (General Data Protection Regulation), Articles 3, 4, 5, 17, 33, 44, 83 and 99, and Recital 26. Official Journal of the European Union, L 119. eur-lex.europa.eu
3. US Department of Health and Human Services. (n.d.). 45 CFR 160.103, Definitions (protected health information; individually identifiable health information). Electronic Code of Federal Regulations. ecfr.gov; US Department of Health and Human Services. (n.d.). HIPAA enforcement. hhs.gov
4. National Institute of Standards and Technology. (n.d.). Personally identifiable information (PII) and Data loss prevention (glossary). PII; DLP
5. PCI Security Standards Council. (n.d.). PCI DSS; Malone, A. (2024, 11 June). Just published: PCI DSS v4.0.1. pcisecuritystandards.org; blog
6. Internal Revenue Service. (n.d.). How long should I keep records? irs.gov

Forty accounts named admin

When Harbor's new administrator lists the database's login roles, she finds forty, and eleven of them are superusers: the original developer, two contractors who left, the backup script, and the web application itself. Every one of them can read every customer's data, change any price and drop any table. Nobody designed it that way; each grant was the quickest fix for an error message on some busy afternoon.

Objective 4.3 asks you to implement policies and best practices for authentication, proving who is connecting, and authorization, deciding what they may do. This lesson rebuilds Harbor's access step by step in PostgreSQL, then places each step among the models and protocols the objectives name.

Step 1: model access by job, not by person

Role-based access control (RBAC), in NIST's words, identifies permitted actions with roles rather than with individual identities. In PostgreSQL a role can be a group or a login, and the GRANT command either grants privileges on an object or grants membership in a role. Harbor's design:

The statements follow: CREATE ROLE reporting NOLOGIN; GRANT SELECT ON shop_order_summary TO reporting; CREATE ROLE ana LOGIN; GRANT reporting TO ana;. When Ana changes jobs, one REVOKE removes her access, and an audit (Lesson 20) can read the whole policy from a dozen grants instead of four hundred.

The application role shows least privilege at its sharpest. It needs to place orders, not to read the customers table. A procedure declared SECURITY DEFINER, which PostgreSQL executes with the privileges of the user that owns it, can do the work, and the application receives only EXECUTE on it. The application can then do exactly one thing: call the procedure.

Step 2: add attribute rules where roles are not enough

Wholesale buyers may see only their own shop's orders. A role per shop would mean hundreds of roles; what decides access is an attribute, the shop the user belongs to. NIST defines attribute-based access control (ABAC) as granting or denying requests based on attributes of the subject, attributes of the object, environment conditions and a policy written in terms of them.

PostgreSQL implements this kind of rule with row security policies, also known as row-level security: ALTER TABLE orders ENABLE ROW LEVEL SECURITY; CREATE POLICY shop_isolation ON orders USING (shop_id = current_setting('app.shop_id')::integer);. The application sets app.shop_id for each session from the signed-in user, and every query on orders silently returns only that shop's rows. Two details from the manual matter: if row security is enabled and no policy exists, a default-deny policy applies, so no rows are visible; and table owners normally bypass row security unless the table is altered to force it.

The four access models, compared

Bottom line: grant by role for job functions, add attribute policies for per-row rules, and give each account only the privileges its job needs, such as EXECUTE on one procedure.

Step 3: authenticate properly

Authorization is worthless if anyone can claim to be Ana. PostgreSQL's client authentication rules in pg_hba.conf (Lesson 13) choose a method for each kind of connection, and its manual lists several.

• Passwords with SCRAM-SHA-256. The current password method. PostgreSQL's documentation says support for MD5-encrypted passwords is deprecated and will be removed, so old MD5 hashes are an audit finding.
• Certificate authentication requires TLS and authenticates users by checking the client certificate they send, which suits service accounts on servers Harbor controls.
• Kerberos through GSSAPI. In a Windows or Kerberos domain, users present tickets instead of passwords. The server is identified by a service principal name (SPN): for PostgreSQL the format is servicename/hostname@realm, and for SQL Server Microsoft documents names of the form MSSQLSvc/FQDN:port. If the SPN is missing or wrong, Kerberos authentication to that service fails.
• LDAP checks the password against a directory server.
• OAuth bearer tokens, which PostgreSQL 18 accepts, validated by a module you configure.

Cloud services add their own token methods. Amazon RDS's IAM database authentication lets an application connect without a database password by presenting an authentication token that, per AWS's documentation, has a lifetime of 15 minutes.

Step 4: identity beyond the database

• Multifactor authentication (MFA) requires more than one distinct factor: something you know, such as a password; something you have, such as a hardware token; something you are, such as a fingerprint. Two passwords are one factor twice. NIST's current authentication guidelines, SP 800-63B-4, were published in July 2025. For database access, MFA is commonly enforced at the identity provider that signs users in, which then vouches for them through SSO or tokens.
• Single sign-on (SSO) lets a user authenticate once to an identity provider and reach many systems without signing in again.
• Federated identity extends that trust across organizations: Harbor's portal accepts sign-ins vouched for by a partner's own identity provider, so partner staff use their company accounts and are disabled when they leave the partner.
• OAuth 2.0 and OpenID Connect. PostgreSQL's documentation describes OAuth 2.0, defined in RFC 6749, as a framework that lets third-party applications obtain limited access to a protected resource: it is about authorization, delegated access. OpenID Connect describes itself as a simple identity layer on top of OAuth 2.0: it adds authentication, telling the application who the user is.

Step 5: certificates keep it all honest

TLS certificates, still often called SSL certificates, appear twice in this lesson: the server certificate that lets clients verify the database (Lesson 19), and client certificates that authenticate service accounts. Both expire. A certificate that lapses at midnight takes every application connection down with it, with nothing changed in the database. Inventory every certificate with its expiry date, monitor it like disk space (Lesson 14), and automate renewal where possible.

Common misconceptions

• "Superuser for the application is simpler." It is simpler for an attacker too: any SQL injection in the application inherits every privilege. Give it EXECUTE on what it needs.
• "A password plus a security question is MFA." Both are something you know, one factor twice.
• "OAuth logs users in." OAuth grants limited access to resources; OpenID Connect adds the identity layer that says who the user is.
• "Row-level security protects against the table owner." Owners bypass it by default unless the table is set to force row security.

The takeaway

• RBAC grants by job role; ABAC uses attributes and policies; DAC lets owners decide; MAC enforces central labels and clearances.
• In PostgreSQL: group roles with GRANT, login roles as members, least privilege such as EXECUTE on a SECURITY DEFINER procedure, and row-level security policies for per-row rules.
• Authenticate with SCRAM-SHA-256 (MD5 is deprecated), certificates, Kerberos with correct SPNs, LDAP or tokens such as OAuth bearer tokens or RDS IAM tokens.
• MFA needs distinct factors; SSO signs in once; federation trusts another organization's identity provider; OAuth authorizes, OpenID Connect authenticates.
• Track every TLS certificate's expiry; an expired certificate is an outage.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 4.3 and acronym list. CompTIA (PDF)
2. National Institute of Standards and Technology. (n.d.). Glossary entries: role-based access control, attribute based access control, mandatory access control, multi-factor authentication. RBAC; ABAC; MAC; MFA
3. National Institute of Standards and Technology. (2025). Digital identity guidelines: Authentication and authenticator management (NIST SP 800-63B-4). csrc.nist.gov
4. PostgreSQL Global Development Group. (2025). GRANT; CREATE PROCEDURE (SECURITY DEFINER); Row security policies; Authentication methods; GSSAPI authentication; OAuth authorization/authentication; Certificate authentication. In PostgreSQL 18 documentation. GRANT; CREATE PROCEDURE; row security; methods; GSSAPI; OAuth; certificates
5. Microsoft. (n.d.). Register a service principal name for Kerberos connections. SQL Server documentation. learn.microsoft.com
6. Neuman, C., Yu, T., Hartman, S., and Raeburn, K. (2005). The Kerberos network authentication service (V5) (RFC 4120). rfc-editor.org; Hardt, D. (Ed.). (2012). The OAuth 2.0 authorization framework (RFC 6749). rfc-editor.org
7. OpenID Foundation. (n.d.). OpenID Connect Core 1.0. openid.net; Amazon Web Services. (n.d.). IAM database authentication. docs.aws.amazon.com

"It is behind the firewall"

After Lesson 13, Harbor's database accepts connections only from the two web servers. When the owner asks whether customer data is safe, the reply comes quickly: it is behind the firewall. A month later a security researcher emails Harbor a list of every customer's email address, obtained through the web shop's order-lookup box. The firewall worked perfectly the whole time. This lesson traces why, and builds the layers objective 4.4 asks you to explain: physical and logical controls, Zero Trust, attack surface management, and defences against four named attacks.

Tracing the breach

The order-lookup page built its query by pasting the visitor's input into the SQL text. Reproduced in Python against a SQLite copy of three orders, with the input x' OR '1'='1, the code produced this statement:

SELECT order_id, email, total FROM orders WHERE email = 'x' OR '1'='1'

and returned all three orders. The quote in the input closed the string early, and OR '1'='1' made the condition true for every row. Run the same lookup with a placeholder, WHERE email = ?, passing the input as a value, and it returns nothing: the database compares the whole input, quote included, with each email, and none match.

Now the firewall's silence makes sense. The malicious SQL arrived through an allowed connection, from the allowed web server, on the allowed port, under the application's legitimate login. A network control decides who may talk to the database; it cannot judge what they say. That is why defence is built in layers, each covering what the others cannot see.

Key idea: network controls stop strangers reaching the database, but attacks that ride on allowed connections, injected SQL, stolen credentials, malicious insiders, need controls on identity, privilege and code as well.

Layer 1: physical

Physical security keeps hands off the hardware: locked server rooms or cages, badge access with logs, cameras, escorted visitors, and controlled handling and sanitization of media (Lesson 20). In the cloud the provider secures its data centres, but Harbor still owns the physical risk of administrators' laptops, any on-premises backups and removable drives. A stolen laptop with a database export in its downloads folder bypasses every network control Harbor has.

Layer 2: logical network controls

The objectives list three logical controls. Firewalls and the perimeter network were covered in Lesson 13: deny by default, allow the database port only from the application tier, and keep the database out of the internet-facing segment. Port security has two senses worth knowing. On network switches it limits which devices may use a physical port, for example by allowed MAC addresses or 802.1X authentication, so a visitor cannot plug a laptop into the server network. Network+ (cs-netplus) covers it in depth. For the database host itself, it means exposing only the ports the service needs and closing everything else.

Zero Trust

NIST's Zero Trust Architecture, SP 800-207, published in August 2020, starts from a principle that the "behind the firewall" answer violates. Zero trust assumes there is no implicit trust granted to assets or user accounts based solely on their physical or network location, or on asset ownership. Applied to Harbor's database:

• Every connection authenticates strongly, even from the web servers (Lesson 22).
• Every account has the least privilege its task needs: the web application's login can execute place_order and read products, not select every customer's email.
• Every connection is encrypted and the server is verified (Lesson 19).
• Activity is monitored as if a breach is already under way (Lesson 14).

Under those rules the breach above shrinks. With the application limited to procedures, there is no direct SELECT on customers for an injected query to borrow.

Attack surface management

NIST defines an attack surface as the set of points on the boundary of a system where an attacker can try to enter, cause an effect on, or extract data from it. Managing it is a cycle.

1. Inventory every point: listeners and ports, login roles and their privileges, installed extensions, functions that run with elevated rights, management consoles, backup files and storage buckets, test copies.
2. Scan for vulnerabilities, only on systems Harbor owns and with written authorization: a scanner compares versions and configurations against known flaws.
3. Patch through the cycle in Lesson 16, with known exploited vulnerabilities first.
4. Remediate what patches do not fix: restrict listen_addresses, drop unused roles and extensions, delete stray exports.
5. Verify by scanning again. A finding is closed only when the rescan no longer reports it.

Four attacks the objectives name

The adversarial machine learning taxonomy that NIST published in March 2025, AI 100-2 E2025, places poisoning among the attacks on AI systems. For a database administrator, the practical point is that the database feeding a model is part of the model's attack surface: whoever can write to its training data can steer its predictions.

Key idea: each named attack has a database-shaped defence. Parameterized SQL and least privilege stop injection; provenance and write control limit poisoning; offline, tested backups beat ransomware; and out-of-band checks with phishing-resistant MFA defeat social engineering.

Common misconceptions

• "Behind the firewall means safe." Injection, stolen credentials and insiders all use allowed connections. Zero Trust grants no trust for network location alone.
• "Escaping quotes stops SQL injection." OWASP marks escaping all dynamic input as strongly discouraged; it is easy to get wrong. Parameterize.
• "Backups protect against ransomware." Only backups the ransomware cannot reach: offline, encrypted and tested.
• "A vulnerability scan is the fix." A scan only finds problems. Remediation and a confirming rescan close them.

Recap

• Network controls decide who can connect, not what they send; layer physical, logical, identity and code controls.
• Physical: locked, logged access to hardware and careful media handling. Logical: firewalls, perimeter networks, switch port security and minimal open ports.
• Zero Trust (NIST SP 800-207): no implicit trust from network location or ownership; authenticate, authorize minimally, encrypt and monitor every connection.
• Attack surface management: inventory, authorized scanning, patching, remediation, verification by rescan.
• SQL injection: parameterize and limit privileges. Data poisoning: guard training data. Malware: offline, encrypted, tested backups. Social engineering: verify out of band and use phishing-resistant MFA.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 4.4. CompTIA (PDF)
2. Rose, S., Borchert, O., Mitchell, S., and Connelly, S. (2020). Zero trust architecture (NIST SP 800-207). National Institute of Standards and Technology. csrc.nist.gov
3. National Institute of Standards and Technology. (n.d.). Glossary entries: attack surface and data poisoning. attack surface; data poisoning
4. National Institute of Standards and Technology. (2025). Adversarial machine learning: A taxonomy and terminology of attacks and mitigations (NIST AI 100-2 E2025). csrc.nist.gov
5. OWASP Foundation. (n.d.). SQL injection prevention cheat sheet. owasp.org
6. Cybersecurity and Infrastructure Security Agency. (n.d.). #StopRansomware guide. cisa.gov
7. Python Software Foundation. (n.d.). sqlite3: How to use placeholders to bind values in SQL queries. docs.python.org

Three ways to copy a database

PostgreSQL's manual opens its backup chapter by saying there are three fundamentally different approaches to backing up its data: an SQL dump, a file-system-level backup, and continuous archiving. Every engine has its versions of the same three, and the choice among them decides what Harbor can recover, how far back, and how fast. Objective 5.1 asks you to implement backup and restoration processes in scenarios, so this lesson builds Harbor's backup plan step by step and then breaks one link in it to show what a restore depends on.

Step 1: decide what kind of copy

Database dumping is the simplest. pg_dump's documentation says it does not block other users reading or writing the database while it runs, and that it dumps a single database. Roles and tablespaces are shared by the whole cluster, so they are exported with pg_dumpall's global objects; forget them and a restored database has no login for the application. MongoDB's mongodump likewise creates a binary export of a database's contents.

Step 2: full, incremental and differential

Copying everything every night is simple but slow and large, so most schedules mix three types.

Microsoft's documentation states the differential rule precisely: a differential backup is based on the most recent previous full backup and captures only the data that has changed since that full backup. PostgreSQL added incremental physical backups in version 17. Its pg_basebackup tool can take an incremental backup containing only blocks modified since a reference backup. The documentation notes that such a backup cannot be used directly: pg_combinebackup must first combine it with the backups it depends on to reconstruct a full one.

Harbor's plan: a full base backup every Sunday, incrementals Monday to Saturday, and continuous WAL archiving between them, so any moment of the week can be recovered.

Step 3: schedule and automate

A backup that depends on someone remembering will be missed. Schedule jobs with the operating system's scheduler, cron or a systemd timer on Linux and Task Scheduler on Windows, or with the engine's own, such as SQL Server Agent; keep WAL archiving running continuously. Every job should write a dated file name, encrypt its output (Lesson 19), check its own exit status (Lesson 6) and report the result. Retention is part of the schedule: how many fulls, how many weeks of incrementals and archived logs, and when copies are deleted under the retention policy (Lesson 21).

Step 4: store copies on site and off site

An on-site copy, on a backup server or NAS in the same building, restores fastest. It also burns in the same fire, floods in the same flood and, if it is reachable over the network, is encrypted by the same ransomware. CISA's #StopRansomware guide warns that many ransomware variants try to find and delete or encrypt accessible backups, and urges organizations to keep offline, encrypted backups and test them regularly. Harbor therefore keeps three tiers: the latest backups on site for speed, and copies in another region of a cloud provider. The third is a weekly copy that is offline or immutable, so no credential on the network can delete it. A common rule of thumb summarizes the pattern as three copies, on two kinds of media, one of them off site.

The point: mix full, incremental or differential backups with continuous log archiving, automate them, and keep copies in more than one place, at least one of them out of reach of anything that can compromise the database server.

Step 5: validate, then prove it by restoring

A backup is a hypothesis until it has been restored. Two checks turn it into evidence.

• Validate the backup's hash. pg_basebackup writes a backup manifest listing each file with a checksum. pg_verifybackup checks the backup against that manifest, recomputing every file's checksum and reporting any mismatch. SQL Server's RESTORE VERIFYONLY checks that a backup set is complete and readable without restoring it. A hash also proves an off-site copy arrived intact.
• Restore it. On a schedule, restore the latest backup to a scratch server, start the database, run integrity checks (Lesson 16) and a few business queries, and record how long the whole restore took. That measured time is the evidence behind the recovery time objective in Lesson 25.

Restore points make point-in-time recovery precise. Before Harbor's risky bulk price change, the administrator runs SELECT pg_create_restore_point('before_price_change');, which, in the manual's words, creates a named marker in the write-ahead log that can later be used as a recovery target. If the change goes wrong, recovery replays the archived log up to that name and stops. MySQL does the same with its binary log: point-in-time recovery restores a full backup first, then brings the server forward incrementally.

Change one input: the missing Wednesday

On Friday the primary's disk fails. The restore needs Sunday's full and the incrementals for Monday through Thursday, combined in order. Wednesday's incremental is missing: its job wrote a zero-byte file and nobody was alerted. The chain stops at Tuesday. Thursday's incremental is useless without Wednesday's, and continuous WAL archiving can only bridge the gap if every log file from Tuesday onwards is intact. Had Harbor used differentials, Thursday's differential would have needed only Sunday's full. The lesson is not that incrementals are bad. It is that a chain is as strong as its weakest link, so every link needs validation and an alert.

Step 6: reports and alerts

Each backup job reports its status, size, duration and destination; each restore test reports its result and restore time. Alerts fire on any failure, on a size far from the usual (a zero-byte file is a failure that exited cleanly), and on the age of the last good backup (Lesson 14). The weekly report shows trends: backups growing faster than data, durations creeping towards the backup window, restore tests skipped.

Archiving and snapshots

Archiving is not backup. A backup is a recent copy kept to recover from loss. An archive is data moved out of the live database for long-term retention, such as order lines older than three years kept for tax law but no longer queried daily. Archives need their own integrity checks and retention rules, and dropping a monthly partition after archiving it is a common pattern (Lesson 17).

Snapshots are fast, but PostgreSQL's documentation sets the condition for using one: it must be a consistent, frozen snapshot of the whole data directory, write-ahead log included. A copy made with ordinary tools while the server runs is not consistent. And a snapshot on the same storage array shares its fate, so copy it off site like any other backup.

Common misconceptions

• "The job succeeded, so we have a backup." Only a validated hash and a successful test restore show that. Zero-byte and truncated files can exit cleanly.
• "pg_dump backs up everything." It dumps one database. Roles and tablespaces need pg_dumpall's globals.
• "Incremental and differential are the same." Incremental copies changes since the previous backup and restores as a chain; differential copies changes since the last full and restores with two pieces.
• "A snapshot is an off-site backup." Until it is copied elsewhere it sits on the same storage as the data it protects.

What to carry forward

• Backup kinds: logical dumps, physical base backups, continuous log archiving for point-in-time recovery, and consistent snapshots.
• Full copies everything; incremental copies changes since the last backup and restores as a chain; differential copies changes since the last full.
• Automate on a schedule with encryption, exit-status checks and retention.
• Store on site for speed and off site, with an offline or immutable copy against ransomware.
• Validate with manifest checksums (pg_verifybackup) or RESTORE VERIFYONLY, and prove backups with timed test restores; use restore points for precise point-in-time recovery.
• Report and alert on every job, size anomalies and the age of the last good backup. Archives are for retention, backups for recovery.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 5.1. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). Backup and restore (chapter 25), File system level backup, Continuous archiving and point-in-time recovery. In PostgreSQL 18 documentation. backup; file system backup; PITR
3. PostgreSQL Global Development Group. (2025). pg_dump; pg_basebackup; pg_combinebackup; pg_verifybackup; System administration functions (pg_create_restore_point). In PostgreSQL 18 documentation. pg_dump; pg_basebackup; pg_combinebackup; pg_verifybackup; restore points; Release 17 (incremental backup). release notes
4. Microsoft. (n.d.). Differential backups (SQL Server) and RESTORE VERIFYONLY. differential backups; VERIFYONLY
5. Oracle. (n.d.). Point-in-time (incremental) recovery. In MySQL 8.4 reference manual. dev.mysql.com; MongoDB, Inc. (n.d.). mongodump. mongodb.com
6. Cybersecurity and Infrastructure Security Agency. (n.d.). #StopRansomware guide (backups). cisa.gov

31 January 2017

On 31 January 2017 GitLab.com, a large public code-hosting service, suffered what its own postmortem calls a major service outage, caused by the accidental removal of data from its primary database server. The postmortem is unusually candid, and it is one of the best free case studies a database administrator can read.

It began with replication. The secondary PostgreSQL server fell behind, and by the time engineers looked, the write-ahead log segments it needed had already been removed from the primary. GitLab.com was not using WAL archiving, so the secondary could not catch up from an archive; it had to be rebuilt by wiping its data directory and copying the database afresh with pg_basebackup. The copy hung, silently. Late at night, trying again, an engineer wiped the PostgreSQL data directory believing he was on the secondary. The command ran on the primary.

Five safety nets, and the one that held

GitLab had several ways to recover, on paper. The postmortem lists them, and what happened to each:

• A daily pg_dump uploaded to cloud storage. It had been failing. The backup host ran a different PostgreSQL major version from the database, so pg_dump stopped with an error. The cron job's failure emails were rejected by the receiving mail system, so nobody knew.
• Replication to the secondary. It was used for failover, not disaster recovery, and it was already broken, with the data gone from both servers.
• Disk snapshots in the cloud. These covered other servers, not the database.
• A daily LVM snapshot loaded into staging. This, together with a snapshot an engineer had happened to take by hand about six hours before the outage, is what saved the service.

Restoring from the staging snapshot meant copying the data back to production over network disks throttled to around 60 Mbps; the copy took around 18 hours. Everything created in the hours between the manual snapshot and the deletion was lost. Every safety net had existed, and almost none had been tested.

The two numbers that define recovery

Disaster recovery planning starts by agreeing two targets with the business, and NIST's glossary defines both.

GitLab's effective RPO that night was about six hours, set by an accidental manual snapshot, and its recovery took far longer than anyone would have chosen. Harbor's owner, after reading the postmortem, agrees targets for the order database: an RPO of 15 minutes and an RTO of two hours. Lesson 24's measured restore time shows that a full restore takes three hours, so the RTO cannot be met by restoring alone. Harbor needs a standby that is already loaded and nearly current, with continuous WAL archiving behind it to meet the RPO.

Why this matters: RPO and RTO turn a vague wish for safety into numbers that dictate the design, and only a measured test shows whether the design meets them.

Writing the plan down

A disaster recovery plan is the procedure for restoring a system after a disaster; NIST's contingency planning guide, SP 800-34 Revision 1, places it within a family of plans. The objectives name three documents a DR plan depends on.

NIST revised its guide to security plans in June 2026 as SP 800-18 Revision 2, which treats security, privacy and supply chain risk management plans together as system plans. Alongside these, the DR plan itself holds the runbooks: step-by-step SOPs (Lesson 12) for failover, restore and failback, with contact lists and the escalation path from the SLA (Lesson 8). GitLab's postmortem notes that the silent waiting behaviour of pg_basebackup was not clearly documented in its engineering runbooks.

Log shipping

Log shipping keeps a standby close to the primary by continuously sending it the transaction log. Microsoft describes SQL Server log shipping as automatically sending transaction log backups from a primary database to one or more secondary databases on separate server instances. PostgreSQL's manual says that using continuous archiving to keep standby servers ready to take over is widely referred to as warm standby or log shipping. The standby replays each log as it arrives, so its data trails the primary by roughly the shipping interval, which therefore sets the achievable RPO. Streaming replication, covered in Lesson 26, sends changes continuously rather than file by file.

Failover and failback

Failover moves service from the failed primary to the standby. In PostgreSQL you run pg_ctl promote or call pg_promote(), and clients find the new primary through a DNS change with a short TTL or a multi-host connection string (Lesson 13). Before promoting, make sure the old primary cannot come back as a primary too. PostgreSQL's documentation calls this STONITH, shoot the other node in the head, and explains why: if both systems think they are the primary, the result is confusion and ultimately data loss.

Failback returns service to the original site once it is repaired. It is not a reverse failover. The old primary has diverged from the new one, so it must first be resynchronized as a standby. PostgreSQL's pg_rewind exists for this: its documentation gives the typical scenario of bringing an old primary back online after failover as a standby that follows the new primary. Only then is a planned switchover, at a quiet time, used to move service back.

Testing the plan

GitLab's safety nets failed because nothing exercised them. DR tests range from cheap to realistic.

• Tabletop exercise: NIST describes a discussion-based exercise in which people with roles in a plan talk through their responses to a scenario. It finds gaps in contacts, decisions and documents for the cost of two hours.
• Restore drills: restore last night's backup to scratch hardware and time it (Lesson 24).
• Failover tests: promote the standby in a maintenance window, run the application against it, then fail back, measuring the real RTO and checking the real RPO.

Every test ends by updating the plan and runbooks with what went wrong, which is exactly what a postmortem like GitLab's does after the fact.

Common misconceptions

• "Replication is a backup." Replication copies mistakes too; a deletion on the primary is replicated within seconds. GitLab's postmortem separates replication for failover from disaster recovery.
• "RPO and RTO are the same thing." RPO is how much data you can lose; RTO is how long you can be down. Different designs meet each.
• "Failback is just failover in reverse." The old primary has diverged and must be resynchronized, for example with pg_rewind, before it can serve again.
• "A DR plan that exists is a DR plan that works." Untested plans fail on details: a version mismatch, a rejected email, an undocumented wait.

What you now know

• RPO is the point in time to which data must be recovered; RTO is how long recovery may take. Agree both, then design and test to them.
• DR plans draw on the SSP (security requirements and controls), the COOP plan (sustaining essential functions within 12 hours for up to 30 days) and the SDD (architecture), plus runbooks.
• Log shipping sends transaction logs to standbys that replay them; the shipping interval bounds the RPO.
• Failover promotes a standby and must fence the old primary; failback resynchronizes the old primary, for example with pg_rewind, before a planned switchover.
• Test with tabletop exercises, timed restores and real failovers, and update the plan every time.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 5.2. CompTIA (PDF)
2. GitLab. (2017, February 10). Postmortem of database outage of January 31. about.gitlab.com
3. Swanson, M., Bowen, P., Phillips, A. W., Gallup, D., and Lynes, D. (2010). Contingency planning guide for federal information systems (NIST SP 800-34 Rev. 1, updated 11 November 2010). csrc.nist.gov
4. National Institute of Standards and Technology. (n.d.). Glossary entries: recovery point objective, recovery time objective, continuity of operations plan, system security plan, tabletop exercise. RPO; RTO; COOP; SSP; tabletop
5. National Institute of Standards and Technology. (2026). Developing security, privacy, and cybersecurity supply chain risk management plans for systems (NIST SP 800-18 Rev. 2). csrc.nist.gov
6. PostgreSQL Global Development Group. (2025). Log-shipping standby servers; Failover; pg_rewind. In PostgreSQL 18 documentation. log shipping; failover; pg_rewind
7. Microsoft. (n.d.). About log shipping (SQL Server). learn.microsoft.com

The two-hour problem

Lesson 25 left Harbor with a gap. The business wants the order system back within two hours of any failure, and a full restore from backup takes three. Restoring faster will not close a gap that large; the only answer is a second database that is already running, already loaded and nearly current. PostgreSQL's manual opens its chapter on the subject with exactly that idea. Database servers can work together so that a second server takes over quickly if the primary fails: high availability. They can also let several computers serve the same data: load balancing.

Objective 5.3 asks you to compare fault tolerance operations: high availability, risk analysis for single points of failure, multizone regions, redundancy through replication, geo-replication and mirroring, and transaction logging. This lesson compares them as Harbor would, by what each protects against and what it costs.

Risk analysis: find the single points of failure

A single point of failure is any component whose failure alone stops the service. Finding them is a walk along the path of a request: DNS, load balancer, application servers, network switch, database server, its storage, its power, its building, its cloud region. Harbor's current design has two application servers behind a load balancer, which is redundant, and one database server on one storage volume, which is not. The database is the single point of failure, and so is anything it depends on alone.

Redundancy works because independent failures rarely coincide. If one server is available 99 percent of the time, it is down 1 percent of the time. Take two servers that fail independently, either able to carry the load. Both are down together only 1 percent of 1 percent of the time, so the pair is available 99.99 percent, ignoring the time a failover takes. The word independently carries the whole calculation. Two servers on one storage array, one switch or one power feed fail together when that shared part fails, and the arithmetic collapses. Risk analysis therefore asks of every redundant pair: what do these two still share?

What matters here: redundancy only helps against independent failures, so risk analysis must find not just the single servers but the shared components, storage, network, power, zone, region, that make two servers fail as one.

Redundancy options compared

Harbor chooses the third row for the order database, plus asynchronous geo-replication to a second region for disaster recovery, plus the backups from Lesson 24, which protect against things no replica can.

Synchronous or asynchronous replication

PostgreSQL's documentation draws the distinction precisely. With synchronous replication, each commit of a write transaction waits until confirmation is received that the commit has been written to the write-ahead log on disk of both the primary and the standby. Committed data is never lost when the primary fails. Asynchronous solutions allow some delay between a commit and its propagation, opening the possibility that some transactions are lost in the switch to a standby, and that load-balanced replicas return slightly stale results.

The price of synchronous replication is latency, because every commit includes a network round trip to the standby. The same chapter warns that a fully synchronous solution over a slow network might cut performance by more than half. Inside one cloud region, zones are close enough that the round trip is small; across regions hundreds of kilometres apart, it is not. That is why the usual pattern is synchronous between zones and asynchronous between regions.

Engines and their names

• PostgreSQL streams its WAL to standbys, synchronously or asynchronously; failover promotes a standby (Lesson 25).
• SQL Server offered database mirroring for many years. Microsoft now marks it as a feature that will be removed in a future version and directs users to Always On availability groups, in which a set of databases fail over together, with secondary replicas in synchronous-commit or asynchronous-commit mode.
• MongoDB uses replica sets, which its manual describes as groups of mongod processes that maintain the same data set. When the primary fails, the members elect a new one; MongoDB says the median time to elect a new primary should not typically exceed 12 seconds with default settings.
• Cloud services package the same ideas. Azure SQL Database's active geo-replication continuously replicates a primary database to a readable secondary database, which can be placed in another region; managed services also offer standbys in a second zone.

Zones and regions

Lesson 13 introduced the multizone region: three or more physically separate data centres, each with its own power, cooling and networking, close enough for low latency. A synchronous standby in a second zone removes the building as a single point of failure while keeping commits fast. A whole region can still fail, through a regional network fault or a provider control-plane outage. A copy in a second region, geo-replicated asynchronously, covers that disaster case at the cost of a few seconds of possible data loss and a slower switch. Choosing where each copy lives is risk analysis again: what does each copy share with the primary?

Transaction logging: the thread through all of it

Every technique in this lesson runs on the transaction log. Microsoft calls SQL Server's transaction log a critical component of the database that records all transactions and the modifications each makes; PostgreSQL's write-ahead log plays the same role (Lesson 12). The log is what crash recovery replays, what streaming replication sends, what log shipping ships, what continuous archiving archives and what point-in-time recovery replays to a restore point. Two practical consequences follow. The log needs fast, reliable storage of its own, because every commit waits for it. And the log must be watched: if a disconnected replica or a failing archive stops old log files from being removed, the log volume fills, and a database that cannot write its log cannot accept writes at all.

What high availability does not cover

A standby faithfully reproduces everything the primary does, including mistakes. An accidental DELETE, a bad migration, corruption written by a faulty application or ransomware encrypting rows through a stolen login all reach the standby within seconds. GitLab's postmortem in Lesson 25 separated replication for failover from disaster recovery for this reason. High availability covers hardware, zone and region failures; backups with point-in-time recovery cover logical errors. A complete design needs both.

What matters here: choose synchronous replication where losing a committed transaction is unacceptable and latency allows it, asynchronous across long distances, and always keep backups, because replicas copy errors as faithfully as data.

Common misconceptions

• "Two servers mean no single point of failure." Not if they share storage, a switch, power, a zone or a region.
• "Asynchronous replication loses no data." Commits not yet sent when the primary fails are lost in the switch.
• "High availability replaces backups." Replication copies deletions and corruption too; only backups with point-in-time recovery undo logical errors.
• "Mirroring is SQL Server's high availability feature." Microsoft marks it for removal and recommends Always On availability groups instead.

The short version

• Risk analysis finds single points of failure, including components that redundant pairs still share.
• Independent redundancy multiplies availability: two 99 percent servers give about 99.99 percent, if their failures really are independent.
• Synchronous replication waits for the standby's WAL write and loses no committed data; asynchronous replication is faster but can lose recent commits.
• Synchronous across zones of a multizone region; asynchronous geo-replication across regions.
• Engine names: PostgreSQL streaming replication, SQL Server availability groups (mirroring is being removed), MongoDB replica sets with automatic elections.
• The transaction log underlies recovery, replication, log shipping and archiving; protect and monitor it.
• High availability covers failures of hardware and places; backups cover mistakes.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 5.3. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). High availability, load balancing, and replication (chapter 26), including synchronous replication. In PostgreSQL 18 documentation. high availability; standby servers
3. Microsoft. (n.d.). Database mirroring (SQL Server); What is an Always On availability group?; The transaction log (SQL Server); Active geo-replication (Azure SQL Database). mirroring; availability groups; transaction log; geo-replication
4. MongoDB, Inc. (n.d.). Replication. MongoDB manual. mongodb.com
5. IBM. (n.d.). Locations (multizone regions). IBM Cloud documentation. cloud.ibm.com

Two feeds on a Monday

Harbor now receives data it did not create. Every night its main supplier drops a CSV file of prices and stock on an SFTP server. Its warehouse partner offers stock updates as JSON, either in a nightly file or as a stream of events. An analyst wants competitors' prices too, though no competitor offers an API. Objective 6.1 asks you to use data acquisition techniques in scenarios like these. You classify the data, read its format, and choose between ETL and ELT and between streaming and non-streaming sources, and you know when scraping is acceptable. This lesson builds Harbor's supplier load step by step, then changes one input, the warehouse feed, to show how a stream changes the design.

Step 1: classify before you move anything

Data classification assigns each data set a level that decides how it must be handled. US federal systems use FIPS 199, which categorizes information by the potential impact of a loss of confidentiality, integrity or availability as low, moderate or high. Many businesses, Harbor included, use a simpler ladder: public, internal, confidential, restricted.

Classification comes first because it drives everything downstream. The supplier's price file is internal. A partner file containing customer delivery addresses is confidential personal data. That means encrypted transfer, masking in test copies, restricted access and a retention rule (Lessons 19 to 21), and the label lets data loss prevention recognize it later. A pipeline built before classification usually has to be rebuilt after it.

Step 2: read the format

Never parse CSV by splitting on commas. Harbor's file contains the line BT-SM,"Summit boot, 10"" ankle",149.00,7. Python's csv module, following the quoting rules, reads it as four fields: BT-SM, the description Summit boot, 10" ankle with its comma and inch mark intact, 149.00 and 7. A naive split on commas would produce five fields and shift every value after the description into the wrong column. JSON and XML are semistructured (Lesson 2), so loading them means mapping keys or elements to columns, or storing documents in a JSON column and extracting what queries need.

Step 3: ETL or ELT

Both move data from a source to a target; Microsoft's architecture guidance puts the difference in one line: ELT differs from ETL solely in where the transformation takes place.

Harbor's supplier load is ELT, in two steps. First, load the file as text into a staging table with a load timestamp, changing nothing: CREATE TABLE stg_supplier (sku text, description text, price text, stock text, loaded_at timestamptz); and a COPY. Then transform with SQL: INSERT INTO products (sku, description, price, stock) SELECT sku, trim(description), CAST(price AS numeric), CAST(NULLIF(stock, '') AS integer) FROM stg_supplier WHERE sku <> '';. NULLIF turns the empty stock field for the trail mug into NULL instead of failing the cast, and the staging table keeps the raw file for the day a rule changes or a value is questioned. Run against a SQLite copy, the transform produced three clean products, with the mug's stock NULL rather than an error.

Remember: classify first, parse formats with real parsers, and choose ETL when data must be cleaned or stripped before it lands, ELT when the target can do the work and keeping raw data has value.

Step 4: batch or stream

A non-streaming source delivers data in batches: a nightly file, an hourly API pull, a weekly export. A streaming source delivers each event as it happens. Apache Kafka's documentation describes event streaming as capturing data in real time from sources such as databases, sensors and applications as streams of events, storing them durably, and processing them as they arrive. Streams cost more to build and run; they are worth it when the business needs the data within seconds.

Change data capture (CDC), in the objectives' acronym list, is how a database becomes a streaming source without querying whole tables. PostgreSQL's logical decoding extracts all persistent changes to a database's tables into a coherent format that can be read without knowing the database's internals. SQL Server's CDC uses the SQL Server Agent to record inserts, updates and deletes in a table. Both read the transaction log (Lesson 26), so the source pays little for the feed.

Change one input: the warehouse feed becomes a stream

The warehouse partner switches from a nightly JSON file to a stream of stock events. The nightly load could simply replace each product's stock. A stream cannot be handled that way, because events can arrive twice after a network retry, or out of order. The load becomes an idempotent upsert keyed on each event's identifier and timestamp. It inserts the event if its id is new, and applies it to the stock level only if it is newer than the last applied event for that product. The same data, delivered differently, needs a different design.

Step 5: scraping, the last resort

Scraping extracts data from web pages built for people. It is brittle, because a page redesign breaks it silently; it can overload the site; and it raises legal and contractual questions. RFC 9309, which standardized the robots exclusion protocol, says plainly that robots.txt rules are not a form of access authorization; they express the site's wishes, and the site's terms of use may forbid scraping entirely. Harbor's rule: use an official API or a data licence where one exists, read the terms first, rate-limit any approved scraper, and get legal advice before scraping anything the site has not offered.

Common misconceptions

• "CSV is simple, so splitting on commas is fine." Quoted fields contain commas, quotes and line breaks. Use a parser that follows RFC 4180.
• "ELT means no transformation." It transforms just as much, after loading, inside the target.
• "A stream is just a faster batch." Streams bring duplicates and out-of-order events, so loads must be idempotent.
• "If robots.txt does not forbid it, scraping is allowed." RFC 9309 says the rules are not access authorization; terms of service and law still apply.

Putting it together

• Classify data first (for example public, internal, confidential, restricted, or FIPS 199's low, moderate, high), because classification sets handling.
• Flat files, JSON and XML each need proper parsing; RFC 4180, RFC 8259 and XML 1.0 define them.
• ETL transforms before loading; ELT loads raw data into staging and transforms inside the target.
• Non-streaming sources arrive in batches; streaming sources deliver events continuously; CDC turns a database's log into a stream.
• Stream loads must be idempotent and tolerate duplicates and reordering.
• Scrape only as a last resort, within terms, rate limits and law.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 6.1 and acronym list. CompTIA (PDF)
2. National Institute of Standards and Technology. (2004). Standards for security categorization of federal information and information systems (FIPS 199). csrc.nist.gov
3. Shafranovich, Y. (2005). Common format and MIME type for comma-separated values (CSV) files (RFC 4180). rfc-editor.org; Bray, T. (Ed.). (2017). The JavaScript Object Notation (JSON) data interchange format (RFC 8259). rfc-editor.org
4. World Wide Web Consortium. (2008). Extensible Markup Language (XML) 1.0 (Fifth Edition). W3C Recommendation, 26 November 2008. w3.org
5. Microsoft. (n.d.). Extract, transform, load (ETL). Azure Architecture Center. learn.microsoft.com
6. Apache Software Foundation. (n.d.). Introduction (event streaming). Apache Kafka documentation. kafka.apache.org
7. PostgreSQL Global Development Group. (2025). Logical decoding concepts. In PostgreSQL 18 documentation. postgresql.org; Microsoft. (n.d.). What is change data capture (CDC)? learn.microsoft.com
8. Koster, M., Illyes, G., Zeller, H., and Sassman, L. (2022). Robots exclusion protocol (RFC 9309). rfc-editor.org

Six ways into one database

Draw every system that exchanges data with Harbor's database and the picture gets crowded. The warehouse partner's Java service reads stock levels. The accountant's spreadsheet pulls monthly sales through a driver. A small supplier still uploads its nightly price file by FTP. Harbor's old accounting package offers its data only as an XML web service. The new product catalogue is published to partners as a web API. And a Linux analytics server reads export files from a shared directory. Each uses a different connectivity method from the list in objective 6.1: ODBC, JDBC, OData, FTP, NFS, SSH, SMB and its versions, RPC, SOAP and APIs. This lesson compares them in three families: database drivers, application interfaces, and file transfer and sharing.

Database drivers: ODBC and JDBC

A driver lets a program talk to a database through a standard interface, so the program's code does not change much when the database does.

Both depend on a driver supplied for each database, and the driver's version matters: an old driver may not support the server's authentication method or TLS settings, a problem Lesson 29 returns to. Python's DB-API (Lesson 6) and .NET's providers play the same role in their languages.

Application interfaces: REST, OData, SOAP and RPC

When systems belong to different organizations, they rarely connect straight to each other's databases. They exchange data through an application programming interface (API) that hides the database behind rules, authentication and versioning.

The standards behind them are worth knowing by name. HTTP's methods, defined in RFC 9110, give REST its verbs. GET requests a representation of a resource, PUT asks that the resource's state be replaced, and DELETE asks the server to remove it. POST commonly creates, which maps the four methods neatly onto CRUD (Lesson 3). The OData project describes OData as an ISO/IEC approved OASIS standard that defines best practices for building and consuming RESTful APIs. The W3C's SOAP Version 1.2 Recommendation (second edition, 2007) describes SOAP as a lightweight protocol for exchanging structured information, built on XML. And gRPC describes itself as a high-performance remote procedure call framework.

In short: drivers such as ODBC and JDBC connect programs straight to a database. APIs, REST, OData, SOAP and RPC, put a controlled interface between systems, the safer default when systems belong to different teams or organizations.

File transfer and file sharing

Much integration still moves files. The four protocols in the objectives differ sharply in security.

The acronym list also names MFT, managed file transfer: products that wrap file movement in scheduling, retries, encryption, integrity checks and audit logs, so a partner's nightly upload becomes a monitored job instead of a script nobody watches.

Choosing the connection

Whatever the method, the same rules from earlier lessons apply. Encrypt in transit, give each integration its own least-privilege identity, and allow its traffic through firewalls only from known sources (Lesson 13). Log every connection, so failed transfers and unusual reads are visible (Lesson 14).

Common misconceptions

• "ODBC is only for Microsoft databases." It is a standard interface with drivers for PostgreSQL, MySQL and many others.
• "REST and OData are competing protocols." OData is a set of standard conventions for building RESTful APIs.
• "FTP is fine inside our network." It still sends credentials in clear text, which anyone on the path can capture. Use SFTP or an encrypted alternative.
• "SMB is SMB." Versions differ: SMBv1 has serious vulnerabilities and should be disabled; SMB 3 adds encryption.

Where this leaves us

• ODBC is a C interface and JDBC a Java API for reaching databases through per-database drivers; driver versions matter.
• REST uses HTTP methods on resources; OData standardizes REST querying; SOAP exchanges XML messages under formal contracts; RPC calls remote functions.
• FTP sends passwords in clear text; SSH and SFTP encrypt; SMB 3 can encrypt while SMBv1 should be disabled; NFS mounts remote directories.
• Prefer APIs between organizations, drivers within a team, and SFTP or MFT for files; secure every connection with encryption, least privilege, firewall rules and logging.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 6.1 and acronym list. CompTIA (PDF)
2. Microsoft. (n.d.). Microsoft Open Database Connectivity (ODBC). learn.microsoft.com; Oracle. (n.d.). JDBC introduction. The Java tutorials. docs.oracle.com
3. Fielding, R., Nottingham, M., and Reschke, J. (Eds.). (2022). HTTP semantics (RFC 9110). rfc-editor.org; OData. (n.d.). OData: The best way to REST. odata.org
4. World Wide Web Consortium. (2007). SOAP version 1.2 part 1: Messaging framework (Second Edition). w3.org; gRPC Authors. (n.d.). gRPC. grpc.io
5. Postel, J., and Reynolds, J. (1985). File transfer protocol (FTP) (RFC 959); Allman, M., and Ostermann, S. (1999). FTP security considerations (RFC 2577). RFC 959; RFC 2577
6. Ylonen, T., and Lonvick, C. (Ed.). (2006). The Secure Shell (SSH) protocol architecture (RFC 4251); Haynes, T., and Noveck, D. (Eds.). (2015). Network File System (NFS) version 4 protocol (RFC 7530). RFC 4251; RFC 7530
7. Microsoft. (n.d.). Overview of file sharing using the SMB 3 protocol; How to detect, enable and disable SMBv1, SMBv2, and SMBv3 in Windows; SMB security enhancements. SMB overview; SMB versions; SMB security

Monday, 06:10

Harbor's nightly supplier load has failed, and the morning stock levels are wrong. The first message in the team chat offers the obvious explanation: the supplier sent a bad file, so ask them to send it again. It is a plausible answer, and in this case it is wrong three times over. By 09:00 the administrator has found three separate problems, none of which a fresh file would have fixed. Objective 6.2 lists the categories such failures fall into. Data can be corrupt or unavailable, or arrive with format issues such as encoding or with a schema mismatch. Connections fail on permissions and timeouts, and encryption fails on data and key encryption keys. There are also infrastructure limits of platform, software version and driver, and programming errors, syntax and runtime. This lesson works through them the way the administrator did, from the evidence.

Read the error before you guess

Database errors carry a five-character SQLSTATE code, and its first two characters, the class, point to a category before you read another word. PostgreSQL's error code appendix lists them; these classes cover most acquisition failures:

The first failure in Harbor's log is a data exception on one row of the price file. That rules out the network and points at the data itself.

Format issues: encoding

The failing row contains the product name Café Alpine. The supplier's system writes files in Windows-1252, where é is the single byte 0xE9; Harbor's loader reads files as UTF-8, where 0xE9 cannot appear on its own. Reproduced in Python, decoding the supplier's bytes as UTF-8 fails with an error that the codec cannot decode byte 0xe9 in position 3. The opposite mistake does not fail at all, which makes it worse: UTF-8 bytes read as Windows-1252 load without complaint as Café Alpine, and the damage is found weeks later by a customer.

The fix is to state the encoding instead of assuming it. PostgreSQL's COPY has an ENCODING option that, its documentation says, specifies the file's encoding; if it is omitted, the current client encoding is used, which is exactly how mismatches slip in. A related trap is the byte order mark. A UTF-8 file that starts with one makes the first header read as an invisible character followed by sku, so a loader looking for the sku column cannot find it. Python's utf-8-sig codec strips the mark; Harbor's contract with suppliers now says UTF-8 without one.

Schema mismatch

With encoding fixed, the second failure appears: every row has one more field than the staging table expects. The supplier added a discount column in the middle of the file without warning. That is a schema mismatch. The same family includes renamed fields, reordered columns, a number that becomes text, and dates or decimals written in a different convention, such as 1.189,00 for 1,189. The fixes are structural:

• Validate the header and field count before loading anything, and fail fast with a clear message.
• Load into a staging table as text, then cast with checks (Lesson 27), so one malformed value rejects one row, not the whole night.
• Agree a data contract with each supplier: columns, types, encoding, and how changes are announced and versioned.

Corruption and unavailability

Data corruption in transit usually shows up as a truncated or partial file: a transfer that stopped halfway, a file read while it was still being written. Check row counts against a trailer record or a manifest, compare a hash supplied by the sender (Lesson 24), and only pick up files after the sender marks them complete.

Data unavailability is the source not being there: the file has not arrived, the server is down, or an API refuses requests. HTTP status 429, Too Many Requests, defined in RFC 6585, means the client is being rate limited; the answer is to slow down and retry with increasing delays, not to retry faster. For event streams, a message that fails repeatedly should not block the rest. A dead-letter queue holds it aside: Amazon's documentation describes dead-letter queues as targets for messages that are not processed successfully, so you can isolate them and find out why processing did not succeed. An alert on missing files and on a growing dead-letter queue turns silent gaps into visible ones (Lesson 14).

Connection issues: permissions and timeouts

The third failure in Harbor's log is a permission error, class 42, on the staging table. During last week's clean-up of privileges (Lesson 22), someone removed the integration account's INSERT on staging. The fix is not to make the account a superuser; it is to grant back exactly what the job needs.

Timeouts are the other common connection issue, and two settings are worth knowing by name. libpq's connect_timeout is the maximum time to wait while connecting; PostgreSQL's statement_timeout aborts any statement that takes more than the specified time. A load that worked for months and now times out usually means the data grew, a lock is blocking it (Lesson 15) or a firewall is silently dropping idle connections. Trace it with the layers from Lesson 13: name, path, listener, authentication.

Encryption issues: data and key encryption keys

Encrypted files and columns add their own failure: the data is intact, but cannot be read. Envelope encryption (Lesson 19) means two keys are involved, the data encryption key that encrypted the file and the key encryption key that protects it. If the KEK is unavailable, every DEK it protects is unusable. AWS's documentation is plain about one cause: a disabled KMS key cannot be used in any cryptographic operation until it is re-enabled. Other causes are a missing permission to use the key, a key rotated without the old version kept for existing data, or an expired certificate on a TLS connection (Lesson 22). The diagnosis is to read the decryption error, check the key's status and policy in the KMS, and confirm which key version encrypted the data.

Infrastructure limitations: platform, versions and drivers

• Platform. On 64-bit Windows there are separate 32-bit and 64-bit versions of the ODBC Data Sources tool, so a 32-bit reporting program cannot see a DSN created in the 64-bit tool.
• Software versioning. pg_dump's documentation says it cannot dump servers newer than its own major version and refuses even to try. The GitLab outage in Lesson 25 began with exactly that kind of mismatch, failing silently.
• Drivers. An old ODBC or JDBC driver may not support the authentication method or TLS version a newly hardened server requires. After any server upgrade, check every integration's driver version.

Programming errors: syntax and runtime

A syntax error stops a statement before it runs: a missing comma or a misspelled keyword fails every time, in testing, at once. A runtime error depends on the data: a division by zero, a cast of an empty string to a number, a duplicate key. It can pass every test and fail on the first unusual row in production. That is why loaders are tested with deliberately bad data, malformed dates, empty fields, duplicates, as well as good data, and why staging tables, NULLIF and validation queries (Lesson 27) catch runtime problems row by row.

The upshot: start from the error class and the evidence, and fix the category of problem rather than the single file. Then change the process, contracts, validation, alerts and grants, so the same failure cannot recur silently.

Common misconceptions

• "Ask the sender to resend" fixes most failures. Encoding, schema, permission, key and version problems all fail again on a fresh copy.
• "If the load did not error, the text is right." UTF-8 read as Windows-1252 loads silently as mojibake such as Café.
• "Retrying faster gets past a 429." Rate limiting needs slower retries with backoff.
• "Intact encrypted files can always be decrypted." Not if the key encryption key is disabled, deleted or no longer permitted.

Pulling it together

• Read the SQLSTATE class first: 08 connection, 22 data, 23 integrity, 28 authorization, 42 syntax or access rule.
• Encoding: declare it (COPY's ENCODING), beware silent mojibake and byte order marks, and agree UTF-8 in contracts.
• Schema mismatch: validate headers, stage as text, cast with checks, and keep data contracts.
• Corruption: counts, trailers and hashes. Unavailability: alerts for missing data, backoff on 429, dead-letter queues for failing messages.
• Connections: least-privilege grants and the right timeouts. Encryption: check the KEK's status, permissions and versions.
• Infrastructure: 32-bit and 64-bit ODBC, pg_dump's version rule, driver support. Syntax errors fail at once; runtime errors wait for unusual data.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 6.2 and acronym list. CompTIA (PDF)
2. PostgreSQL Global Development Group. (2025). PostgreSQL error codes (Appendix A); COPY (ENCODING); Client connection defaults (statement_timeout); Database connection control functions (connect_timeout); pg_dump. In PostgreSQL 18 documentation. error codes; COPY; statement_timeout; connect_timeout; pg_dump
3. Nottingham, M., and Fielding, R. (2012). Additional HTTP status codes (RFC 6585), status 429. rfc-editor.org
4. Amazon Web Services. (n.d.). Using dead-letter queues in Amazon SQS and Enable and disable keys (AWS KMS). dead-letter queues; KMS keys
5. Microsoft. (n.d.). ODBC Data Source Administrator. learn.microsoft.com
6. Python Software Foundation. (n.d.). codecs: Codec registry and base classes (UTF-8 and utf-8-sig). docs.python.org

A proposal on the table

Harbor's new data lead brings three proposals to the Monday meeting. Let a large language model (LLM) read each new supplier's file and draft the mapping to Harbor's schema. Give staff a chat assistant that answers questions about policies and orders from Harbor's own data. And set a software robot to copy order numbers from a supplier's web portal, which has no API, into Harbor's system each morning. The operations manager objects to all three: the model will make things up, the assistant will leak customer data, and the robot will break the first time the portal changes.

Both have evidence on their side, and objective 6.3 asks you to explain the concepts both are using. They include machine learning libraries, generative AI with its hallucinations, prompt engineering and retrieval-augmented generation, human-in-the-loop review, virtual data warehouses and robotic process automation. This lesson sets out each case, then what settles it.

The libraries the objectives name

Most machine learning on business data starts with a handful of libraries that read from databases. Introduction to Data Science (ds-intro) teaches them in depth; a database administrator needs to recognize them and know what they ask of the database.

For the database, these tools are clients like any other (Lesson 6). They need a least-privilege account and can send heavy queries that belong on a replica (Lesson 8). Their results are only as good as the classified, documented data they read (Lessons 11 and 27).

The case for AI in the pipeline

The data lead's evidence is real. Retrieval-augmented generation (RAG), introduced by Patrick Lewis and colleagues in 2020, combines a model's built-in, parametric knowledge with a non-parametric memory, in their work a dense vector index of Wikipedia searched by a neural retriever. Applied to Harbor, the assistant first retrieves the relevant policy passages or order records, often from a vector index like those in Lesson 2. It then answers from them, so its answers can be grounded in Harbor's own current data rather than in whatever the model absorbed in training.

Standards are arriving too. The Model Context Protocol (MCP), in the objectives' acronym list, describes itself as an open-source standard for connecting AI applications to external systems. One interface can then let an assistant query a database or call a tool. A drafted mapping from a model can save hours on every new supplier. And not every gain comes from LLMs. Microsoft describes robotic process automation (RPA) as automating repetitive desktop processes; a bot that copies order numbers from a portal with no API removes an hour of clerical work a day. A virtual data warehouse, or data virtualization, lets analysts query several sources in place instead of copying them first. Microsoft says PolyBase lets SQL Server query Oracle, MongoDB, object storage and other sources directly. PostgreSQL's postgres_fdw accesses data stored in external PostgreSQL servers as foreign tables.

The case for caution

The operations manager's evidence is equally real. NIST's Generative AI Profile, published in July 2024, lists confabulation among the risks of generative AI: the production of confidently stated but erroneous or false content, known colloquially as hallucinations, by which users may be misled. A mapping that silently puts discount into price, or an assistant that invents a 90-day return window, does damage precisely because it sounds right.

There are other risks. Data poisoning (Lesson 23) can steer models trained on data an attacker can write to. Sending customer records to a hosted model is a data transfer that classification, contracts and the GDPR may forbid (Lesson 21). Generated SQL and mappings are not deterministic: the same request can produce different outputs, which complicates testing and audit. Prompt engineering, writing clear instructions and context for the model, helps, but the guidance from model providers is to start with clear success criteria and ways to test against them empirically, not to trust a well-written prompt. RPA has its own fragility: a bot that clicks through screens fails when the screen changes, and needs the same monitoring as any integration (Lesson 29). And virtualization moves load to the sources: every analyst query hits the partner's live database, with its latency and its availability.

What settles it

The dispute dissolves once each use is placed by its risk, which is how Harbor resolves it.

Human-in-the-loop means a person checks, corrects or approves an automated output before it takes effect. It costs time, so it belongs where an error would be expensive and hard to see: data entering the system of record, answers given to customers, changes to schemas.

Worth holding on to: AI tools can draft, retrieve and automate, but generative output can be confidently wrong. Place them where errors are cheap or checked, ground answers in retrieved data, restrict their access, and keep a person in the loop where mistakes would be costly.

Common misconceptions

• "A well-written prompt makes an LLM reliable." Prompts help, but confabulation remains a documented risk; reliability comes from grounding, evaluation and review.
• "RAG eliminates hallucinations." It grounds answers in retrieved text, which reduces invented content, but retrieval can miss the right passage and the model can still misread it.
• "A virtual data warehouse is free." Queries run against the live sources, adding load and depending on their availability.
• "RPA is the same as an API integration." RPA imitates a person using screens; it breaks when the screens change. Use an API when one exists.

Summing up

• NumPy, pandas and scikit-learn (Python), the tidyverse (R) and MATLAB (commercial) are the data manipulation and machine learning tools the objectives name; to the database they are clients needing least privilege and capacity planning.
• Generative AI can confabulate, producing confident false content (NIST AI 600-1). Prompt engineering needs success criteria and testing.
• RAG retrieves relevant data and gives it to the model, grounding answers; MCP is an open standard for connecting AI applications to data and tools.
• Human-in-the-loop review belongs where errors would be costly and hard to spot.
• Virtual data warehouses query sources in place (PolyBase, postgres_fdw); RPA automates repetitive screen work where no API exists.

Sources

1. CompTIA. (2025). CompTIA DataSys+ DS0-002 V2 certification exam objectives (Document version 3.0), objective 6.3 and acronym list. CompTIA (PDF)
2. National Institute of Standards and Technology. (2024). Artificial intelligence risk management framework: Generative artificial intelligence profile (NIST AI 600-1). DOI 10.6028/NIST.AI.600-1. nist.gov
3. Lewis, P., Perez, E., Piktus, A., Petroni, F., Karpukhin, V., Goyal, N., Küttler, H., Lewis, M., Yih, W., Rocktäschel, T., Riedel, S., and Kiela, D. (2020). Retrieval-augmented generation for knowledge-intensive NLP tasks. arXiv:2005.11401. arxiv.org
4. NumPy Developers. (n.d.). What is NumPy?; pandas development team. (n.d.). pandas and pandas.read_sql; scikit-learn developers. (n.d.). scikit-learn; tidyverse team. (n.d.). Tidyverse. NumPy; pandas; read_sql; scikit-learn; tidyverse
5. MathWorks. (n.d.). MATLAB. mathworks.com
6. Model Context Protocol. (n.d.). What is the Model Context Protocol (MCP)? modelcontextprotocol.io; Anthropic. (n.d.). Prompt engineering overview. platform.claude.com
7. Microsoft. (n.d.). Introduction to PolyBase and Introduction to desktop flows (robotic process automation). PolyBase; desktop flows; PostgreSQL Global Development Group. (2025). postgres_fdw. In PostgreSQL 18 documentation. postgresql.org