Managed databases
The provider does the chores; you keep the tables and queries
A managed database leaves you the tables, queries and access, and gives the provider patching, backups and failover.
Every application keeps its data in a database, and a database is hard work to look after. Install one on a virtual machine and nearly all of that work is yours: the operating system, the installation, patches, backups, and a standby in case it fails. A managed database service takes six of those jobs away. You are left with the three that are really about your business: what the tables are, what you ask of them, and who may connect. This is the same shared responsibility idea from lesson two, applied to the thing most painful to run yourself.
Managed databases · Going deeper · 1 of 2
Staying up: a standby in another zone, and read replicas
A standby in a second zone receives every write and takes over in a minute or two. Read replicas add capacity for reading.
Managed databases · Going deeper · 2 of 2
Point-in-time restore: undo a mistake to the minute
Nightly backups plus a continuous log of changes let a managed database be rebuilt as it was at any minute in the last 35 days.
Relational databases
Tables that refer to each other
A relational database stores each fact once in tables and uses SQL to join them into answers.
The oldest and most common kind is the relational database. Data lives in tables. Here are customers, one row each. And here are orders. An order does not repeat the customer’s name and city. It holds the customer’s number, which points at one row in the other table. You ask questions in a language called SQL. This one says: join each order to its customer and add up the totals by name. The database works out how, and answers. Ben, forty-nine ninety-nine.
Relational databases · Going deeper · 1 of 2
Transactions: all of it, or none of it
A transaction makes a group of changes atomic. The guarantee is called ACID.
Relational databases · Going deeper · 2 of 2
Relational services: three tiers on each provider
RDS, Azure SQL Database and Cloud SQL run standard engines. Aurora and AlloyDB are rebuilt for the cloud. Spanner spans regions.
NoSQL databases
Find anything by its key, at any size
A NoSQL database looks items up by key and spreads them over many servers, trading joins for unlimited scale.
Relational databases struggle when a table grows past what one big server can hold. NoSQL databases were built for that. A table is just keys and items. The items need not match: one cart has a coupon, another has a gift note. The key decides which server stores each item, so the table is spread evenly across several. Asking for one user’s cart goes straight to the right server, in a few milliseconds. And when the table grows, the service adds servers. There is no ceiling. What you give up is the join. You can fetch by key, quickly, forever. You cannot ask an unplanned question across the whole table.
NoSQL databases · Going deeper · 1 of 2
The NoSQL family: key-value, document, wide-column, graph
NoSQL databases are grouped by the shape of data: key-value, document, wide-column, graph and in-memory.
NoSQL databases · Going deeper · 2 of 2
Consistency: instant, or guaranteed current
Eventual consistency answers at once from the nearest copy. Strong consistency waits until all copies agree.
Data warehouses
Built for questions about everything
A data warehouse stores data by column to answer large analytical questions; it is separate from the operational database.
So far the databases have served the application: fetch this order, save that cart. A different need appears once the data piles up. Someone wants to know how sales moved over three years. A transaction database stores rows, and it is superb at reading one. Thousands of small questions a second. A data warehouse stores the same data by column. A question like total sales by month reads one column across a billion rows, and skips everything else. The two are kept apart on purpose. Data flows from the first into the second, so an analyst’s heavy question never slows a customer down.
Data warehouses · Going deeper
From raw data to a dashboard: the analytics pipeline
Data is collected and cleaned (ETL), kept raw in a data lake, shaped in a warehouse and shown on dashboards.
Caches
Keep the answer you already worked out
A cache holds recent answers in memory so most requests skip the database.
Most applications ask the same questions again and again. The home page. The best-selling product. So the application first asks a cache: a store that keeps answers in memory and replies in about a millisecond. Only if the answer is not there does it go to the database, which takes twenty times as long, and then it saves the answer for next time. With no cache, every request costs twenty milliseconds. If the cache can answer nine in ten, the average drops to three, and the database does a tenth of the work it did.
Caches · Going deeper
What to cache, and for how long
A cache trades freshness for speed. The skill is in choosing what may be a little old, and for how long.
A cache trades freshness for speed. The skill is in choosing what may be a little old, and for how long. Good things to cache. Sessions who is logged in, kept out of the database so any server can handle any request. Pages and fragments a product page that a million people see and that changes once a day. Results of expensive queries a leaderboard or a count that takes seconds to compute. Keeping it fresh. A cache is not a place to keep the only copy of anything. It may be emptied at any time, and the application must work, more slowly, without it. The services. Amazon ElastiCache runs Valkey, Redis OSS or Memcached. Azure Cache for Redis managed Redis on Azure. Google Memorystore managed Redis, Valkey and Memcached.
Good things to cache
- Sessions who is logged in, kept out of the database so any server can handle any request.
- Pages and fragments a product page that a million people see and that changes once a day.
- Results of expensive queries a leaderboard or a count that takes seconds to compute.
Keeping it fresh
The services
- Amazon ElastiCache runs Valkey, Redis OSS or Memcached.
- Azure Cache for Redis managed Redis on Azure.
- Google Memorystore managed Redis, Valkey and Memcached.
Queues
Hand work over without waiting for it
A queue holds messages between two parts of a system, so a rush becomes a backlog instead of a failure.
The last data service does not store data for long. It passes it along. In a sale, the web servers take a thousand orders a second. They do not process them. They place each one on a queue and reply to the customer at once. Behind the queue, workers take orders off at the pace they can manage: two hundred a second. A one-minute rush leaves forty-eight thousand orders waiting, and the workers clear them in four minutes. Nothing was refused and nothing was lost. Without the queue, the workers would have been buried.
Queues · Going deeper
Queues, topics and streams
A queue delivers to one worker, a topic to every subscriber, and a stream keeps an ordered record for replay.
Choosing a data service
One application, six kinds of data
Match each kind of data to the service built for it: relational, key-value, cache, queue, warehouse or object storage.
Real systems do not pick one of these. They use several, each for the data that suits it. Take an online shop. Orders and payments must never be half done, so they go in a relational database. Carts and sessions are fetched by one key, by millions of people: a key-value database. Product pages are read constantly and rarely change: a cache. Emails and packing jobs can wait a moment but must not be lost: a queue. Three years of sales, for the analysts: a warehouse. And photos and invoices are whole files: object storage, from lesson five.
Choosing a data service · Going deeper
Moving an existing database to the cloud
Each provider has a migration service that copies a running database across and keeps it in step until you switch over.
Each provider has a migration service that copies a running database across and keeps it in step until you switch over. Two kinds of move. Same engine. PostgreSQL to managed PostgreSQL, SQL Server to Azure SQL. The data is copied as it is. This is the quick, low-risk path. Different engine. Oracle to PostgreSQL, for example, usually to stop paying licence fees. The tables and stored code must be converted first, which is real work. How the switch is made with almost no downtime. 1 Copy the migration service takes a full copy of the source database while it stays in use. 2 Follow it then applies every new change as it happens, so the two stay in step. 3 Switch when you are ready, stop writes for a moment, point the application at the new database and carry on. The services. AWS Database Migration Service with the Schema Conversion Tool for changes of engine. Azure Database Migration Service chiefly for SQL Server, PostgreSQL and MySQL into Azure. Google Database Migration Service into Cloud SQL and AlloyDB.
Two kinds of move
Same engine
PostgreSQL to managed PostgreSQL, SQL Server to Azure SQL. The data is copied as it is. This is the quick, low-risk path.
Different engine
Oracle to PostgreSQL, for example, usually to stop paying licence fees. The tables and stored code must be converted first, which is real work.
How the switch is made with almost no downtime
- 1 Copy the migration service takes a full copy of the source database while it stays in use.
- 2 Follow it then applies every new change as it happens, so the two stay in step.
- 3 Switch when you are ready, stop writes for a moment, point the application at the new database and carry on.
The services
- AWS Database Migration Service with the Schema Conversion Tool for changes of engine.
- Azure Database Migration Service chiefly for SQL Server, PostgreSQL and MySQL into Azure.
- Google Database Migration Service into Cloud SQL and AlloyDB.
Lesson complete
You finished “Databases and data services”
Nicely done.