Design and tune storage for an open public dataset

What you will be able to do

Take a real public dataset of 582,849 rows and give it a storage design that you can defend with numbers. You list how the data is read, and check each rule on the raw files before the database enforces it. Then you load the data with a migration, measure the real queries, and choose indexes by measurement. At the end, you change the schema safely for new data and prove the backup with a restore drill.

Case studyPortfolio piece

Dataset

Boston 311 Service Requests, 2024 and 2025, and one week of the city's new 311 system

Licence

Open Data Commons PDDL 1.0 (public domain)

Time

About 4 hours

Runs in

VS Code, Python 3.12+, Docker

Skills used

• Access-pattern inventory
• Constraints checked on raw data
• Staging and load
• Plain-SQL migrations
• Parameterized queries
• Keyset pagination
• Measured and partial indexes
• Backup and restore drill

Download the starter project (.zip)Finished project (.zip)

Unzip the starter, open the folder in VS Code, and follow the steps below. They start with a new virtual environment.

The starter has the tools of the course. It has the settings, the migration runner and a connection pool. It also has the download with its checksum, the benchmark, the plan script and the backup scripts. You write the inventory, the migrations, the load, the queries and the tests. The finished project is the result, for when you are stuck or want to compare. The code is under the MIT licence, so you can publish your version.

Before you startWhat to know and to have readyAbout 240 minutes

What you need to know

This case study uses the whole course, on data that is not Larkfield’s. The lessons it leans on most:

• The inventory’s five questions, from Access-pattern inventory.
• CHECK, UNIQUE and foreign keys, from Integrity constraints.
• Parameters, and one file for all the SQL, from Safe queries.
• The migration runner, its checksums and -- migrate: no-transaction, from Migrations.
• Plans, composite indexes and keyset pages, from Read a query plan, Index deliberately and Bound result sizes.
• The backup manifest and the drill, from Recovery and retention.

The page teaches three things that the course did not: a staging table, a partial index, and a constraint added as NOT VALID.

What you need on your computer

• Python 3.12 or newer, VS Code, and Docker Desktop (or PostgreSQL 18 of your own, as in From SQLite to PostgreSQL).
• About 1 GB of free disk space: 341 MB of downloads, a database of about 300 MB, and a backup of 41 MB.

Tested on macOS (Apple M4, 16 GB, macOS 26.6, PostgreSQL 18.6 in Docker, Python 3.12.13). The Windows and Linux commands follow the course’s commands, but they are not tested.

The situation

A city’s 311 service takes reports about problems in public spaces. Examples are a pothole, a missed trash pickup, a dark street light, or a car on a sidewalk. Each report becomes a service request. A team of city workers handles it, and closes it when the work is done.

Rosa leads the data team of the 311 operations office. Today, every team reads the requests from a nightly spreadsheet export. The crews’ work queues are slow to open, and the public map shows yesterday’s data. Nobody checks whether a new report repeats an open one. Ken, a crew supervisor, asks the question that started the project. “Why does my queue take seconds to open? I only want the 50 oldest requests.”

Rosa wants one PostgreSQL database that serves the queues, the map and the reports. Its rules must match the city’s real data, and its slow queries must become fast with proof. Its backup must be tested. The people are invented. The requests are real: Boston publishes them as open data.

What you will build

You build boston311-storage: a database design, the scripts that load and query it, and the measurements that justify each choice.

1. An access-pattern inventory: who reads what, how often and how much.
2. A rules report on the raw files, and a schema with only the rules that the data satisfies.
3. A load: the city’s CSV files into a staging table, then into the tables, in one transaction.
4. Queries for each pattern: parameters only, with keyset pages.
5. Measured indexes: a baseline, candidates one at a time, then one migration with the winners.
6. A schema change for the city’s new 311 system, written so that it does not block the table.
7. A backup and a restore drill with 7 checks.

The data

The data is the City of Boston’s 311 Service Requests, on its open data portal, Analyze Boston. The city publishes it under the Open Data Commons Public Domain Dedication and License (PDDL 1.0). You may use, change and share it with no conditions. We still name the source. The project’s DATASET.md is the full dataset card.

Why Boston? We compared five cities’ 311 data, and DATASET.md lists them. San Francisco’s data is also public domain, but it comes only as a live export, so no checksum can stay valid. Chicago’s terms are not a standard licence. Boston publishes one fixed file per year, so the project can check each download with SHA-256, a fingerprint of the exact bytes.

File	Rows	Size
boston_311_2024.csv	306,756	182 MB
boston_311_2025.csv	276,093	159 MB
data/boston_311_new_system_2026-10-01_07.csv (in the project)	3,540	1.1 MB

Each yearly file has 30 columns: the case ID, the times (opened, target, closed), the status and the closure reason. Then the type with its reason and subject, the queue with its department, and the place (address, neighborhood, ward, districts, a point). The third file is one week from the city’s new 311 system, which started in August 2025. You meet it at the end. The project ships it without the free-text comments and photo links, which the PDDL allows.

