Managed databases

The provider does the chores; you keep the tables and queries

A database you install on a virtual machineTables and queriesWho may connectChoosing a sizeBackups and restoresFailover to a standbyPatching the databaseInstalling the databaseOperating systemHardware and buildingA managed database serviceTables and queriesWho may connectChoosing a sizeBackups and restoresFailover to a standbyPatching the databaseInstalling the databaseOperating systemHardware and buildingyour workthe provider’sA managed database hands the provider the six jobs that keep database administrators awake.You still design the tables, write the queries and decide who connects. Installing, patching, backing up and failingover are done for you, on a schedule you set. It costs more per hour than a bare virtual machine and far less thanthe people it replaces. Amazon RDS, Azure SQL Database and Google Cloud SQL are the main ones.

A managed database leaves you the tables, queries and access, and gives the provider patching, backups and failover.

Managed databases · Going deeper · 1 of 2

Staying up: a standby in another zone, and read replicas

Applicationone address to connect toZone aPrimarytakes every writereads and writesZone bStandbyan exact copy;takes over in 1 to 2 minutesevery write, at onceZone cRead replicaa moment behind;answers reports and searchescopied a moment laterA standby in another zone keeps the database up. Read replicas share the work of reading.With the standby option on (Multi-AZ on RDS, zone redundancy on Azure SQL, high availability on Cloud SQL) a write isconfirmed only when both copies have it. If the primary fails, the address moves to the standby, typically in 60 to 120seconds, and no data is lost. Replicas are for scale: send heavy reads to them and keep the primary for writes.

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

MonTueWedThuFriSatSuna full backup every nightand every change in between, logged as it happensThursday 14:32a bad update deletes 10,000 ordersrestore to Thursday 14:31a new database, exactly as it was one minute earlierPoint-in-time restore: the nightly backup plus the log of changes rebuilds the database at any chosen minute.Managed databases do both automatically and keep them for a period you choose: up to 35 days on Amazon RDS andAzure SQL Database. The restore creates a new database beside the damaged one, so you can copy back what waslost. A standby does not help here: it faithfully copies the mistake. Only a backup reaches back in time.

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

customersidnamecity1AshaPune2BenLeeds3ChenPerthordersidcustomer_idtotal101240.00102115.5010329.99customer_id 2 points at BenSELECT name, SUM(total) FROM orders JOIN customers ON customers.id = customer_id GROUP BY nameanswer: Ben 49.99 · Asha 15.50A relational database keeps data in tables that refer to each other, and answers questions across them.Each fact is stored once: Ben’s city lives in one row, however many orders he has. The query language, SQL, joins tableson the fly, and the database enforces rules, such as “an order must belong to a real customer”. It is the defaultchoice for business data: orders, accounts, bookings. PostgreSQL, MySQL and SQL Server are the common engines.

A relational database stores each fact once in tables and uses SQL to join them into answers.

Relational databases · Going deeper · 1 of 2

Transactions: all of it, or none of it

Move $100 from account A to account B. The server crashes halfway.Without a transactiontwo separate updates$500account A−100$400account Acrash ✗$200account B$100 has vanished:A lost it, B never got itInside a transactionboth updates, or neither$500account A−100$400account Acrash ✗$500account Aundone on restart:A has $500 again, B $200A transaction is a group of changes that happen completely or not at all.This guarantee has a four-letter name, ACID. Atomic: all or nothing. Consistent: the rules still hold afterwards.Isolated: two transactions at once do not see each other’s half-finished work. Durable: once confirmed, it survivesa crash. Relational databases are built around it, which is why money and stock counts live in them.

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

