Picking a database for your web application can be surprisingly time consuming. There are a variety of databases of various types available and Quicklisp has an interface library for many of them.
Unless you need a very specific capability from the database and you are acutely aware of this fact, chances are that a standard relational database will be more than sufficient to provide for your needs.
Once you have decided to use a SQL database the three main contenders are MySQL, PostgreSQL and SQLite. They are all open-source, mature, actively developed and well-supported by Common Lisp.
For most projects SQLite will work very well, even in production. It can handle database sizes and query volumes far exceeding what most web apps will ever need, it stores the whole database in a single file so the backup process is simply a normal file copy and there is no database server application to configure and maintain.
The down side of SQLite is that the database file must be stored on the same machine as where the application is running because it is not a client/server system. If you need to run the application and the database on separate machines then SQLite will not work. One such reason would be if you need to build a highly available service, i.e. your application must run on multiple machines to ensure it remains available when one server stops working.
If you decide that you need a client/server database it comes down to MySQL or PostgreSQL. This is mostly a choice of personal preference. Both have very good support in Common Lisp. There are areas in which one is better than the other but few applications will ever run into those cases.
I use PostgreSQL in my projects, both for development and production environments. The idea is sometimes put forward that one should use SQLite in development and PostgreSQL in production. This is a bad idea. Even though both implement the SQL standard there are still distinct differences in how they function and in their capabilities. By using different database systems you must cater for two database variants instead of one. In addition, you will always go to production with untested code that can only be tested in production.
