Access-pattern inventory

What you will be able to do

Before you choose where data goes, list how each kind of data is used. Who reads it, and how? How often does it change, how much is there, and how fresh must an answer be?

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• How to load the data and use psql, from From SQLite to PostgreSQL.
• GROUP BY and count(*), from Aggregate correctly in the SQL course.

What you need on your computer

• The project ~/projects/ticket-api from the last lesson, with PostgreSQL running. python -m pytest prints 34 passed.
• About 1 GB of free disk space and an internet connection, once, for the large data.

This lesson adds no file to the project. You write the inventory in this lesson, and you query the large data in psql. At the end, you go back to the small data.

Packing for a move

You are moving to a new flat. Before you buy boxes and shelves, you walk through your old flat and list what you have and how you use it.

The coffee cup: you use it every morning, so it goes on the kitchen counter. The winter coats: twice a year, so they go to the attic. Your passport: you rarely need it, but you must never lose it, so it goes in a safe drawer. Two hundred photo albums: heavy, never changed, looked at once a year. They go in the cellar, with a list on the shelf of what is in each box.

You did not choose a place by what each thing is. You chose it by how you use it: how often, how fast you need it, how big it is, and what a loss would cost.

The problem: storage chosen by guessing

Omar, the product manager, asks Mei a simple question: “How big will the help desk’s database be, and what makes it big?”

Tomás answers first. “Tickets. Everything is a ticket. Let’s put all the data in one place and decide the details later.”

It sounds reasonable. But a guess cannot tell the cup from the passport. Load the large data and count. The large data is Larkfield’s stress fixture: 300,000 made-up tickets over one year, about 800 a day. Larkfield has about 45 a day today, so this is the size to plan for, not today’s size.

1. In the project folder, with the virtual environment active, load the large data:

   python -m scripts.load --size large --reset

   The first time, the loader makes the large data on your computer and downloads its vectors, once:

   Making the large data (once, about 15 seconds) ...
   large: customers 20,000, tickets 300,000, messages 690,300, attachments 61,853, documents 36, ai_runs 449,734, files 0 -> …/ticket-api/data/large
   Downloading the vectors of the large data (15 MB, once) ...
   Database is up to date (4 migrations).
   Loaded the large data in 4.4 s:
     customers             20,000
     tickets              300,000
     messages             690,300
     attachments           61,853
     documents                 36
     ai_runs              449,734
     ticket_embeddings     10,000

   The --reset is needed because the small data is loaded: the loader never replaces data by accident. Later loads of the large data skip the first three lines.

   The load itself takes 3 to 5 seconds on a quiet computer. On a busy computer, it can take up to a minute. Without an internet connection, take the vectors from your copy of the labs repository. Add --vectors ../nextia-learning-labs/C20/data/large to the command. It checks their SHA-256 either way.

2. Open psql:

   docker compose exec db psql -U tickets -d tickets

3. Ask PostgreSQL how many rows each table has and how much disk it uses:

   SELECT relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;
   SELECT pg_size_pretty(pg_database_size(current_database()));

         relname      | n_live_tup | pg_size_pretty
   -------------------+------------+----------------
    messages          |     690300 | 107 MB
    ai_runs           |     449734 | 91 MB
    tickets           |     300000 | 75 MB
    attachments       |      61853 | 17 MB
    ticket_embeddings |      10000 | 16 MB
    customers         |      20000 | 2640 kB
    documents         |         36 | 104 kB
    schema_migrations |          4 | 32 kB
    classifications   |          0 | 16 kB
   (9 rows)
   
    pg_size_pretty
   ----------------
    316 MB
   (1 row)

   Your sizes can differ by a few kilobytes or megabytes. The rows are the same.

pg_stat_user_tables is a view that PostgreSQL keeps about every table. n_live_tup is its count of rows. pg_total_relation_size counts the table and everything that belongs to it.

