In 09-01 we looked at what to read and in 09-02 at where to study and practice. The third vertex is missing, and it is the one that turns the rest into real work: what with. This lesson is the inventory of the tools you actually work with on databases — engines on your own machine, clients, design, migrations, test data, diagnostics and backups — and, above all, the judgment about which ones you need now and which ones you do not need yet.

Because the dominant mistake in this field is not using too few tools, it is accumulating them. It is easy to end up with three GUI clients installed, two diagramming tools, a migration tool that never gets used and no backup configured at all. The final section proposes a minimum toolkit of five pieces you can start with tomorrow, and an explicit list of what you can ignore without remorse.

Everything here is aimed at letting you do what the last lesson of module 8 asked of you: take one of the VallBici cases or the BiblioRed schema, set it up on your machine and break it.

Important warning. The versions of engines and tools move on constantly; command-line options change; and the licenses and business models of tools change too — there are projects that have gone from free to commercial and back again. The commands in this lesson are correct in their general form, but always check the syntax and the package names in the documentation for your version. And the rule that governs everything else: the official documentation overrides whatever any course says, this one included. No prices and no free-tier conditions are quoted either, because they change without notice: check at the official source before signing up for anything.

1. Engines on your machine: installation and startup
2. Docker and Docker Compose: the 08-03 environment in one command
3. Managed databases in the cloud
4. Console clients: psql and mongosh
5. GUI clients
6. Design and diagrams
7. Migrations and schema version control
8. Test data
9. Performance and diagnostics
10. Backups, monitoring and administration
11. The minimum toolkit
12. Common Mistakes and Tips
13. Exercises
14. Conclusion: closing the course

Having the engine installed locally is not negotiable. You can learn a lot against a managed database, but you cannot shut it down halfway through a transaction to see what happens, and that kind of experiment is exactly what you are missing.

After installing on Linux there is a system user postgres, who is the engine's superuser. The sensible first step is to create your own role and your own database, instead of always working as the superuser — exactly what was argued in 06-04:

The postgresql-contrib package deserves a note: it brings extensions you are going to want, among them pg_stat_statements (section 9), pg_trgm (similarity search, the one compared with Elasticsearch in 08-03) and btree_gist (needed for exclusion constraints over ranges).

SQLite is the most underrated tool in the inventory. For trying out a schema idea, for analyzing a CSV with SQL or for carrying sample data in a repository, there is nothing faster. Remember what 01-02 said: its typing is dynamic and its concurrency model is single-writer, so it is not a substitute for PostgreSQL in an application with several users writing at the same time.

For MongoDB and Redis, and even more so for Elasticsearch, the advisable route on a work machine is not installing them as system services but bringing them up in containers. That is what comes next.

Installing four engines as system services means four services always starting up, four configurations to maintain and a painful uninstall the day you want to clean up. With containers, the whole environment is a text file you can version, share and destroy without a trace.

And this is the file that brings up the environment of the polyglot case study in 08-03: PostgreSQL as the transactional source of truth, MongoDB for telemetry and profiles, and Redis for real-time availability.

Four details of the file deserve attention, because they are what separates a toy compose file from a useful one:

1. Pinned version (postgres:16, not postgres:latest). With latest, one day you update the image by accident and the data format is no longer compatible.
2. Named volumes. Without them, docker compose down takes your data with it. With them, it survives until you explicitly ask for -v.
3. docker-entrypoint-initdb.d. Any .sql you leave in ./sql runs when the database is created for the first time. It is the natural home for the BiblioRed or VallBici schema: cloning the repository and running docker compose up -d leaves you with the schema created.
4. healthcheck. It lets your application — or a migrations container — wait until PostgreSQL is genuinely accepting connections, not merely started.

If you also want Elasticsearch to reproduce the station search, add it as one more service; bear in mind that it consumes considerably more memory than the other three together and usually needs memory tuning on the host machine.

A managed database is the same engine, operated by somebody else: backups, upgrades, high availability and monitoring come included. You do not need them in order to learn; for publishing a project of your own they are very convenient.

