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?
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.
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
