The documents page takes forever
Context
An attorney clicks into a matter, and the documents list sits empty for a long moment before
anything appears. That list is GET /api/matters/<id>/documents: the matter's patent documents,
newest first, each one showing the drafting attorney's name and how many comments it has. It was
quick at launch, when the biggest firm on the platform had a few thousand documents. Those firms
now have hundreds of thousands, and this endpoint is the slowest thing on the dashboard.
This repo is that code, with SQLite in place of PostgreSQL so it runs on a laptop with nothing
installed. The seed script builds data with the same shape as production: a handful of very large
matters and a long tail of small ones.
Your task
- Measure before you change anything. Run
python3 seed.py, then python3 bench.py, and write
the numbers down. p50 is the middle request time. p95 is the time the slowest 5 percent of
requests take, and p99 the slowest 1 percent. Those last two are what people feel.
- Find out what the database is really doing. Putting
EXPLAIN QUERY PLAN in front of a query
makes SQLite print its query plan, which is what it will actually do to answer that query:
which index it uses, or that it is reading the whole table. Run it on the query this endpoint
sends, with and without the filters. It is also worth counting how many separate SQL
statements one page costs, not just how long the page takes.
- Fix it. You can change the schema and the indexes in
db.py and the query code in
documents.py. Two things have to stay put. list_documents(db, matter_id, options) must
return the same results for the same data: the same documents in the same order, the same
documents, page, page_size and has_more keys, and the same keys inside each document
dict. And every table and index must still be created by create_schema() in db.py, because
the seed script, the benchmark and the tests all build their databases through it. Extra
options and extra top-level keys are fine to add.
- Optional, if there is time: offer a second way to page through a matter that does not get
slower the further in you go, alongside the existing page numbers rather than instead of them.
- Re-run the benchmark on the same seeded data, then write
NOTES.md (5 to 10 lines): your
before and after p50, p95 and p99, what each change bought you, and the trade-offs you took
on. Say what a new index costs on writes and on disk, what is still slow and why, and how you
would add the index to a live table that people are writing to all day.
What's here
db.py connection settings plus SCHEMA_SQL and create_schema(): all tables and indexes
documents.py list_documents(), the function behind GET /api/matters/<id>/documents
seed.py deterministic seed script: 2,000 matters, 5,000 users, 200,000 documents, 400,000 comments
bench.py times the request shapes from a day of access logs and prints p50/p95/p99
tests/ existing unit tests describing today's behavior (they pass now, keep them passing)
documents.db is written by the seed script and is not checked in.
Running it
python3 seed.py # about 5 seconds, writes documents.db (~110 MB)
python3 bench.py # p50/p95/p99 per request shape and overall
python3 bench.py --repeats 40 # more samples, steadier percentiles
python3 -m unittest discover -s tests -v # the unit tests
python3 seed.py --documents 20000 --comments 40000 --db small.db # a smaller database
Python 3.11, standard library only. Nothing to install.
Time
Aim for about 25-30 minutes. You don't need to finish everything; we care more about how you
approach it than about completeness.