syncer

That's starting to look quite interesting, thanks! 5TB of rsyncable storage for 22€/month could definitely replace one of my NAS boxes. The 10 concurrent connections are the only big limitation really. Not being able to run my own stuff is also not ideal. But it's the kind of thing I've envisioned to stop using a NAS and just point syncer at: https://github.com/pedrocr/syncer/

Is there a reason you are managing locking at the application layer on the SQLite connection object? https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... This is going to absolutely destroy your throughput. I am not sure about go, but in .Net, the underlying SQLite connection instance is inherently thread-safe and can be shared across execution contexts. If you are unable to share a single SQLite connection instance between multiple logical parallel execution contexts in go, I would recommend just creating a new SQLite connection in each case where you need one (e.g. at the beginning of every method currently performing a lock on connection). The only thing you need to remember with this approach is that the synchronous=normal pragma needs to be set each time you new up a connection. This is an extremely fast operation (doesn't

The code for my most recent case is here: https://github.com/pedrocr/syncer/ The db code itself is here: https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... WAL is being used but that's not enough to get a mixed INSERT/SELECT to not completely starve reads without first caching INSERT and batching it. >In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implementation. I do agree that SQLite with 100% defaults will easily be overtaken by Postgres, and that if your use case requires that more than 1 node persist transactional data you should definitely be using Postgres. This is single node with WAL. If there's something I've missed I'd love to see it (or get a PR). But the research I did when I wrote the code seemed to indicate this was a known limitation of sqlite.

There's nothing about being embeddable that's a limitation. All I really want is a lib I can link to that gives me an entry point to launch the database against a given folder, with some of the tweaks the postgres config file has, and that gives me a connection back. That entry point can launch however many threads it wants and behave exactly like postgres itself. After that my app can connect to a standard postgres and take advantage of all the features and performance. We've been moving so much into web apps that optimizing for desktop apps like these is not a big concern these days. Sqlite ends up being used in that niche but it's extremely easy to run into it's limitations even in very simple personal usage. It's fine for what it is, an alternative to open(), but there are plenty of use cases where optimizing for fast open is not rele

I've hacked around this issue in a contended hashmap by doing a Vec<RwLock<HashMap>> where you index into the Vec with the first bits of the key hash and then use the HashMap within it: https://github.com/pedrocr/syncer/blob/master/src/rwhashes.r... Worked fine but a full ConcurrentHashMap would be much nicer.

I've recently used SQLite to store some metadata for a FUSE filesystem that pretends all your data is available but that just keeps a local cache and fetches it from the server on demand[1]. Initially performance was atrocious because INSERT is really slow if you don't batch several into transactions. I've worked around it with some application caching but there does seem to be a large gap between what the postgres engine can do and what sqlite can do. I definitely don't want to depend on a postgres setup for this application but it would be nice to have an SQL database for app use that had a bit more write performance. It seems that when you want that you end up going to LevelDB or LMDB and losing the niceness of an SQL interface. [1] https://github.com/pedrocr/syncer