SQLite is surprisingly performant for single node applications even when comparing to Postgres. Postgres consumes a lot more memory and requires IO to hop through IPC whereas you can keep everything in process in SQLite with a shared connection pool.

I've been testing different storage engines for my agent harness and I can get up to 7.5k concurrent sessions on a single vCPU with SQLite whereas Postgres crashes or runs out connections.

[0] https://github.com/impalasys/talon/pull/23#issuecomment-4577...

▲

bob1029 6 hours ago | parent | next [-]

When used properly, SQLite is effectively an in-process method invoke. If the only remaining things in the way are your runtime, kernel, file system and a local NVMe storage device, you may find it massively outperforms hosted alternatives.

Leaving the current thread is where you lose the game in terms of latency. SQLite can work on timescales measured in microseconds if you don't force interthread communication.

	▲	themafia 3 hours ago \| parent [-]
		> an in-process method invoke. Pedantically it's an in process virtual machine for operating on structured data. Which is precisely where it shows it's weakness, in my experience, when you end up with complicated table structures and complex join mechanics you then need to start thinking ahead of the query planner and VM code a bit in order to maintain reasonable performance. There are more than a few unusual things worth knowing: https://sqlite.org/optoverview.html

▲

onlyrealcuzzo 6 hours ago | parent | prev [-]

> SQLite is surprisingly performant for single node applications even when comparing to Postgres.

In the context of SQLite being understood to be a quite excellent piece of software - shouldn't we expect it to be?

In the context of a single-node, Postgres is overkill. It should not be expected to be competitive with SQLite.

This is almost like benchmarking an in-memory HashMap to Redis and being surprised that it performs well in ideal conditions.

▲

shukantpal 6 hours ago | parent [-]

Yes, agreed on SQLite/Postgres. But I'm going to benchmark RocksDB next and see what the performance characteristics are. I suspect the LSM tree storage engine of RocksDB might perform better since agents are so write heavy when running highly concurrent workloads. After all, you are streaming LLM tokens into disk and fanning them out to subscribed clients.

▲

onlyrealcuzzo 6 hours ago | parent [-]

You might want to start here: https://docs.cozodb.org/en/latest/releases/v0.3.html

▲

andriy_koval 6 hours ago | parent [-]

That project has 0 commits for 2 years.

▲

onlyrealcuzzo 5 hours ago | parent | next [-]

What does that have to do with their research on the exact topic OP was looking into?

	▲	andriy_koval 5 hours ago \| parent [-]
		Abandoned research of unknown quality is strong signal to downprioritize that direction

▲

recursive 6 hours ago | parent | prev [-]

Sounds pretty stable