About free tiers. Several of these offerings have a free tier or initial credit, and they are perfectly suitable for a published learning project. But the conditions — storage limits, pauses for inactivity, expiry — change frequently, so check them on the provider's official website at the moment you are going to use them. And two practical cautions: first, always turn on spending alerts before creating anything, because pay-per-use billing can surprise you; second, a managed database does not exempt you from knowing how to administer: if you do not understand what a VACUUM is or what the isolation level implies, the pretty dashboard is not going to save you.

We start with the console clients and not the graphical ones, deliberately. psql is not the basic option: it is the most powerful tool of all the ones in this lesson. Everything that exists in a GUI client exists in psql, and quite a lot of what is in psql exists in no GUI client. Besides, it is always available: on the server, inside the container, over SSH, in a script.

They pick up what 01-04 called the system catalog: each one of these metacommands is, in reality, a query against the catalog written by you without realizing it.

Two habits worth a lot that cost little: turn on \timing always, so every query tells you how long it takes; and use \watch to observe a counter live while another session works — it is the most direct way to see the concurrency phenomena of 06-02 with no additional tooling.

A ~/.psqlrc file with your preferences does the rest:

That \pset null '(null)' is more useful than it looks: by default a NULL and an empty string look exactly the same in the output, and that confusion has cost a lot of people a lot of debugging hours.

mongosh is a complete JavaScript interpreter, not just a client. You can write loops and functions and load files with load('script.js'), which makes it the natural tool for generating test data or for one-off document migrations.

A GUI client gives you three real things: browsing an unfamiliar schema much faster, seeing results in a comfortable grid, and generating diagrams by reverse engineering. It does not give you — and it is best not to fool yourself — any capability the console does not have.

An honest recommendation. Install one. If you touch several engines, DBeaver. If only PostgreSQL and you do administration, pgAdmin. If you work with MongoDB, Compass on top of the previous one because it does things no other tool does. Having three relational clients installed is an almost infallible sign that none of them has been learned properly.

And a warning that recurs over time: tool licenses change. Projects that started out free have moved to commercial models and some the other way round. Check the current license before resting a team workflow on a particular tool.

In 04-02 you drew entity-relationship diagrams and in 04-03 you transformed them into schemas. These are the tools with which that is done outside a course.

The criterion. For a diagram that has to live over time — in the repository, reviewed on every change — use Mermaid: it is text, it gets versioned and it renders in the README. For an afternoon's sketch, dbdiagram.io. To understand somebody else's sixty-table schema, reverse engineering with DBeaver or SchemaSpy, and you decide from there.

This is the Mermaid erDiagram you can start documenting your own schema with; it is exactly the format you have seen throughout the course:

Save that block in your project's README.md and update it in the same commit in which you change the schema. It is the literal application of "document and version the schema" from 04-01, and it costs two minutes.

This section is the most important of the lesson, and the one most people skip.

In 04-01 it was said that the schema has to be documented and versioned. A migration is the professional way of doing it: every schema change is a file, numbered and kept in the repository next to the code, applied in order and recorded by the database. The consequence is that the schema stops being the result of a series of stray ALTER TABLEs in somebody's console and becomes a reproducible sequence.

Why this is not optional. Four reasons, all verifiable on your first day working in a team:

1. Reproducibility. Anyone clones the repository, runs the migrations and obtains exactly your schema. Without migrations, "setting up the environment" means asking a colleague.
2. The schema and the code travel together. The commit that adds the return_date column also contains the code that uses it. Going back one version reverts both things.
3. Repeatability across environments. What was applied in development is applied identically in staging and in production, without anyone typing anything by hand under pressure.
4. History and auditing. "When was this index added and why?" has an answer: the commit, its date and its message.

How to choose. If your framework already ships migrations, use those and that is that. If you use no framework, or you want explicit, controlled SQL, Flyway or an equivalent lightweight tool. Liquibase pays off in environments with several different engines and a need for formal rollback.

A minimal example with the numbered SQL file format, applied to BiblioRed:

Three golden rules about migrations, all learned the hard way:

1. An applied migration is never modified. If it was wrong, it gets corrected with a new migration. Changing an already applied file breaks the tool's checksum and desynchronizes the environments.
2. Every migration must be runnable against real data. Adding a NOT NULL column with no default to a table with two million rows fails; it has to be done in steps.
3. CREATE INDEX CONCURRENTLY in production. A normal CREATE INDEX blocks writes on the table for as long as it lasts. It is the perfect example of how what you learned in 06-02 about locking translates into a concrete operational decision.

