Hacker News Reader

Comment by hermitcrab | Hacker News Reader

I have a C++ application. Everything is in memory during execution. Saved to disk between session as XML. Works great, except that that it is strictly single user and some of my customers would love me to generalize it for multiple concurrent users reading and writing. Performance requirements are quite low - a few thousand records being updated by 2 or 3 people at a time. Would DuckDb + Quack be a good choice for this? Or are there better choices? I looked at SQLite, but I understand it doesn't operate as client server.

View on HN

https://firebirdsql.org has been flying under the radar in-between SQLite and full-blown PostgreSQL for decades, but if you're asking which client-server database to use PostgreSQL is the default recommendation.

Did some reading. Given my modest performance requirements, Firebird might be a good choice due to simpler install and admin. Thanks.

If postgres is too heavyweight for you but you still want client-server, I'd consider MySql. It's an old classic, pretty fast and scalable, and has much better mainstream support and a bigger ecosystem than Firebird.

I'm not really sure what Firebird is for at this point in life really. It was pretty exciting when it was open sourced in the early 2000s, before postgres became the mature beast it is, before mysql acquired something as basic as transactions, and before sqlite became the default embedded db. But then it never really went anywhere.

Good to know. Thanks.

DuckDB is more for analytics. I don’t think you’re going to find good options for a DB that can handle concurrent users without hosting it in some way server side. It’s certainly possible (think how some games create their own client servers for direct multiplayer) but honestly hosting Postgres or SQLite is ridiculously cheap, easy, and more importantly the standard approach to this issue.

IIRC SQLite is in-process and says in it's documentation that it is not a client-server database.

It’s not, but you could do something like https://litestream.io/ and just continuously replicate it to pretend to be multi-user

Does SQLite + replication have any advantages over client-server, for someone not already using SQLite?

Trivial deployment model, can avoid owning/managing your own server, easier coding in the sense that no-network means you can do normally-psychotic things like N+1 queries and single-row inserts and get away with it.

SQLite/DuckDB actually enables a bunch of normally-illegal behavior when you compare to normal databases. Backups is just copy&paste of a file; spamming queries willy-nilly becomes cheap; you can version the whole DB in git (can’t diff it properly.. but you can do cross-db queries with SQLite ATTACH); locking concerns goes out the window because it’s single-writer anyways.

But if I were actively trying to support multiple users with a single source of truth, I’d probably default to Postgres. If it’s single-user, default to SQLite/DuckDB. If it’s single-user with multiple devices, default to SQLite + replication.

That is helpful, thank you.

I think the term you want to search for is local-first.

My understanding is that Local First means syncs across multiple devices, which is not the same thing as multi-user concurrent access.

It's both. I recommend looking into it a bit deeper

https://www.inkandswitch.com/essay/local-first/

Sounds like a good use case for CRDTs, which would also enable offline editing

In my use case I have 2 or 3 users editing the same database concurrently and they all want to see other's updates in near real time (within a second or two). Would a CRDT support that? It would be great if it did and I could just keep using XML to persist everything with no server. But that sounds unlikely.

Highly suggest you take a look here: https://github.com/yjs/yjs

CRDT can absolutely do what you’re asking.

Reply on news.ycombinator.com