By Township Canada
Energy Bundle Parcel Reports Got Faster: Drought Monitor in 5 Seconds, Crown Tenure Paginated
The Energy Bundle's drought vs production card went from 41 minutes to 5 seconds, and the expiring Crown tenure list now paginates in SQL instead of loading 60,000 rows into memory.
On August 21, a single parcel report request took the Township Canada database offline. One user opened the drought vs production card on the Energy tab, and the query behind it ran for 41 minutes. CPU hit 100% and stayed there. Memory filled and pushed into swap. Every other request on the platform waited behind it.
That same card, the same data, the same query shape, now loads in about 5 seconds cold and instantly once the cache is warm. This post covers what broke, why it broke, and the three things we changed to fix it. It also covers a quieter improvement to the expiring Crown tenure list that went out in the same set of changes.
What the drought vs production card shows
The card sits on the Energy tab of the parcel report sidebar. It maps basin-wide drought severity from the Canadian Drought Monitor against 12-month oil, gas, and water production from Petrinex, aggregated per LSD across Alberta, Saskatchewan, British Columbia, and Manitoba. The point is operational awareness: if a drought class is worsening across a region where your wells are producing, you want to know before it affects water disposal or surface access.
The data behind the card comes from app.drought_monitor_current (58 national drought polygons, each with a severity class) joined against lsd_production_12mo (trailing production by LSD). The endpoint is /api/report/energy/drought-vs-production, and it is the same query the AI assistant's drought tool uses.
Three compounding problems
The query was slow because three separate issues stacked on top of each other:
The spatial join could not use its index. The drought monitor table holds 58 continent-scale MultiPolygons. Each polygon's bounding box covers most of Canada. When PostgreSQL checks a GiST spatial index, it first tests bounding-box overlap, then runs the exact geometry test (ST_Intersects) on the candidates. With bounding boxes that span the country, every LSD in the production table passed the bounding-box filter, and every one went through the expensive exact-geometry test. The index existed but did nothing useful.
A redundant join doubled the I/O. The query joined public.lsd_lookup to get geometry for each LSD, even though lsd_production_12mo already carries geometry in its own column (verified to be identical across a 2,000-LSD sample). lsd_lookup is a 6.5 GB table with 11.98 million rows, sitting on a host with 4 GB of memory and 1 GB of shared buffers. Random probes into a table that large, on a host that small, meant every probe pushed something else out of the buffer pool and eventually pushed the entire working set into swap.
No caching or timeout. The drought data is national and identical for every caller, but the card renders on every parcel report. Each viewer who opened the Energy tab started their own full scan. Two concurrent viewers meant two 41-minute queries fighting for the same memory. Nothing bounded the query, so it ran until it finished or the host ran out of resources.
How it is fixed
Subdivided drought polygons. A new table, app.drought_monitor_subdiv, splits those 58 continent-scale MultiPolygons into approximately 7,150 smaller pieces, each carrying the same severity class as its parent. The pieces are small enough that their bounding boxes are useful: the GiST index now filters out most of the production table before ST_Intersects runs on the rest. The results are identical to the original join. The query time dropped from 41 minutes to about 3.8 seconds.
Removed the redundant join. The query now reads geometry directly from lsd_production_12mo instead of joining lsd_lookup. That eliminates approximately 153,000 random lookups into the 6.5 GB table. On a host with 4 GB of memory and 1 GB of shared buffers, those lookups were enough to flush the buffer pool and push the entire working set into swap.
Single-flight cache with a 30-second timeout. The result set is now cached so that concurrent callers share one query instead of each running their own. If the query exceeds 30 seconds (which it no longer does, but the guard is there), it is killed rather than being allowed to run for 41 minutes. The Energy Bundle entitlement check still runs per request ahead of the cache, so only subscribers see the data.
Crown tenure pagination: 60,700 rows to one page
The expiring Crown tenure list on the Energy tab had a separate problem. The endpoint at /api/report/energy/tenure/expiring lists Crown petroleum and mineral dispositions (PNG and mineral) approaching their expiry date across Alberta, Saskatchewan, British Columbia, and Manitoba. At the maximum query window (3,650 days), that covers about 60,700 rows between app.petroleum_tenure and public.mineral_tenure.
The old implementation fetched all of them, computed a PostGIS centroid (ST_PointOnSurface) for each row, concatenated the two sets in JavaScript, sorted them, and sliced out one page of 50. Every request paid the full centroid cost for 60,700 rows and only used 50.
The new implementation pushes the UNION, ORDER BY, and pagination into SQL. Only the rows on the requested page run ST_PointOnSurface. The query is bounded by a statement timeout.
The same change also fixed a quiet reporting bug. About 92% of rows in petroleum_tenure and 78% in mineral_tenure store area_ha as a numeric NaN (an upstream ETL issue that remains open). The old JavaScript code summed area with Number(r.area_ha) || 0, and since NaN is falsy in JavaScript, those rows silently counted as zero hectares. NaN is now normalized to NULL at the SQL boundary, which makes the handling explicit while keeping the response shape unchanged.
Also in this release: API multi-key support
A separate change landed in the same week. The Developer Portal previously refused to create a second API key when an active key already existed on the same API product and tier. That guard was meant to prevent accidental duplicate purchases, but it also blocked the most common reason to hold two keys: separate credentials for development, staging, and production, all on the same quota tier.
The guard now asks instead of refusing. When you click Add Key on an API product you already subscribe to, the Developer Portal shows which keys are already active and lets you confirm. Nothing reaches Stripe until you do. The accidental-repeat protection still holds, but the deliberate case works. For background on API key management, see Unlimited API Keys.
What to do next
If you subscribe to the Energy Bundle, open the Energy tab on any parcel report and check the drought vs production card. If you tried it before and gave up because it timed out or the page hung, try it again. The same card, the same data, and it loads in seconds.
If you use the expiring tenure report at /api/report/energy/tenure/expiring in your own tools or workflows, the response shape has not changed. Pagination, filtering, and sorting work the same way. The page just arrives faster, and the area figures are more honest about what the upstream data actually contains.
The Energy Bundle is available on Pro and Business plans at $50 CAD per month. For a broader look at what the bundle includes, see Behind the Map: Energy and Agriculture Data Sources and the oil and gas industry page.