Two facts matter for storage. The times have no time zone: they are Boston local time. And the status is a snapshot: the files were published on 18 September 2026, and a request that was open then may be closed now.

Set up the project

The steps use VS Code and its terminal. Every command runs in the project folder.

1. Download the starter project from the box at the top of this page. Unzip it into your projects folder, for example ~/projects. You get the folder boston311-storage.

2. In VS Code, choose File > Open Folder, and open boston311-storage. Then open a terminal: Terminal > New Terminal.

3. Make and activate a virtual environment, and install the packages:

   macOS

   python3 -m venv .venv
   source .venv/bin/activate
   python -m pip install -r requirements.txt

   Windows

   python -m venv .venv
   .venv\Scripts\Activate.ps1
   python -m pip install -r requirements.txt

   Linux

   python3 -m venv .venv
   source .venv/bin/activate
   python -m pip install -r requirements.txt

   The prompt starts with (.venv). The packages are psycopg 3.3.6 with its pool, pytest and ruff.

4. Copy the settings file. Then open .env and replace change-me-boston with a password of your own, in all three places:

   macOS

   cp .env.example .env

   Windows

   Copy-Item .env.example .env

   Linux

   cp .env.example .env

5. Start PostgreSQL. The Compose project is called boston311 and uses port 5433, so it can run next to the database of ticket-api on 5432:

   docker compose up -d db

6. Download the two years:

   python -m scripts.download

   Output

   boston_311_2024.csv: downloading 182 MB ...
   boston_311_2024.csv: 182 MB in 5 s, checksum OK
   boston_311_2025.csv: downloading 159 MB ...
   boston_311_2025.csv: 159 MB in 4 s, checksum OK

What this tells us. You have the same bytes that this page used. If the city publishes a revised file, the checksum differs and the script stops with a message. You can then accept the new file with --accept-changed, but your counts will differ from this page.

Step 1: the access-pattern inventory

Before any table, write down how the data is read. Rosa’s team asked the crews, the map team and the managers. The result is docs/access-patterns.md:

	Pattern	Who	Shape	How often	Rows
P1	One request by its ID	a resident on the phone	case_id = ?	all day	1
P2	Work queue: open requests of one queue, oldest first, 50 a page	crews	open, one queue, by opened_at	all day, many times	50 of up to 61,333
P3	Area feed: requests of one neighborhood, newest first, 50 a page	the public map, analysts	one neighborhood, by opened_at descending	often; analysts page deep	50 of up to 77,047
P4	Duplicate check: open requests of the same type at the same address, last 30 days	the system, before it saves a new request	address, type, open, recent	about 800 a day	0 to a few
P5	Overdue dashboard: open requests past their target, per department	managers	a count over open requests	every few minutes	11
P6	Monthly report: requests and the on-time share per department	managers, the public	a count over a year	once a month	about 150

Writes are about 800 new requests a day, and a nightly refresh from the city’s file that closes and reopens requests. P2 and P4 must be fresh at once. P6 can be a day old.

The inventory already shapes the design. P1 needs only the primary key. P2, P3 and P4 each need a fast path to a few rows. P5 and P6 read most of the table, so an index probably cannot help them. You will test each of these guesses.

Step 2: check the rules before you write them

The problem: a rule that the data breaks

Rosa’s first schema had a rule on ward: a number from 1 to 22, as Boston’s wards are. The first load stopped with this error, and with nothing loaded:

Output (end, shortened): the first load

psycopg.errors.CheckViolation: new row for relation "service_requests" violates check constraint "requests_ward_range"
DETAIL:  Failing row contains (101005234149, 2024-01-03 13:49:00+00, ..., null, 0, 1, null, ...)

The load runs in one transaction, so nothing was half-loaded. That is good. But the error shows only the first bad row. How many more are there, and of how many kinds? Fixing them one error at a time takes one load per problem.

The idea: a staging table and a rules report

Think of the customs hall at an airport. Every bag enters the hall first. The officers check the bags there, before any bag goes into the city.

A staging table is that hall. It has the columns of the raw file, all of type text, so COPY accepts every row. Then SQL counts the rows that break each rule you want, before the rule exists. A rule with 0 broken rows can become a constraint. A rule with broken rows needs a decision.

scripts.check_rules copies both files into staging_requests and counts each candidate rule:

python -m scripts.check_rules

Output

Copied boston_311_2024.csv: 306,756 rows in 1.0 s
Copied boston_311_2025.csv: 276,093 rows in 1.0 s
582,849 raw rows. Rows (or groups) that break each candidate rule:
  each case ID appears once                                       0
  case ID is 12 digits                                            0
  status is Open or Closed                                        0
  closed <=> has a closing time                                   0
  closed <=> has a closure reason                               464
  closed after opened                                             0
  target time after opened                                        0
  ward written as a plain number                            432,892
  ward between 1 and 22 (after removing 'Ward ' and zeros)      262
  council district between 1 and 9                              324
  ZIP code is 5 digits                                            0
  point in or near Boston                                         0
  each type has one reason and subject                            0
  each queue has one department                                   0
  each type has one department                                  116