A schema with twelve rows teaches you nothing. The query planner will do a sequential scan every time, any index will look useless and no concurrency problem will show up. To learn about performance you need volume, and for that there are three routes.

It is the fastest route and it requires no external tooling. It generates half a million plausible loans for BiblioRed:

That final ANALYZE is not decoration. It is the number one cause of "I created the index and it still is not being used" in home-made tests: the planner from 06-03 decides using statistics, and after a bulk load the statistics are stale.

A warning about realism: random() produces a uniform distribution, and real data is never uniform. In BiblioRed a few titles account for the majority of loans, and that skew is exactly what makes indexes and plans interesting. If you want realistic tests, skew the distribution on purpose.

pgbench deserves a separate mention because it does something the others do not: concurrent load. With pgbench -c 20 -T 60 you have twenty sessions writing at the same time for a minute, which is the way to really see what you saw in 06-02 with two terminals.

Sometimes you do not want to set anything up: you want to practice queries against a non-trivial schema that already exists.

You find them by searching for their name; they are usually distributed as a SQL file loaded with psql -f. With Chinook on SQLite you have query practice in under a minute and without installing any server.

A direct continuation of 06-03, where you read your first execution plan.

pg_stat_statements deserves its own profile because it changes the way you work. Without it, you optimize the query somebody has complained is slow. With it, you optimize the one consuming the most total time, which is often a fast query run a hundred thousand times a day that nobody complains about.

That second query is the one you will run the day the application "has hung". Almost always the answer is right there: a transaction open for twenty minutes that nobody has committed, blocking everyone else. It is 06-02 in real life.

A continuation of 06-04. There is only one rule here that matters: a backup that has never been restored is not a backup, it is a hope.

The minimum, which you can have running this afternoon:

Automating it is a couple more lines — a scheduled task that runs the dump, compresses it and copies it somewhere else — but the part people omit is not the automation, it is the test restore. Put a quarterly restore test in the calendar. It is the only way to know the backup exists.

Everything above is the full inventory. This is what you actually need to start tomorrow.

And this is what you do not need yet, said explicitly so you do not waste time:

The tip that sums up the section: do not accumulate tools before you need them. Every installed tool has a maintenance, upgrade and attention cost. A tool is adopted when you have a problem that hurts and it solves it, not when you see it recommended in a list, this one included.

Mistake 1: not having a local engine. Working only against a shared or managed database means not being able to experiment: you cannot kill the process, fill the disk, provoke a deadlock or restore a backup over it. Everything interesting in this course requires a database you can destroy.

Mistake 2: using latest in container images. One day you update and the data format is no longer compatible with the previous version. Always pin the major version.

Mistake 3: changing the schema by hand in the console. The ALTER TABLE you typed straight into production is nowhere: not in the repository, not in your colleague's environment, and not in your memory three weeks from now. Every schema change, a migration.

Mistake 4: testing performance with two hundred rows. At that volume the sequential scan always wins and you will learn nothing about indexes. Generate hundreds of thousands of rows and run ANALYZE.

Mistake 5: forgetting ANALYZE after a bulk load. It is the most frequent cause of "the index is created but it is not being used". The planner decides using statistics, and after inserting half a million rows they are stale.

Mistake 6: trusting a backup that has not been restored. Restore onto a new database and count the rows. Quarterly. No exceptions.

Mistake 7: leaving transactions open in the GUI client. Several clients work in manual transaction mode: you open a tab, run an UPDATE, go for lunch, and the lock sits there stopping everyone. Check which mode your client is in and look at pg_stat_activity when something gets stuck.

Mistake 8: installing four engines and using none. The enthusiasm of module 3 leads to installing Cassandra and Neo4j "to try them out". Install them when you are going to do something specific with them, and delete them if within a month you have not.

Tip 1: version everything around the database. The docker-compose.yml, the migrations, the README with the Mermaid erDiagram, the test data scripts and the backup script. Making "clone the repository and run two commands" leave a working environment is an achievable and very profitable goal.

Tip 2: learn psql before any GUI client. The graphical one is learned in twenty minutes once you know what you are doing; the other way round, the graphical one hides what is happening and slows the learning down.

