Specialized Stores

A fan types "tayler swift sp dezembro" into the BoxOffice search box. Marketing wants "tickets sold per minute, per city, for the last two years" every morning. The event page shows a 2 MB poster to millions of visitors. The support assistant must find the refund policy paragraph that answers "can I get my money back if the show moves to another date?" — a sentence that shares almost no words with the policy text.

A relational database can do a weak version of each of these, and that is often the right start. But each one has an engine designed for it: a search engine, object storage, a data warehouse and a vector database. All four hold derived data (see the overview): copies of the system of record, reshaped for one kind of question.

How derived data flows

The system of record stays in the services' relational databases. Changes flow out to the specialized stores through change data capture (CDC): a connector reads the database's log (the PostgreSQL WAL, the MySQL binlog) and publishes every insert, update and delete as an event.

flowchart LR
    DB[("catalog + order<br/>PostgreSQL")] -->|"WAL"| CDC["CDC connector<br/>(Debezium)"]
    CDC --> K[["event stream<br/>(Kafka)"]]
    K --> IDX["indexer"] --> S[("search index")]
    K --> ELT["loader"] --> W[("data warehouse")]
    K --> EMB["embedding worker"] --> V[("vector DB")]
    UP["poster upload"] --> O[("object storage")]

Why not have the service write to the database and to the search index directly (a dual write)? Because the two writes can fail independently or arrive in different orders under concurrency, leaving the stores permanently inconsistent. CDC (or the transactional outbox, see Messaging patterns) gives one ordered source of changes, and any derived store can be rebuilt by replaying it.

Search engines

Why LIKE '%taylor%' is not search

SELECT id, title FROM events
WHERE title ILIKE '%tayler%' AND title ILIKE '%dezembro%';

It scans the whole table (a leading % defeats B-tree indexes), finds nothing for the typo "tayler", does not know that "SP" means "São Paulo", ignores accents and plurals, and returns results in arbitrary order. Users expect typo tolerance, relevance ranking, prefix autocomplete and facets ("filter by city, date, genre") in tens of milliseconds.

The inverted index

A search engine builds an inverted index: for each term, the list (postings list) of documents that contain it — the index at the back of a book¹.

A query intersects or unions postings lists, which is fast because they are sorted and compressed. Before indexing, text goes through an analyzer:

1. Tokenize: "Taylor Swift — São Paulo" → Taylor, Swift, São, Paulo.
2. Normalize: lowercase and strip accents → taylor, swift, sao, paulo.
3. Stem (language-specific): shows → show, ingressos → ingress.
4. Synonyms: sp ↔ sao paulo.

The same analyzer runs on the query, so "SÃO PAULO" and "sao paulo" match the same terms. Fuzzy matching accepts terms within an edit distance (tayler → taylor, distance 1), and edge n-grams or completion suggesters index prefixes (tay, tayl, …) for autocomplete.

Ranking

Matching documents are scored for relevance. The default in Elasticsearch, OpenSearch and Lucene is BM25², which grows with how often a query term appears in a document (with diminishing returns), shrinks for terms common to every document (show, ingresso), and normalizes for document length. Products then blend it with business signals: popularity, date proximity, ticket availability.

{
  "query": {
    "function_score": {
      "query": {
        "multi_match": {
          "query": "tayler swift sp dezembro",
          "fields": ["title^3", "lineup^2", "city", "description"],
          "fuzziness": "AUTO"
        }
      },
      "functions": [
        { "field_value_factor": { "field": "popularity", "modifier": "log1p" } }
      ]
    }
  },
  "aggs": { "by_city": { "terms": { "field": "city.keyword" } } }
}

ALTER TABLE events ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('portuguese', unaccent_immutable(title)), 'A') ||
    setweight(to_tsvector('portuguese', unaccent_immutable(coalesce(description, ''))), 'B')
  ) STORED;
CREATE INDEX events_search_idx ON events USING GIN (search);

SELECT id, title, ts_rank(search, q) AS rank
FROM events, websearch_to_tsquery('portuguese', 'taylor swift dezembro') q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;
-- unaccent_immutable: an IMMUTABLE wrapper around unaccent(), required in generated columns

Running a search cluster

• An index is split into shards (fixed at creation, see Partitioning) with replicas for availability and read throughput. A query fans out to one copy of every shard and merges the top results — scatter/gather.
• Search is near real time: new documents become searchable after a refresh (1 second by default in Elasticsearch), and the CDC pipeline adds its own lag. The event the venue just created may take a few seconds to appear — acceptable for search, not for inventory.
• The index is derived: when the mapping or analyzer changes, build a new index from the stream or a snapshot and switch an alias to it.

Try it. The search-autocomplete scenario puts a search service and a cache behind the gateway. Run it and watch what fraction of keystrokes the cache absorbs (popular prefixes like "tay" repeat a lot); then raise the load and see which component saturates first when the cache is cold.

