Data & storage

An AI application needs somewhere reliable to remember things.

A database is software that stores organised data and lets you retrieve or change it. The model is not the database. A language model may produce an answer, but your storage system remembers users, documents, permissions, jobs, feedback, and previous results.

Why this matters

A document assistant must remember uploaded files. A support bot needs ticket history. A recommendation service needs user events. An AI API may need to start a slow indexing job without making the user wait.

One storage tool does not solve every problem. Reliable systems separate durable truth, fast temporary copies, meaning-based retrieval, and background work.

The intuition

The analogy gives each tool one memorable job:

One request, four storage jobs

Read the diagram from left to right. The API accepts a new support ticket. One transaction records the ticket and its pending background job. PostgreSQL keeps the durable facts. Redis may hold a short-lived copy for faster reads. A worker processes the slow embedding task later.

Step 1 — Model facts as related tables

A relational database stores data in tables. A table has columns that define the shape and rows that hold individual records.

Consider two tables:

users
  id | email

tickets
  id | user_id | subject | status | created_at

tickets.user_id is a foreign key. A foreign key connects one row to another and can prevent a ticket from referring to a user who does not exist.

Good table design starts with facts and relationships, not screens. A ticket belongs to one user. One user can own many tickets. That is a one-to-many relationship.

Step 2 — Read and change rows with SQL

Structured Query Language, shortened to SQL, is the language used to work with relational databases.

The four basic operations are often called CRUD:

• Create a row with INSERT.
• Read rows with SELECT.
• Update a row with UPDATE.
• Delete a row with DELETE.

import sqlite3

database = sqlite3.connect(":memory:")
database.execute("""
    CREATE TABLE tickets (
        id INTEGER PRIMARY KEY,
        subject TEXT NOT NULL,
        status TEXT NOT NULL
    )
""")

subject = "Cannot reset password"
database.execute(
    "INSERT INTO tickets (subject, status) VALUES (?, ?)",
    (subject, "open"),
)

rows = database.execute(
    "SELECT subject, status FROM tickets WHERE status = ?",
    ("open",),
).fetchall()

print(rows)

The code uses SQLite because it is included with Python and needs no server. PostgreSQL uses the same core SQL ideas and adds production features for multiple users, larger workloads, richer data types, and extensions.

Never build SQL by inserting user text into the query string:

# Unsafe: user text becomes part of the SQL command.
query = f"SELECT * FROM tickets WHERE subject = '{user_subject}'"

Use parameters instead. The database driver sends the command and the values separately, so a value cannot quietly become a new SQL instruction.

Step 3 — Keep related changes together

A database transactionDatabase transactionA group of database operations that succeed or fail as one unit. A transaction commits when every required operation succeeds and rolls back when one fails, preventing half-finished changes such as creating an order without reducing its stock. groups operations into one all-or-nothing unit.

Imagine transferring £20:

1. Subtract £20 from account A.
2. Add £20 to account B.

If the program crashes after step one, money disappears. A transaction solves that:

• Begin the transaction.
• Run both updates.
• Commit if both succeed.
• Roll back if either fails.

The database provides four useful promises, often shortened to ACID:

• Atomicity: the whole transaction commits or none of it does.
• Consistency: declared rules remain true.
• Isolation: concurrent transactions do not see unsafe half-finished states.
• Durability: committed data survives a crash.

These are goals with different implementation choices. Isolation in particular has levels and trade-offs; this beginner lesson uses the central mental model: other work should not observe a dangerous partial change.

The first update violates the rule balance >= 0. The database raises an error and the context manager rolls back the transaction. Neither account changes.

Step 4 — Add indexes for real query patterns

A database indexDatabase indexAn additional data structure that helps a database find selected rows without scanning every row. It speeds up matching reads but consumes storage and adds work to inserts and updates, so indexes should follow real query patterns rather than being added to every column. is a separate lookup structure that helps the database find rows without examining the whole table.

Think about a contacts app. Searching an alphabetised name index is faster than reading every contact from the beginning. The database can use a similar structure.

CREATE INDEX tickets_by_user_and_time
ON tickets (user_id, created_at DESC);

This index matches a common query:

SELECT *
FROM tickets
WHERE user_id = 42
ORDER BY created_at DESC;

An index is not free:

• It takes storage.
• Every insert or relevant update must also update the index.
• An unused index adds write cost without helping reads.
• A badly chosen column order may not support the query you expected.

Use EXPLAIN to inspect the database’s query plan. Test with representative data. “There is an index” is not proof that the database will use it.

Step 5 — Store embeddings with pgvector

An embedding is a list of numbers that represents meaning. Later lessons build this idea carefully. For now, picture each document as a coordinate on a meaning map.

pgvector is a PostgreSQL extension. An extension adds optional capability to the database. pgvector can store embedding vectors and compare them by distance.

CREATE EXTENSION vector;

CREATE TABLE document_chunks (
    id BIGSERIAL PRIMARY KEY,
    document_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(3)
);

SELECT content
FROM document_chunks
ORDER BY embedding <=> '[0.12, 0.88, 0.31]'
LIMIT 3;