AWSAzureGoogle CloudStandard engines, managedAmazon RDSAzure SQL DatabaseCloud SQLsix enginesand Azure Database forPostgreSQL, MySQL,PostgreSQL and MySQLSQL ServerRebuilt for the cloudAmazon AuroraAzure SQL HyperscaleAlloyDBOne database, many regionsAurora Global DatabaseAzure SQL geo-replicationSpannerThree tiers of relational service: a familiar engine run for you, a faster cloud-built one, and a global one.RDS runs PostgreSQL, MySQL, MariaDB, SQL Server, Oracle and Db2 unchanged, so existing applications move over asthey are. Aurora and AlloyDB keep the PostgreSQL or MySQL interface on storage redesigned for the cloud: copiesacross three zones, faster recovery, storage that grows by itself. Spanner spreads one relational database acrosscontinents and keeps transactions correct everywhere.

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

Table: cartsno fixed columnskeyitem: any shapeuser#41{ items: 2, coupon: "SPRING" }user#42{ items: 5 }user#43{ items: 1, gift: true, note: "…" }server 1: a third of the keysserver 2: a third of the keysserver 3: a third of the keysthe key alone decides which server holds an itemneed more? add servers: there is no ceilingget("user#42") goes straight to server 2: under 10 ms,with a thousand items or a trillionA NoSQL database gives up joins to gain size: look an item up by its key, at any scale.Items need not share columns, and the table is spread over as many servers as it needs, automatically. It suitssimple, enormous, fast workloads: carts, sessions, game state, device readings. It does not suit questions youhave not planned for. Amazon DynamoDB, Azure Cosmos DB and Google Firestore and Bigtable are the main ones.

A NoSQL database looks items up by key and spreads them over many servers, trading joins for unlimited scale.

NoSQL databases · Going deeper · 1 of 2

The NoSQL family: key-value, document, wide-column, graph

KindShape of the dataTypical useServicesKey-valueone key, one itemcarts, sessions, user settingsDynamoDB · Cosmos DBDocumentJSON documents, searched by fieldproduct catalogues, contentDocumentDB · Cosmos DB · FirestoreWide-columnbillions of rows, columns that varysensor readings, historiesKeyspaces · Cosmos DB · BigtableGraphthings and the links between themsocial networks, fraud ringsNeptune · Cosmos DBIn-memorykeys held in memory, not on diskcaches, leaderboards, countersElastiCache · Memorystore“NoSQL” is a family name. Its members are told apart by the shape of the data they hold.Azure Cosmos DB appears in four rows because it is one service with several interfaces: document, key-value,wide-column (Cassandra) and graph (Gremlin). DynamoDB also stores documents inside its items. On an exam, matchthe words: “key-value” or “single-digit millisecond” means DynamoDB; “relationships” means a graph database.

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 consistencyCopy in Irelandprice = 12Copy in Tokyoprice = 10on its wayreads 10, at oncea moment out of dateprice changedfrom 10 to 12Strong consistencyCopy in Irelandprice = 12Copy in Tokyoprice = 12on its wayreads 12, after a waitalways the latestprice changedfrom 10 to 12When data is copied across places, a read can be instant or guaranteed current, and you choose which.Eventual consistency answers from the nearest copy without waiting; all copies agree within a second or so. Strongconsistency waits until the copies agree. Ask what a stale answer would cost: a like count can lag, a bank balancecannot. DynamoDB lets each read choose. Cosmos DB offers five levels between the two.

Eventual consistency answers at once from the nearest copy. Strong consistency waits until all copies agree.

Data warehouses

Built for questions about everything

A transaction database stores rows“Show order 103”: read one rowthousands of small questions a secondRDS · Azure SQL Database · Cloud SQLA data warehouse stores columns“Total sales by month”: read one columna few huge questions, across years of historyRedshift · Synapse Analytics · BigQueryRunning the business and analysing it are different jobs, and they get different databases.A transaction database is tuned to fetch and change single rows. Analysis reads one or two columns of every row, so awarehouse stores each column together, compresses it and scans it with many machines at once. Data is copied in fromthe operational databases, so a heavy report never slows down the checkout.

A data warehouse stores data by column to answer large analytical questions; it is separate from the operational database.

Data warehouses · Going deeper

From raw data to a dashboard: the analytics pipeline