Disk space of each help-desk table in PostgreSQL, with the large data: 316 MB in all. msgs = messages, runs = ai_runs, attach = attachments, vecs = ticket_embeddings, cust = customers, docs = documents.

Text description of the chart

A column chart in megabytes. messages 107, ai_runs 91, tickets 75, attachments 17, vectors (ticket_embeddings) 16, customers 2.6, documents 0.1. The whole database is 316 MB.

Tomás’s guess was wrong twice. The tickets are only the third-largest table: the messages and the AI runs are larger. And the biggest data is not in the database at all. The attachments table only describes files. Ask it how large those files are:

SELECT count(*) AS files, pg_size_pretty(sum(size_bytes)) AS all_files, pg_size_pretty(max(size_bytes)) AS largest FROM attachments;

 files | all_files | largest
-------+-----------+---------
 61853 | 11 GB     | 9277 kB
(1 row)

The details of the files take 17 MB. The files themselves would take 11 GB: about 36 times the whole database. In the large data, the files do not exist; only their details do. A guess would have missed the biggest thing that Larkfield stores.

The idea: an access-pattern inventory

An access pattern is one way that a program uses data: which rows it reads or writes, how it finds them, and how often. “Show the open billing tickets, newest first, every few seconds” is one access pattern. “Sum last month’s AI cost, once a month” is another.

An access-pattern inventory is a list of the data and its access patterns. It answers five questions for each kind of data:

1. What is it? The entity: a ticket, a message, a file.
2. How is it read? The query patterns: by its ID, by a filter, by nearness, as a whole file.
3. How often does it change? Written once and never changed, or changed many times.
4. How much is there? The volume today, and how fast it grows.
5. How fresh and how exact must an answer be? This is consistency. Must every reader see a change at once? Or is an answer from an hour ago good enough?

It is the moving list of the flat, for data. You make it before you choose a store, because each answer points to a kind of store.

A tiny worked example: the AI runs

Do one row of the inventory with the small data first, where you can see every number. Quit psql with \q, load the small data with python -m scripts.load --reset, and open psql again. Then run:

SELECT count(*), min(created_at), max(created_at) FROM ai_runs;
SELECT task, count(*) FROM ai_runs GROUP BY task;
SELECT created_at::date AS day, count(*) FROM ai_runs WHERE created_at >= '2026-10-02' GROUP BY 1 ORDER BY 1;

 count |          min           |          max
-------+------------------------+------------------------
   294 | 2026-09-25 07:26:50+00 | 2026-10-08 19:13:23+00
(1 row)

    task     | count
-------------+-------
 classify    |   200
 draft_reply |    94
(2 rows)

    day     | count
------------+-------
 2026-10-02 |    30
 2026-10-03 |    26
 2026-10-04 |    19
 2026-10-05 |    25
 2026-10-06 |    22
 2026-10-07 |    13
 2026-10-08 |    11
(7 rows)

Every ticket was classified once (200 runs), and 94 got a drafted reply. In the last 7 days, 146 runs were written: about 21 a day. The table takes 80 kB. Now answer the five questions:

Question	AI runs (small data)
What is it?	One call to a model for a ticket: model, prompt version, tokens, cost, time, output
How is it read?	By ticket (“the latest run of T-30002”), and by month and model for Priya’s cost report
How often does it change?	Written once, about 21 a day. Never changed after that
How much is there?	294 rows, 80 kB. It grows with every ticket
How fresh and exact?	The report can be a day old. But the sums must be exact: they are money

One row tells a lot already. The AI runs are an append-only record: nobody changes the past. Priya reads them in bulk, and a little delay is fine. But the money must add up. Migrations in Module 3 changes the type of the cost column for that reason.

Larkfield’s inventory

Now do the whole help desk, on the large data. Quit psql, load the large data again with python -m scripts.load --size large --reset, and open psql. How fast does each kind of data grow? The data ends on 8 October, so count the rows of the last seven days:

