Single-tenant SQLite vs Postgres + SQLite

Question

About the app

I am building an application which will have 2 clients: web and mobile. Both clients will need to auth to sync the user data. On average, each user will generate 2000 rows of data.

The mobile application should be able to run in offline mode, making SQLite is an obvious choice for the mobile app to store all user-usage data.

The problem is: how to keep both clients in data sync? Given data the mobile app generates records in offline mode that need to be synced to the web app?

---

Solution 1: Single-tenant SQLite as source of truth. Save periodic progress to a single SQLite and use this same database by the web client.

Cons:

• Data loss due to periodic syncing.
• Simplest solution is to overwrite (merge) databases from the 2 clients into a new database. This is more expensive that saving only the newly generated data logs.

---

Solution 2: Use a Postgres database as source of truth. Local SQLite for the mobile app and sync the data between Postgres and mobile SQLite database.

Cons:

• Need to support additional DB format
• Syncing logic between databases of different formats
• Probably slower than SQLlite (at 10K users, its 20M rows)

---

Can anyone shed some light into best practice here?

Answer 1

Solution 2: Use a Postgres database as source of truth

Probably slower than SQLlite (at 10K users, its 20M rows)

Amount of data at rest has no implications on the performance of a database. PostgreSQL is a standard well built-out RDBMS to utilize. 20 million rows is a small amount of data. It wouldn't have any problems with this.

And yes, typically mobile apps with offline support have a centralized remote database in addition to their localized one. I personally use SQLite as my localized one, and SQL Server as my choice for the centralized server. But any modern RDBMS (such as PostgreSQL) would be just fine.

If all of the data that's operated on in the mobile app is segregated by the User then that makes syncing data changes easy, as there'll never be data collisions. Otherwise, for data with possible collisions, you'll need to decide on how to resolve (last one in wins?, last one created wins?, first one created wins?, etc).

Answer 2

I think, you need to dive into ETL. There currently two major approaches to it which can be useful in your case:

replication by subscription.

Replication by subscription approach, is built on the central DB, with multiple remote DBs, each getting a subset of data. A replicated table usually has some group identifier field and subscribers (remote databases) receive only records with matching identifiers.

Almost all mature RDBMS has a variant of such ETL. For PostgreSQL it is described here https://www.postgresql.org/docs/current/logical-replication.html

log reading

Another approach to that issue is a log replication. You get changes since last checkpoint from the database log, and send it to remote database - the remote applies it.

For SQLite there is an extension https://sqlite.org/sessionintro.html which does exactly that. So you do not need to worry of data loss from the offline mobile app. Just start the "session" at the start of the app, dump gathered blob to a file at the app shutdown. Send gathered blobs to a central machine during the syncing.

---

In your case, it would probably be wise to do a combination. Mobile app can do a straight log reading (since you have SQLite there anyway). But the central DB must decode the incoming blobs and do conflict resolution on them, then it can extract subscribed data, convert it into similar blobs and leave them in exchange location for mobile apps to pick them up.

Answer 3

I second using PostgreSQL as central database and then using logical replication to sync data to clients. This approach works really well.

This tool is designed on that architecture and handles the complexities for you: https://powersync.co/

Disclaimer I am the Founder & CEO of Journeyapps (company) which provides PowerSync (product).