Skip to content

Instantly share code, notes, and snippets.

@AlessandroMinali

AlessandroMinali/1brc.sql

Created August 30, 2025 17:59
Show Gist options
  • Save AlessandroMinali/d5126b5362a43da98dad0f6d7e7237a3 to your computer and use it in GitHub Desktop.
Save AlessandroMinali/d5126b5362a43da98dad0f6d7e7237a3 to your computer and use it in GitHub Desktop.
Download ZIP
1brc in sqlite3
Raw
1brc.sql
PRAGMA synchronous = OFF;
PRAGMA journal_mode = OFF;
PRAGMA temp_store = MEMPORY;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA ignore_check_constraints = TRUE;
PRAGMA automatic_index = FALSE;
PRAGMA busy_timeout = 0;
PRAGMA wal_autocheckpoint = 0;
.mode csv
.separator ;
BEGIN TRANSACTION;
CREATE TABLE temperatures(city TEXT, temperature REAL);
.timer ON
.import measurements.csv temperatures
COMMIT;
CREATE INDEX idx_city_temp ON temperatures(city, temperature);
select city, min(temperature) min_t, avg(temperature) avg_t, max(temperature) max_t
from temperatures
group by city;
@AlessandroMinali
Copy link
Author

https://github.com/gunnarmorling/1brc attempt in sqlite3

Original size: 13.8gb
CSV import to sqlite size: 26.3gb(~2x increase from original)
time to query 630s
time to index 1100s, size: 52.8gb(~4x increase from original)
time to query with index 130s

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment