16 — CI/CD and Deeper Databases — Automating Delivery, Modeling Data Correctly

Delivery automation and database theory sat in separate mental drawers until a single thread surfaced between them. The thread: CI/CD is the automated path a change takes from commit to production, and the database nodes are one principle repeated — model the data once, correctly, and protect it under concurrency. [1][2] Both are about reliability — one in motion (the release), one at rest (the data).

The frame that helped is that "Databases" appears twice in the roadmap for a reason. The first pass (relational databases, in an earlier post) covered the shape — tables, schema, migrations, the N+1 trap. This second pass is the guarantees — what transactions and ACID promise, how normalization removes redundancy, what ORMs trade away, where databases fail, and how I find slow queries. CI/CD sits alongside because shipping changes to a database-backed system safely requires the same discipline: automated, repeatable, reversible.

CI/CD: automating commit-to-production

CI/CD (Continuous Integration / Continuous Delivery or Deployment) automates the build, test, and deployment of code changes [1]. The pipeline runs on every commit (or every PR), and the stages are deterministic:

• Continuous Integration — every change is merged frequently and automatically built and tested. The goal is to catch integration bugs within minutes of committing, not weeks later at release time.
• Continuous Delivery — the tested change is automatically prepared for release, so deploying to production is a one-click decision.
• Continuous Deployment — the tested change is automatically deployed to production, no human gate.

The shift in mindset CI/CD demands is from "releases are scary events we do rarely" to "every commit is a potential release, and the pipeline makes that safe." Small, frequent changes are easier to test and easier to roll back than large, rare ones. When a deployment breaks something, a CI/CD setup makes "deploy the previous commit" a one-click action — reversibility is the property that makes frequent deployment survivable.

The tooling lives in the hosting platforms (GitHub Actions, GitLab CI) or standalone (Jenkins, CircleCI). The pipeline is defined as code (a YAML file in the repo), versioned with the application, and run on every push. The CI part — build and test on every PR — is now table stakes; the CD part — automated production deploys — is the discipline that requires confidence in tests.

Transactions and ACID: the guarantees

A transaction is a sequence of database operations executed as a single atomic unit — either all of them commit, or none of them do [3]. The classic example is a bank transfer: debit one account, credit another. If the debit succeeds but the credit fails, the money vanishes. Wrapping both in a transaction means either both happen or neither does.

ACID is the four-property contract a transactional database provides [4]:

• Atomicity — all-or-nothing. If any operation in the transaction fails, the whole transaction rolls back.
• Consistency — the database moves from one valid state to another. Constraints (foreign keys, checks) must hold after the transaction.
• Isolation — concurrent transactions don't interfere. Two transfers running simultaneously produce the same result as if they ran one after the other.
• Durability — once a transaction commits, it survives crashes. The data is on disk (or replicated) before the commit returns.

ACID is the reason relational databases dominate anything consistency-critical — finance, orders, inventory. The guarantees make complex multi-step writes safe. The cost is performance: strict isolation (serializable) is expensive, so most databases offer weaker isolation levels (read committed, repeatable read) that are faster but allow some concurrency anomalies. Knowing which level my database uses by default, and what anomalies that permits, is a real part of backend work.

Normalization: model the data once, remove redundancy

Normalization is the process of structuring a relational schema to reduce redundancy and improve integrity, through a series of "normal forms" (1NF, 2NF, 3NF, BCNF) [5]. The principle: every piece of data lives in exactly one place. If a customer's address is stored in both the orders table and the customers table, updating it in one place and not the other creates inconsistency — denormalization's classic bug.

Normal forms are the rules that prevent this. The practical summary:

• 1NF — each column holds atomic values (no lists in a cell).
• 2NF — no partial dependency on a composite key (every non-key column depends on the whole key).
• 3NF — no transitive dependency (non-key columns don't depend on other non-key columns).

Fully normalized schemas are clean and consistent but can require joins to reassemble. Denormalization — intentionally duplicating data for read performance — is a deliberate trade-off made when read load dwarfs write load and the consistency cost is manageable. The default is normalize; denormalize only with a measured reason.

ORMs: convenience with a cost

An ORM (Object-Relational Mapping) lets me interact with the database using objects in my language instead of raw SQL — tables map to classes, rows to objects [6]. Prisma, Hibernate, SQLAlchemy, Django ORM, TypeORM are all ORMs. The appeal is developer ergonomics: I write code in my language's idioms, the ORM generates and executes the SQL.

The benefits are real — type safety, migration generation, less boilerplate. The costs are the ones I flagged in the relational-databases post: the ORM can hide inefficient queries (the N+1 problem lives here), and complex ORM queries can be harder to optimize than the equivalent SQL. The discipline is to treat the ORM as a productivity tool, not an abstraction I trust blindly — I watch the generated SQL on slow paths, and I drop to raw SQL when the ORM's generated query is worse than what I'd write by hand.

Failure modes: where databases break

Databases fail in characteristic ways [7], and knowing them is the difference between a 3am incident and a 3am non-event:

• Hardware failure — disk dies, machine disappears. Mitigated by replication and backups.
• Data corruption — bit rot, torn writes. Mitigated by checksums and transaction logs.
• Replication lag — a read replica falls behind the primary; readers see stale data. A common cause of "I updated it but it still shows the old value."
• Deadlocks — two transactions each hold a lock the other needs; both stall until one is aborted. Mitigated by consistent lock ordering and short transactions.
• Performance degradation — a slow query, a missing index, an N+1. Mitigated by profiling and monitoring.

The unifying discipline: backups I've tested restoring, replication I understand the consistency model of, and monitoring that catches slow queries before users do.

Profiling: finding the slow queries

Performance profiling is how I find the queries that are slow and why [8]. The tools vary by database (Postgres's EXPLAIN ANALYZE, the slow query log, APM tools like DataDog), but the method is constant: measure where time is spent, then attack the biggest cost.

The common findings:

• A query doing a full table scan because of a missing index.
• An N+1 pattern (always).
• A join that grew the working set unexpectedly.
• Lock contention from a long-running transaction.

EXPLAIN ANALYZE is the single tool I reach for first — it shows the query plan the database chose and the actual time each step took. Reading query plans is a learnable skill, and it's the difference between guessing why a query is slow and knowing.

How I use this

The delivery and data halves each have a discipline:

• CI/CD — every PR runs build + test automatically; production deploys are one-click and reversible. If I can't roll back in one step, I'm not done.
• Transactions — any multi-step write that must be atomic is wrapped in a transaction. Period. No "it usually works" multi-step writes.
• Normalization — start fully normalized; denormalize only with measured read-load justification.
• ORMs — use them for productivity, watch their generated SQL, drop to raw SQL when needed.
• Failure modes — tested backups, understood replication, and slow-query monitoring.
• Profiling — EXPLAIN ANALYZE on any slow path before I guess at the cause.

The thread connecting them — automate the release, model the data once, protect it under concurrency, measure before optimizing — is the reliability spine of backend work. The roadmap lists these as separate nodes because they're separate skills, but they serve one goal: changes ship safely, and the data stays correct while they do.

References

[1] GitLab, "What is CI/CD?," 2024. [Online]. Available: https://about.gitlab.com/topics/ci-cd/

[2] Oracle, "What is a Database?." [Online]. Available: https://www.oracle.com/database/what-is-database/

[3] "SQL Server Transactions Tutorial," sqlservertutorial.net. [Online]. Available: https://www.sqlservertutorial.net/sql-server-basics/sql-server-transaction/

[4] "What is an ACID Compliant Database?," Retool. [Online]. Available: https://retool.com/blog/whats-an-acid-compliant-database/

[5] "What is Normalization in DBMS (SQL)? 1NF, 2NF, 3NF, BCNF," guru99. [Online]. Available: https://www.guru99.com/database-normalization.html

[6] "What is an ORM, how does it work, and how should I use one?," Stack Overflow. [Online]. Available: https://stackoverflow.com/a/1279678

[7] roadmap.sh, "Database Failure Modes." [Online]. Available: https://roadmap.sh/ai/course/database-failure-modes-prevention-and-recovery

[8] "How to Profile SQL Queries for Better Performance," Servebolt. [Online]. Available: https://servebolt.com/articles/profiling-sql-queries/