Company locations: the GeoNames index is built inside the first save of every worker, 6 s and 190 MB each time #399

New issue

Closed

opened 2026-10-01 10:46:25 +00:00 by tiagoagueda · 1 comment

tiagoagueda commented

2026-10-01 10:46:25 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by reading jobs/places.py and timing places._index() against the real geonames-cities1000.txt (171,075 rows, 32 MB).

Checked again on 2026-10-01 against main at 0a5cad587: still present. The code is now at jobs/models.py:322-331 (Company.save) and jobs/models.py:349 (places.resolve in apply_location_guess), and at jobs/forms.py:352 and :398 (a location correction); jobs/places.py, fetch_geonames.py, the Dockerfile and the entrypoint are where they were.

places._index() reads the whole cities table and folds every name, ascii name and alternate name into a dictionary in Python. It is built lazily, per process, on the first places.resolve(), which is inside the first request that saves a company with a new location. Nothing builds it ahead of time, and the image runs three gunicorn workers that are each replaced after about 500 requests, so a person pays for the build again and again, and each process then keeps a large index for the rest of its life.

Where: jobs/places.py:71-104 _index (@lru_cache(maxsize=1), per process), jobs/models.py:319-327 Company.save → apply_location_guess → places.resolve (jobs/models.py:346), also jobs/forms.py:351 and :397 (a location correction); docker/Dockerfile:354 (--max-requests 500) and :362 (--workers 3); docker/entrypoint.sh:26-30 (the table fetched at first start). The comments size the table wrongly: "the table is a few megabytes" (jobs/places.py:77-78) and "The cities1000 table is twenty thousand rows" (jobs/management/commands/fetch_geonames.py:37-38).
What happens: measured on the snapshot with the real table: _index() takes 6.2 s on a desktop, produces 845,136 keys, holds 194 MB and peaks at 306 MB (tracemalloc); a later resolve("Lisboa, Portugal") takes 3 ms. After a restart, or after a worker's 500th request, the next person who adds a company with a location, saves a capture as a listing, records an application for a new employer, corrects a location or imports a spreadsheet waits those seconds, several times longer on the small machines the project targets. The save runs inside ATOMIC_REQUESTS, so on SQLite the whole instance waits with it. Each web worker that has resolved a place then carries about 190 MB more, three times over, plus the worker and scheduler processes if they save companies.
Why it matters: a Raspberry Pi-class host running three workers spends over half a gigabyte on a lookup table that is only read, and random saves take seconds for reasons nobody can see.
Fix: parse once at provisioning rather than per process: have fetch_geonames write an indexed lookup beside the text file (for example a small SQLite database keyed on the folded name) and have resolve() query it. At the least, index the primary and ascii names plus alternates in the languages Postulo offers rather than every alternate, and build the index at worker start (gunicorn --preload, or on the scheduler's and web process's first tick) so no request pays for it. Correct the two comments. jobs/esco.py loads the ESCO classification the same lazy way and would gain from the same change.

Done when

A test in tests/test_places.py saves a company with a location while counting the rows read from the cities file, and fails today because the whole file is parsed on that save; or, with a lookup database, asserts that resolve never calls _index.
tests/test_fetch_geonames.py checks that the command writes the lookup it produces.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by reading `jobs/places.py` and timing `places._index()` against the real `geonames-cities1000.txt` (171,075 rows, 32 MB). Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. The code is now at `jobs/models.py:322-331` (`Company.save`) and `jobs/models.py:349` (`places.resolve` in `apply_location_guess`), and at `jobs/forms.py:352` and `:398` (a location correction); `jobs/places.py`, `fetch_geonames.py`, the Dockerfile and the entrypoint are where they were. `places._index()` reads the whole cities table and folds every name, ascii name and alternate name into a dictionary in Python. It is built lazily, per process, on the first `places.resolve()`, which is inside the first request that saves a company with a new location. Nothing builds it ahead of time, and the image runs three gunicorn workers that are each replaced after about 500 requests, so a person pays for the build again and again, and each process then keeps a large index for the rest of its life. - **Where:** `jobs/places.py:71-104` `_index` (`@lru_cache(maxsize=1)`, per process), `jobs/models.py:319-327` `Company.save` → `apply_location_guess` → `places.resolve` (`jobs/models.py:346`), also `jobs/forms.py:351` and `:397` (a location correction); `docker/Dockerfile:354` (`--max-requests 500`) and `:362` (`--workers 3`); `docker/entrypoint.sh:26-30` (the table fetched at first start). The comments size the table wrongly: "the table is a few megabytes" (`jobs/places.py:77-78`) and "The cities1000 table is twenty thousand rows" (`jobs/management/commands/fetch_geonames.py:37-38`). - **What happens:** measured on the snapshot with the real table: `_index()` takes 6.2 s on a desktop, produces 845,136 keys, holds 194 MB and peaks at 306 MB (`tracemalloc`); a later `resolve("Lisboa, Portugal")` takes 3 ms. After a restart, or after a worker's 500th request, the next person who adds a company with a location, saves a capture as a listing, records an application for a new employer, corrects a location or imports a spreadsheet waits those seconds, several times longer on the small machines the project targets. The save runs inside `ATOMIC_REQUESTS`, so on SQLite the whole instance waits with it. Each web worker that has resolved a place then carries about 190 MB more, three times over, plus the worker and scheduler processes if they save companies. - **Why it matters:** a Raspberry Pi-class host running three workers spends over half a gigabyte on a lookup table that is only read, and random saves take seconds for reasons nobody can see. - **Fix:** parse once at provisioning rather than per process: have `fetch_geonames` write an indexed lookup beside the text file (for example a small SQLite database keyed on the folded name) and have `resolve()` query it. At the least, index the primary and ascii names plus alternates in the languages Postulo offers rather than every alternate, and build the index at worker start (gunicorn `--preload`, or on the scheduler's and web process's first tick) so no request pays for it. Correct the two comments. `jobs/esco.py` loads the ESCO classification the same lazy way and would gain from the same change. ## Done when - A test in `tests/test_places.py` saves a company with a location while counting the rows read from the cities file, and fails today because the whole file is parsed on that save; or, with a lookup database, asserts that `resolve` never calls `_index`. - `tests/test_fetch_geonames.py` checks that the command writes the lookup it produces.

tiagoagueda added this to the 0.6.0 milestone

2026-10-01 10:46:25 +00:00

tiagoagueda added the

enhancement

label

2026-10-01 10:46:25 +00:00

tiagoagueda referenced this issue

2026-10-03 01:59:10 +00:00

Maps on OpenStreetMap data: choose the engine, the precision and where the tiles come from #700

tiagoagueda referenced this issue

2026-10-03 01:59:11 +00:00

Place a company on the map from its Wikidata coordinates #701