The map rebuild and its speed test: what happened, what we learned (2026-09-24)

This is a plain-language record of one day's work on the Complied map. It covers what Charlie asked for, what got built, how it was tested, what worked, what did not, and what I recommend next. Read it top to bottom, or jump to The short version.

All of the work is on the branch map-query-rebuild. Nothing has gone to production.

The short version

Charlie wanted the map filters to match how he thinks: department → category → order number → status. He also wanted "on the public record" vs "already in Complied". And whatever he picks, the pins, the list and the citywide totals must all show the same filtered result.
Another agent built that (commit 8cc1ca9). It uses Postgres only: a slim table of open violations, one query function, and a result cache.
I speed-tested it on 38.7 million violation rows (about prod size) on your laptop, without touching production.
Narrow questions are fast (lead, overdue lead, 616 in Brooklyn): about 1 second the first time, and under 0.1 seconds after that.
Broad questions are too slow (every building, all violations, presumed lead): 11–14 seconds the first time. Supabase stops logged-in users' queries after 8 seconds, so on prod these would fail with an error. Even when cached they take 1–2.4 seconds. The nightly update takes about 2 minutes, which probably will not fit inside the sync function's time limit.
I then ran the same 10 questions in DuckDB, a column database. Every one answered in 0.02–0.23 seconds with no cache at all. It gave exactly the same building counts, and it also handled questions over all 38.7M rows of history in half a second.
My recommendation: move the map's counting to DuckDB. Postgres stays the main database for everything else. The details, costs and trade-offs are further down.

1. What Charlie asked for

His complaint: the map was hard to use for real work. When he picked a filter, the numbers did not follow it. The citywide totals only knew about boroughs, the pins only knew a few counts, and the ranked list only looked at a 20,000-building sample. So he saw "the unfiltered city with a filter chip on top".

What he wants:

A searchable window onto all public NYC building data, not just lead.
Filters by department (HPD, DOB, ECB/OATH, FDNY, DOHMH, DSNY), category (lead, pests, bedbug, mold, boilers…), order number (lead 616–626, bedbug 1507…) and status. Status means open, overdue, contested, postponed, and HPD's own status names such as "NOV SENT OUT".
Public record vs already in Complied. For example, buildings with an open violation where we have no project or filing yet.
One answer everywhere. The pins, the list and the totals all show the same filtered world.
Later: Zillow-style building pages, and a plain-English search box.

His full list of filter categories is in the "THE MAP" section (MAPFILTERS, MAPFLAGS, DOCLIB) of the Compliance Service Ledger artifact: https://claude.ai/code/artifact/67685070-9496-4a61-860b-14402adcc976

2. Why the old map could not do it

The old map got its numbers from three different places, and each knew a different amount:

What you saw	Where it came from	What it could filter by
Pins and coloured squares	Pre-built map tiles	Only a few counts baked in: open lead, overdue lead, DOB, ECB, year, borough
Ranked building list	`map_buildings_in_view`	More filters, but only over a 20,000-building sample
Citywide totals	`map_citywide_totals()`	Only borough

The data Charlie wanted mostly already existed in public_events: order numbers, HPD's detailed statuses and due dates. The map just never read it.

3. The options we talked through

Before any code was written, we compared these approaches:

Option	In one line	What we decided
Tags per building	A list like `hpd:616:overdue` on each building	Too rigid. Every new kind of question needs new tags, and "overdue" depends on today's date
Search engine (Elasticsearch, Typesense)	Built to find the best 20 matches	Its counts of distinct buildings are approximate, and Charlie needs exact totals. Not a fit
Column database (ClickHouse, DuckDB)	Stores data by column, so counting millions of rows is very fast	Strong fit, but it adds a second system. We parked it and tried Postgres first
Just Postgres	A slim table plus smart indexes plus a cache	Chosen to try first: no new systems, and the data is already there
Redis cache	Remembers answers to questions already asked	Not needed. The data changes once a day, so a cache table inside Postgres does the same job

The LLMs question: yes, but only in one small place. A search box where Charlie types "overdue lead in Brooklyn we haven't touched", and a small fast Claude model turns it into the same filter the menus produce, shown as chips he can check. The LLM never writes SQL and never produces a number. It needs a searchable list of real status names, so "NOV sent" finds NOV SENT OUT. That is the one bit of "retrieval" worth doing, and plain text search is enough. There is no need for a vector database. Not built yet. It comes after the filters are solid.

Costs we looked at:

Supabase compute: Large, 8 GB RAM, about $110/month. XL, 16 GB, about $210. 2XL, 32 GB, about $410. (Supabase pricing)
ClickHouse Cloud: about $0.39 per compute-hour, and it turns off when idle. Storage is about $25/TB-month, and there is a $300 trial credit. Roughly $40–70/month for light use, or about $285/month if always on. (ClickHouse pricing)
DuckDB itself is free: it is a library, not a service. You pay only for wherever you run it (see section 9).

4. Getting enough data to test with

We needed prod-sized data (prod has 35M+ violation rows) without testing on production.

Try 1: copy prod down (cancelled). I wrote scripts/pull-prod-snapshot.sh. It copies prod's buildings and public_events into a separate local database, complied_prod, so your normal local data stays untouched. It worked, but prod sent data at about 0.55 MB per second, which means about 3 hours. You cancelled it.

⚠ Security note: the first version of that script put the database password inside the connection string. On Linux, command-line arguments are visible to anyone on the machine (ps), and the password showed up in this session's output. The script now reads the password from PGPASSWORD and refuses a URL that contains one. Consider changing the prod database password in the Supabase dashboard.

Try 2: copy local data 7 times (used). Your local database has 5.5M real violation rows. scripts/map-bench/build-bench-db.sh copies them 7 times into a separate local database, complied_bench, for 38,690,253 rows. Each copy is moved onto different buildings, so violations spread across the city the way they do in prod. It takes about 40 minutes.

The counts in complied_bench are fake, because every violation exists 7 times. Only the timings mean anything.
My mistake, then fixed: the first build blanked raw_data to save space. The new map code reads 8 fields out of raw_data (order number, HPD status and so on), so the rebuild keeps exactly those 8.

The local ingest (for everyday development). The ingest tool runs feeds one after another, but you can run one terminal per feed at the same time. Each feed has its own bookmark, so that is safe. Never run the same feed in two terminals. Set NYC_OPEN_DATA_TOKEN first, or NYC Open Data throttles you. Locally, only hpd-violations had started going back in time (to Jan 2023).

5. What the other agent built (commit `8cc1ca9`)

In simple terms:

map_events: a slim copy of public_events. It holds only open violations, has no raw JSON, and each row is tagged with department, category, order number and status. The nightly sync updates it by reading only the rows that changed (using updated_at).
map_query(filter, map area, zoom): the one question the map asks. It returns the totals, the count inside the visible area, the ranked list, and the pins (dots when zoomed in, grid squares when zoomed out). All of them come from the same set of buildings.
A cache. The first time anyone asks a given question, the answer (one row per matching building) is saved, keyed by filter + last sync + today's date. The next person gets the saved copy.
map_taxonomy(): the menus (departments → categories → order numbers → status names), with live counts.
The UI: a tree of record types plus a status picker, built from Charlie's artifact.
Docs: docs/MAP_MODEL.md was rewritten. Follow-ups are in docs/LIFECYCLE_ASSESSMENT.md §8 #20.

Build, lint and tests were green when it was committed. It has not been opened in a browser yet.

6. How I tested it

scripts/map-bench/apply-map-migration.sh did on complied_bench what prod would have done with the migration as it stood that day: apply the same migration file, then run the same one-time full fill, refresh_map_events(true). That was verified at the time. It can no longer be reproduced from the repo as it stands: 20260924120000 has since been trimmed to the taxonomy and map_event_source, and refresh_map_events no longer exists (the script is kept as a historical record; the pre-trim migration is git show 9072062:supabase/migrations/20260924120000_map_query.sql). The only differences from prod, then:

The "who is logged in" function (current_tenant_id()) is replaced by a stand-in that returns a fixed test tenant, because a test database has no logins.
raw_data holds only the 8 fields the code reads. That makes the one-time fill a bit faster than on prod. It does not affect the map's query times, because the map never reads raw_data.

scripts/map-bench/time-lenses.sh then times 10 real questions ("lenses"), each in 4 ways:

Cold: the first time anyone asks it, citywide.
Warm: the same question again, citywide.
Pan: moving around at zoom 13.
Street: zoomed in to a block at zoom 16.

A mistake worth remembering: my first run put all the calls in one database transaction. That made first-time answers look up to 20× slower than they really are, because each one waited behind the rows the previous ones had just written. Real users send separate requests, so the numbers below are from the fixed run (one transaction per call).

7. Results: Postgres (the committed design)

Setup steps:

Step	Time	Notes
Apply the migration	51 s	Almost all of it is building a new index on `public_events.updated_at`
One-time full fill (`refresh_map_events(true)`)	16 min 10 s	Produces 10.0M open rows (2.2 GB). Run it by hand once on prod, via `psql`
Nightly update after a normal day (~1.3% of rows changed)	2 min 3 s	About half is re-building yesterday's popular questions ahead of time

The 10 lenses (the building counts are fake, as explained above):

