SQLite Forum

Sqlite high IO load makes usage impossable
Login

Sqlite high IO load makes usage impossable

(1) By anonymous on 2021-11-25 08:17:03 [link] [source]

Hi everyone, I'm working on an open source project that uses sqlite. We use sqlite as a key-value store (as one of multiple possible backends). See https://conduit.rs we also have a matrix room at https://matrix.to/#/#conduit:fachschaften.org

https://gitlab.com/famedly/conduit/-/blob/next/src/database/abstraction/sqlite.rs#L51 This is the main file that interacts with sqlite (through the rusqlite rust library).

Unfortunately sqlite seems to be very slow when we make many small requests in a row. My small server becomes unusable because of very high IO read. Ram usage also seems higher than expected, which may or may not be sqlite's fault.

I would be very grateful if someone could help to improve the situation. I'm happy to answer questions or explain code here or on matrix.

Analysing the program gives these logs:

611680 19:23:44.438807703 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
611683 19:23:44.438812703 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
611686 19:23:44.438815303 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
611846 19:23:44.439135824 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
611849 19:23:44.439139524 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
611852 19:23:44.439144024 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
611855 19:23:44.439146524 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
612015 19:23:44.439459344 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
612018 19:23:44.439463044 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
612021 19:23:44.439467845 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
612024 19:23:44.439470445 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
612181 19:23:44.439794765 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
612184 19:23:44.439799966 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
612187 19:23:44.439805266 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
612190 19:23:44.439807966 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
612350 19:23:44.440108185 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
612353 19:23:44.440111786 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
612356 19:23:44.440116986 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
612359 19:23:44.440119686 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
612510 19:23:44.440412305 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24
612513 19:23:44.440416105 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
612516 19:23:44.440422105 0 rocket-worker-t (11532) > write fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=24

lot's of small 2K chunks written to WAL and then flushed into database file... first 24B then 2K that's the pattern here

2907642 19:26:26.867277520 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907648 19:26:26.867284521 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907654 19:26:26.867291321 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907660 19:26:26.867296821 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907666 19:26:26.867302822 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907672 19:26:26.867308822 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907678 19:26:26.867315223 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907684 19:26:26.867322123 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907690 19:26:26.867328423 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907696 19:26:26.867334224 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907702 19:26:26.867340124 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
2907708 19:26:26.867351725 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048

That's the syscall pattern for the database file... guess the WAL chunks will be moved to database file using the same chunk size

