I use both PostgreSQL and SQLite in my job. I have mixed feelings about
SQLite. If you play by its rules, it works very well, but I think you
have to understand its rules well. If you do not set up your indexes
correctly and do not use transactions correctly, performance can be
horrible, but if you do, its performance is excellent. For some reason
I doubt I will ever understand, its developer thinks that it is a good
thing not to require a column to hold any particular type of
information. One record can have an integer in a field, while the next
record in the table can have a string in the same field. I would much
rather have my database enforce type consistency, and tell me when I'm
screwing up. There is no fixed date format. I suddenly found that one
program that used SQLite began writing dates as human-readable text
strings for no reason I could understand, after it had been writing them
as Julian dates (a floating-point number representing the number of days
since a given date) happily for years. I had to rewrite the
corresponding program that reads the data to be able to handle either
strings or Julian dates correctly and transparently, which was not easy.
But it is nice to have SQLite available for use in programs that will be
installed at multiple customer sites where we can't be sure if the main
database will be PostgreSQL, SQL Server, Oracle, or something else.
RobR