Tip 3: always keep a diagnostic query file to hand. The ones for pg_stat_activity, pg_stat_statements, table and index sizes and index usage. The day of the incident is not the moment to write them.

Tip 4: and once again, the official documentation rules. Versions move on, options get renamed and defaults get revised from one version to the next. Faced with any doubt between what this lesson says and what your version's manual says, the manual is right.

These exercises are about real practice: they are solved with the machine switched on, not from memory. There is no single correct answer; the solutions are reasoned model answers and your version may be different and better.

Use Docker Compose to bring up the 08-03 environment (PostgreSQL + MongoDB + Redis), load the VallBici or BiblioRed schema into PostgreSQL and then:

1. Check that the three engines answer from their console client.
2. Insert at least 300,000 rows into the trips or loans table with generate_series, and run ANALYZE.
3. Break it on purpose: stop the Redis container with the application running and document what stops working and what keeps working. Then run docker compose down (without -v) and bring it up again: check that the data is still there.
4. Run docker compose down -v and bring it up again: check that it is not there, and explain why.
5. Leave the result in a repository with docker-compose.yml, the initialization .sql files and a README that lets somebody else reproduce it in under ten minutes.

Take the BiblioRed schema as you left it in module 5 and turn it into a sequence of migrations:

1. Choose a tool and justify the choice in two lines.
2. Write V1 with the base schema and, at minimum, three later migrations representing real changes: a new index justified by an EXPLAIN, an integrity constraint that today lives in the application, and a new column on a table that already has data.
3. The new-column migration must be runnable against the 300,000 rows from exercise 1 without leaving the table locked for an unacceptable amount of time. Explain how you achieve that.
4. Document in each file why the change is made, not just what is done.
5. Check the essential thing: drop the database, run the migrations from scratch and verify that you obtain the same schema.

Without installing anything new yet, write out your personal toolkit for the next six months. For each piece:

1. Which tool you choose and for which specific task.
2. Against which alternative you chose it and why — a real reason, not "it is the most popular".
3. Which tool from the lesson you explicitly rule out and under what condition you would reconsider it.
4. Add an acid test: describe the specific task with which you will verify in a month that the choice was right.

Model answers, not the only correct ones. What is assessed is the reasoning and that the machine really does what you say.

Solution 1 (model answer)

Points 1 and 2. Checking the three engines and loading volume:

The load is done with the generate_series from section 8, always followed by ANALYZE. Verification: EXPLAIN (ANALYZE) of a query filtered by member should go from a sequential scan to an index scan after creating the index and running ANALYZE; if nothing changes, the ANALYZE is almost certainly missing.

Point 3 — breaking it. With docker compose stop redis:

That table is the empirical check of the thesis of 08-03: "that Redis can go down without preventing a single charge is the proof that the split is well made". If stopping Redis made charging impossible, the split would be wrong and would need revisiting.

Points 4 and 5. docker compose down stops the containers but keeps the named volumes (pgdata, mongodata, redisdata), so when you bring it back up the data is there. down -v removes those volumes and, with them, the data; furthermore, when the PostgreSQL volume is recreated empty, the docker-entrypoint-initdb.d scripts run again, since they only run when the data directory is uninitialized. Understanding that asymmetry is precisely the point of the exercise: the container is disposable, the volume is not.

Solution 2 (model answer)

1. Choice: numbered SQL files with Flyway or an equivalent lightweight tool. Reason: BiblioRed uses no framework with migrations of its own, the schema is thought out in SQL and I want what gets applied to be exactly what I read, with no intermediate abstraction layer.

2 and 3. The delicate migration — adding a NOT NULL column to a table with 300,000 rows — is done in three steps, not one:

The index justified by EXPLAIN is the V3 from section 7, with CONCURRENTLY and with a reasoned column order. The constraint that moves up from the application to the engine is the V4 of the non-overlapping room reservations.

4 and 5. The header comment of each file explains the why — the ticket, the execution plan that motivated it, the course lesson that grounds it — because a year from now the "what" is read in the SQL and the "why" is nowhere. The final verification is what gives the whole exercise its point: dropdb bibliored && createdb bibliored, apply the migrations from scratch and compare the resulting schema (pg_dump --schema-only) with the original database's. If they do not match, there is some change that was made by hand and is in no migration: exactly the problem this section exists to solve.

