Not everything is a table: other kinds of databases

The right shape for the job

Thirteen lessons of tables. They're the right default for most data: things with a clear shape, linked by keys, that you'll ask new questions about later. But not every job is shaped like that:

• "Remember this answer for an hour, and hand it back instantly" doesn't need a table, a schema or a query planner. It needs a name and a value.
• "Count this API key's requests in the last minute, across six servers, thousands of times a second" needs a counter everyone can reach that's faster than a disk.
• "Store each town's profile, where every town has different fields" doesn't fit fixed columns well.
• "Which friends of friends live in Ashford?" is a question about connections, many hops deep.

For those, there are other kinds of database, often grouped as NoSQL ("not only SQL"). Think of a toolbox. A relational database is the good adjustable wrench: it does most jobs well, and it's what you reach for first. But a socket set, a screwdriver and a tape measure each beat it at one thing, and a good mechanic owns all of them, and knows which job is which.

The main kinds

Most real systems use two or three of these together, each for its job. CivicGrid uses three: Postgres for the data itself, Redis for a cache and the rate limits, and Prometheus, a time-series database, for the cluster's metrics. This lesson gets hands-on with Redis, the one you're most likely to meet first, and with documents inside Postgres.

Redis: values under names

Redis keeps everything in memory, not on disk, which is why it's fast: a lookup takes microseconds. It has no tables and no SQL. You store a value under a key, a name you choose, and get it back by that name. Start one, and say hello:

docker run -d --name larkspur-redis redis:7-alpine
r PING
r INFO server | grep -E "^redis_version"

PONG
redis_version:7.4.12

Set a value, and get it back:

$ r SET town:1:name Ashford
OK

$ r GET town:1:name
Ashford

$ r GET town:99:name

A missing key just comes back empty. Keys are free-form text, and town:1:name is a convention, not a rule: colons group related keys, the way folders group files. Nothing checks that town:1 is a real town, or that two people won't use the same name for different things. Choosing key names carefully is the nearest Redis has to a schema.

SET replaces, EXISTS checks, DEL removes:

$ r SET town:1:name "Ashford City"
OK

$ r GET town:1:name
Ashford City

$ r EXISTS town:1:name town:99:name
1

$ r DEL town:1:name
1

$ r GET town:1:name

EXISTS counts how many of the keys you named exist: one of two.

A cache that expires

The most common job for Redis is a cache: keep the answer to an expensive question, so the next person asking gets it instantly, without the database doing the work again. Ask Postgres for every town as JSON, and store the answer in Redis:

pg -tA -c "SELECT json_agg(t ORDER BY id) FROM (SELECT id, name, kind, population FROM towns) t" \
  | r -x SET cache:towns:all
r EXPIRE cache:towns:all 5

OK
1

json_agg turns rows into one JSON array, and redis-cli -x takes the value from what's piped in. EXPIRE gives the key a time to live (TTL): 5 seconds here, so you can watch it happen. Here's the cached answer:

$ r STRLEN cache:towns:all
765

