SQLite performance tuning: concurrent reads, multiple GBs and 100k SELECTs/s

This page summarizes the projects mentioned and recommended in the original post on news.ycombinator.com

Our great sponsors
  • InfluxDB - Collect and Analyze Billions of Data Points in Real Time
  • Mergify - Updating dependencies is time-consuming.
  • SonarCloud - Analyze your C and C++ projects with just one click.
  • LevelDB

    LevelDB is a fast key-value storage library written at Google that provides an ordered mapping from string keys to string values.

    Google didnt, thus Chrome started replacing sqlite with https://github.com/google/leveldb

  • fast-sqlite3-inserts

    Some bunch of test scripts to generate a SQLite DB with 1B rows in fastest possible way

    I am experimenting with SQLite, where I try inserting 1B rows in under a minute. The current best is inserting 100M rows at 23s. I cut many corners to get performance, but the tweaks might suit your workload.

    I have explained my rationale and approach here - https://avi.im/blag/2021/fast-sqlite-inserts/

    the repo link - https://github.com/avinassh/fast-sqlite3-inserts

  • InfluxDB

    Collect and Analyze Billions of Data Points in Real Time. Manage all types of time series data in a single, purpose-built database. Run at any scale in any environment in the cloud, on-premises, or at the edge.

  • dqlite

    Embeddable, replicated and fault tolerant SQL engine.

    I'd be curious for a similar tuning with Dqlite: https://github.com/canonical/dqlite

  • planetiler

    Flexible tool to build planet-scale vector tilesets from OpenStreetMap data fast

    I spent a while optimizing sqlite inserts for planetiler, this is what I came up with:

    https://github.com/onthegomap/planetiler/blob/db0ab02263baaa...

    It batches inserts into bulk statements and is able to do writes in the 500k+ per second range, and reads are 300-400k/s using those settings.

NOTE: The number of mentions on this list indicates mentions on common posts plus user suggested alternatives. Hence, a higher number means a more popular project.

Suggest a related project

Related posts