Lens	Cold	Warm	Pan z13	Street z16	Buildings
616 overdue, Brooklyn	0.7 s	35 ms	24 ms	10 ms	2,997
618 child at risk	0.2 s	34 ms	30 ms	14 ms	9,016
Open lead	1.0 s	112 ms	64 ms	50 ms	48,490
Overdue lead	1.0 s	107 ms	62 ms	47 ms	45,400
Our portfolio	0.2 s	219 ms	220 ms	220 ms	5
Pests + bedbug	5.2 s	0.8 s	404 ms	342 ms	389,209
"NOV SENT OUT"	7.1 s	1.0 s	508 ms	395 ms	457,292
All violations	11.6 s	1.6 s	724 ms	615 ms	719,875
Presumed lead prospects	11.8 s	1.8 s	833 ms	697 ms	831,206
Every building	13.9 s	2.4 s	1.1 s	938 ms	1,082,616

What is wrong, in plain words

Broad questions fail the first time. Supabase stops logged-in users' queries at 8 seconds (authenticated has statement_timeout=8s). Three lenses take 11–14 s, and "NOV SENT OUT" at 7.1 s is on the edge. Charlie would see an error.
Broad questions are slowish even when cached: 1–2.4 s citywide, and about 1 s even at street level. Every request re-counts the whole cached answer, up to 1.08M rows, even when the screen shows one block.
The nightly update probably won't fit. It takes about 2 minutes and runs inside the sync edge function, once per feed (about 6 times a night), after that feed's own sync. The code already records a ~150-second gateway limit on that function.
Ingestion gets slower. The new index on updated_at, a column every sync write changes, means every write now updates all 8 indexes on the 38.7M-row table instead of just the row. (Postgres calls this losing "HOT updates".) A synthetic 500k-row update took 12 minutes. That number is exaggerated, but the direction is real.
The cache grows fast. One broad question saves about 1M rows. Twenty different broad questions in a day is 20M rows, cleared only on the next sync or the next day.

How it could be fixed while staying on Postgres

Build every lens preset ahead of time during the nightly update, so presets are never cold.
Save the totals when a cached answer is built, and read only the rows inside the map area. Warm, pan and street times should drop to milliseconds.
Make first-time broad questions cheaper, for example with a per-building × category count table.
Run the map refresh once a night as its own database cron job, not inside each feed's edge function call.
Track changed rows another way (a small change-log table, or a BRIN index) instead of the updated_at index.

That is a lot of extra machinery, and each item adds something new to maintain.

8. Results: DuckDB (a column database), same data, same questions

scripts/map-bench/duckdb-lenses.py exports the same data from complied_bench into Parquet files, DuckDB's native file format, and answers the same 10 lenses the way map_query does: per-building counts, totals, the top 500, and grid squares. It has no cache: every call starts from scratch.

Export (what a nightly job would do):

What	Time	File size
`map_events` (10M open rows)	2.5 s	0.18 GB
`buildings` (1.1M)	0.2 s	0.06 GB
All `public_events`, slim columns (38.7M rows, full history)	76.5 s	0.96 GB
Load it all into memory	2.2 s

The 10 lenses: no cache, every call, from scratch:

Lens	City z10	Pan z13	Street z16	Buildings
616 overdue, Brooklyn	29 ms	24 ms	28 ms	2,997
618 child at risk	24 ms	30 ms	28 ms	9,016
Open lead	47 ms	38 ms	38 ms	48,490
Overdue lead	40 ms	40 ms	41 ms	45,400
Our portfolio	138 ms	151 ms	146 ms	5
Pests + bedbug	115 ms	126 ms	107 ms	389,209
"NOV SENT OUT"	142 ms	134 ms	156 ms	457,292
All violations	228 ms	210 ms	205 ms	719,875
Presumed lead prospects	200 ms	181 ms	175 ms	831,206
Every building	202 ms	179 ms	201 ms	1,082,616

The building counts match Postgres exactly on all 10 lenses, so both engines are answering the same question.

History, over all 38.7M rows. Postgres can't answer these at all in the current design, because map_events holds only open violations:

"Buildings with more than 3 HPD violations since 2020": 251 ms
"Distinct buildings per HPD status since 2015": 529 ms

Postgres vs DuckDB, side by side

	Postgres (committed design)	DuckDB (test)
Worst first-time question	13.9 s (fails the 8 s limit)	0.23 s
Worst repeat question	2.4 s	0.23 s (same every time)
Needs a result cache	Yes, with warming, growth and invalidation	No
History questions (all 38.7M rows)	Not possible in this design	~0.25–0.5 s
Nightly work	2 min incremental, 16 min full, plus a costly index	~80 s full export
Slows down ingestion	Yes (the `updated_at` index)	No
New system to run	No	Yes, one small service

Be honest about the test:

It ran on your laptop (12 cores, 46 GB RAM) with the data already in memory. A smaller server would be slower, but DuckDB needs only about 1–2 GB of RAM for this data.
DuckDB read the map_events table that Postgres had already prepared (with departments, categories and statuses worked out). In a real setup, that preparation step either stays in Postgres or moves into the export.
It was a single user. Many users at once still needs testing, although DuckDB handles many readers well.

9. My recommendation: DuckDB for the map, Postgres for everything else

The Postgres design works for narrow questions. But the main point of this work was "handle a huge number of rows, any filter, and history", and there it needs a pile of workarounds: a cache, warming, timeouts, a change-tracking index and a nightly job squeezed into an edge function. DuckDB answered everything in under a quarter of a second with none of that. With FDNY and more feeds on the way (100M+ rows), the gap will only grow.

What the setup would look like:

NYC feeds ──► Postgres / Supabase  (unchanged: app, logins, tenants, all writes)
                  │  nightly: export slim tables to Parquet (~80 s)
                  ▼
            Parquet files in storage (Supabase Storage or Cloudflare R2, ~1 GB)
                  │
                  ▼
            Small map service running DuckDB (holds ~1–2 GB in memory)
                  ▲
   /map ── filter + the user's login token ──┘   returns totals + list + pins

Postgres stays the source of truth. Nothing about ingestion, logins or tenants moves.
Only public data goes into DuckDB. Tenant-specific parts ("our buildings", "contested") are looked up per request, the service's list of tracked buildings is small, and the service must check the login token first.
The frontend barely changes. src/data/mapQuery.ts calls the service instead of the map_query RPC, with the same filter object and the same response shape. The UI the other agent built stays as it is.
What gets deleted: map_query_cache, its rows table, the warming, the updated_at index, and the refresh inside the sync function.

Where the service could run:

A small always-on VM or container (Fly.io, Hetzner, Railway…). Roughly $10–40/month for 2–4 GB of RAM. These prices are from memory, so check them.
MotherDuck (hosted DuckDB). Less to run, but check the current price.
DuckDB inside Postgres (the pg_duckdb extension). That would mean no new service, but check whether Supabase allows it. I have not verified that.

DuckDB's downsides:

One more thing to deploy, monitor and secure.
It must enforce logins itself, because Postgres row-level security does not follow the data into DuckDB.
The data is as fresh as the last export (nightly, the same as today). A refresh during the day would mean another export.
It is new tech for the team to learn.

If you'd rather not add a service yet: do the five Postgres fixes in section 7. Narrow questions are already fine. Broad ones could probably get under the 8-second limit, but not anywhere near DuckDB's speed, and history questions would stay out of reach.

ClickHouse is the other column database. It's worth it if the data outgrows one machine's memory or you need several servers. At 35–100M rows, DuckDB is simpler and enough.

10. Where everything is

Thing	Where
The map rebuild	commit `8cc1ca9`, on branch `map-query-rebuild`
Benchmark scripts, this document, the prod-copy script	the second commit on the same branch
The migration	`supabase/migrations/20260924120000_map_query.sql`
The map's model and vocabulary	`docs/MAP_MODEL.md`
Follow-up list	`docs/LIFECYCLE_ASSESSMENT.md` §8 #20
Test database, 38.7M rows (fake counts)	local `complied_bench` (port 54322, ~23 GB)
Your normal local data (untouched)	local `postgres` database
Benchmark scripts	`scripts/map-bench/` (build-bench-db, apply-map-migration, time-lenses, duckdb-lenses)
Prod copy script (fixed; don't need it now)	`scripts/pull-prod-snapshot.sh`

To delete the test database when you're done: psql postgresql://postgres:postgres@127.0.0.1:54322/postgres -c 'drop database complied_bench'

11. Still open

This list has moved. The live items are in docs/LIFECYCLE_ASSESSMENT.md §8 #20, the repo's one next-fix list. The decision this section once tracked (DuckDB) is made and built, the one-time fill it mentioned no longer exists, and the LLM search box has shipped.

The short version​

1. What Charlie asked for​

2. Why the old map could not do it​

3. The options we talked through​

4. Getting enough data to test with​

5. What the other agent built (commit 8cc1ca9)​

6. How I tested it​

7. Results: Postgres (the committed design)​

What is wrong, in plain words​

How it could be fixed while staying on Postgres​

8. Results: DuckDB (a column database), same data, same questions​

Postgres vs DuckDB, side by side​

9. My recommendation: DuckDB for the map, Postgres for everything else​

10. Where everything is​

11. Still open​