Syncing database with minimal data delivery.

Feature
1

Some local softwares use database such as sqlite to store the user data. These database size could range 0MB - 2GB or more.

If use syncthing to sync these file, every small change would require the who uploading and downloading of the database.

So I wonder if it’s possible to treat these database files specially, using some other mechanism and sql language to change the database files?

2

I don’t think so as syncthing operates block based: FAQ — Syncthing v1 documentation

3

Syncthing do sync with blocks, but database is not like text files, it’s operation is not like appending texts at the end of file.

If an item is inserted into the middle of the database, then the whole data follows would have a data position shift, then these blocks follows would all need to be delivered.

4

Let me explain this.

Suppose these is the origin data:

1 2 3 4 5 6 7 8 9

and syncthing slice them into blocks, each block size is 3:

block1: 1 2 3
block2: 4 5 6
block3: 7 8 9

if the data is appended to the end of file:

1 2 3 4 5 6 7 8 9 a

then the blocks would become:

block1: 1 2 3
block2: 4 5 6
block3: 7 8 9
block4: a

in this case, only the last block need to be delivered.

But, if the new data is inserted in the middle of the original data:

1 2 3 a 4 5 6 7 8 9

the following data position would all have a shift, then the blocks would become:

block1: 1 2 3
block2: a 4 5
block3: 6 7 8
block4: 9

the block2, block3, block4 all need to be delivered.

So the block transfer is not suitable for large, often changing database.

5

Syncthing syncs files, period. Nothing more, nothing less.

You should not use syncthing to sync databases, as many database don’t consist of only one file (the syncthing database itself is such a database), which will result in inconsistent state if not all files are synchronized at the same time.
Database files are also often blocked for the time the using software is running, so cannot be synchronized in that time.

6

Also, databases use mmapped files, which are not real files (atleast while they are open), so syncing them will probably lead to corruption and data loss.

You might be better of looking at rqlite or something

1 Like
7

We have various optimizations to handle inserts in the middle. Though I’ve yet to hear about a database that would do what you describe. And yeah, don’t sync databases.