Hacker News Reader

Comment by le-mark | Hacker News Reader

SQLite is such a fantastic database. I've always wondered, is anyone using it at scale in a client server application? How do people handle syncing online/offline in mobile apps?

View on HN

I have but I'd say that SQLite is not intended for use in that scenario. It's an in-process library for persisting data to disk in a generally relational format with a SQL interface. It starts to degrade under highly concurrent read and write workloads that you can experience in client-server applications. At that point, a typical RDBMS with more robust concurrency support starts to be a better choice. I experienced that when using SQLite and we eventually moved the application to a full RDBMS which was more complex but also performed and scaled much better.

Note that this is very much in line with their recommendations from the docs (http://sqlite.org/whentouse.html):

>>SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

SQLite does not compete with client/server databases. SQLite competes with fopen().<<

It's worth noting that when considered as competition to fopen(), SQLite is quite good.

I used sqlite as a backend for scraping 160 million websites a day. It works extraordinarily well if you hold it right - in my case, that meant devoting a database file to each thread. (Arguably in this context, it's competing with flat files, but it does a really good job there.)

Fossil-SCM.

I've always wondered why the creator of SQLite (who's also the creator of Fossil SCM) will state that SQLite is not meant for client/server scenarios ... yet his Fossil SCM (which is client/server) uses SQLite.

https://www.fossil-scm.org

Fossil is the server in this case, not SQLite.

It is fine to use SQLite as the local storage in the implementation of some custom server like Fossil, as there is a separate server (Fossil) that sits in between the client and the data. In fact, SQLite excels at this and usually works better than traditional client/server databases. The scenario you want to avoid is where the client attempts to access SQLite data directly across the network, with no intermediary server. The proverb is: "Always send your query to the data, not the data to the query."

It will work to access an SQLite database across a network filesystem. Just remember that the amount of information that flows between the SQL engine and the storage medium is much greater than the amount of information that flows between the SQL engine and the application. So if the storage is separated by from the application by a relatively slow network, you want to position that network on the low-bandwidth link to obtain the best performance. That means that the SQL engine needs to be on the same side of the network as the storage - hence a client/server database. If you use SQLite to access a network file, the SQL engine will be on the application side of the network and a lot more content will need to traverse the relatively slow network link.

And so, while SQLite will work in a client/server situation, you might be disappointed by the resulting network load and/or performance of the system.

In the case of Fossil, Fossil itself is the "client" for SQLite and it is on the same machine as the data, which is exactly what you want with SQLite. The client of the Fossil server might well be across a network, but that does not matter to SQLite.

@SQLite

So how is what you described above architecturally different than a web application architecture?

Which is a use case I thought you did not recommend.

SQLite does not have a client-server interface. That’s ok because the creator says it is supposed to be an embedded library that gives a better fopen interface. That is, you don’t need to invent file formats (think JSON, YAML, etc). SQLite is not just a database, it’s a persistence layer that happens to have SQL interface.

It places no restrictions whatsoever on the applications that uses it. That will be pointless. Fossil-SCM uses SQLite and is itself client server.

fossil on the server gets low numbers of writes, and not many reads. And being a distributed system, most of the SQLite action is on the client.

I'm running into a weird case where even within an application, concurrency has become relevant because of parallel processing that the application wants to do over the data stored in the database.

But, our desire for both simplicity and obscenely low latency means that SQLite is still the better choice for us at the moment. In the future, though, I can see robust concurrency support being important even in single application use-cases.

If you're in-process, though, you can just lock when you need exclusive access to the datastore.

Ideally, I'd have several separate threads each with fine grained (table-level, maybe even row-level with deferred index updating) control on reading and writing. I admit that I haven't gone sufficiently down this path to know exactly what I'd want though, as it isn't the top priority right now. (We are "fast enough" without concurrency, ... for now.)

In my strength-tracking iOS app Riker, I use SQLite directly (not CoreData) as my local data store, and support full offline-mode. The backend is Postgres.

In the app, for each relation (e.g., a "workout set"), I have 2 tables: a master and a scratchpad. When a user saves a set, a row is written to the scratchpad table. When the user syncs it with the server, a row is written to the master table and deleted from the scratchpad table. When the user wants to edit the record, I first copy it down from the master table to the scratchpad table. All local editing impacts the scratchpad row. When the user wants to sync, only if a 200 response is returned will I copy-up the scratchpad row to the master row. If the set was edited on another device and the local copy is out-of-sync, the server would have responded with a 409 (http conflict code), and the body would contain the server copy, which is then written to the master table. The user can then figure how they want to merge the scratchpad row and the master row.

Anyway...trying to do all this with CoreData would have been a pain, so I use SQLite directly, and works great.

Or to summarize, I handle offline mode, syncing and conflict detection using "updated_at" timestamp columns along with logic in my REST API to returned appropriate HTTP status codes, interpret "if-unmodified-since" headers, etc.

https://itunes.apple.com/us/app/riker/id1196920730?mt=8

Riker on Android is currently in-progress...

I find it difficult to get full offline-mode + sync working correctly. Don't know if you have duplicates in the remote db but I think your approach has a problem: the app send the data (from scratchpad table) to server, the server saves it in DB but the connection drops on device (ex bad connection, user turn off WIFI/3G while syncing) so the app will never get a 200 response. In this case your new data was saved on server but it remains on scratchpad table. On next sync the data will be uploaded as being new: this will cause duplicates.

You are right. The way I solved for this scenario is that when a record is first created on the device, a GUID is created for it (and stored using another column of course). When POSTing new records to the server for syncing, the server will check the GUID and see if the record already exists in its database, and if so, can ignore it (so the duplicate isn't written).

But yes, you're right overall - full offline mode w/syncing, etc is a big pain :)

OpenStack Swift (it's S3-like service) uses SQLite to store account and bucket metadata.

For question #1, an approach is to build a full-fledged Web/REST server wrapping around SQLite: https://github.com/synopse/mORMot

The performance is high!

Spiceworks has a network management app that runs on a rails server on your network and uses sqlite to store everything and run the multi-tenant help desk. I was always surprised how well it worked.

One problem we've ran into with it, is tables with > 400 columns.

Sounds to me like you're using RDBMS wrong.

Not that it necessarily relates to SQLlite, but I've seen this _many many_ times in real world databases in the banking industry.

Personally I don't like it and I am luckily not in IT, but as an analyst I have seen many weird database structures. One can only imagine the business requirements that gave birth to some of them.

To quote a solutions architect to the head of marketing in a previous job: "You asked for a monster, you got a monster."

Is there something about SQLite's architecture that would make such a schema less problematic in MySQL/PgSQL/etc?

Well, don't do that, normalize your scheme.

When your application grows, eventually you need to de-normalize data if you want to keep having reasonable performance for some operations.

You can reach a point where it's not feasible to have everything done by joins.

EDIT: I mean in relational DBs in general. In this case, 400+ columns probably mean SQLite may not be the engine to use, regardless of schema :)

Can you expand on that?

Isn't 400 columns expanded enough?

It's simply creating a local cache of a remote database that runs on Oracle.

I mean...what kinds of problems did you run into? Thanks.

Check out Realm:

https://realm.io/

I really don't understand why majority of websites use mysql or even postgres for that matter. The vast majority of websites would run perfectly fine on sqlite.

Not only would you get rid of network latency, you would also vastly reduce your dependencies.

I have a side project to try to see if I can build a minimal micro-blogging type server application on sqlite and try to test how many requests it can handle per second (reads and writes).

I have a hunch it will be able to handle quite a lot. Specially if the server code is written in a fast language that compiles to native code (e.g. D) with some caching of the most commonly requested items in memory.

Concurrent writes is a problem since the locking is at database level[1]. For a small to medium-low website might be OK depending on the writes volume.

[1] https://www.sqlite.org/lockingv3.html

I'd be willing to bet that 99% of the sites/apps on the web have low to medium writes and reads. Developers like to plan on needing Facebook and Uber scale for their new task list and subscription box services though. SQLite benchmarks are impressive, they beat out commercial RDMS in a lot of comparisons.

As the header of your page denotes, things got significantly improved years ago with the addition of a WAL as the default mode rather than an exclusive write lock.

Also, I think most websites don't even need constant access to the DB, because the data doesn't change often.

Compile a static site from SQLite when someone enters new data, push it somewhere to be accessible and be done with it.

Network latency is only really a problem if you host your db on another machine. An Unix socket has something like 1ns. As for dependencies, the package manager takes care of that.

  > An Unix socket has something like 1ns.

That seems implausible, given that you're lucky to get a context switch down to 1µs if the cache gods smile on you.

Sorry, I meant 1 microsecond.

With the complicated aws-based deployment schemes people are using these days, what's the chance that the db is on the same machine?

SQLite does not work smoothly in many languages (like python) in regards to concurrent access. Without a controlling process (or thread) it's synchronisation possibilities are limited, so it can never scale too far.

Nonetheless I think it's a great idea to build your own single-threaded layer over SQLite and scale that, using a message bus or whatever.

But it's _not_ a drop-in replacement for MySQL based apps.

Yea my hunch/hypothesis does not apply to slow languages like python. That's why I said a fast language that's compiled to native binaries. For example, D, Swift, or Go.

SQLAlchemy with transactions on SQLite should get you very close, probably close enough.

Why get very close when I can just spin up a single micro RDS instance of MySQL or Postgres and not have to worry about it?

Whoa! That's nearly $20 a month, if you're past the free tier, which you will be if you're not already. Edit that's t1.micro, t2 is $13.

Not sure how other people do it but 1 micro RDS serves many projects for me. Well worth the money for me.

It's only a few lines of terraform to spin up a mysql or pg RDS instance. It can't get any easier, so why would I jump through extra hoops to use sqlite?

Its free to host compared to other RDMS which aren't cheap on most IaaS, and spinning up an instance is as easy as deploying it alongside your code.