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