SELECT count(*) FROM tickets WHERE created_at >= '2026-10-02';
SELECT count(*) FROM messages WHERE created_at >= '2026-10-02';
SELECT count(*) FROM ai_runs WHERE created_at >= '2026-10-02';
SELECT count(*) FROM attachments WHERE uploaded_at >= '2026-10-02';

The answers are 5,761, 10,247, 8,724 and 1,161. No customer and no document was added in that week.

Rows added to each table from 2 to 8 October 2026 in the large data, with the short names of the chart above. Messages and AI runs grow faster than tickets.

Text description of the chart

A column chart of the rows written from 2 to 8 October 2026. Messages: 10,247. AI runs: 8,724. Tickets: 5,761. Attachments: 1,161. Customers and documents: 0.

Next, how much of the data is “hot”, in daily use? Agents work on open and pending tickets:

SELECT status, count(*) FROM tickets GROUP BY status ORDER BY 2 DESC;

  status  | count
----------+--------
 closed   | 254364
 resolved |  33822
 open     |   7216
 pending  |   4598
(4 rows)

Only 11,814 of the 300,000 tickets are open or pending: about 4 in 100. The agents’ queue shows a few of those at a time. For example, SELECT count(*) FROM tickets WHERE status = 'open' AND team = 'billing'; gives 1,659, and the queue shows the newest 20. A design that reads every ticket to find 20 does 15,000 times too much work. Module 4 measures exactly that.

Now Mei draws the access patterns: who reads and writes what.

The help desk's access patterns: each arrow is one way a part of the help desk reads or writes a table.

Text description of the diagram

Seven parts of the help desk use the data. The agents’ queue and the ticket page read all day. The ticket page reads four tables for one ticket. Agent replies write messages and change the status. Each new ticket gets AI runs. Similar tickets reads vectors by nearness. Priya’s cost report reads a month of AI runs, once a month. Grace’s erasure deletes a customer’s data, rarely.

Then she writes the inventory. The counts are from the large data. The freshness column is the help desk’s own answer: Grace and Priya said what they need.

Data	How it is read	How often it changes	How much (large data)	How fresh and exact
Customers	by ID; all data of one customer for an erasure	rarely: 0 new in the last week	20,000 rows, 2.6 MB	exact; an erasure must reach every store
Tickets	by ID; open tickets of one team, newest first; by customer	status, team and priority change many times; about 820 new a day	300,000 rows, 75 MB; 4% open or pending	at once: two agents must not take the same ticket
Messages	all messages of one ticket, in order	written once, about 1,460 a day	690,300 rows, 107 MB	at once, and never lost: a customer wrote it
Files (photos, receipts)	one whole file at a time, by its ticket	written once, never changed; about 165 a day	61,853 files, 11 GB; details 17 MB	the file must match its checksum
Policy documents	by ID and the version in force on a date	a new version a few times a year	36 versions of 35 policies	exact version: an old answer must be checked against the version in force at that time
AI runs	latest run of a ticket; a month by model for the report	written once, about 1,250 a day	449,734 rows, 91 MB	a day old is fine for the report; sums exact
Ticket vectors	by nearness to another ticket’s vector	made when a ticket is written	10,000 vectors, 16 MB	approximate is fine: “similar”, not “equal”

Read the table by column, and four groups appear:

• Small records with many queries. Customers, tickets, messages, documents and AI runs. They are found by IDs and by filters, they change, and they need exact answers. That is a job for a relational database.
• Large files. 11 GB that nobody changes and nobody filters. They are read whole, one at a time.
• Vectors. They are searched by nearness, not by equal values, and an approximate answer is fine.
• Repeated answers. The ticket page asks the same questions all day. If a slow answer comes up again and again, a short-lived copy can save work.

The next lesson gives each group its kind of store.

When to make an inventory, and what it costs

