Hacker News Reader

> It is true that SQLite does not support concurrent-writes, but (and that's a big BUT) if you carefully open connections only when you need them and use prepared statements, I can't see how you could run into problems with modern SSD hardware (unless you're Google-scale of coure).

WAL mode is your friend. (Various SQLite drivers, including the Python one, are however somewhat buggy in their transaction handling and need workarounds; essentially they delay the BEGIN of a transaction until you issue DML statements which obviously breaks snapshot isolation entirely).

WAL mode allows one writer at a time without impeding readers.

(See https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p... for the pysqlite workaround)

I see your point, but I'm very hesitant to change any configuration of SQLite. It kinda feels like one step too close to doing devops - which one wants to avoid by using SQLite I guess.

Having said that, I do play around with PRAGMA statements when it's really needed, but usually tweaking the code usually works fine - even increasing the timeout is probably enough :D

I read that first sentence like 3 times and still don't get it.

Often people use SQLite for sheer convenience instead of perceived performance or resource efficiency i.e. because SQLite is zero config, works out of the box. Sure spinning up a docker container with PostgreSQL is easy enough but why do that when you can use the default standard library with SQLite already embedded and linker configed?

SQLite is an alternative to fopen not a full blown DB...

And it’s performance and reliability are a huge part of why it runs on millions of devices everywhere.

Your browser was never going to embed a build of PostgreSQL and Docker.

Enabling WAL can hardly be called config, it’s a one liner in every driver I’ve ever seen.

It’s like saying specifying the file directory SQLite uses is config.

> It’s like saying specifying the file directory SQLite uses is config.

Or, to continue the fopen analogy, like saying that the file you've given fopen is to be opened with mode "a+". The only real difference is that fopen's mode argument is required whereas SQLite's mode has a useful default.

> Enabling WAL can hardly be called config, it’s a one liner in every driver I’ve ever seen.

Even in the raw libsqlite3 C API (as long as you don't need any error checking ;) ):

    sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);

presumably because it's just a one-liner in SQLite's dialect.

> (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) )

This hits a little bit too close to home for me. I am quite proficient at writing performant SQL queries and recently started using Pandas. I find the data frame abstraction better for certain data manipulation tasks. Assuming there is enough RAM available is it still better to offload everything to the database engine?

As usual: it depends... If you're doing prototyping and are working in a Jupyter notebook, sure, go ahead and work on the Pandas-level. Once however you're done with prototyping and have settled to a "final_df" (I bet you have something like that in your last notebook cells), maybe you should think transforming some of the "columns" to sql queries (which are VCS-able, sometimes are faster, and most importantly other people can use them too. And instead of 10 people loading 10 different DFs, you can have 10 people querying the same table/view.

Dask has an out-of-memory dataframe implementation. Works great! I think it might support sql queries for that matter.

> Assuming there is enough RAM available

You will also have to assume you don't care much about the latency introduced by transferring everything over to your working process.

(And in batch data processing situations you typically don't care much.)

Dplyr works great with SQL (both SQLite and others).

> (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) )

You really don't even need to know SQL anymore to keep things out of memory. In R, the dplyr/dbplyr package has SQL translations so you can utilize the exact same syntax as you would on in-memory data frames and it will execute as SQL using the database as a backend.

Not saying people shouldn't learn SQL regardless, but even that shouldn't be an excuse for doing everything in-memory these days.