Sourcesdatabases, files,eventsCollect and cleanAWS GlueAzure Data FactoryGoogle DataflowData lakeeverything, raw, cheapS3 · Data Lake StorageCloud StorageData warehousecleaned, in columnsRedshift · SynapseBigQueryDashboardsAmazon QuickSightMicrosoft Power BILookerAnalysis is a pipeline: collect from everywhere, keep it all cheaply, shape what matters, then show it.The data lake is object storage holding raw files of any kind; the warehouse holds the cleaned tables people query.The step that moves and reshapes data is called ETL: extract, transform, load. Some tools query the lake directlyand charge by data scanned: Amazon Athena about $5 per terabyte, BigQuery about $6.25.

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

ApplicationCache: in memory1 ms1 ask the cache firstDatabase: on disk20 ms2 only if it is not thereAverage time to answer one requestevery request goes to the database20 msthe cache answers 90 in every 1003 msand the database does a tenth of the workA cache keeps recent answers in memory, so most requests never reach the database.Memory is far faster than disk and a ready answer far faster than a query. Product pages, sessions and the results ofexpensive queries are typical contents. The price is freshness: a cached answer can be out of date, so each one isgiven an expiry time. Amazon ElastiCache, Azure Cache for Redis and Google Memorystore run Redis-style caches for you.

A cache holds recent answers in memory so most requests skip the database.

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.

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

TTLeach entry expires after a set time and is fetched again
Evictwhen memory is full, the least recently used entries are dropped
Invalidatedelete an entry the moment its source changes
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.

Queues

Hand work over without waiting for it

Web serversaccept 1,000 ordersa second in a rushQueueholds what cannot be done yeteach message waits its turnWorkersprocess 200 ordersa second, steadilyA 60-second rush leaves 48,000 orders waiting. The workers clear them in 4 minutes. None is lost.Without the queue, the workers would be sent five times what they can handle, and orders would fail.A queue lets one part of a system hand work to another without waiting for it, or overwhelming it.The sender drops a message and moves on; the receiver takes messages at its own pace. If a worker crashes, itsmessage returns to the queue for another. The two sides can be scaled, repaired and released separately, which iscalled decoupling. Amazon SQS, Azure Service Bus and Queue Storage, and Google Pub/Sub provide it.

A queue holds messages between two parts of a system, so a rush becomes a backlog instead of a failure.

Queues · Going deeper

Queues, topics and streams

KindHow a message is deliveredAWSAzureGoogleQueueto one worker, then it is removedSQSService Bus, Queue StoragePub/SubTopica copy to every subscriberSNS, EventBridgeEvent Grid, Service BusPub/SubStreamkept in order; many readers can replay itKinesis Data StreamsEvent HubsPub/SubQueue: orders waiting to be packed. Topic: “order placed” told to billing, email and stock at once. Stream: every click on a site, for analysis.Three ways to pass messages: to one receiver, to all receivers, or as a record anyone can replay.A topic is also called publish and subscribe: the sender does not know who is listening, so new listeners can beadded without touching it. Queues usually deliver each message at least once, so workers must cope with a repeat;a FIFO queue keeps strict order and removes duplicates at lower speed. A message that keeps failing is moved to adead-letter queue for a person to inspect. SQS keeps unread messages for 4 days by default, 14 at most.

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

One online shop’s dataWhat it needsSo it lives inOrders and paymentsmust never be half done; joined to customersa relational databaseCarts and sessionsfetched by one key, for millions of usersa key-value databaseProduct pagesread constantly, changed rarelya cacheEmails and packing jobscan wait a few seconds, must not be losta queueThree years of salesbig questions from a few analystsa data warehousePhotos and invoiceswhole files, served over the webobject storageOne application, six kinds of data, six places to keep it. Choose by what each piece of data has to do.Forcing everything into one database is the common mistake: reports slow the checkout, sessions bloat the tables,photos fill the disk. Each managed service does one job well and bills for what is used, so using several costslittle. Start with a relational database and add the others when a real need appears.

Match each kind of data to the service built for it: relational, key-value, cache, queue, warehouse or object storage.

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.

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.

Open in a new tab