Storage categories

What you will be able to do

Give each kind of help-desk data the store that fits how it is used. The choices are a relational database, object storage, a cache and a vector index.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• The help desk’s inventory and its four groups, from Access-pattern inventory.
• psql and the first vector query, from From SQLite to PostgreSQL.

What you need on your computer

• The project ~/projects/ticket-api with the small data loaded and PostgreSQL running. python -m pytest prints 34 passed. If you are not sure, run python -m scripts.load --reset.

This lesson adds no file to the project. You look at the data in psql and in the project’s data/ folder.

A library has four ways to keep things

Think of a public library. It keeps things in four different ways, because people use them in four different ways.

• The catalogue. A card for every book: title, author, year, shelf number. You can ask it many questions: “books by this author”, “books from 2024 about gardens”. The cards are small, and the librarians change them often.
• The stacks. Rows of shelves with the books themselves. Each book has a shelf number. You do not search inside the stacks. You look up the number in the catalogue, then you fetch the whole book.
• The desk trolley. Books that people asked for today wait near the desk, so the next reader gets them fast. Every evening, the trolley is emptied. Nothing is lost: each book also has its place in the stacks.
• The “you may also like” shelf. Books placed near each other because they are about similar things. You find it by standing near a book you like, not by a title.

Nobody would keep the books in the card drawers. Nobody would search the stacks for “all books from 2024”. Each way is good at one job.

The problem: one store for everything

Tomás likes simple answers. “PostgreSQL can store anything,” he says. “A photo is just bytes, and a vector is just numbers. One store, and we are done.”

He is right that it can. But look at what each kind of data asks for. The last lesson found that the files that the help desk describes would take 11 GB. Compare that with the rest:

files: the files that the large data's attachments table describes (11,478 MB). database: the whole PostgreSQL database (316 MB). details: the attachments table itself (17 MB).

Text description of the chart

A column chart in megabytes. The files that the attachments table describes: 11,478. The whole database: 316. The attachments table, which holds the files’ details: 17.

In the small data, an average ticket row takes 219 bytes (SELECT round(avg(pg_column_size(t.*))) FROM tickets t;). An average file in the large data is 190 kB: as much as about 900 ticket rows. With the files inside, the database would grow from 316 MB to about 12 GB. Every backup, every copy and every restore would carry those gigabytes, even though nobody ever changes a file.

The opposite mistake fails too. A table answers exact questions well, but “tickets like this one” is not an exact question. In psql, ask both ways about T-30002, “Charged twice for order LK-182074”:

SELECT count(*) FROM tickets WHERE subject ILIKE '%charged twice%';

 count
-------
     6
(1 row)

The words find six tickets with the same phrase, and nothing else. Now run the vector query of the setup lesson again, with LIMIT 12 instead of LIMIT 4. It returns the same six tickets first. Then come T-30192 “Locked out of my account”, four tickets “Wrong price charged”, and T-30021 “Discount code did not apply”. Most of those are money problems in other words, and no filter on words would return them. “Near” is not always “right”, either: the locked-out customer is near only because the text also mentions an order. A similarity search gives candidates, not facts.

So one store for everything is possible, and it is a poor fit for at least two kinds of data.

The idea: four kinds of store

A store is any place where an application keeps data. Four kinds cover the help desk. Each one is good at one way of finding data.

A relational database keeps rows in tables with typed columns, and rows refer to each other by keys. “Relational” comes from the mathematical word for a table, a relation. You find rows by any column with SQL, join tables, and change many rows in one transaction: all of the change, or none of it. PostgreSQL is one. It is the library’s catalogue.

Object storage keeps files, called objects, each under a name, called its key: for example tickets/T-30004/34ef…/photo-1.png. You put a whole object and get a whole object. It cannot answer a question such as “files of open billing tickets”, but it keeps many terabytes cheaply. Amazon S3, Azure Blob Storage and Google Cloud Storage are object storage. This course uses Azurite, an emulator of Azure Blob Storage that runs on your computer, from Module 5. It is the library’s stacks.

A cache is a fast store for copies of answers, found by a key, and kept only for a short time. Each entry has an expiry: after it, the entry is gone, and the next request asks the real store again. Redis is a common cache. A cache may lose anything at any moment, by design. It is the desk trolley.

A vector index keeps vectors and finds the ones nearest to a given vector, fast. “Index” means a structure built to find things quickly. Dedicated vector databases exist, such as Qdrant or Milvus. This course uses pgvector, an extension that adds vectors and vector indexes to PostgreSQL, so the vectors sit next to the tickets they describe. It is the “you may also like” shelf.

A tiny worked example: one ticket, four stores

Take one ticket and follow its pieces. T-30004 is a resolved shipping ticket: “robot vacuum not delivered”. In psql, look at its attached files:

SELECT file_name, content_type, size_bytes, object_key FROM attachments WHERE ticket_id = 'T-30004';

       file_name       |  content_type   | size_bytes |                                 object_key
