Under review

Postgresql Compatibility

Luis Angel Uriarte 8 years ago updated by glpi 4 years ago 7
Allow the installation to choose between MySQL and PostgreSQL

This would be nice. Today, in my company, we have about 30000 tickets per month and when we need to do some reports MySQL performance is a bottleneck and we need to run many of the queries overnight in order not to impact operation. Other technic we use is to pre calculate and pre process some data in real time using triggers.

Using PostgreeSQL would help a lot.

Under review

That discussion is not really about pgsql as such, but more about using a standardised tool which can talk to any database and because it's intended to talk to simple databases, it cripples available queries by disallowing JOIN or subqueries.

I really don't get why people are afraid of pgSQL. MySQL is ok for small jobs but it has a limited range of queries and data types, plus its clustering is badly broken in ways which make it look OK until it falls over unexpectedly (as our devs found out the hard way).

OK, I do get why people are afraid. Yes it uses a bit more memory when you install it, but the days of 64Mb machines are a long way in the past. After having used MySQL for 20 years and then learned to use pgSQL, everything is minor. pgSQL is stricter about some queries but our experience has been "you should have written them better in the first place" - and when fixed they ran faster on the mysql boxes too.

When I converted my Bacula database to pgSQL (50 million entries) it dropped ram requirements by over 60% - 40Gb down to about 7. Queries run 10 times faster and database dumps run 3 times faster. 

MySQL simply doesn't scale. It's good for small, simple databases but GLPI isn't small or simple.

Hi Alan,

Everything is about contribution. We need more time & people to work on the subject.

Yes we know that PostgreSQL is good, and yes we know that we want to support it.

We just don't have right now the time & money needed for such a big change.

If you're willing to help, there's a lot of different ways to do it. Feel free to join us on the mailing list, IRC or Telegram to discuss that.



Adding PostgreSQL support is a very, very huge job.... But we've already made some changes in order to achieve this, like:

- dropping all raw SQL queries (this is not yet finished but a lot of job has been done already),

- switching to PHP PDO (will be possible only in a major release, since this would break many things), etc

I've posted threads about that on the dev mailing list:

- https://mail.ow2.org/wws/arc/glpi-dev/2017-10/msg00007.html

- https://mail.ow2.org/wws/arc/glpi-dev/2017-12/msg00002.html

See https://github.com/glpi-project/glpi/pull/5968 (this is still a WIP, and there is no planned date).