The full design — top-k prefix tries, debouncing on the client, per-prefix caching — is in the search autocomplete case study.

Object storage

Object storage keeps blobs — images, videos, PDFs, backups, data-lake files — as immutable objects in buckets, addressed by a key and accessed over HTTP (PUT, GET, DELETE, LIST)³. The namespace is flat: posters/42/hero.webp looks like a path, but there are no real directories.

Since 2020, Amazon S3 offers strong read-after-write consistency for all operations, and the other major clouds do as well. What object storage does not do: append to an object, rename cheaply (rename = copy + delete), lock, or query inside objects (except through engines that read files, like a warehouse).

The pattern that keeps blobs off your servers is the presigned URL: the service authorizes the upload, but the bytes go straight from the browser to the bucket.

sequenceDiagram
    participant V as Venue admin (browser)
    participant C as catalog service
    participant S as Object storage
    participant W as Thumbnail worker
    V->>C: POST /events/42/poster (name, type, size)
    C->>C: check permission, choose key posters/42/hero.webp
    C-->>V: presigned PUT URL (valid 5 min)
    V->>S: PUT posters/42/hero.webp (2 MB)
    S-->>V: 200 OK
    S->>W: event: object created
    W->>S: PUT posters/42/hero-400w.webp
    Note over C,S: the database stores only the key,<br/>the CDN serves the images

import boto3

s3 = boto3.client("s3")

def poster_upload_url(event_id: int) -> str:
    return s3.generate_presigned_url(
        "put_object",
        Params={"Bucket": "boxoffice-media",
                "Key": f"posters/{event_id}/hero.webp",
                "ContentType": "image/webp"},
        ExpiresIn=300,  # seconds
    )

Reads go through the CDN (see DNS & CDN) with long cache lifetimes and versioned keys (hero.v3.webp), so the bucket sees a tiny fraction of the traffic. Lifecycle rules move old objects to cheaper tiers (infrequent access, archive) and delete temporary ones automatically; multipart upload sends large files in parallel parts that can be retried individually.

Data warehouses and lakehouses

OLTP vs OLAP

The services run OLTP (online transaction processing): many small reads and writes of individual rows, by key, with transactions. Analysts run OLAP (online analytical processing): few, huge queries that aggregate millions of rows over a few columns. They want opposite things from storage⁴:

Why columns

A column store keeps each column in its own files. A query that reads 3 of 30 columns reads about a tenth of the bytes, and a column of similar values (city, status) compresses extremely well (dictionary and run-length encoding)⁵. With illustrative numbers for BoxOffice's clickstream — 20 GB/day for two years ≈ 15 TB raw:

That difference — and running the scan in parallel on many machines — is why a warehouse answers in seconds what would take hours on the OLTP database (while hurting checkout).

Modelling: the star schema

Warehouses are usually modelled as a star schema: a central fact table of events (one row per ticket sold) with foreign keys to dimension tables that describe them.

erDiagram
    FACT_TICKET_SALES }o--|| DIM_EVENT : "event_key"
    FACT_TICKET_SALES }o--|| DIM_VENUE : "venue_key"
    FACT_TICKET_SALES }o--|| DIM_DATE : "date_key"
    FACT_TICKET_SALES }o--|| DIM_CUSTOMER : "customer_key"
    FACT_TICKET_SALES {
        bigint ticket_id
        timestamp sold_at
        int price_cents
        string channel
    }
    DIM_EVENT {
        bigint event_key
        string title
        string genre
    }
    DIM_VENUE {
        bigint venue_key
        string city
        string country
    }

SELECT v.city,
       date_trunc('minute', f.sold_at) AS minute,
       count(*)                        AS tickets,
       sum(f.price_cents) / 100.0      AS revenue
FROM fact_ticket_sales f
JOIN dim_venue v ON v.venue_key = f.venue_key
WHERE f.sold_at >= '2027-03-01 10:00' AND f.sold_at < '2027-03-01 12:00'
GROUP BY 1, 2
ORDER BY 2, 1;

Getting data in: ETL, ELT, streaming

Lakes and lakehouses

A data lake stores raw files — usually Parquet, a columnar file format — in object storage, cheaply and in any shape. The weakness of a plain lake is that files have no transactions or schema. Open table formats — Apache Iceberg, Delta Lake, Apache Hudi — add a transaction log, schema evolution and time travel on top of those files, so several engines (Spark, Trino, Flink, DuckDB, cloud warehouses) can query the same tables. That combination is the lakehouse⁶.

Vector databases

Embeddings: meaning as geometry

An embedding model turns a piece of text (or an image) into a vector of numbers — typically hundreds to a few thousand dimensions — such that texts with similar meaning end up close to each other. "Can I get my money back if the show moves to another date?" and "Refunds for rescheduled events" share few words but have nearby embeddings. Closeness is usually measured with cosine similarity:

This powers semantic search and the retrieval step of RAG (retrieval-augmented generation), where the BoxOffice assistant fetches the most relevant policy chunks and gives them to the language model (see AI Systems — RAG & memory).

flowchart LR
    Q["fan question"] --> E["embed<br/>(embedding model)"]
    E --> V[("vector DB<br/>top-k nearest")]
    V --> RR["rerank<br/>(optional)"]
    RR --> L["LLM prompt:<br/>question + chunks"]
    L --> A["answer with sources"]

Exact vs approximate search

The exact answer compares the query with every vector: for 10 million vectors of 1,024 dimensions, that is about 10 billion multiply-adds per query — fine offline, too slow for interactive traffic. Vector databases use approximate nearest neighbour (ANN) indexes that look at a small fraction of the vectors and accept missing a few true neighbours. The quality measure is recall@k: of the true k nearest, how many did the index return?

• IVF (inverted file): cluster the vectors (e.g. with k-means); at query time scan only the nprobe clusters closest to the query.
• HNSW (hierarchical navigable small world)⁷: a layered graph in which each vector links to its near neighbours; a search starts at the sparse top layer and greedily walks toward the query, layer by layer. Very good recall/latency trade-off, at the cost of memory for the graph and slower inserts. Parameters: m (links per node), ef_construction, and ef_search (how wide the search is — the recall knob at query time).
• Quantization (product quantization, scalar or binary) compresses vectors 4–32× to fit more in memory, trading a little recall.

A small, real experiment makes the trade-off visible: 20,000 synthetic 64-dimensional vectors around 200 topics, an IVF index with one bucket per topic, and 60 queries. Each point is a value of nprobe:

Comparing about 8% of the vectors already returns roughly 9 of the 10 true neighbours; each extra point of recall costs more work. Real embeddings and HNSW behave differently in detail, but the shape — a tunable recall-versus-cost curve — is the same, and it is a product decision: for RAG, a reranker after the ANN step forgives a slightly lower recall.

Where to keep vectors

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE kb_chunks (
    id        BIGSERIAL PRIMARY KEY,
    doc_id    TEXT NOT NULL,          -- 'refund-policy'
    content   TEXT NOT NULL,
    embedding vector(1024) NOT NULL
);

CREATE INDEX kb_chunks_embedding_idx
    ON kb_chunks USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 40;              -- recall knob at query time
SELECT doc_id, content
FROM kb_chunks
ORDER BY embedding <=> :question_embedding   -- cosine distance
LIMIT 5;

Sizing, as a worked example: 100 k chunks × 1,024 dimensions × 4 bytes (float32) ≈ 400 MB of raw vectors, plus the HNSW graph (with m = 16, roughly a few hundred bytes per vector) — it fits comfortably in the catalog database. At 100 M vectors (≈ 400 GB raw) quantization or a dedicated cluster becomes worth discussing.

SCHEMA = f"""
CREATE EXTENSION IF NOT EXISTS vector;
CREATE SCHEMA IF NOT EXISTS assistant;
CREATE TABLE IF NOT EXISTS assistant.documents (
    id          TEXT PRIMARY KEY,                       -- e.g. "event:123"
    kind        TEXT NOT NULL,                          -- "event" (add "faq", "policy" ... later)
    title       TEXT NOT NULL,
    content     TEXT NOT NULL,                          -- the text that was embedded
    embedding   vector({settings.embed_dim}) NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- approximate nearest neighbours (HNSW) on cosine distance, the metric of the embedding model
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw
    ON assistant.documents USING hnsw (embedding vector_cosine_ops);
"""

Choosing among them

How it connects

flowchart LR
    SOR["system of record<br/>sql"] -->|"CDC, outbox"| IDX["inverted index<br/>search"]
    SOR -->|"CDC, ELT"| DWH["warehouse, lakehouse<br/>OLAP, columns"]
    SOR -->|"keys only"| OBJ["object storage<br/>presigned URLs"]
    SOR -->|"embeddings"| VEC["vector database<br/>ANN, HNSW"]
    IDX --> RANK["ranking, autocomplete<br/>→ Case studies"]
    OBJ --> CDN["served by the CDN<br/>→ Edge"]
    DWH --> ANA["analytics off<br/>the OLTP primary"]
    VEC --> RAG["RAG memory<br/>→ AI systems"]
    SOR -.-> STREAM["change streams<br/>→ Messaging"]

Palette components: search · objstore · dwh · vectordb (the four derived stores — rebuildable from the source) · sql (the system of record; pgvector before a dedicated product) · cdn (in front of the object store) · kafka (Debezium CDC into the index and the warehouse) · analytics (reads the warehouse, never the primary) · memory (the assistant's retrieval layer). Scenarios: search-autocomplete, video-streaming, ai-assistant, event-fanout. Continues in: Edge — DNS & CDN, Messaging — patterns (outbox, CDC), AI systems — RAG and memory, Case study: search autocomplete, Case study: video streaming.