-----------------------+-----------------+------------+----------------------------------------------------------------------------
 photo-1.png           | image/png       |       3508 | tickets/T-30004/34ef8e4b-458e-4c68-91a0-79728852e080/photo-1.png
 receipt-LK-630122.pdf | application/pdf |        757 | tickets/T-30004/50ba0cc3-e31b-4b3b-9fe1-11a4e205d6a0/receipt-LK-630122.pdf
(2 rows)

The table has two small rows of details. The files themselves are not in the database. In the small data, they wait in the project’s data/small/files/ folder, under the same keys. In a second terminal, in the project folder:

ls -l data/small/files/tickets/T-30004/*/

On Windows, use Get-ChildItem -Recurse data\small\files\tickets\T-30004. You see photo-1.png with 3508 bytes and receipt-LK-630122.pdf with 757 bytes: the sizes that the details promise. The made-up files are small. Real photos are hundreds of kilobytes. Module 5 puts these files into object storage.

The rest of T-30004 sits in PostgreSQL: its row in tickets, 2 messages and 2 AI runs. The runs are a classification and a drafted reply that ended with error. Its vector is a row in ticket_embeddings:

SELECT ticket_id, vector_dims(embedding), left(embedding::text, 60) FROM ticket_embeddings WHERE ticket_id = 'T-30004';

 ticket_id | vector_dims |                             left
-----------+-------------+--------------------------------------------------------------
 T-30004   |         384 | [0.02035228,0.01017145,-0.04077965,-0.03433716,0.07428094,-0
(1 row)

384 numbers, the first five visible. No person can read them, but two tickets about the same problem get similar numbers. That is why the nearest vectors of T-30002 were the other “Charged twice” tickets.

One ticket uses three stores already. The catalogue has its row, messages, runs and file details. The stacks have its two files, and the “you may also like” shelf has its vector. If the ticket page became slow, a cache could keep a copy of its answer for a few minutes. That would be the fourth store.

The storage map

Mei draws all of it on one page. Each arrow says how the ticket API reaches a store, and the dotted lines show which data refers to which.

The help desk's storage map: four kinds of store, how the API reaches each one, and the links between the data.

Text description of the diagram

The API uses SQL for the relational database: any column, joins and transactions. It gets or puts one whole file by its key in object storage. It asks the vector index for the nearest vectors. It gets a cached answer by its key. Two dotted links matter. An attachments row holds the key of its file in object storage. Each vector in ticket_embeddings is made from a ticket’s text, and the HNSW index, added in Module 5, is built from the vectors. The cache is optional: the project does not have one.

Now try it for each piece of data. The explorer reads the project’s storage map, the file that you add in the next lesson.

Explore

Where does each piece of help-desk data go?

Store

Object storage

Original or copy

Original (source of truth)

A photo a customer attached goes to object storage. It is an original: no other store can make it again.

A photo a customer attached

Example	photo-1.png on T-30004 (made up by a program)
Store	Object storage: one object per file; its name, size and checksum are a row in the attachments table
Kind of store	Object storage
Why there	A file is large, and nobody changes it after the upload. The database keeps only its details.
How you read it	Get or put one whole file by its key, for example tickets/T-30004/<id>/photo-1.png.
This store is bad at	Questions. It cannot answer 'which files belong to open billing tickets?' without a database.
Original or copy	Original (source of truth)
How many	43 files (small data); 61,853 described (large data)

PostgreSQL tables

• A ticket
• A message
• A policy document
• An AI run

Object storage

• A photo a customer attached (chosen)

Cache

• A cached answer

Vector index (pgvector, in PostgreSQL)

• A ticket vector
• The similar-ticket index (HNSW)

Eight pieces of Larkfield's data and the store that fits each one, from the project's docs/storage-map.md. Counts: small data and large data. The HNSW index comes in Module 5; the project has no cache.

Text description of the explorer

The explorer has one control: a button for each piece of data. For each one, it shows an example from Larkfield, the store and the place in it, and the kind of store. It also shows why the data goes there, how you read that store, and what that store is bad at. Last, it says whether the data is an original or a copy, and how many there are. Below that, every piece of data is listed under its store.

A ticket, a message, a policy document and an AI run go to PostgreSQL tables (a relational database). Each is an original. A photo goes to object storage, and its name, size, checksum and object key go to a row in the attachments table. A ticket vector and the similar-ticket index (HNSW) go to the vector index, pgvector in PostgreSQL. Both are copies, made from the ticket text and from the vectors. A cached answer goes to a cache, and it is a disposable copy.

The answer to the prediction: the photo goes to object storage, and its details go to a row in attachments in PostgreSQL. The row is what makes the file findable: “the files of T-30004” is a SQL query, and the answer contains the keys.

Larkfield’s four groups, four stores

Now put the inventory’s groups and the stores together:

Group from the inventory	Store	Why	What this store is bad at
Small records with many queries: customers, tickets, messages, documents, AI runs	Relational database (PostgreSQL)	Exact answers by any column, joins, transactions, rules that reject bad data	Large files; search by meaning
Large files: photos and receipts	Object storage (Azurite in this course)	Cheap per gigabyte; a file is written once and read whole	Any question about the files; that needs the details in the database
Vectors: search by nearness	Vector index (pgvector, in PostgreSQL)	Finds the nearest vectors fast; near the records it describes	Exact facts; every vector depends on one model and its version
Repeated answers	Cache (not in the project)	Answers a repeated question without the database	Keeping anything: entries expire or disappear

Sam asks the beginner’s question: “So the vectors are in PostgreSQL too. Is that a vector index or a relational database?”

“Both,” Mei says. “The category is about how you find the data, not about the product. pgvector gives PostgreSQL a second way to find rows: by nearness. A product that does only that is a vector database.”

When to add a store, and what it costs

Each store that you add costs something every day:

• Another thing to run and secure: a server or a service, its users, its keys, its updates.
• Another thing to back up and restore. A backup of PostgreSQL does not contain the files in object storage. Module 5 restores both and finds the gaps.
• Data that must agree across stores. An attachments row says that a file exists. If the file is missing, or a file has no row, the stores disagree. Module 5 scans for both.

So Larkfield adds a store only when the inventory asks for it. Files clearly need object storage: 11 GB that never change. Vectors stay in PostgreSQL, because 10,000 vectors fit easily, and the tickets they describe are in the same database. Module 5 compares that choice with a dedicated vector database. A cache waits until a measurement shows a slow answer that is asked for again and again. In Module 4, an index and a better query make the ticket list 134 times faster, and no cache is needed.

Your turn: three new kinds of data

The help desk wants three new features. For each piece of data, choose a store and give one reason from its access pattern:

1. The voice recordings of phone calls, about 2 MB each, kept for 90 days.
2. A dashboard box “today’s ten most common problems”, recomputed from the tickets every five minutes.
3. Each customer’s preferred language, which agents filter on.

Show an example answer

1. Object storage, with a details row in PostgreSQL: the call’s ticket, the key, the size, the date. The recordings are large, never change, and are played whole. The 90 days are a lifecycle rule (Module 5).
2. A cache, or a small table that a job refreshes. The answer is expensive to compute and the same for every agent. Five minutes old is fine, and a lost entry is computed again.
3. A column in the customers table (relational). It is a small fact, it changes rarely, and agents filter by it: an exact question by a column.

Common mistake

Common mistake

Choosing the store by the product, not by the access pattern

Symptom: a design says “PostgreSQL” or “the vector database” for everything. Nobody can say how a photo is read, how often a vector changes, or what happens when an entry disappears. The database grows by gigabytes of files, or exact questions go to a store that can only answer “what is near”.

Cause: the team chose the store first and fitted the data into it. Nobody made the inventory.

Diagnose: for each piece of data, ask the inventory’s questions. How is it read? How often does it change? How much is there? How fresh and exact must the answer be? Then ask what the chosen store is bad at. If the answer is the data’s main access pattern, the store is wrong.

Fix: start from the inventory, and pick the category before the product. One product can play two roles, as PostgreSQL with pgvector does here, but each role must fit an access pattern.

Knowledge check

Check your understanding

Name the four kinds of store, and say for each one how you find data in it.

Show the answer

A relational database: by any column with SQL, with joins, and with changes inside transactions. Object storage: one whole object at a time, by its key. A cache: a copy of an answer by its key, until the entry expires or disappears. A vector index: the vectors nearest to a given vector.

Check your understanding

Tomás says: 'PostgreSQL can store bytes, so let's keep the photos in a column. One store is simpler.' Give two costs of this, with numbers from the help desk.

Show the answer

First, size: the large data’s files would add about 11 GB to a 316 MB database, about 36 times its size. Every backup, copy and restore would carry those gigabytes, although no file ever changes. Second, mismatch: an average file (190 kB) is as large as about 900 ticket rows, and a file is read whole by its key. That is object storage’s job. The database needs only the details: name, size, checksum and key, 17 MB in all.

Check your understanding

Practical check: for ticket T-30004, list where each of its pieces is stored, with the query or command that shows it.

Show the answer

Its row: tickets (SELECT * FROM tickets WHERE ticket_id = 'T-30004';). Its 2 messages: messages. Its 2 AI runs: ai_runs. Its 2 file details: attachments, with the object keys. Its 2 files: data/small/files/tickets/T-30004/… now, and object storage from Module 5 (ls -l data/small/files/tickets/T-30004/*/). Its vector: ticket_embeddings, 384 numbers (vector_dims(embedding)).

Recap

• A relational database finds rows by any column, with joins and transactions. Object storage keeps whole files by key. A cache keeps copies of answers until they expire. A vector index finds the nearest vectors.
• One store for everything fails twice. The described files (11 GB) would make the database about 36 times larger. And a filter on words cannot find “tickets like this one”.
• T-30004 lives in three stores. Its records and file details are in PostgreSQL, its files go to object storage (from Module 5), and its vector is in pgvector.
• Choose the category from the access pattern, then the product. Every extra store costs running, securing, backing up and keeping the stores in agreement.

Next: some of these stores keep the original data, and some keep copies. You decide which is which, and you make a copy again from its original.

Next: Ownership and source of truth