9

I've seen a couple of SQLite performance questions here on Stackoverflow, but the focus was on websites, and I'm considering using this DB in a client-server scenario:

  • I expect 1-10 clients for one server for now, could go up to 50 or more in the future.
  • slightly more reads than writes
  • the DB would sit behind a server process (i.e: not using direct DB access through a network)

Would using SQLite make the app less responsive as opposed to using PostgreSQL? My intuition tells me that it should be ok for these loads, but maybe someone has some practical experience with this kind of scenario.

rpg
  • 7,746
  • 3
  • 38
  • 43
  • 2
    Since a website *is* a client-server application, I don't get why this is different. What do you think is different? Why are you saying a client server application is not essentially the same as a web server application? – S.Lott Aug 24 '09 at 10:14
  • Essentially it is the same, but the SQLite scalability question was a Stackoverflow rep counter, so I would imagine that it had totally different access patterns. Another important difference would be that I can tightly control all the tech stack, whereas with a browser some choices are predetermined for you. – rpg Aug 24 '09 at 10:58
  • 1
    @rpg: Yes, when you have your own c/s setup, you have more control over the whole as in a website setup. This in my opinion speaks for SQLite -- when it is scalable in a website setup, why shouldn't it be in your setup, where you have more control? Further the access patterns can be a problem, of course. Many Writes (intermixed with reads) lay more strain on a DB engine as a pure read-only-setup. Sure! I would say it depends on the amount of data you typically change in one round-trip (number of rows, columns and tables ...). Are you able to put those writes into one transaction ... and so on – Juergen Aug 24 '09 at 12:01

3 Answers3

14

I did use SQLite for a major client/server product used with ~10 concurrent users and I deeply regret that decision. In my opinion - PostgreSQL is much more suitable for client/server scenarios than SQLite due to its fine locking granularity.

You simply can't get very far when the entire database is locked whenever someone needs to write something ..

I like SQLite very much (I even wrote a commercial utility for comparing SQLite databases - SQLite Compare but I don't think it fits the bill when you have client/server scenarios.

Even SQLite's author says that it should be used as a replacement for custom file formats and not as a full blown database server. I wish I took his advice more seriously..

Bleeding Fingers
  • 6,993
  • 7
  • 46
  • 74
Liron Levi
  • 1,139
  • 6
  • 10
  • In my opinion you can only get into trouble with fine locking granularity when either you have multiple server processes accessing one DB file or your clients access the DB directly. When you have one server process -- how can you get locking problems (Ok, I must add, that you don't use threads ...)? – Juergen Aug 25 '09 at 15:32
  • 2
    Actually - I have a single server that access the database in the name of multiple client processes. So what? In my case - every client is represented by a worker thread in the server and when you have multiple clients working on the server you'll also have multiple threads that are working on the database engine concurrently. Since it is very hard to predict in advance how database access will actually be performed (i.e., read vs. write access patterns) I strongly suggest not to make such a limiting decision (using SQLite instead of PostgreSQL) right from the beginning. – Liron Levi Aug 25 '09 at 16:36
  • 1
    Is there a new home for your SQLite Compare utility? – Jonathan Hall Aug 28 '12 at 20:32
3

You didn't mention what operating system and Postgres versions you are using. However, before considering change of database engine, try to do some logging and benchmarking your current database with typical usage, then optimize "heaviest" questions. And maybe your backend processing load makes DB question time irrelevant? As SQLite is a file-based DBMS, concurrent access from multiple processes will degrade performance when client number grows up (edited after comment)

Following question may be helpful: How Scalable is SQLite?

Community
  • 1
  • 1
tomash
  • 12,742
  • 15
  • 64
  • 81
  • The performance degredation will only happen with direct concurrent access -- but as the OP said, the accesses are indirect with one server process inbetween ... this might give a little slowdown, but no degredation. – Juergen Aug 24 '09 at 10:35
  • 1
    Right, I missed the one server per multiple clients part. Anyway, if DB construction is so simple it can be migrated to SQLite (no DB-side processing, triggers and stuff), I don't think DBMS performance can be serious problem when DB is reasonably indexed. – tomash Aug 24 '09 at 10:59
  • Haven't written any code yet. I'm working on the project's risk list atm, and scalability is one of them. Not using any Postgres, but the OSes will most likely be XP, Vista at first, later Linux. – rpg Aug 24 '09 at 11:02
  • 1
    @rpg: As I said in my answer, all depends on the actual strain on the DB. I also would say, that SQLite is in a similar performance range as PostgreSQL, when you can cross out concurrent accesses -- so with your described setup I would claim (without knowing Postgress to much) that the difference will be small. The dependence of scalability on your actual coding will be most likely much higher. When you don't need all the higher DB-stuff (eg. triggers) I would recommend SQLite -- and 50 users/clients don't seam many to me! – Juergen Aug 24 '09 at 11:42
  • @Juergen: maybe 50 users/clients don't seem much to you but you need to consider that every one of them can potentially generate multiple SQL statements that need to run concurrently with queries that are ran by other clients. In my experience - SQLite locking causes major delays and GUI timeouts even when ~10 users are working concurrently. It is true that SQLite is very fast database (when only a single user is working on it). It is also very easy to administer. However - SQLite is EVERYTHING BUT a scalable database solution (and I've learned this lesson the hard way)... – Liron Levi Aug 26 '09 at 10:42
1

I would confirm to S.Lott's answer.

I dont know how SQLite performs in comparison to PostgreSQL, since I don't know any newer meassurements, but my own experience with SQLite in a rather similar environment is rather good.

The only thing that might cause troubles in my view is that you have rather many writes. But it all depends on the total number per second I would say.

Also your setting to have one server process is optimal for SQLite in my opinion -- so you circumvent its weakness in multi-tasking.

Juergen
  • 12,378
  • 7
  • 39
  • 55