Each rule is one SQL count. For example, “closed ⇔ has a closure reason” is count(*) … WHERE (case_status = 'Closed') <> (closure_reason IS NOT NULL). The 15 counts lead to three decisions:

The rules report decides each rule before the database enforces it: enforce it, clean the data first, or accept that it is not a rule.

Text description of the diagram

Two CSV files go through COPY into a staging table of 30 text columns, and a script counts the rows that break each rule. Eight rules have 0 broken rows: they become constraints (CHECK, UNIQUE, NOT NULL, foreign keys). Three need cleaning first. The ward, written three ways in 432,892 rows, becomes a number. Ward 0 (262 rows) and council district 0 (324 rows) become NULL. Then the constraints check the ranges. Two are not rules of the real data. 464 rows break “closed if and only if it has a closure reason” (143 reopened, 321 closed in bulk in June 2024). And more than one department handles 116 of the 175 types. They get no constraint, and DATASET.md explains them.

The rule that is not true

The closure reason looked like a safe rule: a closed request says why, and an open one does not. Try it on the loaded table (you load it in Step 3), in psql:

ALTER TABLE service_requests ADD CONSTRAINT requests_reason_matches_status
    CHECK ((status = 'closed') = (closure_reason IS NOT NULL));

Output

ERROR:  check constraint "requests_reason_matches_status" of relation "service_requests" is violated by some row

Output: status against reason

 status | has_reason | count
--------+------------+--------
 closed | f          |    321
 closed | t          | 462388
 open   | f          | 119997
 open   | t          |    143

The 143 open requests all have a reason that starts “Case Closed.”: they were closed, then reopened. The 321 closed requests without a reason were all closed on 10, 13 and 17 June 2024: a bulk closing. These are real events in the city’s work. A constraint would reject 464 real requests, and the 143 reopened ones are in the crews’ queues. So this is not a rule. You keep the rows, and you write the finding in DATASET.md.

Compare the ward. Ward 0 does not exist, so 0 means “not known”. NULL says exactly that, and then the range rule is true. Cleaning is right when the value has one clear meaning. Dropping the rule is right when the world does not follow it.

Step 3: the schema and the load

The schema follows the report. Types and queues get their own tables, because the report proved that a type always has one reason and subject, and a queue one department. Here is the table of requests:

migrations/001_service_requests.sql (from line 22)

CREATE TABLE service_requests (
    case_id        text PRIMARY KEY,
    opened_at      timestamptz NOT NULL,
    sla_target_at  timestamptz,
    closed_at      timestamptz,
    status         text NOT NULL,
    on_time        text NOT NULL,
    closure_reason text,
    title          text,
    type_id        smallint NOT NULL REFERENCES case_types,
    queue_id       smallint NOT NULL REFERENCES queues,
    source         text NOT NULL,
    neighborhood   text,
    ward           smallint,
    council_district smallint,
    pwd_district   text,
    zipcode        text,
    address        text,   -- a street address or an intersection, without the city
    latitude       double precision,
    longitude      double precision,

    CONSTRAINT requests_case_id_format CHECK (case_id ~ '^[0-9]{12}$'),
    CONSTRAINT requests_status_known CHECK (status IN ('open', 'closed')),
    -- A closed request has a closing time; an open one has none.
    CONSTRAINT requests_closed_at_matches_status
        CHECK ((status = 'closed') = (closed_at IS NOT NULL)),
    CONSTRAINT requests_closed_after_opened CHECK (closed_at >= opened_at),
    CONSTRAINT requests_target_after_opened CHECK (sla_target_at >= opened_at),
    CONSTRAINT requests_on_time_known CHECK (on_time IN ('ontime', 'overdue')),
    CONSTRAINT requests_ward_range CHECK (ward BETWEEN 1 AND 22),
    CONSTRAINT requests_council_district_range CHECK (council_district BETWEEN 1 AND 9),
    CONSTRAINT requests_zipcode_format CHECK (zipcode ~ '^[0-9]{5}$'),
    -- A point is either complete and in or near Boston, or missing.
    CONSTRAINT requests_point_complete CHECK ((latitude IS NULL) = (longitude IS NULL)),
    CONSTRAINT requests_point_in_boston
        CHECK (latitude BETWEEN 42.2 AND 42.45 AND longitude BETWEEN -71.2 AND -70.95)
);

The times are timestamptz, a moment in time. The city’s text “2024-01-04 10:43:17” is Boston local time, so the load reads it with AT TIME ZONE 'America/New_York'. Without that, every time would be 4 or 5 hours wrong. Eight raw columns that no pattern reads are not loaded: two photo links, a second form of the address, the geometry, and four other districts.