Make one before you choose a store. Make it again when the product changes: a new feature, a new kind of file, a model that writes more. Here, the inventory replaced a guess (“tickets are the big part”) with a count.

It costs little: an hour, a few count(*) queries, and questions to the people who use the data. Two warnings come with it:

• The numbers age. Write down the date and the data that you counted, and count again before a big decision.
• Plan for growth, not for a fantasy. The large data is about 18 times Larkfield’s ticket volume today. That is enough to make slow queries visible. A design for a billion tickets would cost far more and help nobody.

Your turn: the documents

Fill in the documents row yourself, on the large data. First predict: how many rows, how often do they change, and how are they read? Then check in psql with SELECT doc_id, version, effective_from, effective_to FROM documents ORDER BY doc_id, version;.

Show an example answer

36 rows describe 35 policies. Only returns-policy has two versions: version 3 is in force until 31 October 2026, and version 4 from 1 November 2026. SELECT count(*), count(DISTINCT doc_id) FROM documents; gives 36 and 35.

Data	How it is read	How often it changes	How much	How fresh and exact
Policy documents	by ID, in the version in force on a date; the assistants read them for answers	a new version a few times a year; an old version is never changed	36 rows, about 100 kB	exact version: an answer of 20 October must use version 3

The documents are tiny and change rarely, but each version must stay. A store that keeps only the newest copy would lose version 3, and nobody could check an old answer.

Go back to the small data

The next lessons use the small data. Quit psql with \q and load it again:

python -m scripts.load --reset

It prints the counts of the small data: tickets 200, ticket_embeddings 200. python -m pytest still prints 34 passed. The large data stays in data/large/ for Module 3, so its next load takes seconds.

See it end to endDesign and tune storage for an open public datasetCase study

The Boston 311 case study starts with an inventory of six access patterns, and each one decides an index, or no index, later.

Knowledge check

Check your understanding

Name the five questions of an access-pattern inventory, and answer them in one line each for the messages.

Show the answer

What is it: one message after the first, from an agent or a customer, on one ticket. How is it read: all messages of one ticket, in order, on the ticket page. How often does it change: written once, about 1,460 a day on the large data, never changed. How much: 690,300 rows, 107 MB, the largest table. How fresh and exact: at once, and never lost, because a customer wrote it.

Check your understanding

Predict: Larkfield adds photo uploads to its mobile app, and twice as many tickets get a photo. Which numbers of the inventory change most, and which store does that put pressure on?

Show the answer

The files: today about 165 a day and 11 GB on the large data. They would double. The attachments table grows too, but its rows are small: 17 MB today. So the pressure is on the place that keeps the files, not on the database. If the files were in the database, it would grow from 316 MB by many gigabytes, and every backup and copy with it.

Check your understanding

Practical check: on the large data, how many tickets are open in the billing team, and what share of all tickets are open or pending? Show the queries.

Show the answer

SELECT count(*) FROM tickets WHERE status = 'open' AND team = 'billing'; gives 1,659. SELECT status, count(*) FROM tickets GROUP BY status; gives open 7,216 and pending 4,598: 11,814 of 300,000, about 4%. The queue shows 20 of the 1,659, so the agents’ main query needs a few rows out of 300,000.

Recap

• An access pattern is one way a program reads or writes data. An access-pattern inventory answers five questions for each kind of data. What is it? How is it read? How often does it change? How much is there? How fresh and exact must an answer be (consistency)?
• Counting beat guessing. On the large data, messages (107 MB) and AI runs (91 MB) are larger than tickets (75 MB). The files that the attachments table describes would be 11 GB: about 36 times the 316 MB database.
• Only 4 in 100 tickets are open or pending, and the queue shows 20 at a time.
• The inventory shows four groups: small records with many queries, large files, vectors searched by nearness, and repeated answers.
• You are back on the small data, and python -m pytest prints 34 passed.

Next, each group gets its kind of store: a relational database, object storage, a vector index or a cache.

Next: Storage categories