Solution 3 (model answer)

Explicit exclusions. Elasticsearch and Neo4j: I do not install them until I have a typo-tolerant search or graph traversal requirement that PostgreSQL with pg_trgm or recursive queries does not cover. pgBackRest: not until I administer a database somebody other than me depends on; in the meantime, automated pg_dump restored quarterly. A paid commercial client: not until DBeaver genuinely gets in my way.

Let us start small and end big.

The small part: of the whole inventory in this lesson, six pieces are enough — local PostgreSQL, psql, Docker Compose, one GUI client, one migration tool and EXPLAIN with pg_stat_statements. With those you can design, measure, version and recover, which is everything you need in order to work well. Add tools when a real problem calls for them, and not before. And remember the two warnings that govern the whole of module 9: versions, licenses, prices and free tiers change without notice, and the official documentation overrides any course, this one included.

And now the big part, because this lesson closes the entire course.

It has been nine modules and thirty-six lessons. It is worth looking back at the full route, because from the inside you do not always see it.

You started in 01-01 with a question that seemed naive: what is a database and why is a spreadsheet not enough. The answer took up the whole of module 1 — types of databases, fifty years of history from hierarchical systems to today, and the inside architecture of a database engine. In module 2 you learned the relational model and SQL for real: not just SELECT, but joins across several tables, aggregation, correlated subqueries and referential integrity, with the idea that holds it all together: if the rule is about integrity, it lives in the database. Module 3 pulled you out of there on purpose to teach you NoSQL, its four families and its modeling, and above all so you would see the relational model from the outside and understand that it is a choice and not a law of nature.

Modules 4 and 5 were the ones about the silent craft: designing. Entity-relationship diagrams, transformation into schemas, data types and constraints, functional dependencies, normal forms up to Boyce-Codd, and — the part many courses do not tell you — denormalizing on purpose and knowing how to justify why. Module 6 put the system under pressure: transactions and ACID, two terminals open watching a lost update with your own eyes, execution plans read line by line, permissions and backups. Module 7 was four lessons of exercises without a safety net. And module 8 made you build from scratch: BiblioRed in relational, VallBici in document form, and finally four engines sharing a municipal service with a table declaring, field by field, who rules over what.

What you can do now. You can sit down in front of a domain you do not know, ask the right questions, draw the model, transform it into tables with their types and their constraints, normalize it, decide where breaking the normalization is worthwhile and defend it. You can write multi-table queries with aggregation without looking up the syntax. You can read an execution plan, decide on an index and justify its column order. You can reason about what isolation level an operation needs and which anomaly you are accepting. You can decide whether a problem calls for a document, a key-value pair, a graph or a table, and — more importantly — when it does not. And you can set up the environment, version it and recover it.

That is not a small thing. It is, quite precisely, what is expected of someone who works with data with judgment.

What is missing, said honestly: experience. No lesson gives what having had a database in production with people depending on it gives. Nobody fully understands why backups get tested until they have needed one; nobody internalizes isolation levels until they have seen two duplicate charges on a Tuesday morning. That comes with time, and all this course has done — which is already a lot — is prepare you to recognize it when it happens, instead of suffering it without understanding it.

So the final invitation is the same one that closed 08-03, and now you have all the tools to accept it: choose one of the cases — the relational BiblioRed of modules 4 and 5, the document-based VallBici of 08-02, or the polyglot one of 08-03 — set it up on your machine with the docker-compose.yml from this lesson, load it with half a million rows, and break it. Kill the Redis container and see what keeps working. Open two terminals and provoke a deadlock. Drop the whole database and restore it from your backup. Create an index that is good for nothing and find out with EXPLAIN why it is good for nothing. Each of those things will teach you more than rereading the corresponding lesson, because the knowledge that sticks is the one you pay for with a mistake of your own.

Thank you for making it this far. Thirty-six lessons are a lot, and finishing a complete course — not abandoning it in module 3, which is what happens to most people with most courses — says something real about how you work. Databases are one of the few areas of computing where what you learn ages slowly: Codd's paper is more than fifty years old and it is still the basis of what you have studied, and the decisions you now know how to make will keep serving you when the languages, the frameworks and the fashions change.

There is no next lesson now. There is an empty database waiting for you to decide what goes inside. Good luck with it.