'Lite' Databases (Re: sqlite3 and dates)

[Chris Angelico]

On Thu, Feb 19, 2015 at 2:33 PM, memilanuk <memilanuk at gmail.com> wrote:
> At this point... I don't think concurrency is going to be a major
> requirement for what I have in mind.  For one project, only a few people
> will be writing to the DB, and only by a stroke of luck would it be at the
> same time, and it would be very unlikely that they would be modifying the
> same record at the same time due to physical constraints.
>
> For the other... there may be anywhere from 1-10 (maybe more, but doubtful)
> entering data (creating new records for competitors, or entering existing
> competitors in a tournament).  I have a hard time picturing that few people
> stressing a modern computer system enough to where SQLite couldn't keep up
> (thinking web-based interface using Flask or something similar).  In the
> latter case, one of the over-arching priorities is that it be easily
> distributable, as in that people with relatively little knowledge of a
> database be able to set it up and run it.

Both of these need concurrency. You may not need _heavy_ concurrency,
but you certainly do need to cope adequately with multiple
simultaneous users. Your first case is a perfect example of why you
need a database rather than flat files; in fact, you want the
granularity of record-level locking rather than table-level. Alas,
SQLite3 does not actually offer this (in fact, I'm not sure it even
offers table-level locking); once any process begins writing to the
database, all others are locked out (even for reading) until it
finishes. That's fine if you (a) don't write very often, and (b) don't
write very much, but the fact that you're trying to modify different
records doesn't help you here. It does with full-scale database
systems, where you actually do have record-level locking, but not with
SQLite3.

Your second case definitely demands concurrency. I've seen tournaments
for various games where database-level write locking would be a
critical problem, and that with only a couple hundred players and a
handful of people keying in data. Of course, it depends how much
effort it takes to key that in. If the humans have to enter extensive
reports on the tournament results, they'll spend most of their time
doing that; but if their job is to quickly say "X beat Y 2-1" and then
get back results saying "X plays Z next, Y gets a bye", then you need
your database to react quickly, even if three other people are
entering results. So it's a huge question of human versus computer
workload... but once again, chances are you need record-level locking.

It may very well turn out that SQLite3 is entirely capable of the job.
But it's certainly not proven by your above statements, and I would
start by assuming PostgreSQL by default.

ChrisA