scripts/load.py applies the migrations and stages the files. Then it moves the rows with one INSERT … SELECT. These lines of it are the cleaning rules:

scripts/load.py (part of INSERT_REQUESTS)

    s.open_dt::timestamp AT TIME ZONE 'America/New_York',
    lower(s.case_status),
    replace(nullif(s.case_title, ''), '&', '&'),
    nullif(nullif(regexp_replace(s.ward, '^Ward ', ''), '')::smallint, 0),
    nullif(nullif(s.city_council_district, ''), '0')::smallint,

Run it, with the migrations up to 001 only. The indexes come later, after the measurements:

python -m scripts.load --to 001

Output

applied  001_service_requests
Database is up to date (1 migrations).
Copied boston_311_2024.csv: 306,756 rows in 0.9 s
Copied boston_311_2025.csv: 276,093 rows in 0.8 s
Staged 582,849 rows; loaded 582,849 requests in 10.0 s.
Cleaned: ward written 'Ward N' 374,953, ward with a leading zero 57,679, ward 0 -> NULL 262, council district 0 -> NULL 324, '&' -> '&' 109,802.
case_types 175, queues 172, service_requests 582,849. Database: 203 MB.

What this tells us. 582,849 rows went in, and 582,849 requests came out. The constraints rejected nothing, because the report found every problem first. The table is 166 MB, and the database 203 MB. The load drops the staging table at the end.

Step 4: one file for the queries

boston311/queries.py has one function per pattern. The values always travel as parameters, as in Safe queries. Lists use keyset pages, as in Bound result sizes. Here is the work queue, P2:

boston311/queries.py (P2)

def open_queue_sql(queue: str, *, limit: int = 50, after: str | None = None) -> tuple[str, dict]:
    params = {"queue": queue, "limit": limit + 1}
    keyset = ""
    if after:
        params["after_at"], params["after_id"] = decode_cursor(after)
        keyset = "AND (r.opened_at, r.case_id) > (%(after_at)s, %(after_id)s)"
    return (
        f"""
        SELECT {LIST_COLUMNS}
        FROM service_requests r
        WHERE r.status = 'open'
          AND r.queue_id = (SELECT queue_id FROM queues WHERE name = %(queue)s)
          {keyset}
        ORDER BY r.opened_at, r.case_id
        LIMIT %(limit)s
        """,
        params,
    )

The f-string inserts only fixed pieces of this file: the column list and the keyset condition. Each value travels as a parameter. Each query has a *_sql function that returns the SQL and its parameters, so scripts.explain shows the plan of the exact query that the code runs. One extra row (limit + 1) tells whether a next page exists. P3 has the same shape, newest first, with < in the keyset.

Step 5: measure the baseline

A fixed workload

A measurement means something only if you can repeat it. boston311/workload.py builds the same calls every time, from the data itself, with fixed rules and no random numbers. It takes 50 case IDs, the 20 busiest open queues, and every neighborhood with more than 5,050 requests. It adds a deep page 5,000 rows in, and 50 open requests for the duplicate check. scripts.bench runs the workload 5 times, 4 calls at a time through a pool, and prints the median of each pattern.

python -m scripts.bench

Output: keys only

Indexes on service_requests: service_requests_pkey
5 passes, 4 at a time. Median and p95 per call:
  P1 one request         50 calls  median     0.953 ms  p95     1.316 ms
  P2 work queue          20 calls  median    59.536 ms  p95    73.681 ms
  P3 area feed           19 calls  median    61.978 ms  p95    85.056 ms
  P3 deep page, OFFSET    5 calls  median    63.532 ms  p95    77.955 ms
  P3 deep page, keyset    5 calls  median    63.445 ms  p95    83.115 ms
  P4 duplicates          50 calls  median    66.125 ms  p95    91.646 ms
  P5 overdue              1 calls  median    35.901 ms  p95    37.118 ms
  P6 monthly report       1 calls  median   360.473 ms  p95   369.935 ms

Other work shared the computer while these runs ran, so read the shape, not the exact times. Your numbers will differ. A second complete run, from an empty database, gave the same shape. P1 uses the primary key and is fast. P2, P3 and P4 take about 60 ms each to return 50 rows or fewer. Even the keyset page is slow, because nothing is in order yet.

Why P2 is slow

python -m scripts.explain queue

Output (some lines)

Limit  (cost=25842.06..25848.00 rows=51 width=85) (actual time=27.913..29.898 rows=51.00 loops=1)
  ->  Gather Merge  (cost=25837.91..25930.73 rows=797 width=85) (actual time=27.911..29.892 rows=51.00 loops=1)
        Workers Planned: 2
        ->  Sort  (cost=24837.88..24838.71 rows=332 width=85) (actual time=26.649..26.655 rows=41.00 loops=3)
              Sort Key: r.opened_at, r.case_id
              ->  Parallel Seq Scan on service_requests r  (cost=0.00..24826.81 rows=332 width=85) (actual time=0.761..24.489 rows=20444.33 loops=3)
                    Filter: ((status = 'open'::text) AND (queue_id = (InitPlan 1).col1))
                    Rows Removed by Filter: 173839
