Hi all, reading the docs I saw a line that says that SQLite it’s ok for small to medium apps, but what does that means in terms if users knowing upfront that this is not an exact science??
In a normal, say blog or forum app, how many users is a small to medium app, and what are the pro-cons of using this approach (SQLite) instead of a Postgres or MySQL ?? (Besides setup time)…
If you’re using a monolithic database design, keep your transactions tight, and you may run into OS issues (like available file descriptors) before you run into database issues with SQLite. Depending on your application, you might also consider using separate databases for each user. Really easy to do with SQLite. Just create a new file from your app. Maybe maintain a master database for login credentials. Not very easy to do with a typical database server, where database creation is typically an admin-level feature.
Here’s how I approach most any app unless I have a budget for Oracle iron and a DBA to babysit it… 1. Be flexible with design. 2. Keep transactions tight. 3. Monitor performance continually during development so I can attack bottlenecks before they become expensive.
Thank you for your answer Brad, can you explain what you mean by keeping a transaction tight?
Everything Brad said. From user side we use the SQLite/RealDatabase in a multiuser environment and have 10K+
records with 3 - 4 concurrent users at a time and haven’t had any problems.
So around 100 concurrent users are ok in SQLite?
Read the docs on transactions. It means you need to start and commit you transactions quickly. For example don’t start a transaction then wait for user input before committing them. Make sure you accommodate error checking and read up on rollback, etc. If your in a multiuser environment you need to handle the possibility of multi-write transaction errors and handle it, either retry or notify user, etc.
I wouldn’t say that. There is no clear definition on this. It depends on how it is used. If 99 users are only reading the database and one is writing - no problem. 100 users adding record would probably be problematic, but I have not had not had experience with that many users. My guess others would tell you to move to something more robust.
It depends. SQLite only allows one connection to write to the database at a time. So if you’ve got a lot of users writing to the database at the same time don’t use it.
In my opinion it sounds like you should avoid it and either use PostgreSQL for free or purchase CubeSQL.
See High Concurrency.
[quote=22855:@Francisco Lobo]So around 100 concurrent users are ok in SQLite?
You’re not gonna get that kind of a number out of anyone who really understand the issue. If you have transactions that remain open for several seconds and block other users, the comfortable user count is going to go down quickly. It really is entirely dependent on your database design and your transaction design.
Transactions tight… A common rookie mistake with any database is to use a database record/table lock for a user action like editing. So user opens record, it gets write locked on the database, no other clients can read/write from that table until that user is done. That’s an example of not keeping transactions tight. Another might be computing something while a transaction is open, rather than precomputing and then doing all your inserts/updates quickly together.
Perfect explanations… Thanks everyone!!!
I’m often surprised when some developers on here say things like ‘Oh you won’t have that many users so you don’t have to worry about that. Look at this forum it only has about 30 users online at the same time.’
Be smart. Plan ahead.
Something for you to think about:
Mason, You are totally right! Specially when you are investing an insane amount of time in your tools and code base. So why not spend it planing thinking big… Code once and re-use!. How ever the monolithic approach sounds very very interesting where users have their own database. It is not for my current application but I will keep that in mind!
Any how. It is an amazing community! I am grateful with your help!
@Brad: “A common rookie mistake with any database is to use a database record/table lock for a user action like editing. So user opens record, it gets write locked on the database, no other clients can read/write from that table until that user is done. That’s an example of not keeping transactions tight.”
So how do you deal with it? What is the proper way to make sure the record hasn"t been modified in the meanwhile? I always see those “dont do it this way posts” but miss the “a good way to deal with it is” ones.
[quote=22897:@Markus Winter]So how do you deal with it? What is the proper way to make sure the record hasn"t been modified in the meanwhile? I always see those “dont do it this way posts” but miss the “a good way to deal with it is” ones.
Use columns or tables to keep track of what records are checked out to whom and for how long, e.g. “EditingUserId” and “ReservedUntil” columns. You need to manage that logic.
In the Xojo (nee REALbasic) Database API, don’t use the “Edit” method to lock up a record while a user edits it. Turns out that’s exactly what it was designed for back in the day, and it probably works fine for one-person databases, but it is horrible for multi-user.
I’d say with 100 users concurrent, things may be far too slow for the users.
So you would need to use load balancing and run serveral copies. But than you don’t use SQLite any more and better a Database server to keep the data.
Look up optimistic vs. pessimistic concurrency control. I wrote a column about this for XDev Magazine a couple years ago.
The dirty little secret is, it doesn’t matter. Update only the columns that have changed. And update only the delta change for accumulated values, like totals.