Attempt at PostgreSQL made

2010-06-08 00:30
Written by

I am writing this post to take community feedback on porting CiviCRM to PostgreSQL, the best way to do it, and to team up with any possible members willing to contribute to this effort. Here are my efforts until now. I have not been able to make it 'all' work on PostgreSQL, but certainly to a degree where I can see light at the end of the long tunnel. I am linking 3 PostgreSQL compatible files here:

structure.sql - this has the columns, primary keys, indexes, unique constraints
data.sql - this is the sample data included with CiviCRM
fk_constraints.sql - this has the ALTER statements to introduce foreign key constraints into the structure, after the data has been loaded

Since last month, I have spent some time on trying to port CiviCRM to PostgreSQL. The primary reason being MySQL's performance. In this era of cloud computing and slower multi-core processors, rather than 3.0+ GHz multi-processor machines, MySQL fails to perform well. I went through various benchmarks on the Internet before starting with this, and discovered that PostgreSQL's performance was by far better than MySQL's on multi-core or multi-processor systems. MySQL's InnoDB tables, which are the engine of choice by CiviCRM, cannot perform parallel query execution. InnoDB relies on the clock speed of the CPU to deliver fast results; whereas PostgreSQL can do parallel query execution (GridSQL/PgPool). I mostly take dedicated servers from The Planet. Finding a high clock speed machine with them is becoming difficult each day.

As of CiviCRM's code, changes to get things working seem endless. Here are a few major changes I remember:

1. PostgreSQL likes column names in double quotes when required. Back-ticks are incompatible. Inclusion of hyphens and/or capital letters, or keywords as column names mandate the use of double quotes.
2. Dates and Times in queries must be enclosed in single quotes, and if mentioned together, must be separated by a space. The date format (same) is specified at several places in the code. Each occurence requires manual repair.
3. Automatic Type-Casting is not available in the latest versions of PostgreSQL. I have included some functions to automatically typecast 1 and 0 as True and False respectively. And Integers as Characters. Directly using TRUE, FALSE instead of 1, 0 is compatible with MySQL. But these clauses occur at thousands of places in the code. A global find and replace did not work because of changing syntax, usage and formatting.
4. LIMIT's syntax needed to be changed to include the OFFSET keyword. MySQL supports this variation.
5. Several functions like IF, LEFT, TIMESTAMPDIFF do not exist or work the same way in PostgreSQL.
6. SHOW COLUMNS had to be replaced with a SELECT query to the information schema. Replacement looks compatible with MySQL though.
7. LIKE is case sensitive in PostgreSQL. ILIKE solves the purpose but is incompatible with MySQL.

Again, these are only some of the things that required change. There are many other types of changes I come across whenever I continue testing CiviCRM on PostgreSQL.

Filed under