Using PDO/PostgreSQL instead of PDO/MySQL

[Pierre-Luc]

Hi,

I was wondering if any of you ever tried to run ProcessWire on PostgreSQL. Since 2.4, Ryan seems to have moved PW to PDO completely, and that would make things easier to change the database driver. From running quick greps on wire/ and site/ on a few installed projects, it seems like not too much hassle.

I'm asking this because I'm thinking of implementing a few geometry fieldtypes and "geoselectors" using PostgreSQL/PostGIS.

@Ryan, any thoughts on doing this?

[ryan]

The change to PDO was motivated more by the support of named parameters. Though bringing more abstraction to the DB layer also seemed like a good move for the long term, even without current plans to support other DBs. While PDO does abstract some of the considerations with supporting other databases, ProcessWire's queries are designed for MySQL, and are optimized for MySQL behavior. There's a lot of influence from the book: High Performance MySQL. Supporting another DB platform like PostgreSQL would probably not be such a straightforward thing. Though the truth is I don't know for sure, as I've not worked enough with PostgreSQL to know how similar or different the queries we use would be. But if you are looking at implementing some new fieldtypes, it would be best to look for how you might implement them with MySQL, since support for other DBs like PostgreSQL is not currently in the short term plan.

[Pierre-Luc]

Thanks for your answer. There are big limits in regards to geodatabases in MySQL that I would prefer not to deal with. I'll see if there are ways to implement what I have in mind using MySQL without killing performance and making sure the calculations are ok. I hear there have been improvements in 5.5 but my go-to is usually PgSQL for that kind of work.

Do you have in mind any requests or methods that might be very specific to MySQL in Wire?

[Beluga]

Have you looked at MariaDB (drop in replacement for MySQL, soon all major Linux distros will have it instead of MySQL):

MariaDB Geographic Features

Screencast: MariaDB GIS demo

[Pierre-Luc]

MariaDB pretty much has the same features MySQL has, and still has a problem that all calculated on a plane, which can lead to problems when calculating distances, mostly if points are very far from each other.. PostGIS is much more advanced, but I'm still considering if MySQL/MariaDB would do the job.

[Beluga]

MariaDB pretty much has the same features MySQL has, and still has a problem that all calculated on a plane, which can lead to problems when calculating distances, mostly if points are very far from each other.. PostGIS is much more advanced, but I'm still considering if MySQL/MariaDB would do the job.

Distance calculation is improved in MariaDB 10.1

https://blog.mariadb.org/jquery-and-gis-distance-in-mariadb/

In MariaDB 10.1 the third coordinate will be present, which then means that the height differences could be considered in the distance calculations and the outcome would be more precise.

[clsource]

Can I use MariaDB instead of MySQL?

or Processwire will have some issues?

[diogo]

https://processwire.com/talk/topic/3458-mariadb/

[rusjoan]

Hope to Ryan that select between DB engines will be real soon

[mrjasongorman]

MariaDB works fine with Processwire, we're using MariaDB where i work as our productions database for a few websites running on Processwire.

PostgreSQL does sound interesting though. Is it possible to use a 3rd party ORM to hook into different databases?

[Pete]

+1 for MariaDB - running smoothly.

The only issue I found, and it was a non-PW issue, was I was querying an integer field in MySQL with more characters than the field can accept and MySQL just truncates that - MariaDB does not and I think treats it as 0 or empty or something. Not that that is at all relevant for anything to do with ProcessWire, other than there is no harm switching to MariaDB.

In fact if you're using WHM and cPanel you can just switch for all your sites now via WHM. Take backups first though of course as it's very much all-or-nothing.

[toni]

@Pierre-Luc Even if this issue is from 2015. Did you give Postgres/Postgis a try? What did you end up with?