Execution Time: 29.929 ms

Three processes read the whole table. Together they keep the 61,333 open parking requests (20,444 each) and sort them, to return 51. Note the estimate: the planner expected 797 rows, not 61,333. It plans before the subquery finds the queue’s ID, so it guesses an average queue. Remember this when a plan surprises you.

Step 6: index candidates, one at a time

scripts.candidates creates one candidate index and runs the queries it is meant for 5 times (EXPLAIN ANALYZE, median). Then it drops the index and tries the next. Each candidate is judged alone, on the same data.

P2, the work queue, and the partial index

Candidate	Size	Biggest queue (61,333 open)	A smaller queue (2,376 open)
none	—	29.2 ms	23.1 ms
(queue_id)	4.09 MB	42.5 ms	2.9 ms
(status, queue_id)	4.16 MB	20.7 ms	0.900 ms
(queue_id, opened_at, case_id)	28.93 MB	5.8 ms	0.039 ms
the same, WHERE status = 'open'	5.98 MB	0.032 ms	0.027 ms

The index on queue_id alone made the big queue slower than no index. It finds all 124,285 requests of that queue, open or closed, and fetches each row from a random place. (status, queue_id) finds the right group, but it must still sort it. The composite index with the order is fast, but it is 29 MB and still steps over every closed request of the queue.

The last row is a new kind of index. Think of a library’s trolley of returned books. It does not list every book of the library, only the books that wait to go back. A partial index is an index with a WHERE clause: it has an entry only for the rows that match. The work queue asks only for open requests, and only 21% of the requests are open. So the index keeps only those, sorted by queue and time:

A partial index keeps only the rows that the query asks for. Here it is the open requests, already in queue order and time order.

Text description of the diagram

Ten real requests of two small queues, 3 of them open. The full index (queue_id, opened_at, case_id) has 10 entries; to find the open requests of queue 168, the database steps over its 5 closed entries. The partial index, WHERE status = ‘open’, has only the 3 open requests, already in order: the answer and nothing else. When a request closes, its entry leaves the index; when it reopens, the entry comes back. On the whole data the two indexes are 28.9 MB and 6.0 MB.

A query can use a partial index only when its own WHERE implies the index’s condition. P2 says status = 'open' in the SQL text, so the planner sees that it fits. The partial index made both queues about 900 times faster than no index, at 6 MB. The cost: a query about closed requests cannot use it.

P3, the area feed: two pages, two answers

The rule from Index deliberately says: the = columns first, then the order. Here is why the whole rule matters. The workload has busy neighborhoods, such as Dorchester (77,047 requests). scripts.explain also has a rare one, Chestnut Hill, with 27 requests. Predict first: which single-column index is fast for each?

• No index
• One column
• Both: (neighborhood, opened_at DESC, case_id DESC)

The first page of the area feed for Dorchester (77,047 requests), with each candidate index. EXPLAIN ANALYZE, median of 5 runs.

Text description of the chart

A column chart with four bars for the Dorchester page. No index: 26.6 ms. An index on neighborhood: 28.3 ms. An index on opened_at: 0.047 ms. The composite index on both: 0.020 ms.

• No index
• One column
• Both: (neighborhood, opened_at DESC, case_id DESC)

The same candidates for Chestnut Hill (27 requests). The fast single-column index changes sides.

Text description of the chart

A column chart with four bars for the Chestnut Hill page. No index: 25.1 ms. An index on neighborhood: 0.022 ms. An index on opened_at: 25.9 ms. The composite index: 0.015 ms.

(opened_at) walks all requests, newest first, and keeps those of the neighborhood. In Dorchester it finds 51 quickly. Chestnut Hill has only 27, fewer than a page, so the walk never fills the page and crosses the whole index. (neighborhood) is the opposite: it finds Chestnut Hill’s 27 rows at once, but must sort all of Dorchester’s 77,047. Only the composite index is fast for both, because it has each neighborhood together and already newest first. It is also the largest, 36 MB. A test with only the busy page would have chosen (opened_at), and the small neighborhoods would stay slow.

P4, P5 and P6

For the duplicate check, (address) gave 0.030 ms and (address, type_id) WHERE status = 'open' gave 0.029 ms. Both are fast, because an address has few requests. The partial one is half the size (3.39 MB against 7.69 MB), so it wins.

