'Lite' Databases (Re: sqlite3 and dates)

[Chris Angelico]

On Fri, Feb 20, 2015 at 7:26 AM,  <rurpy at yahoo.com.dmarc.invalid> wrote:
> I'll point out that five people in this thread (by my
> count) have said that Postgresql requires a significant
> amount of work to setup and use.  Only you and Steven claim
> the opposite.  (And it sounds to me like Steven does not
> have a lot of experience installing, configuring and
> maintaining Postgresql -- please correct me if I am wrong
> Steven.)

I don't know about Steven, but I do have a certain amount of
experience installing, configuring, and maintaining; and the biggest
thing I've noted about the "configuring" part is that you can ignore
it most of the time. The docs caution you that the defaults are
deliberately NOT set for maximum performance... but you won't normally
need maximum performance initially anyway. (In any case, "maximum
performance" requires a lot of knowledge of workloads, resource
availability, concurrency model, etc.) The defaults are usually good
enough; the most common changes I've needed to make to Postgres
configs are:

1) Weakening the default security model of listening only on
localhost, to allow other computers on the network to connect to the
database. Obviously it's correct for the default to be secure, here;
anyone who wants to allow remote access will need to be aware of what
s/he is doing anyway, so requiring that a couple of files be edited
isn't a big deal.

2) Increasing logging, eg turning on the logging of all queries. Not
particularly common, but can be handy; and it's a trivial edit anyway.

3) Permitting certain special-purpose users to bypass the normal login
mechanisms, eg for scripted backups. Similarly to #1, the defaults are
absolutely correct, and if you want to weaken your security, it's only
fitting that you have to go in and edit some configs.

Given that I frequently don't need to do _any_ of these, it's safe to
say that configuring PostgreSQL doesn't take a lot of effort at all.
Similarly, "maintaining" isn't really a thing; apart from maintaining
my own data tables (cleaning out junk tables that were for one quick
test, which I often forget to drop when I'm done with them), I don't
have to spend any effort making sure the database keeps running. (In
contrast, a busy MySQL server probably needs to have myisamchk run on
it periodically, which *does* count as maintenance.)

So while it makes sense to talk about *experience* "installing,
configuring, and maintaining", it's really just the installing part
that actually demands effort. And that can be mitigated in a few ways,
but ultimately, there's no single simple solution for everything. Even
SQLite3 has issues with that - as soon as you have two programs that
try to read the same file (eg your application, using Python's module,
and the sqlite3 command line tool), you have version differences, and
quite a number of my students have come to me asking why on earth they
can't upgrade their SQLite3 properly. (Though usually both versions
are "new enough", so it doesn't matter. It's just a point of
confusion.)

ChrisA