Menu

The last month is always a lie: lessons from querying 1 billion rows of Brazilian crime data
📰

The last month is always a lie: lessons from querying 1 billion rows of Brazilian crime data

DEV Community: postgres·Alexandra de Lima Diniz·18 days ago
#4gxfYo1K
#devtopostgresql#devto#dev#state#paulo
Reading 0:00
15s threshold

We run Crime Brasil , a site that turns official Brazilian public-safety data into maps and rankings for every neighbourhood and municipality in the country. Under the hood that is about 1 billion rows: police reports from state security departments, health-ministry mortality records, court and prison data, traffic and more.

Most of the hard problems are not about scale. They are about data that looks right and isn't. Here are two rules we now apply before any number leaves the database, plus what they say about São Paulo in 2026.

Rule 1: the last month in the file is always incomplete

State police data arrives in batches. A report filed today may describe a crime from last week, so the newest month keeps growing for weeks after it "ends".

Here is the São Paulo police-report feed by day, at the end of our current extract:

Day Reports

2026-08-26 2,459

2026-08-27 2,413

2026-08-28 2,275

2026-08-29 2,061

2026-08-30 1,665

2026-08-31 994

A normal day in São Paulo is 2,400 to 2,900 reports.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.