Supabase - Mobile - Offline first - Sync remote Postgres DB to a local SQLite DB

Question

What is the best way to Sync remote-Postgres DB into local-SQLite DB on mobile apps ?

I am currently working on a Flutter mobile project that supports offline-first.

On every opening of the app (or refresh button tapped), I am sending a read query for each table one after the other, getting the last updated rows depending on last_update_time that I can get from my local tables separately.

So each response of the read query will contains the last updated rows of the corresponding table. after that I update my local DB with these rows.

The question is: Is it better and faster If I create an edge function that takes as parameters last_update_time of all the local tables, and this edge function will call the read query of all the remote tables then it returns to the app all the updated rows of all the tables at once ?

Or should I create an RPC Postgress function instead of the edge function ?

Or is the current approach is enough ?

Thanks in advance for any suggestions.

Answer 1

The question is: Is it better and faster If I create an edge function that takes as parameters last_update_time of all the local tables, and this edge function will call the read query of all the remote tables then it returns to the app all the updated rows of all the tables at once ?

Or should I create an RPC Postgress function instead of the edge function ?

Or is the current approach is enough ?

I think batching the reads into a single API call will be better for performance. The edge function approach should be okay, and I suppose Postgres RPC will have marginally better performance.

However, in my experience the complexities of data sync can quickly get unwieldy, so you may want to look into a more comprehensive solution to the problem, especially if you want to sync offline writes back to Postgres.

What is the best way to Sync remote-Postgres DB into local-SQLite DB on mobile apps ?

Therefore, I think this is what you're looking for: https://www.powersync.co/

Works with PostgreSQL and Flutter.