Background data sync for mobile apps

Question

We're building an API and mobile app on top of a database that has a few hundred thousand records in the main table of interest.

Our mobile developer is pushing hard about pre-loading the app with the full table in a local db, then having a service which the phone can sync changes against an updated_at column.

While this can definitely increase performance of the app by having it search a local store, I'm worried this will create a lot of load on the server as we acquire more customers.

Has anyone else dealt with this? Is it a good idea, bad idea? Can you share some insights and links?

Homan

Check out SymmetricDS – Muskrat

Thanks @NeilMcGuigan , I'm more interested in the merit of the idea over finding tools that implement the sync. Like is there even a point to syncing data to a client that it may never use at a heavy cost to the server. – Homan

I'm not sure why it would necessarily mean a heavier load on the server. Whether a database insert is done "live", or later via sync shouldn't change the load. One main advantage of storing data locally is that the app can be used offline: alistapart.com/article/offline-first – Muskrat

It's not inserts from the phone we're trying to sync. It's changes on the sever getting out to the phone. Obviously the benefit on the phone is offline usage... What I'm worried about though is a ton of clients trying to sync a ton of data that may not even matter to them. – Homan

This article you linked to isn't bad @NeilMcGuigan, but it doesn't cover the problem I'm addressing here. Just think about an outbound reverse proxy cache for example. The server would only ever cache things that were queried by the users. It wouldn't try to cache the entire database. Similarly for this concept of offline usage on mobile, I'm wondering if a more clever synchronization scheme is called for. Moreover I'd like to hear from folks that have already tackled this problem. – Homan

Answer 1

Disclaimer: I've been working on open-source sync databases since 2008, so I am biased about the solution space.

First, don't start out thinking you are going to track updated_at columns on a table, down that path lies madness. Teams of dedicated specialized engineers routinely take years to build sync solutions that don't feel like a pile of hacks.

My team has been building a lightweight embedded database that allows you to store and interact with local JSON objects from native iOS, Android, .NET, or JavaScript code. The database API is all local so you can query, read, and write, without worrying about your network link status. For a developer, the network connection becomes something you configure once, instead of something you deal with every time you need data.

We've also built a server to go with it, that makes it easy to build apps with different kinds of data flows. Your app's server side can end up as simple as a short JavaScript sync function (based on map reduce) which can route data to channels (and grant channel access to users or groups). You can also drive backend processes via channels so user actions trigger real world events like sending a push notification or updating an existing backend API.

If any of this sounds interesting to you we have an active user and contributor community on our mailing list. Everything we do is open source, and since we use open source protocols, there is a wide ecosystem of other projects we can sync with.

Quigley

Chris, thanks for this. I'm on vacation but will read your links. If the phone isn't changing any of the data locally, can we get away with an updated_at sync safely? – Homan

Answer 2

Although this is an old question, I have recently faced this several times and wanted to generalise the problem by writing a design pattern for this issue for any database (in my case sqlite on mobile devices and mySQL on the central Server) and any schema. Here is the design Pattern on google docs:

mobileSync on Google Docs

Also, there is an App and Unit tests to prove that the pattern works as specified. See GitHub Link:

IOS App for MobileSync

Or, search on github for johngoodstadt and then mobile Sync. The Android version is coming soon (May 2016).

It does not provide total sync services that paid for services would provide but if your requirements are typical of most mobile solutions it could save you much time in experimentation. Please feedback any issues errors,bugs so I can refine this designPattern/Code

Thanks
John Goodstadt

Unexperienced