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.
- 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.
- Re-run
bench.py and write a short NOTES.md, three or four lines: what the
database was actually doing, what you changed, and the before and after p95.
Worth knowing: putting EXPLAIN QUERY PLAN in front of a query makes SQLite print
what it will actually do to answer it, which index it will use or that it is going
to read the whole table. It is also worth counting how many separate SQL
statements one page costs, not just how long the page takes.
Start here
documents.py list_documents, the endpoint behind the slow page
db.py the schema and the indexes
bench.py the benchmark. You should not need to change it.
seed.py builds the database and tests/ describes today's behavior.
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 30 minutes. You do not need to finish everything. We care more
about how you approach it than about how much you get through.