The vector(3) in this tiny example stores three numbers. Real embedding models commonly produce many more dimensions. The <=> operator orders rows by cosine distance, so the closest meanings appear first.

PostgreSQL plus pgvector is attractive when ordinary metadata and embeddings belong together. It is not automatically the best answer for every scale. Later semantic-search lessons compare exact search, approximate indexes, filtering, and specialised vector databases.

Step 6 — Cache repeated reads carefully

A cacheCacheA faster temporary copy of data whose durable source lives elsewhere. Applications use caches to avoid repeating expensive reads or computations, but must choose when cached entries expire or are invalidated so users do not receive stale information. stores a faster temporary copy of data. Redis is a popular in-memory key-value store: you provide a key such as ticket:42 and receive its value.

Cache-aside is a common pattern:

1. Ask the cache for the key.
2. If found, return it. This is a cache hit.
3. If missing, read the database. This is a cache miss.
4. Store a temporary copy in the cache.
5. Return the value.

A time to liveTime to liveThe length of time a cached value or message may remain valid. After its time to live, often shortened to TTL, the system expires it automatically and must fetch or compute a fresh value., shortened to TTL, tells the cache when to expire a value automatically.

TTL limits how long stale data can survive, but it does not decide what “fresh enough” means. A public article count may tolerate a minute-old value. A user’s permissions may require immediate invalidation.

The cache is an optimisation, not the source of truth. The database update happens first. Only after that succeeds does the code invalidate the temporary copy.

The hardest cache problem is invalidation: deciding when a cached value is no longer safe to use. Common strategies include short TTLs, deletion after a write, versioned keys, or avoiding a cache when correctness matters more than saved latency.

Step 7 — Queue slow work for a worker

A message queueMessage queueA durable waiting line for work that can be processed later by a separate worker. The producer adds a message, the queue keeps it, and a consumer handles it, allowing an API to respond without making the user wait for a slow background task. is a durable waiting line between the code that requests work and the code that performs it.

• The producer publishes a message.
• The queue stores it.
• A consumer or worker receives it.
• The worker sends an acknowledgement after success.

For an uploaded PDF, the API can store the document and enqueue index_document(document_id=42). It returns 202 Accepted immediately. A worker later parses the PDF, creates chunks and embeddings, then marks the job complete.

def upload_document(file):
    document_id = save_document(file)
    queue.publish({"job": "index_document", "document_id": document_id})
    return {"document_id": document_id, "status": "queued"}

Real workers can crash after doing the work but before acknowledging the message. The queue may deliver that message again. This is called at-least-once delivery. Therefore a worker must tolerate duplicates, usually by checking a stable job identifier and making its actions idempotent.

A queue is not a place to hide every slow function. It introduces retries, monitoring, delayed results, and duplicate-delivery design. Use it when the request does not need the final result immediately or when work must survive a web process restarting.

Worked example — upload and index a document

Follow one document from input to searchable chunks:

1. The client sends POST /documents.
2. The API validates the owner and file metadata.
3. A transaction inserts documents(id=42, status='queued').
4. The same transaction inserts an outbox job with document_id=42.
5. The transaction commits. The API returns 202 Accepted.
6. A publisher moves the committed outbox message to the job queue.
7. A worker claims the message and changes the document status to processing.
8. The worker extracts text, divides it into chunks, and creates embeddings.
9. One transaction stores all chunks and changes the status to ready.
10. The worker acknowledges the message.
11. A later search filters chunks by owner_id and ranks permitted matches by vector distance.

Why use an outbox table in step four? Publishing to a queue and writing to a database are two different systems. If the row commits but publishing fails, the document could remain queued forever. Recording the intended message in the same database transaction makes the state recoverable. A separate publisher can retry unsent outbox rows.

This is an optional advanced pattern, but the principle is essential: do not claim two independent operations are atomic when they are not.

In a real AI system

Common mistakes

Exercises

Knowledge check

Practice activity

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE tickets (id INTEGER PRIMARY KEY, status TEXT NOT NULL)")
db.execute("""
    CREATE TABLE audit_events (
        id INTEGER PRIMARY KEY,
        ticket_id INTEGER NOT NULL,
        event TEXT NOT NULL
    )
""")
db.execute("INSERT INTO tickets VALUES (1, 'open')")
db.commit()

# Write the transaction here.

try:
    with db:
        db.execute("UPDATE tickets SET status = ? WHERE id = ?", ("closed", 1))
        db.execute(
            "INSERT INTO audit_events (ticket_id, event) VALUES (?, ?)",
            (1, "ticket_closed"),
        )
except sqlite3.DatabaseError as error:
    print("rolled back:", error)

print(db.execute("SELECT * FROM tickets").fetchall())
print(db.execute("SELECT ticket_id, event FROM audit_events").fetchall())

[(1, 'closed')]
[(1, 'ticket_closed')]

Key takeaways

Flashcards

One-page sketchnote summary

with database:
  save_document()
  enqueue_job()

Resources