$ r GET cache:towns:all | cut -c1-120 | head -3
[{"id":1,"name":"Ashford","kind":"city","population":48210}, 
 {"id":2,"name":"Ashford","kind":"village","population":1904}, 
 {"id":3,"name":"Brennan Falls","kind":"city","population":21544}, 

$ r TTL cache:towns:all
5

765 characters, ready to send as an API response with no query at all. Wait six seconds:

$ sleep 6

$ r TTL cache:towns:all
-2

$ r GET cache:towns:all

Gone. A TTL of -2 means "no such key". Redis deleted it by itself. That's what makes caches safe to use: an answer can only ever be so stale. The next request finds nothing, asks Postgres, and caches the fresh answer.

Why not set the TTL at the same time as the value? You can: SET key value EX 5 does both. But with -x, redis-cli puts the piped value at the end of the command, after EX 5, and Redis refuses SET key EX 5 value as a syntax error. Two commands is clearer anyway.

Counters, and a rate limit

INCR adds one to a number, and returns the new value, in a single step:

$ r INCR visits:ashford
1

$ r INCR visits:ashford
2

$ r INCRBY visits:ashford 10
12

$ r GET visits:ashford
12

The key didn't exist before the first INCR, so Redis started it at 0. And the step is atomic: if six servers all run INCR on the same key at the same instant, Redis does them one at a time, and every one gets a different answer. No count is lost, and no two get the same number. That's exactly what a rate limit needs.

A simple one: at most 10 requests a minute per API key. Each request does INCR on the key's counter; the first one also sets a 60-second expiry, so the count resets itself:

for i in $(seq 1 12); do
  n=$(r INCR rl:demo-key:minute)
  [ "$n" -eq 1 ] && r EXPIRE rl:demo-key:minute 60 >/dev/null
  if [ "$n" -le 10 ]; then echo "request $i: allowed ($n of 10)"
  else echo "request $i: refused, 429 ($n of 10)"; fi
done

request 1: allowed (1 of 10)
request 2: allowed (2 of 10)
request 3: allowed (3 of 10)
request 4: allowed (4 of 10)
request 5: allowed (5 of 10)
request 6: allowed (6 of 10)
request 7: allowed (7 of 10)
request 8: allowed (8 of 10)
request 9: allowed (9 of 10)
request 10: allowed (10 of 10)
request 11: refused, 429 (11 of 10)
request 12: refused, 429 (12 of 10)

Ten allowed, then refused. 429 is the HTTP status code for "too many requests". And the counter will vanish by itself:

$ r TTL rl:demo-key:minute
60

This is a fixed window: the minute starts at the first request, and everything resets when it ends. Its weakness: 10 requests at 0:59 and 10 more at 1:01 are 20 in two seconds, all allowed.

A sliding window, with a sorted set

A sorted set stores members, each with a number called a score, kept in score order. Use each request's time as its score, and you can count "requests in the last 60 seconds" at any moment: a sliding window, with no edges to game. Add five requests, made 130, 70, 45, 20 and 5 seconds ago:

now=$(date +%s)
for s in 130 70 45 20 5; do r ZADD rl:sliding $((now - s)) "req-$((now - s))"; done
r ZCARD rl:sliding
r ZCOUNT rl:sliding $((now - 60)) +inf
r ZREMRANGEBYSCORE rl:sliding 0 $((now - 120))
r ZCARD rl:sliding

5
3
1
4

• ZCARD: 5 requests stored.
• ZCOUNT from 60 seconds ago to now: 3 in the last minute, the ones from 45, 20 and 5 seconds ago.
• ZREMRANGEBYSCORE drops everything older than two minutes: 1 removed, the one from 130 seconds ago.
• ZCARD again: 4 left.

Prune the old, count the recent, decide, record: that's a sliding-window rate limiter, and it's the one CivicGrid's API runs.

Hashes: several fields under one key

A hash holds several named fields under one key, a little like one row:

$ r HSET town:1 name Ashford kind city population 48210
3

$ r HGETALL town:1
name
Ashford
kind
city
population
48210

$ r HINCRBY town:1 population 150
48360

HSET set 3 fields. HGETALL returns them as name, value, name, value. HINCRBY adds to one field, atomically, like INCR. Every key has a type, and the commands only work on the right one:

$ r TYPE town:1
hash

$ r TYPE visits:ashford
string

$ r TYPE rl:sliding
zset

What a key-value store can't do

Add two more towns as hashes, and ask the most ordinary question from Lesson 3: towns over 20,000 people. Redis has no WHERE. The only way is to find every town key, fetch each one, and check it yourself:

r HSET town:3 name "Brennan Falls" kind city population 21544
r HSET town:12 name Linden kind town population 9734
r KEYS "town:*"
for k in $(r KEYS "town:*"); do
  p=$(r HGET "$k" population)
  [ "${p:-0}" -gt 20000 ] && echo "$k $(r HGET "$k" name) $p"
done

town:12
town:1
town:3

town:1 Ashford 48360
town:3 Brennan Falls 21544

It works for three keys. For a million, it's a million round trips. And KEYS walks every key in the database while Redis does nothing else, which on a busy server stalls every other client; real code uses the gentler SCAN, which is still a walk through everything. The same question in Postgres, over the real data:

SELECT id, name, population FROM towns WHERE population > 20000 ORDER BY id;

 id |     name      | population 
----+---------------+------------
  1 | Ashford       |      48210
  3 | Brennan Falls |      21544
  7 | Fairview      |      33102
 11 | Kestrel Bay   |      27450
(4 rows)

One line, with an index to help if the table were big. (Redis found two because it only holds the three towns put into it.) That's the trade, in one example: a key-value store answers "what's under this name?" faster than anything, and almost nothing else. No questions by value, no joins, no constraints, no GROUP BY. If you'll need to ask the data new questions later, it belongs in a relational database. Put in Redis only what you'll look up by name.

After a restart

Redis lives in memory. So what happens when it restarts?

$ r SET survives:restart yes
OK

$ docker restart larkspur-redis
larkspur-redis

$ r GET survives:restart
yes

$ r DBSIZE
7

It survived, along with the other six keys still there (DBSIZE counts them). On a clean shutdown, Redis writes a snapshot of memory to disk, and reads it back when it starts. But only a clean shutdown, or every so often: if it crashes, anything since the last snapshot is gone. That's fine for a cache, which can always be rebuilt from Postgres, and for rate-limit counters, where losing a minute's count costs nothing. It's why Redis is rarely the only home for data you can't afford to lose. (Redis can also log every write, like a database does, at some cost in speed.)

Documents, inside Postgres

A document database stores each record as a JSON-like document, and each document can have its own fields. Postgres can do that too, with the jsonb type: JSON, stored in a binary form it can search and index. A table of town profiles, where each town has whatever details it has:

CREATE TABLE town_profiles (
    town_id integer PRIMARY KEY REFERENCES towns (id),
    profile jsonb NOT NULL
);
INSERT INTO town_profiles VALUES
 (1, '{"motto": "Built on the river", "twin_towns": ["Ashby", "Fordwich"], "services": {"library": true, "pool": true}}'),
 (3, '{"motto": "Where the falls begin", "services": {"library": true, "pool": false}, "festival": {"name": "Falls Fest", "month": "June"}}'),
 (6, '{"services": {"library": false}, "elevation_ft": 2210}'),
 (12, '{"twin_towns": ["Lindau"], "services": {"libary": true}}');

Ashford has twin towns, Brennan Falls a festival, Elk Ridge an elevation: no fixed columns needed. And it's still Postgres, with a real key and a real foreign key alongside. Read inside the documents with ->, which returns JSON, and ->>, which returns text:

SELECT town_id, profile->>'motto' AS motto, profile->'services'->>'library' AS library
FROM town_profiles ORDER BY town_id;

 town_id |         motto         | library 
---------+-----------------------+---------
       1 | Built on the river    | true
       3 | Where the falls begin | true
       6 |                       | false
      12 |                       | 
(4 rows)

@> ("contains") finds documents that include a piece of JSON, and ? finds ones that have a key:

SELECT t.name FROM town_profiles p JOIN towns t ON t.id = p.town_id
WHERE p.profile @> '{"services": {"library": true}}' ORDER BY t.name;
SELECT t.name, p.profile->'festival'->>'name' AS festival
FROM town_profiles p JOIN towns t ON t.id = p.town_id WHERE p.profile ? 'festival';

     name      
---------------
 Ashford
 Brennan Falls
(2 rows)

     name      |  festival  
---------------+------------
 Brennan Falls | Falls Fest
(1 row)

Document queries, joined to the towns table, in one query. A separate document database couldn't join to your tables at all.

Now look at Linden. Its library came back empty in the first query, and it's missing from the libraries list. List the keys inside each services:

SELECT town_id, jsonb_object_keys(profile->'services') AS service_key
FROM town_profiles ORDER BY town_id;

 town_id | service_key 
---------+-------------
       1 | pool
       1 | library
       3 | pool
       3 | library
       6 | library
      12 | libary
(6 rows)

libary. A typo, accepted without a word, because a document has no fixed shape to check it against. Linden has a library, and every query for libraries says it doesn't. In a table, libary would have been an error the moment it was written: Lesson 3's nmae, the database knowing every column's name. That's the price of flexible documents: every rule Lessons 6 to 10 were about, you now enforce yourself, in your code, or not at all. (Postgres can add CHECK constraints on jsonb too. Few people do.)

To make document queries fast on a big table, a GIN index covers every key and value inside the documents, and helps @> and ?:

CREATE INDEX town_profiles_gin ON town_profiles USING gin (profile);

            Table "public.town_profiles"
 Column  |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 town_id | integer |           | not null | 
 profile | jsonb   |           | not null | 
Indexes:
    "town_profiles_pkey" PRIMARY KEY, btree (town_id)
    "town_profiles_gin" gin (profile)
Foreign-key constraints:
    "town_profiles_town_id_fkey" FOREIGN KEY (town_id) REFERENCES towns(id)

The demo then drops the table, and removes the Redis container with docker rm -f -v.

How to choose

A rough guide, in order:

1. Start with Postgres. It handles far more than people expect: tables, jsonb documents, full-text search, even time-series and vectors with extensions. One database is one thing to back up, secure and understand.
2. Add a cache when the same expensive answer is asked for over and over. Redis, with a TTL, and with Postgres still the source of truth.
3. Add something specialized when you hit a limit Postgres really can't meet, not one you imagine you might: millions of writes a second, search with relevance ranking and typo tolerance, questions ten hops deep.
4. Keep each piece in its lane. The cache can always be thrown away and rebuilt. If losing it would lose data, it isn't a cache.

Spot the problem

Work out what's wrong before you read the explanation.

The update nobody saw. An API caches the full city list in Redis for an hour, and clears the cache whenever an admin edits a city through the API. That works. Then the weekly script that verifies mayors runs, finds three new mayors, and writes them straight to the database, not through the API. For up to an hour, the API keeps serving the old mayors. Why, and what limits the damage? The cache is only cleared by writes that go through the API; a script writing directly to Postgres doesn't know the cache exists. The cached copy stays until its TTL runs out. The TTL is the backstop: however a write happens, nothing can be stale for longer than an hour. This is a real trade-off in CivicGrid's own cache, and it's written down in the code. The general rule: every cache needs an expiry, even when you also clear it on purpose, because sooner or later something will change the data a way you didn't plan for.

Where you are now

That's the track. You started with a spreadsheet that grew up, and you can now ask a database questions, join and count, design tables that refuse bad data, change data safely, find what's wrong with checks, make it fast, decide who sees what, use it from code without opening it to attack, back it up and restore it, and know when a table isn't the right tool at all. Larkspur County is yours to keep breaking: reload larkspur.sql any time, and start again.

Check yourself

Key terms

• NoSQL: the non-relational kinds of database, "not only SQL".
• Key-value store: a database of values under names, looked up by name. Redis is one.
• Cache: a copy of an expensive answer, kept for fast reuse, that can always be rebuilt.
• TTL: time to live. How long until a key deletes itself.
• Atomic: done in one step that nothing can interrupt or interleave with.
• Fixed and sliding window: rate limits that reset each period, or always count the last period.
• Document: a JSON-like record, each with its own fields. jsonb stores them in Postgres.
• GIN index: an index over every key and value inside documents.

Go deeper

• Redis data types (opens in a new tab), the official tour of strings, hashes, sorted sets and the rest.
• The PostgreSQL manual: JSON Types (opens in a new tab).
• Try: rewrite the fixed-window rate limit as a sliding window, using ZADD, ZREMRANGEBYSCORE and ZCARD, and send it 12 requests.
• Try: add a CHECK to town_profiles so services can only contain library and pool, and watch it refuse Linden. (Hint: profile->'services' - 'library' - 'pool' removes the allowed keys; whatever's left should be '{}'.)