4970 19:28:42.851418442 0 rocket-worker-t (11532) > read fd=16(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
4971 19:28:42.851431543 0 rocket-worker-t (11532) > lseek fd=16(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
4973 19:28:42.851433243 0 rocket-worker-t (11532) > read fd=16(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
4974 19:28:42.851442544 0 rocket-worker-t (11532) > fcntl fd=11(<f>/srv/conduit/.local/share/conduit/conduit.db-shm) cmd=8(F_SETLK)
4980 19:28:42.851517149 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
4981 19:28:42.851522949 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
4983 19:28:42.851524749 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
4984 19:28:42.851529449 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
4986 19:28:42.851531049 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
4987 19:28:42.851536150 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
4989 19:28:42.851537750 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
4990 19:28:42.851544950 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
4992 19:28:42.851546650 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
4993 19:28:42.851550351 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
4995 19:28:42.851551951 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
4996 19:28:42.851556751 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
4998 19:28:42.851558251 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
4999 19:28:42.851570452 0 rocket-worker-t (11532) > fcntl fd=11(<f>/srv/conduit/.local/share/conduit/conduit.db-shm) cmd=8(F_SETLK)
6003 19:28:43.432241388 0 rocket-worker-t (11532) > read fd=16(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
6004 19:28:43.432254588 0 rocket-worker-t (11532) > lseek fd=16(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
6006 19:28:43.432256289 0 rocket-worker-t (11532) > read fd=16(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
6007 19:28:43.432265289 0 rocket-worker-t (11532) > fcntl fd=11(<f>/srv/conduit/.local/share/conduit/conduit.db-shm) cmd=8(F_SETLK)
6013 19:28:43.432338494 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
6014 19:28:43.432344494 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
6016 19:28:43.432346194 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
6017 19:28:43.432351495 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
6019 19:28:43.432353195 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
6020 19:28:43.432358195 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
6022 19:28:43.432359795 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
6023 19:28:43.432367596 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
6025 19:28:43.432369196 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
6026 19:28:43.432373096 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
6028 19:28:43.432374596 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
6029 19:28:43.432379096 0 rocket-worker-t (11532) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
6031 19:28:43.432380696 0 rocket-worker-t (11532) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048

That's for the read syscalls... many syscalls seeking to 0 and reading like in a loop... Thats why disk read input is like crazy

And finally a pattern that makes no sense to me either...

202965 19:53:26.866046815 0 rocket-worker-t (11532) > fstat fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db)
202969 19:53:26.866063216 0 rocket-worker-t (11532) > read fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
202970 19:53:26.866071717 0 rocket-worker-t (11532) > lseek fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
202972 19:53:26.866073117 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
202975 19:53:26.866094318 0 rocket-worker-t (11532) > read fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
202976 19:53:26.866096218 0 rocket-worker-t (11532) > lseek fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
202978 19:53:26.866097318 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
202981 19:53:26.866101619 0 rocket-worker-t (11532) > read fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
202982 19:53:26.866103719 0 rocket-worker-t (11532) > lseek fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
202984 19:53:26.866104719 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
202987 19:53:26.866108719 0 rocket-worker-t (11532) > read fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
202988 19:53:26.866110919 0 rocket-worker-t (11532) > lseek fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
202990 19:53:26.866111919 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
202993 19:53:26.866115319 0 rocket-worker-t (11532) > read fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
202994 19:53:26.866117420 0 rocket-worker-t (11532) > lseek fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
202996 19:53:26.866118420 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
202999 19:53:26.866122720 0 rocket-worker-t (11532) > read fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
203000 19:53:26.866125320 0 rocket-worker-t (11532) > lseek fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
203002 19:53:26.866126320 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
203005 19:53:26.866129620 0 rocket-worker-t (11532) > read fd=10(<f>/srv/conduit/.local/share/conduit/conduit.db-wal) size=2048
203006 19:53:26.866131521 0 rocket-worker-t (11532) > lseek fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=0 whence=0(SEEK_SET)
203008 19:53:26.866132521 0 rocket-worker-t (11532) > write fd=9(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048

fd=10 reads 2K chunks from the WAL file but fd=9 seeks to the beginning of the database file, writes a 2K block of data then fd=10 reads the next chunk and fd=9 overwrites the first block? Or do I st. misunderstand here how lseek() works (https://man7.org/linux/man-pages/man2/lseek.2.html)

(2) By anonymous on 2021-11-25 11:46:36 in reply to 1 [link] [source]

Ok if you run sysdig on Azure please use the lastest version from github (repository version is too old and buggy).. using v0.27.1 lseek offset is reported correctly

15428536 08:56:05.124769124 0 rocket-worker-t (1991) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=6004736 whence=0(SEEK_SET)
15428538 08:56:05.124772524 0 rocket-worker-t (1991) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048
15428638 08:56:05.125263126 0 rocket-worker-t (1991) > lseek fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) offset=4851712 whence=0(SEEK_SET)
15428640 08:56:05.125264926 0 rocket-worker-t (1991) > read fd=14(<f>/srv/conduit/.local/share/conduit/conduit.db) size=2048

(3) By Gunter Hick (gunter_hick) on 2021-11-25 12:33:20 in reply to 1 [link] [source]

What kind of "many small requests in a row"?

You show no schema, no SQL and neglect to mention even the SQLite release.

I can only guess that you have just 1 table and that you are updating 1 value at a time using autocommit, so each update has to do its own commit, which is costly.

(4) By anonymous on 2021-11-25 13:06:24 in reply to 3 [link] [source]

We are currently using sqlite 3.36.0. There are ~60 tables and the schema is the same for each of them: A blob primary key and a blob value. The database is ~30GB on my server with most of that concentrated on 2-3 tables. The key is usually < 50 bytes and the value is often a json with 500-1000 characters.

With many small requests I meant selects in the form SELECT value FROM table_name WHERE key = ?. When not idle we probably send 500k of these selects in a short time (<1 minute). Often split among multiple threads (each thread has its own db connection).

(5) By anonymous on 2021-11-25 14:22:34 in reply to 4 [link] [source]

We use WAL with synchronous=normal and autocheckpoints disabled. Instead we checkpoint manually from time to time from a dedicated thread. More details in the abstraction/sqlite.rs file I linked above.

Currently the page size is 2048 but I plan to change that to 4096 soon.

(7) By Gunter Hick (gunter_hick) on 2021-11-25 15:06:56 in reply to 5 [link] [source]

We don't use WAL mode here because we have only virtual tables in the SQLite db file, so I am no expert on this, but here goes anyway:

A read transaction on a WAL mode db file needs to record an "end mark" to prevent a checkpoint from transferring changed pages to the main db file that the reader should not (yet) be seeing. Since you do not mention transactions, I am guessing that what you are seeing is "end marks" constantly being added and removed in the WAL file. This would be reduced if you would read more than one value in one transaction.

You have not mentioned writes. Depending on how frequent and how lengthy writes are, it may be very much faster to use journal mode instead.

(10) By Gunter Hick (gunter_hick) on 2021-11-25 15:34:50 in reply to 5 [link] [source]

Attempting to read your code, it seems you are also preparing the statements anew for each execution. This adds substantial overhead, as the SQLite byte code is re-generated and the schema data re-parsed for each statement execution.

(11.1) By Scott Robison (casaderobison) on 2021-11-25 16:20:21 edited from 11.0 in reply to 10 [link] [source]

So much this. A few years ago I had to refactor some code that was doing this very thing. Every query for a single row was built from scratch, then prepared, stepped, and finalized. A database update could take over 24 hours (update in this case meaning re-writing the database to change the schema between version 1 and version 2 of the application).

When I rewrote it to reuse queries across multiple rows, our migration time went down to 5 to 15 minutes for a 5 or so GB database.

There is a ton of overhead preparing queries. It is to your advantage to create queries that can be prepared once and used over and over again.

(6) By anonymous on 2021-11-25 14:58:56 in reply to 4 [link] [source]

That roughly a little under 100K requests per second.

Things to note: Do you perform frequent writes to the DB? These will invalidate the page cache for all open connections, potentially slowing subsequent selects.

If not, consider that the default page cache size is anemic (~10MB), consider increasing that, substantially.

Also did you consider mmap? This can ensure more of the db is held in system memory despite the writes and can prevent expensive trips to the actual storage media. You might want to try larger values, with and without page cache under your workload to see what works. I am hazarding a guess that mmap will yield the highest benefit in your particular case.

(8) By Gunter Hick (gunter_hick) on 2021-11-25 15:10:43 in reply to 4 [link] [source]

What is the difference between those 60 tables? Having multiple tables with identical schemata und just differing in names is often indicative of an improper database design that abuses metadata as data.

Using a relational database as a key/value store for unrelated data may not be the best design decision.

(13) By anonymous on 2021-11-25 17:35:54 in reply to 8 [link] [source]

Contributor here, we've tried to work with alternative KV databases such as LMDB and RocksDB, but sqlite gave the best performance wrt memory vs db size vs CPU times, so we've stuck to this.

We're fully aware that we're abusing a relational database for a KV database, but it's served us relatively fine up until now.

(19) By Gunter Hick (gunter_hick) on 2021-11-26 07:16:19 in reply to 13 [link] [source]

Sorry for the following remark, but jumping out of an airplane with a defective parachute also works relatively fine. Until you run out of airspace.

(9) By Gunter Hick (gunter_hick) on 2021-11-25 15:18:01 in reply to 1 [link] [source]

Can you isolate the syscalls that result from just one select?

(12) By Simon Slavin (slavin) on 2021-11-25 16:51:53 in reply to 1 [link] [source]

Most important and easiest:

If you have a known set of SELECT statements to execute, for example you know you're going to need to look up four values, put them in a transaction, i.e. surround them in BEGIN and END.

Less easy:

Where convenient, use a number of pre-prepared statements and just rebind and restep them, rather than preparing a new statement every time.

(14) By anonymous on 2021-11-25 17:41:08 in reply to 12 [link] [source]

Thank you all for the responses. I think I have to stay with WAL because we need to concurrently run multiple selects, sometimes from different threads.

Unfortunately I also can't group together the selects in most cases because the selects depend on each other (they build a graph).

I will try to group the selects where I can and try mmap and reusing the prepared statements as well as increasing the page size a lot.

(15) By Richard Damon (RichardDamon) on 2021-11-25 19:09:52 in reply to 14 [link] [source]

You can group them into the same EXPLICIT transaction to avoid the repeated setting of the checkpoint limit, and in fact, if you are building queries based on the results of queries, you WANT them all in a single transaction so the data can't change on you while you are building the selects.

This can help things some.

(16) By Igor Tandetnik (itandetnik) on 2021-11-25 21:36:33 in reply to 14 [source]

WAL mode is not required in order to run multiple selects in parallel. The difference between journal mode and WAL mode is this: journal mode allows multiple readers OR one writer at the same time, while WAL mode allows multiple readers AND one writer.

(17) By anonymous on 2021-11-26 01:53:44 in reply to 16 [link] [source]

Yeah, we need the latter one, this is effectively a web app with many random reads going on at the same time.

When we tried to mutex the reads and write connections, there was already so much entanglement and difficulty getting all continuous SELECTs to stop (as some iterate over values, and then begin a new special/informed SELECT iteration from that value individually)

So in this case we would indeed need both.

(18) By Gunter Hick (gunter_hick) on 2021-11-26 07:02:26 in reply to 14 [link] [source]

If I understand you correctly, your KV pairs are relational in that they form a graph, with the connections being implicit in the values. And sometimes the graph is changed.

In that case it would be imperative to make "reading a graph" a transaction, to avoid som eother thread changing the graph while you are in the middle of reading it - which could lead to reading an invalid state.

BEGIN;
<read graph>;
COMMIT;

It would also be imperative to make "changing a graph" a transaction, lest concurrent changes completely mess up each others changes

BEGIN IMMEDIATE;
<read graph>;
<compute necessary changes inside app code>
<update graph>;
COMMIT;

Now SQLite will handle syncing properly, allowing just one writer in sequence to make changes, and isolating readers from seeing transient invalid states.

It strikes me as a very roundabout way to abstract relationships into KV pairs and then painstakingly rebuilding them instead of having "nodes" and "connections" that can be traversed with CTEs in SQLite doing the heavy lifting.

But then again, you have chosen to share problems with your chosen implementation instead of sharing the original problem.

(20) By Gunter Hick (gunter_hick) on 2021-11-26 07:29:27 in reply to 14 [link] [source]

Do you see one major drawback of having 60 identical tables with different names now? 

You need to prepare 60 statements just to "SELECT value FROM table WHERE key = ?" and another 60 to "UPDATE table SET value = ? WHERE key = ?" and maybe even keep track of which you have and have not yet prepared. And do this for every connection.

Unless your threads only ever read/write a distinst set of tables, and no two threads access the same table ever. That would be a use case for a separate SQLite db file for each thread, which would eliminate any contention.