The overdue dashboard counts the open requests past their target. Most open requests are overdue, so a partial index on sla_target_at made it slower: 53.8 ms against 35.1 ms with no index. The monthly report reads a whole year. Neither gets an index. About 35 ms every few minutes, and 0.3 seconds once a month, are fine. If they grow, run them on a schedule or on a copy of the database, not with more indexes.

What the indexes cost

Every write must update every index. The nightly refresh closes about 10,000 requests and adds 10,000 new ones. scripts.candidates timed it, in transactions that it rolled back:

The refresh (10,000 requests closed, 10,000 new) with the primary key only, with the 3 chosen indexes (45.8 MB), and with all 10 candidates (109.2 MB). Median of 3 runs.

Text description of the chart

A column chart of the refresh time. Keys only: 1.476 s. The 3 chosen indexes, 45.8 MB: 1.404 s. All 10 candidates, 109.2 MB: 1.771 s.

With the 3 chosen indexes, the difference was within the noise of this shared computer. The refresh was 5% faster in this run, and 9% slower in the repeat run. All 10 candidates made the refresh 1.2 and 1.4 times slower in the two runs, for no faster query. The partial indexes help here too: a closed request leaves them, so they stay small.

Common mistake

The index that made the queue slower

Symptom: after a new index on queue_id, the busiest work queue takes 42.5 ms instead of 29.2 ms. The smaller queues became faster, so nobody noticed at first.

Cause: the index finds every request of the queue, open or closed: 124,285 for the busiest one. The database fetches each row from a random place, then filters and sorts. For that many rows, one ordered read of the whole table costs less. A test on a small queue showed only the gain.

Diagnose: measure the busiest value and a small one, as the workload does. Then read the plan of the slow one with python -m scripts.explain queue.

Fix: an index that matches the whole query: the = columns, then the order, and a WHERE for the rows that the query wants. Here: (queue_id, opened_at, case_id) WHERE status = 'open'.

Step 7: migration 002, and the fair comparison

The winners go into one migration. CREATE INDEX CONCURRENTLY builds an index without blocking writes, so the file runs outside a transaction, as in Migrations:

migrations/002_workload_indexes.sql (from line 7)

CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_open_queue_idx
    ON service_requests (queue_id, opened_at, case_id) WHERE status = 'open';
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_area_idx
    ON service_requests (neighborhood, opened_at DESC, case_id DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_open_address_idx
    ON service_requests (address, type_id) WHERE status = 'open';

python -m boston311.migrate --to 002
python -m scripts.bench

The migration command took 0.89 s for the three indexes. Then the same workload ran on the same data, with the same code:

Pattern	Keys only	With 002
P1 one request	0.953 ms	0.689 ms
P2 work queue	59.5 ms	0.761 ms
P3 area feed	62.0 ms	0.779 ms
P3 deep page, OFFSET 5000	63.5 ms	1.7 ms
P3 deep page, keyset	63.4 ms	0.836 ms
P4 duplicates	66.1 ms	0.678 ms
P5 overdue	35.9 ms	30.2 ms
P6 monthly report	360.5 ms	317.2 ms

The benchmark measures from Python, so its times include the trip to the database: about 0.7 ms even for a key lookup. Inside the database, the plans are much shorter. The work queue page takes 0.028 ms. The area feed takes 0.020 ms. P2, P3 and P4 went from about 60 ms to under 1 ms. P5 and P6 did not change, as planned.

With the index, the deep page repeats the lesson of pagination. OFFSET 5000 reads 5,050 index entries and throws 5,000 away (rows=5050.00 in its plan): 1.0 ms in the database. The keyset page jumps to its place: 0.020 ms, for page 2 or page 1,000.

Step 8: new input from the city’s new system

The problem: data that the schema has never seen

In August 2025, Boston started a new 311 system. Its file has other column names and times in UTC with a zone. Its case IDs look like BCS-00253943, and it has six statuses: “Closed”, “In progress”, “Needs Reallocation”, “Submit”, “Apply” and “Create”. Rosa’s database must take the new requests next to the old ones. The schema says no:

Output: one new-system row, inserted in psql

ERROR:  new row for relation "service_requests" violates check constraint "requests_case_id_format"

The rule was true for the old system. Now the world has changed, so the rule must change too, with a migration.

Migration 003: change a rule without blocking the table

ADD CONSTRAINT … CHECK reads every row while it holds the strongest lock. No query can read the table until it ends. On a large table, that is the block of Migrations again. PostgreSQL has a two-step way. NOT VALID adds the rule for new rows only, with a short lock. VALIDATE CONSTRAINT then checks the old rows with a weaker lock, so reads and writes go on.

migrations/003_new_case_system.sql (from line 10)

ALTER TABLE service_requests ADD COLUMN source_system text NOT NULL DEFAULT 'legacy';
ALTER TABLE service_requests
    DROP CONSTRAINT requests_case_id_format,
    ADD CONSTRAINT requests_case_id_format CHECK (
        (source_system = 'legacy' AND case_id ~ '^[0-9]{12}$')
        OR (source_system = 'bcs' AND case_id ~ '^BCS-[0-9]{8}$')
    ) NOT VALID;
ALTER TABLE service_requests VALIDATE CONSTRAINT requests_case_id_format;

The file starts with -- migrate: no-transaction, so each statement commits on its own. The validation does not keep the lock of the statement before it. The new column has a constant default, so PostgreSQL stores the default once and does not rewrite the 582,849 rows. The new rule is stricter than the old one: each ID must fit its own system.

The migration command took 0.38 s here. On this table, the safe way saves little. The habit matters when the table is ten times bigger.

Load the week, twice

scripts/load_new.py maps the new file to the schema. “Closed” becomes closed, and the five other statuses become open. The service name becomes the type, and the team becomes the queue. It uses INSERT … ON CONFLICT (case_id) DO UPDATE, so a request that is already there is updated, not added again:

python -m boston311.migrate
python -m scripts.load_new data/boston_311_new_system_2026-10-01_07.csv
python -m scripts.load_new data/boston_311_new_system_2026-10-01_07.csv

Output (the two loads)

boston_311_new_system_2026-10-01_07.csv: 3,540 rows; 3,540 new requests, 0 updated, in 0.1 s.
boston_311_new_system_2026-10-01_07.csv: 3,540 rows; 0 new requests, 3,540 updated, in 0.1 s.

Now run the whole pipeline on the new input. The duplicate check, P4, ran for each new request against the requests opened before it. 83 of the 3,540 new requests had the same type open at the same address in the previous 30 days. The plan of P4 uses requests_open_address_idx and takes 0.25 ms. The workload, run again with the new rows, kept its shape: P2 1.0 ms, P3 0.9 ms, P4 0.8 ms.

One finding is not about speed. The new system names neighborhoods differently:

Output: one area, several names

              neighborhood              | legacy | new_system
----------------------------------------+--------+------------
 Allston                                |   2452 |        104
 Allston / Brighton                     |  45993 |          0
 Brighton                               |   4311 |        183
 South Boston                           |   5577 |        205
 South Boston / South Boston Waterfront |  47057 |          0

The area feed for “South Boston / South Boston Waterfront” stops at December 2025, but the area still has requests. No constraint can catch this: both names are valid text. Only the inventory’s question, “how is this read?”, finds it. A table that maps the names to areas is the next migration.

Step 9: back up, and prove it

The backup and the drill are the course’s scripts from Recovery and retention, with two new checks. The manifest stores the answers of three workload queries. The drill checks that the restored database gives the same answers, and that the work queue still uses its index.

python -m scripts.backup
python -m scripts.restore_drill backups/boston311-20261010-054448.dump

Output

Backup: backups/boston311-20261010-054448.dump (40.6 MB, 2.78 s)
Manifest: backups/boston311-20261010-054448.json: 3 migrations, case_types 228, queues 202, service_requests 586,389
A backup is not proven until a restore works: python -m scripts.restore_drill backups/boston311-20261010-054448.dump
PASS  dump matches its manifest
PASS  pg_restore finished: 4.0 s
PASS  same migrations: 3 of 3
PASS  same rows and contents in every table: case_types 228, queues 202, service_requests 586,389
PASS  every constraint and index is valid
PASS  the work queue uses its index
PASS  workload answers are the same: P2, P3 and P5
Restore drill: 7 of 7 checks passed.

Your file name has your own time. One real finding shaped the drill. A restored database has no planner statistics: pg_stats had 0 rows for service_requests. The overdue count then ran in one process, in 63.5 ms. After ANALYZE, it used two parallel workers and took 36.5 ms. So the drill runs ANALYZE before it checks a plan, and your restore runbook should too.

Run the tests

python -m pytest

Output

........................                                                 [100%]
24 passed in 0.35s

The tests make their own database, boston311_test, with the migrations and 304 real rows chosen by a fixed rule (tests/fixtures/). They check that the load keeps every row and applies each cleaning rule. They check that each constraint rejects its bad row, by name. They check that keyset pages never skip or repeat a row, even when a new request arrives between two pages. They check that a value never becomes SQL. And they check that the new-system load counts nothing twice. In the starter, the tests need your scripts/load.py first.

Insights and limits

• The inventory chose the indexes. Three patterns got one index each; two got none, on purpose. P2, P3 and P4 went from about 60 ms to under 1 ms in the workload.
• Measure the extremes. One index looked good on a small queue and was slower on the big one. Another was fast for a busy neighborhood and slow for a small one.
• A partial index fits a query about a small, changing set, such as open requests. It is smaller and faster, but only queries with the same condition can use it.
• A constraint states a fact about the world. Count first. Clean a value that has one clear meaning, and drop a rule that the world does not follow.
• Limits of these numbers: one shared laptop, warm caches, and 4 clients at a time. The write cost of the 3 indexes was within the noise. A real service needs the same tests on its own hardware.

Responsible use

• A request is not a problem. Several people report the same pothole. Count requests, and call them requests.
• Who reports. 311 shows where people report, not only where problems are. City workers made 42% of the 2024 requests. Do not rank neighborhoods by request counts as if they measured conditions, or the people who live there.
• A snapshot. Statuses are as the city published them. A dashboard must show the snapshot date.
• Two systems. The old and the new system have different categories and neighborhood names. A trend across August 2025 is not like for like.
• Free text and places. Titles and closure reasons are the city’s text, and addresses point to homes and businesses. The project stores no reporter’s name. Do not join the data with other sources to find out who reported what.

Make it a portfolio piece

This project shows what employers look for in a data or backend engineer. It has a design from access patterns, constraints proven on real data, migrations that do not block, measured indexes, and a tested restore.

What to put in the repository:

boston311-storage/
  README.md            what it is, the design choices, how to run and check it, the numbers
  DATASET.md           the source, the licence (PDDL), the files' SHA-256, the changes, the limits
  LICENSE              MIT, for the code
  docs/access-patterns.md
  migrations/          001 schema, 002 indexes, 003 the new system
  boston311/           config, migrate, db (the pool), staging, queries, workload
  scripts/             download, check_rules, load, load_new, bench, explain, candidates, backup, restore_drill
  tests/               24 tests and the two fixtures
  data/                the new-system week (the 2024 and 2025 files are downloaded)

1. Make a new Git repository from your project folder, and push it to GitHub. .gitignore keeps out .env, the raw files and the backups.
2. Run python -m scripts.bench --json results/after.json. Put your before and after tables in the README, with your computer’s name.
3. Make sure that python -m pytest passes from a fresh clone, and say so in the README.

How to describe it in an interview: “I designed PostgreSQL storage for 583,000 real Boston 311 requests from an access-pattern inventory. I checked every constraint on the raw files first, and chose three indexes by measurement. They took the work queue, the area feed and the duplicate check from about 60 ms to under 1 ms. A 7-check restore drill proves the backup.”

Go further: each of these is a good next step.

• Neighborhood names. A table that maps both systems’ names to one area, and a migration that uses it.
• The nightly refresh. Run load_new on the city’s full new-system file every day. Measure the refresh and the index sizes over a week.
• The monthly report. A materialized view per month, refreshed at night. Measure P6 before and after.
• Map search. Requests within a distance of a point, with a spatial index (PostGIS). Measure it as you measured P3.

Knowledge check

Check your understanding

The rules report found 464 rows that break 'closed if and only if it has a closure reason', and 262 rows with ward 0. Why did one rule become a constraint after cleaning, and the other no constraint at all?

Show the answer

Ward 0 does not exist, so the value has one clear meaning: “not known”. Storing NULL keeps that meaning, and then the rule (a ward from 1 to 22) is true. The closure-reason rule is false in the real world. 143 requests were reopened and keep their old reason. 321 were closed in bulk without one. A constraint would reject real requests, some of them in the crews’ queues. So the rows stay, and the dataset card explains them.

Check your understanding

An index on queue_id made the busiest queue slower (42.5 ms against 29.2 ms). Which index fixed it, and why can only queries about open requests use it?

Show the answer

The partial index (queue_id, opened_at, case_id) WHERE status = 'open'. It holds only open requests, already in queue and time order, so the first 51 entries are the page: 0.032 ms, at 6 MB. The planner uses a partial index only when the query’s own WHERE implies the index’s condition. The work queue says status = 'open'. A query about closed requests, or about all requests, cannot use it.

Check your understanding

With only the Dorchester page, an index on opened_at looked almost as good as the composite index (0.047 ms). What did the Chestnut Hill page show, and what does it teach about workloads?

Show the answer

With (opened_at), Chestnut Hill took 25.9 ms, as slow as no index. The index walks all requests newest first, and Chestnut Hill has only 27, fewer than a page, so the walk crosses the whole index. The composite index (neighborhood, opened_at DESC, case_id DESC) was fast for both. A workload must include the extremes of each pattern, a busy value and a rare one. Otherwise it chooses an index that fails some of the users.

Recap

• Start with the access-pattern inventory: P1 to P6, who reads what, how often, how much.
• Stage the raw files as text, and count the rows that break each candidate rule. Enforce, clean then enforce, or accept that it is not a rule.
• Load in one transaction, with the cleaning rules in SQL and the times read in the city’s time zone.
• Measure a fixed workload, then candidate indexes one at a time, with busy and rare values. Three won: a partial index for the work queue, a composite one for the area feed, a partial one for the duplicate check.
• Change rules safely for new data: a constant default, NOT VALID, then VALIDATE. Load with ON CONFLICT, so a second load adds nothing.
• Prove the backup with a drill that also checks plans and answers, and run ANALYZE after a restore.

Next: Vector search over an open document collection