Hacker News Reader

Comment by aasasd | Hacker News Reader

Personally I'm more interested in indexing a boatload of data as fast as possible, with modest resource requirements. Had a couple of cases where I'd like to do that on a mid-tier laptop, with no particular success so far. I have to guess, but it seems that ‘data scientists’ either buy big fat boxes with tons of ram and cpu, or offload everything to big fat boxes in datacenters, or twiddle thumbs for a quite while. You'd think that by now writing indexes on all columns at the top sequential drive speed would be a solved problem from any ‘Learn data science in two days’ tutorial.

View on HN

If you’re trying to use SQLite for this, it’ll be much faster if you import the data first, and then create your indexes.

Also check out DuckDB, which is a similar tool tailored for data science; I haven’t used it but I’ve heard good things. https://duckdb.org/

The other tricks to making SQLite fast:

* Ensure you're in WAL mode. https://sqlite.org/wal.html

* Use prepared statements and transactions.

* Set the cache pragma to ideally hold the DB in RAM.

* Run optimize and vacuum periodically (especially if the data is transactional): https://sqlite.org/pragma.html#pragma_optimize

* Pick the synchronous mode you're comfortable with: https://sqlite.org/pragma.html#pragma_synchronous

> Ensure you're in WAL mode.

That was what I thought as well, but in this case non-WAL turned out to be faster. Not yet sure why that is.

Dunno why it'd be surprising: WAL has higher concurrency but it translates to more overhead, writes have to hit the WAL then be flushed to the database, and reads have to check the database and the WAL.

WAL could have faster writes for small amounts of data, but once you have enough contents that the WAL has to be flushed during processing you're better off skipping the WAL entirely.

That indexing perf isn't unique to SQLite; it's true for PostgreSQL too.

It's true for pretty much any rdbms - mostly an artifact of the b-tree implementation.

Sorry I don’t mean to hijack the original post but for performant insert and indexing (which I assume is for analysis), I’d recommend using Clickhouse or QuestDB

I’d second the rec for clickhouse.

+1 for clickhouse

It is certainly feasible to saturate NVMe with just index writes in many niche implementations today. The trick is usually copious amounts of batching so that IO can do more per unit.

How does vanilla SQLite 'create index' fare, with correct pragmas e.g. cache_size?

Reply on news.ycombinator.com