PSCS Wiki

PostgreSQL is an SQL database engine.

In VPOP3 2.x, VPOP3 uses a database engine called SQLite for its database needs. SQLite has limited locking facilities, so on busy systems we encountered problems where databases would need very frequent updates, and the lock limitations would cause the updates to take a long time. For this reason we added optional support for PostgreSQL to later versions of VPOP3 2.x.

Starting in VPOP3 3.x, we have moved the message store to a database to improve performance and reliability. We decided to use PostgreSQL for this purpose since it was used successfully in VPOP3 2.x. This means that PostgreSQL is required for VPOP3 3.x to work.

We evaluated several database systems for use in VPOP3. Because of the low cost of VPOP3 it was necessary to consider the cost of the database system. Obviously, it wouldn't be practical for a 5 user copy of VPOP3 (costing £50) to require a database system which costs £500 to licence.

Some open source software, such as MySQL, is licenced using a 'GPL' licence. This means that it can be used in other GPL projects free of charge. VPOP3 is not GPL. To use MySQL in VPOP3 would require a commercial licence for each installation of VPOP3, which would be prohibitively expensive. (There are also other issues with MySQL, also see MySQL vs PostgreSQL).

SQLite, PostgreSQL and MSSQL Server Express were products we considered which had suitable licences.

MSSQL Server Express was ruled out because it has a database size limit of 4GB. Given that we were planning on using it as a message store, that limit could be easily reached with copies of VPOP3 Enterprise where all messages are stored on the server.

VPOP3 has quite unusual database requirements in that it makes a lot of writes to the database in comparison to reads. MySQL, for instance, performs best when there are many more reads than writes (eg on a typical website). SQLite suffers from this as well. It is extremely fast if doing lots of reads, but when you do lots of writes the lack of a coordinated lock system can hit badly.

This left PostgreSQL which is generally considered to be very stable and robust, although it is less well known than MySQL.

Firebird was another option we considered, but did not have much experience with it, so, although it seems suitable, we chose to use PostgreSQL instead.

Theoretically SQL is SQL so we could support other database systems as well. However, most SQL database systems do things in different ways.

For instance:

• to get 3 items out of a query, in PostgreSQL and MySQL you use SELECT * FROM table LIMIT 3. To do the same in MSSQL you use SELECT TOP 3 * FROM table
• to implement an automated sequential column, in PostgreSQL you use SERIAL as a column attribute. In MSSQL you use the IDENTITY column attribute. Also, these two things are not identical in behaviour.

Also, we use triggers and stored procedures widely. These use different languages. PostgreSQL uses PL/pgSQL, MSSQL uses Transact-SQL and MySQL uses its own syntax.

There are lots more differences between the different database systems as well. This means that there is no realistic way to 'abstract' the database system without restricting ourselves to the 'lowest common denominator' which would impact performance and maintainability. We would have to write a set of database rules for each type of database to be used, which would make it harder to add features and support VPOP3.