# Syncthing on SQLite -- help test!
**URL:** https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981
**Category:** Development
**Created:** [March 13, 2025, 9:26am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981 "2025-03-13T09:26:51Z")
**Posts on this page:** 20
**Page:** 2
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 15, 2025, 8:34am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/21 "2025-03-15T08:34:57Z")
Oh and in case I sounded unnecessarily dismissive before, I’m all for producing statically linked musl binaries and whatnot, it’s just that that’s a nonissue if the whole thing is unsound for other reasons before then. 🙂 Once we’re at a point that it seems like this is an acceptable future we can absolutely spend all the effort to fine tune the build process to generate the best possible binaries for the most platforms.
---
### Author:  [@pixelspark](https://forum.syncthing.net/u/pixelspark)
#### Post date: [March 15, 2025, 11:06am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/22 "2025-03-15T11:06:48Z")
Hi @calmh, this is a great effort. I have tried this branch on the iOS side (simulator only for now) and apart from some [code changes that were needed on Synctrain (see PR)](https://github.com/calmh/syncthing/pull/20), this seems to be working just fine at first glance.
---
### Author:  [@Martchus](https://forum.syncthing.net/u/Martchus)
#### Post date: [March 15, 2025, 6:50pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/23 "2025-03-15T18:50:37Z")
> [@calmh](#):
>
> Interesting. I ran into this also in my setup, and then set the max number of database connections (i.e., open file descriptors on the database file) to 128. It would be interesting to know if you are running with a fairly low limit, or if I’m leaking database connections/FDs somewhere. Syncthing should try to increase the rlimit to the hard max on startup.
I don’t have a very low limit (hard limit: 500000, soft limit: 1024). I was already using my own Syncthing build where I ran Syncthing as library when those errors occurred. However, I’m pretty sure my c-bindings run the code where it increases the limit (as the c-bindings simply invoke `Start()` from `lib/syncthing/syncthing.go`).
> [@calmh](#):
>
> I also noticed the database is bigger, about twice the size on my systems. I attributed this to indexes and stuff. I experimented with using lz4 on stored FileInfos and block lists, but it made no relevant difference. (The block lists are mostly random data anyhow.)
Good to know that it is “just” twice as big. I would say that’s still acceptable. Maybe it is _really_ a good thing that one can then move the database to the SD-card on Android, though.
> [@calmh](#):
>
> ~~I’ll look into it!~~ Found and fixed, needed an `ORDER BY` to be compatible with the old setup.
Thanks. If there’s a commit I can cherry-pick I can test it. I saw that you added actually quite a few more commits on `calmh/jb/sqlite`. If you want us to test them, let us know. (Although I’m afraid I’ll be a bit busy in the next two weeks.)
For now I’m still testing the `v2.0.0-beta.1` version a little bit. So far I have nothing more to add based on the testing.
Just a few thoughts on the idea and motivation in general:
- “maintaining indexes ourselves has been a source of inconsistencies” - I also wrote a piece of software using a DB that is a mere key-value store (LMDB) and I must say that this is definitely true. It is just hard. I “solved” this with a lot of C++ template magic but reading/understanding this code is not easy, too. So using a more high-level DB seems like a good idea for Syncthing, indeed.
- “We see a lot of odd crashes in the database layer.” - That doesn’t speak for leveldb but I must say that as a user I rarely ever experienced and problems.
- “One advantage of a simple key-value database like leveldb is that inserts and changes are incredibly fast. Now, changes entail triggers, multiple indexes to update, and transactions.” - I guess that’s a price we can pay. Testing shows that the SQLite-based version is definitely slower and the DB takes more space but the resource usage still seems to be in reasonable bounds.
- I’m wondering whether it would be possible to support other databases (e.g. PostgreSQL) or even the old and the new database at the same time. That would probably eliminate concerns about resource usage as one could still use leveldb on low-end devices.
---
### Author:  [@Nummer378](https://forum.syncthing.net/u/Nummer378)
#### Post date: [March 15, 2025, 7:05pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/24 "2025-03-15T19:05:34Z")
> [@Martchus](#):
>
> On Android SQLite seems to have one advantage over leveldb: When moving the Syncthing database to the SD-card one so far wasn’t able to start Syncthing because it couldn’t create the database lock file (even when staying within the private app folder within the SD-card). In both cases (SD-card and internal storage) Android uses FUSE. So I guess the problem is that the actual underlying filesystem on the SD-card (e.g. exFAT) doesn’t support locking in the way leveldb requires it. With SQLite3 this problem seems no longer apparent. (I’m only wondering whether concurrent accesses are handles as well by SQLite then.)
That’s probably caused by syncthing running SQLite in WAL mode. With WAL, SQLite synchronizes by sharing memory between processes (if needed) instead of using POSIX advisory locks (flocks) [which SQLite can also use].
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 15, 2025, 7:09pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/25 "2025-03-15T19:09:05Z")
The tip of the `jb/sqlite` branch is the thing to test, it’s what I’m running, which is also always in the container image `ghcr.io/calmh/synchthing:jb-sqlite`. I’ll make a new “release” in the morning to have ready binaries for those who prefer that, I just didn’t want to overload everyone with a new release every five minutes.
I don’t usually point to my fork for stuff, preferring people don’t even look at it, but in this case that was the simplest way to produce binaries and containers without mixing it in with the more stable stuff in the regular repo. I don’t want people suddenly getting onto the sqlite train by mistake just because they want the latest nightly or from main or whatever.
> [@Martchus](#):
>
> I’m wondering whether it would be possible to support other databases (e.g. PostgreSQL)
This should in fact be quite straightforward, and may be a win for those trying to run it at scale. I’m sure the table creation scripts etc need to be tweaked, but at least all the principles are in place so it would mostly be a question of abstracting some of the query SQL.
---
### Author:  [@tomasz86](https://forum.syncthing.net/u/tomasz86)
#### Post date: [March 15, 2025, 9:50pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/26 "2025-03-15T21:50:10Z")
I’ve been trying out the binaries in my test environment (under Windows 10 x64). Here is what I’ve noticed so far.
1. There seem to be quite a large performance impact when scanning new folders, e.g. with this one:
2. The old database produces a lot of information with `db,model` debug logging enabled, while the new one doesn’t seem to produce anything. I suppose the lack of detailed debug information is expected at this stage?
3. If you try to cancel scanning, e.g. by removing the folder from Syncthing while the initial scan is still going on, it always takes a long time, and this error is recorded in the log:
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 16, 2025, 9:16am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/27 "2025-03-16T09:16:28Z")
> [@tomasz86](#):
>
> There seem to be quite a large performance impact when scanning new folders,
Indeed, thanks for bringing this up. I could see the same and have fixed it, I think.
> [@tomasz86](#):
>
> The old database produces a lot of information with `db,model` debug logging enabled, while the new one doesn’t seem to produce anything.
It’s under `sqlite` now, but I haven’t had the reason to add much debug output. There are metrics instead. 🙂
I couldn’t immediately reproduce the slow cancel, but if it remains I can look again. There’s a new 2.0.0-beta.2 release coming with the latest fixes.
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 16, 2025, 9:38am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/28 "2025-03-16T09:38:58Z")
> **[Release SQLite preview 2 · calmh/syncthing](https://github.com/calmh/syncthing/releases/tag/v2.0.0-beta.2)**
>
> Performance fixes and other things.
> Container image in ghcr.io/calmh/syncthing:v2.0.0-beta.2
@pixelspark sorry I didn’t merge your changes yet, will get them in during the day.
For performance problems you guys see, a support bundle from while it’s ongoing is helpful.
---
### Author:  [@tomasz86](https://forum.syncthing.net/u/tomasz86)
#### Post date: [March 16, 2025, 10:47am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/29 "2025-03-16T10:47:09Z")
> [@calmh](#):
>
> > [@tomasz86](#):
> >
> > There seem to be quite a large performance impact when scanning new folders,
>
> Indeed, thanks for bringing this up. I could see the same and have fixed it, I think.
Thanks! Performance is much better now. Actually, even better than the old database, as it takes just 4m 20s to scan the same files.
I’ve also tested the `386` version (albeit still under Windows x64) and the result is 7m with the old database vs 6m 40s with the new one.
Just two more questions:
1. The database is a single file now. Is this going to be a problem when running on filesystems that have filesize limitations (e.g. 4 GB maximum file size on FAT32)?
2. I’m seeing an empty `syncthing.lock` file created next to `syncthing.exe` on each run, and it stays there even after shutting Syncthing down. What is the purpose of this file?
---
### Author:  [@bt90](https://forum.syncthing.net/u/bt90)
#### Post date: [March 16, 2025, 12:15pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/30 "2025-03-16T12:15:47Z")
In terms of database size, when do we perform a full database vacuum? Because that’s the only point at which the DB file can shrink.
---
### Author:  [@bt90](https://forum.syncthing.net/u/bt90)
#### Post date: [March 16, 2025, 1:02pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/31 "2025-03-16T13:02:16Z")
We might also benefit from partial indexes for queries on flags like `files.local_flags & {{.FlagLocalGlobal}} != 0`
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 16, 2025, 1:03pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/32 "2025-03-16T13:03:25Z")
You have the vacuuming in `db_service.go`, I set it to incremental vacuuming once an hour.
> [@tomasz86](#):
>
> The database is a single file now. Is this going to be a problem when running on filesystems that have filesize limitations (e.g. 4 GB maximum file size on FAT32)?
Maybe? If so, don’t do that I guess.
> [@tomasz86](#):
>
> I’m seeing an empty `syncthing.lock` file created next to `syncthing.exe` on each run, and it stays there even after shutting Syncthing down.
We (mis)used the old database as a lock for having only one Syncthing instance running. SQLite doesn’t work like that, so I added a separate lock file. It should be in the data directory right beside the database, but I guess if that’s where you keep the exe file it could be beside that as well?
> [@bt90](#):
>
> We might also benefit from partial indexes for queries on flags like `files.local_flags & {{.FlagLocalGlobal}} != 0`
Please tell me more, I know nothing of this.
---
### Author:  [@bt90](https://forum.syncthing.net/u/bt90)
#### Post date: [March 16, 2025, 1:09pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/33 "2025-03-16T13:09:23Z")
> [@calmh](#):
>
> You have the vacuuming in `db_service.go`, I set it to incremental vacuuming once an hour.
Incremental vacuum runs are fine, but they don’t defragment the database file. It would be nice to have CLI flag and/or API endpoint to trigger a full vacuum run.
> [@calmh](#):
>
> Please tell me more, I know nothing of this.
[https://www.sqlite.org/partialindex.html#queries\_using\_partial\_indexes](https://www.sqlite.org/partialindex.html#queries_using_partial_indexes)
---
### Author:  [@tomasz86](https://forum.syncthing.net/u/tomasz86)
#### Post date: [March 16, 2025, 1:41pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/34 "2025-03-16T13:41:49Z")
> [@calmh](#):
>
> > [@tomasz86](#):
> >
> > The database is a single file now. Is this going to be a problem when running on filesystems that have filesize limitations (e.g. 4 GB maximum file size on FAT32)?
>
> Maybe? If so, don’t do that I guess.
Yeah, I’m just worried that this has a potential to break some existing installations if the database format change ends up being a part of a standard automatic upgrade.
> [@calmh](#):
>
> We (mis)used the old database as a lock for having only one Syncthing instance running. SQLite doesn’t work like that, so I added a separate lock file. It should be in the data directory right beside the database, but I guess if that’s where you keep the exe file it could be beside that as well?
Understood, just curious if the lock file is supposed to stay there even after shutting Syncthing down. The location is the same for both, yes, but this is the default for Syncthing running in Windows 🙂.
---
### Author:  [@tomasz86](https://forum.syncthing.net/u/tomasz86)
#### Post date: [March 16, 2025, 1:46pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/35 "2025-03-16T13:46:11Z")
By the way, I’m still getting the `svcutil.go:163: DEBUG: serviceMap[string, ]@0xc000285080: Service sendreceive/nd4du-fsaee@0xc00011a008 failed to terminate in a timely manner` thing when trying to interrupt a folder scan. Should I provide any specific logs to help with finding the culprit?
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 16, 2025, 1:48pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/36 "2025-03-16T13:48:27Z")
Where do we expect people to be running fat32 for the system partition or equivalent? Is this an expected Android thing? It sounds like something that would be for external SD cards or something, but also those could then not be used to store video or anything like that so what’s the situation? I don’t want to get too deep into possibly unlikely what-ifs, especially given “we” don’t really have an Android app… 😉
The lock file is just a file that we can hold an exclusive lock on, I didn’t add anything to remove it on shutdown.
> [@tomasz86](#):
>
> failed to terminate in a timely manner
Can you run with the internal profiler enabled, and take a goroutine profile before and after asking it to cancel?
Having the exe running in %localappdata% surprises me, but on the other hand I don’t know that we have a default as such, so I guess it’s fine. 🤷
---
### Author:  [@tomasz86](https://forum.syncthing.net/u/tomasz86)
#### Post date: [March 16, 2025, 2:22pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/37 "2025-03-16T14:22:56Z")
> [@calmh](#):
>
> Where do we expect people to be running fat32 for the system partition or equivalent? Is this an expected Android thing? It sounds like something that would be for external SD cards or something, but also those could then not be used to store video or anything like that so what’s the situation? I don’t want to get too deep into possibly unlikely what-ifs, especially given “we” don’t really have an Android app… 😉
I was thinking maybe of some portable installations running from USB sticks and such. I do this myself, although I still don’t use FAT32 as the filesystem there. Just for the record, Windows still defaults to FAT32 when formatting removable drives, at least the smaller ones (just confirmed with an 8GB-sized USB stick).
> [@calmh](#):
>
> Having the exe running in %localappdata% surprises me, but on the other hand I don’t know that we have a default as such, so I guess it’s fine. 🤷
Now I see what you mean… Putting the exe file there is my own “invention” 😇. However, by default, there is no separation between Syncthing data and config in Windows, so both the config, keys, and database are all located under the same path.
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 16, 2025, 2:31pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/38 "2025-03-16T14:31:04Z")
As to the database size, much of it is blocks and the index on the blocks:
```plaintext
sqlite> SELECT name, pgsize/1024, (pgsize-unused)*100.0/pgsize FROM dbstat WHERE aggregate=true order by pgsize desc;
name pgsize/1024 (pgsize-unused)*100.0/(pgsize)
----------------------------- ----------- ------------------------------
blocks_hash_position 735868 88.2333268171483
blocks 726972 99.0076247724723
blocklists 482808 90.3920487996781
fileinfos 238152 97.4371836316722
files 217344 96.8620920349768
files_device_name 166384 78.7915832595832
files_name_only 161312 79.7674062918444
files_remote_sequence 23692 87.4221289095475
files_blocklist_hash_only 10464 99.0309222393444
sqlite_autoindex_blocklists_1 4900 89.9025031887755
mtimes 604 90.0023605649834
sqlite_autoindex_mtimes_1 576 73.8147311740451
sqlite_schema 12 49.5686848958333
folders 4 3.271484375
sqlite_autoindex_folders_1 4 3.2470703125
devices 4 3.662109375
sqlite_autoindex_devices_1 4 3.6376953125
indexids 4 8.8623046875
sqlite_autoindex_indexids_1 4 3.369140625
sqlite_sequence 4 0.5615234375
kv 4 40.52734375
sqlite_autoindex_kv_1 4 21.38671875
sqlite_stat1 4 14.3310546875
counts 4 51.4404296875
sqlite_autoindex_counts_1 4 40.8447265625
schemamigrations 4 4.5166015625
```
It might be worth it to separate out the blocks to a separate database file, keeping the data for most queries more compact and presumably faster.
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 16, 2025, 5:28pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/39 "2025-03-16T17:28:54Z")
One more for today, now with static binaries for Linux again (though gnu and not musl, for you libc connoisseurs out there).
> **[Release SQLite preview 3 · calmh/syncthing](https://github.com/calmh/syncthing/releases/tag/v2.0.0-beta.3)**
>
> Now with static Linux binaries
---
### Author:  [@tomasz86](https://forum.syncthing.net/u/tomasz86)
#### Post date: [March 16, 2025, 7:29pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/40 "2025-03-16T19:29:33Z")
> [@calmh](#):
>
> Can you run with the internal profiler enabled, and take a goroutine profile before and after asking it to cancel?
Here are three goroutine profiles:
- [1. goroutine - while scanning.txt](https://forum.syncthing.net/uploads/short-url/92h4RL17iBXvHjHT1rGh0BPLwvH.txt) (38.0 KB)
- [2. goroutine - when stuck removing folder.txt](https://forum.syncthing.net/uploads/short-url/rbvdcWUCZbLSu1i6i1keKgmsgQd.txt) (42.6 KB)
- [3. goroutine - after removing folder.txt](https://forum.syncthing.net/uploads/short-url/xemY0iCdxVCbEJd5opWQdOBlo0.txt) (33.5 KB)
[Previous page](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981.md?page=1)
[Next page](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981.md?page=3)