@anonymous-piwik-user opened this Issue on January 13th 2009

I want to be able to use a postgres database (instead of mysql).

please see http://piwik.org/faq/how-to-install/#faq_55

Latest update: see the Piwik fork with Postgresql support. Great contribution by Sridhar from the community
Keywords: wishlist

@robocoder commented on January 13th 2009 Contributor

See ticket #425.

@mattab commented on February 2nd 2009 Member

Thank you for the suggestion!
The piwik team decided to not support Postgresql as it would make the development process much harder for us, for a quite small number of users requesting Postgresql.

Piwik officially supports MySQL only for now.

@anonymous-piwik-user commented on February 28th 2009

Let's go here : http://github.com/klando/pgpiwik/tree/master

If you are interesting, then :

git clone git://github.com/klando/pgpiwik.git

@anonymous-piwik-user commented on March 2nd 2009

too bad. I would have loved to see this happen. I would say that people that would use postgres would rival that of mysql.

@anonymous-piwik-user commented on March 5th 2009

Do I interpret right that you expect to have more tickets opened to request postgres? :-) I usually don't even bother to check a mysql-only project, let alone open a bug about it in their project, so your assumption about the size of the postgresql userbase may be extremely skewed.

Apart from letting you know that many people use db other than mysql it is a bit more important to note that even php makes it possible to write db backend independent code, which could support nearly any database backends, including, but not limited to psql and sqlite. I do not believe it would visibly make development harder to use standard SQL and standard DB API, since at a first blick your code is pretty much mysql independent (only a few places may require tuning) and you seem to use backend independent code (at least you include it in the distribution). So it may only required a few changes here and there. Pity I do not like php at all, so I cannot offer to patch it up, and you'd rejct anyway from an outsider. ;-)

So, please consider to use database backend independent code and API. It would be nice and The Right Thing(r)(tm).

@robocoder commented on March 6th 2009 Contributor

Re-closing. This is a resource issue; not a techical one.

We need people to step up to:

  • implement the changes to core
  • provide ongoing support to end users
  • test releases for regressions
  • maintain the backend as development continues
@anonymous-piwik-user commented on March 6th 2009

Replying to vipsoft:

Re-closing. This is a resource issue; not a techical one.

We need people to step up to:

  • implement the changes to core
  • provide ongoing support to end users
  • test releases for regressions
  • maintain the backend as development continues
  • I have implement change to core,
  • I provide ongoing support via mail and via irc freenode #piwik (well, it is irc, ask, wait, wait,wait, answer)
  • I didn't have test yet, but I saw that your are in the pocess of Zendify so, it will be easy.
  • I merge with the svn as soon as I am aware of svnchanges (despite the fact that the ML for svncommits is down, so I have to check from time to time)
@mattab commented on March 11th 2009 Member

A good ressource to read by wordpress team: http://codex.wordpress.org/Using_Alternative_Databases

@anonymous-piwik-user commented on August 3rd 2009

Well, since Piwik uses ZF for database handling, it wouldn't be that hard, "just" editing the SQL statements to be compatible with other DBMS. I see further problems in plugin development, there will be coder who make their plugins only MySQL compatible.
I don't know how many people here would like to see their Piwik with postgres, but I think it can be done if we work together.

@robocoder commented on September 11th 2009 Contributor

Blockers:

@anonymous-piwik-user commented on May 6th 2011

I'm keeping MySQL on my VPS just because Piwik requires it. I decided to start support for PostgreSQL in Piwik, the plugin approach seems the right first try. When it stabilizes in my production VPS, I'll report here.

@anonymous-piwik-user commented on November 9th 2011

I strongly ask for Postgres support as MySQL has various bugs and critical problems (like security, speed, scaling).

Supporting any database is very easy. Everybody knows that plain SQL queries within sourcecode are a big risks, because some people know how to write good queries, others don't. Injection and speed issues are unavoidable.

There must be something like http://search.cpan.org/~abraxxa/DBIx-Class-0.08195/lib/DBIx/Class.pm for PHP. Switching shouldn't take that much time, if you still use plain queries in sourcecode, and will greatly improve security and portability.

PS: MySQL is already on it's way to become payware and I doubt that many users would pay for it just to use Piwik :-)

@anonymous-piwik-user commented on January 24th 2012

I'm interested to use Piwik with Postgresql too !

Right now, the only reason why i'm still using Google Analytics is because Piwik don't support pgsql...

Is there any chance to use piwik with alternatives databases soon ?

@anonymous-piwik-user commented on June 2nd 2012

Replying to grin:

I usually don't even bother to check a mysql-only project, let alone open a bug about it in their project, so your assumption about the size of the postgresql userbase may be extremely skewed.

+1 for Postgres support
(I registered just to reduce the skewing effect)

@anonymous-piwik-user commented on December 23rd 2012

Another +1 for PostgreSQL support. Piwik is a really nice piece of software, but I definitely won't install another DBMS so I can use it.

@anonymous-piwik-user commented on December 23rd 2012

Another +1 for a non mysql version of Piwik here! PostgreSQL would be great! Also other great software like Drupal is supporting postgresql nowadays.

@mattab commented on January 2nd 2013 Member

like we said we are keen to do it if someone submits a patch. There is clearly a lot of interest out there but not yet someone who has the skills and time to invest in such a huge coding spree!

@anonymous-piwik-user commented on January 25th 2013

Reopening, as it appears that the devs are now welcoming patches for this request.

Also +1'ing, because I refuse to deal with MySQL and Oracle anymore. I could switch to Maria DB, but Postgres seems to have all the momentum.

Would love this feature. Without it, I'm going to go looking for another solution.

@reedstrm commented on March 27th 2013

+1 here as well. I've looked at Piwik several times, and mysql has been the deal breaker each time.

@anonymous-piwik-user commented on June 10th 2013

+1 here as well. I'm using Piwik for a volunteer organization, and we are sensitive to the privacy issues posed by for instance Google analytics.

We're using Piwik with mysql locally right now, but all other resources are running against a postgres cluster, with all the bells and whistles, most importantly proper data backup and protection routines. It would be great to get rid of the lone mysql service running locally.

@anonymous-piwik-user commented on December 18th 2013

+1. We have a small VPS that we use for hosting our site (Apache+Postgresql), and it's working quite nicely, but having to add MySQL just for Piwik uses up almost all memory, even if we configure MySQL to use as little as possible.

@anonymous-piwik-user commented on January 24th 2014

+1

MySQL is a big joke. (It never makes me laugh though.)

Unfortunately MariaDB has to have all the MySQL strangeness in order to work as a drop-in replacement. This, however, makes it just as irrelevant as an alternative.

I want to get rid of that installation for the same reasons already mentioned by others:

@Jon-IB commented on January 24th 2014

I'm interested in taking on the work of adding support to PostgreSQL.

The actual amount of SQL that needs to be changed doesn't seem too dramatic. I'm more concerned with the following:

1) There isn't much of a db abstraction layer, as far as I can tell.
2) Plugin developers would need to support both MySQL and PostgreSQL. Or alternatively, each plugin would need meta-data to indicate which database(s) it supports.
3) As noted by others, testing and CI would need to cover both databases.

Most importantly (for me), does anyone on the core team have the time to map out an approach for a database abstraction layer? Once that's done, I think we can handle the pgsql-specific changes.

Also, any thoughts on the other concerns above?

EDIT: Looks like there's some active discussion on this in the mailing list. I'll follow up there.

@mattab commented on January 27th 2014 Member

Thanks for showing your interest in this topic! While we are not actively engaged in this work (yet) many team members are interested in running other DBs with Piwik. the issue is, that it's super complex.

Most importantly (for me), does anyone on the core team have the time to map out an approach for a database abstraction layer? Once that's done, I think we can handle the pgsql-specific changes.

We could map out an approach, but honestly, unless we do the full work, do you think anyone would do it?

If anyone is really interested, yes we could work for a few days / weeks on the basics of DB abstraction layer. But converting the whole codebase to use such layer, is rather huge and complicated task.

If you know any software engineer/developer with 3-4 weeks available and a big brain willing to explode, and a big interest in this work, please let us know here!

@Jon-IB commented on January 28th 2014

(Apologies, I think I misspoke -- it looks like Piwik has a db abstraction layer, in the form of the Db class and the Zend_Db_Adapter. What's missing is a data-access layer, since all of the SQL code is intermingled with the business logic.)

Matt, I can spare a developer for 3-4 weeks, but I doubt we'll have time to do much more than that right now. So I'm hesitant to dive in, given that we might realize after 2 or 3 weeks that we'd need to spend a lot more time on this. If that happened, I would need to find an alternative solution quickly.

One creative solution is to do this incrementally, by updating the code file-by-file to use a new data access layer. This could only work if some of the code could be updated to use a new data access layer, but the rest of the code was left as-is. I'm not sure how exactly to do this, but I'm guessing the two approaches could co-exist, since most or all queries seem to go through the Db class.

This would allow the core team to implement and test an approach that you're happy with, since you'd only have to implement a data-access tier for a few files. That would prove that it works, and your code would give us a template to follow. You wouldn't even need a PostgreSQL database to test those changes; just set up two different MySQL logins with access to the same schema.

Once you're done with the proof of concept, we would come in and move the SQL into a new data-access tier for as much of the code as possible, following your template. Our initial focus would be all of the SQL that is specific to MySQL, since the app would be usable for both PostgreSQL and MySQL if we get through all of that code and leave the rest of the files as-is. But we'd try to get through all of the SQL while we had focus.

I can offer at least 3 person-weeks of work, and hopefully a bit more, which might be enough to get through all of the code. Other developers could help with this as well, if any are available. And any remaining files could be handled by the community or by us as time permits.

I'd be more comfortable diving in if we followed this approach, because we'd be more likely to have a production-quality solution within 3-4 weeks that we could deploy.

The big downside is that the code might end up in a Frankenstein-like state for some period of time, with some code calling to a data-access tier, and other code with inline SQL. You might not consider that to be "production-quality".

Having said that, a data-access tier might help structure the code more cleanly. If so, then the incremental approach would let you migrate the code towards that structure without having to take on all of the work within a single release.

@Jon-IB commented on January 28th 2014

Ah, I see a few classes under the Piwik\DataAccess namespace. Is this the start of a full data-access tier? Or is this a solution for a much smaller problem?

@anonymous-piwik-user commented on January 28th 2014

Please see https://github.com/sri-soham/piwik . This is an (almost) working version of Piwik with PostgreSQL suppport.

@Jon-IB commented on January 29th 2014

Thanks, @low. I saw a link to that repo in another ticket, so our developers have been looking closely at it for the past few days. I myself looked too quickly; I saw a change to a file that commingled a MySQL and PostgreSQL error code (core/DataAccess/ArchiveTableCreator.php maybe?), so I quickly assumed that there was no db abstraction layer in this repo. I assumed incorrectly that @sri-soham was working initially on a proof of concept, without changing the code structure.

One of my developers mentioned that this repo uses DAOs to encapsulate both data access and database abstraction, so I looked more closely. The DAO implementation looks pretty darn solid to me. The pgsql classes extending the MySQL classes to take advantage of common logic. It would be straightforward to add support for additional DBMSes, if that was desirable.

Matt et al, have you been following this work? What do you think of this implementation? If the remaining issues are resolved, is it reasonable to assume that this code or something like it will make it into the upstream repo in the foreseeable future?

@mattab commented on February 1st 2014 Member

Increasing priority to reflect the very high interest of the community in supporting other databases! Please keep commenting.

I am a bit swamped at the moment, but I'll do my best to review the code and give a feedback here in the next week or so. Thanks for your patience and trying to make this happen!

@mattab commented on February 6th 2014 Member

hey Jon! I have posted a message to Sridhar on the piwik-hackers mailing list: http://lists.piwik.org/pipermail/piwik-hackers/2014-February/001452.html

I updated the description of this ticket with: see the Piwik fork with Postgresql support. Great contribution by Sridhar from the community

@Sridhar if you read this, maybe you could update the port to sync with master? I am looking at this diff in particular: https://github.com/sri-soham/piwik/compare/piwik:master...master

If you confirm this is the right place to look, I will discuss the fork with the Piwik team and will come back to you here about our feedback!

@Jon-IB commented on February 11th 2014

Matt, sounds great! Please keep us posted on the discussion with the rest of the team.

We'll reach out to Sridhar on the mailing list.

@mattab commented on April 12th 2014 Member

Have you tried the Postgresql fork and how does it work for you?

@Jon_A
@reedstrm
@bfg1981
@joril
@bugout

@anonymous-piwik-user commented on April 14th 2014

@matt: I have the 2014-01-25 revision in production, it's not perfect but it works. :) I should try updating to the latest revision when I find some time

@Jon-IB commented on April 15th 2014

We're running the latest from sri-soham's master. It's in production on a few test sites.

We made a few minor bug-fixes, so we need to submit a pull request to sri-soham.

We're seeing occasional HTTP 500 error responses on some pages of the admin UI and on some reporting API calls, especially on requests for same-day data. But we think this might be because we're running the code on PHP v5.5. We're going to downgrade PHP and see if that resolves the issue.

Apart from the occasional HTTP 500 errors, everything seems to be working fine. I don't see any HTTP 500 errors on the tracking calls in our server logs.

@pirate commented on October 15th 2014

+1 for postgresql support :)

@m13rr0r commented on October 23rd 2014

+1

@bfg1981 commented on October 23rd 2014

@mattab Sorry for the late reply, I didn't notice your reply until the +1 by m13rr03. No, I haven't tried the PostgreSQL fork.

Not really a priority right now, since MySQL is already in production on the servers that need Piwik. However it would be nice to get rid of the scattered MySQL installations, and rather rely on the centrally managed PostgreSQL farm. I've put it down as an issue in our backlog, If you're still interested in feedback, I can reasonably expect to get around to deploying a test installation it within 2 months.

@Jon-IB commented on October 27th 2014

We're now running the pgsql fork on about 5000 sites, though all of these sites are fairly low-traffic.

We found a few bugs, though I've only managed to submit one to the fork so far. When things calm down I'll submit the rest.

The code is still running a bit slowly. For example, it takes roughly 8 seconds to run daily, weekly, monthly, and yearly archives for a single site with just a few daily page views. Part of this is because we're putting a decent amount of API load on our servers now. And part is because we partitioned a couple tables so we could delete old data much more easily. Alas, that causes some queries to scan all of the partitions, which slows things down. I could limit those queries to just one or two partitions, but I'm trying to avoid core modifications that aren't legitimate bug fixes.

So it's not as fast as I'd like, but I think that just requires a little attention, which I don't have right now. Otherwise, it is very solid. I feel much more comfortable about expanding this implementation than if it were on MySQL.

I'm still a big fan of the way Sridhar separated the business logic and data access logic. There's still a little work to do there for plugins, but that doesn't seem difficult.

@craig commented on November 27th 2014

Awesome! I'd love to see postgres support! :)

@Hadlaskard commented on January 13th 2015

Hi!

I tried this fork yesterday to get work with PostgreSQL, our favorite DB, but didn't get it work. Does anyone know how to solve the following error I've got after finishing installation process of piwik:

Apache-Log:

PHP Fatal error: Class 'Piwik\Db\DAO\Pgsql\Archive' not found in /srv/www/piwik/core/Db/Factory.php on line 98, referer: http://webgis/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=today

...and following is displayed at every analyzing frame of the piwik-gui:

lockNameForNextIdarchive($table); $Generic = Factory::getGeneric($this->db); if ($Generic->getDbLock($dbLockName) === false) { throw new \Exception('loadNextIdarchive: Cannot get lock on table '. $table); } $this->deleteByIdarchiveName($table, $idArchive, $name1, $name2); ...endless.

I tried some debugging... with no success, it's hard to find out. (SLES 11, PHP Version 5.3.17)

@jlmadurga commented on January 23rd 2015

Hi all,

I have installed the postgres fork successfully but I am getting problems loading css. I have just patched with https://github.com/sabl0r/piwik/commit/f7f5296b19316427c34551138b98b639e505546e but I am getting same issue.
Any one now how to fix it?
(I am deploying in heroku which has php 5.6 I think)

@augustf commented on March 1st 2015

@Runaway82 I used the https://github.com/sri-soham/piwik fork and had the same issue you did. Enabling PHP short tags by default in my php.ini sorted it though.

While I have the fork working with an appropriately dated LDAP plugin version, I've been unable to successfully merge with the latest Piwik -- though that seems mostly to do with architecture changes than the Postgres changes.

@mattab While this Postgres fork doesn't represent the sort of robust abstraction being discussed earlier in this thread, it's a tremendous step forward. I would say that it is at least worth the effort to add it as a branch in the main repository and make a modest effort to keep it in step with master until further work such as @Jon-IB described can be considered.

@antwan commented on March 2nd 2015

+1 to confirm the interest for this feature.

@lenovouser commented on March 2nd 2015

:+1:

@augustf commented on March 2nd 2015

Reading through the tickets mentioned in this thread -- #425, #1046, #1368, #2593, #3418, and #5124 -- it seems like this ticket is really only about Postgres "support" to the extent that it's about having no bizarre MySQL-proprietary queries scattered around. Coming from a Rails background, even the memory of not having totally abstracted queries gives me nightmares.

The ticket really ought to be called "review code for database-layer abstraction," with the main objective being to improve Piwik's code quality, readability (having queries abstracted nicely so it's easier for new folks to contribute), reliability, and ease of maintenance (say when schema changes happen). As a side effect, Postgres (and any other relational DB support) will be either an obvious feature or something trivially added by interested parties.

@mattab commented on March 3rd 2015 Member

@augustf Yes, you make a good point here. To have a Postgresql support in Piwik, we would need first to refactor heaps of code. Therefore it would be realistic to create a separate issue for this project "review code for database-layer abstraction". If you are comfortable, feel free to create the issue and even get the ball rolling :+1:

@augustf commented on March 3rd 2015

@mattab I can do that. I'm not at all familiar with the code base of Piwik, but I'm assuming that the way the current Postgres fork is working is not readily maintainable going forward?

Either way, though, I suspect you're right; the solution will be to refactor non-standard queries.

@mattab commented on March 3rd 2015 Member

I'm not at all familiar with the code base of Piwik, but I'm assuming that the way the current Postgres fork is working is not readily maintainable going forward?

I think both solutions are possible, but clearly maintaining the fork is quite a difficult and tedious thing to do, as we often refactor things around and move files, etc. clearly it's not fun for the maintainer to maintain the fork, nor is it secure for users of the fork since they may miss the latest updates to Piwik including security fixes

so realistically i think the very best way to move forward would be to refactor the code and allow to have two options, one for mysql and one for postgresql. I would roughly estimate this work to take several week of full time development and unfortunately the core team cannot work on this yet. But maybe if the community gets it started then we could join the effort and help :+1:

@augustf commented on March 3rd 2015

Ahh-I haven't looked at the diff for the fork. I suppose a library that took in MySQL code and spit out nice generic queries was too much to hope for. As I mention in #7347, I think the first step is finding out the extent of the proprietary queries and going from there.

@sri-soham commented on March 4th 2015

This https://github.com/sri-soham/piwik supports both PostgreSQL and MySQL. It trails the main piwik by an year though.

@augustf commented on March 4th 2015

@sri-soham I'm actually using your fork now-it's been great so far. But I'm assuming that trying to keep it up to date with piwik master is not something you can just keep doing. So we've been trying to think about how we can get piwik to be a bit more friendly to non-mysql databases.

@aphorise commented on March 11th 2015

+1 for postgre!
6 years & counting - come on Matt! :runner:

@augustf commented on March 14th 2015

@aphorise Oh, if only it were that easy. In the course of looking at #7347, it's more than just supporting Postgres (which honestly isn't in super-widespread use). It's really about having a maintainable and future-proof codebase not dependent on sketchy old MySQL-proprietary behavior. This is a large codebase and in just grep'ing through it to get a sense of the situation, I'm overwhelmed. To boot, this likely involves separating queries, logic, and presentation in a language where such separation has been an afterthought. The piwik guys need all the help they can get there; this is a huge job.

@aphorise commented on March 15th 2015

@augustf From my minimal involvement with piwik back in 2013 - it seems as though there's still a pertinent need for proper data structuring / segregation and abstraction layering (PDO like) for store + query, use, etc. In the past it was also a challenge to considering document-models or NoSQL with heatmaps as an alternative approach. I do not recollect how entity-relational piwik data store and querying was or is - nevertheless I'm not sure how or why there'd be such specific need for MySQL.

Added to this I've had hopes for the ability to use any store / format (csv, logs, json, sql / nosql) or dynamically parse other log formats where for piwik is integrated into an traditional and existing analytics's setup.

@RMastop commented on March 16th 2015 Contributor

Perhaps http://propelorm.org/ could be good choice, as ORM library, to implement in Piwik?

@Jon-IB commented on March 19th 2015

I doubt anyone will be able to implement support for multiple databases in a single pull request. It's just far too much work, and the core development is happening too quickly. I still believe that the only feasible solution is an incremental approach.

This is doable if we have an overall code design for multiple databases, and then we allow contributors to submit piecemeal improvements that conform to that code design. Each piecemeal improvement would take one of two forms -- it could restructure one portion of the MySQL code to fit the agreed-upon code design for multiple databases, or it could add support for PostgreSQL (or any other database). Most importantly, any such pull request would be required to keep MySQL functionality completely unharmed. These changes should be transparent to an end-user running Piwik on MySQL.

Here's an example. An enterprising community member could extract the SQL out of /plugins/SitesManager/Model.php and put it into a new class named /plugins/SitesManager/DAO/mysql.php. By this point the infrastructure to load and use the new class would already exist, so the new class would work just as well as the prior state of the code.

Later on, someone else would port the MySQL class to work with Postgres, in a new class named /plugins/SitesManager/DAO/pgsql.php. At that point the SitesManager plugin should work for both MySQL and Postgres. Any subsequent bug fixes and improvements to the SitesManager plugin would need to cover both the MySQL and Postgres files if necessary.

To be clear, this isn't a recommended code design; it's just an instructive example.

Some negatives with the incremental approach:

  1. Someone (the core team?) needs to define the overall approach for supporting multiple databases, including the interim period while the code is in transition. No work can proceed until this design work is complete.
  2. The infrastructure needs to be built to support both the existing approach and the multi-database approach concurrently. This is a decent chunk of work, though the Piwik community could contribute.
  3. Performance could be impacted during the interim period when both the old and new approaches are supported. Perhaps this could require Piwik to use twice as many database connections. I haven't really thought carefully about this, and I'm not too familiar with the internals of PDO. So this might be a non-issue.
  4. Improving the code will get more time-consuming once we need to support multiple databases. However, 1) this is unavoidable if the goal is to support multiple databases, and 2) in most cases the additional work to support a second SQL database is straightforward.
  5. The test coverage needs to be expanded to include additional databases. All of the work that was required to validate that the MySQL implementation is production-ready would also be required for Postgres. Again, this is unavoidable if the goal is to support multiple databases. And Travis makes this a little easier.
  6. The amount of code to improve is huge. It's quite possible that the MySQL code will never get re-organized, and/or Postgres support won't be completed. If the core team decides to abandon this work, then that's a non-issue if the work was done in a fork. But if this work is happening in the mainline, then we're already half-way into the forest, and it will take just as much effort to revert it as it took to implement it.

The positives should be fairly obvious:

  1. It's a daunting amount of work, but the incremental approach seems to be the only way that's feasible.
  2. The entire community can contribute and share in the increased maintenance effort, reducing the burden on a single hero (such as @sri-soham) and/or the core team.
  3. This approach also allows end-users to use the "official" Piwik implementation before the Postgres support is 100% complete. No need to run a fork and then do your own merging to get the latest changes. The standard Piwik upgrade process will include Postgres changes as well. Plugins can report which databases they support, allowing end-users to choose whether the Postgres implementation is complete enough for their needs.
  4. The initial design work to separate business logic from data access should be generally beneficial, as others have noted.
@augustf commented on March 19th 2015

@Jon-IB It sounds liked you've really thought this through. Through these posts though, I've had one thread of continuing confusion. Is it not possible to just work towards to goal of having a PDO-based arrangement that's developed and tested with MySQL but is database-agnostic, without getting into the database support side of things?

My thought has been that it's a lot easier to get people on board with making the codebase cleaner and more modular for the sake of maintainability/futureproofing than it is to say "write a ton of code for a database you'll never use."

@Jon-IB commented on March 19th 2015

@augustf, I'm not sure I follow. Are you arguing for an ORM like Propel or Doctrine, so the Piwik code can just perform high-level data access calls, and the ORM will generate the correct SQL for each specific DBMS? As far as I know, PDO doesn't handle generation of the correct SQL for each DBMS. It only generalizes the API for interacting with database objects.

Or are you arguing that the data access abstraction should be completed first, before work is started on supporting additional databases?

@aphorise commented on March 20th 2015

@Jon-IB I'd argue that an ORM / OODBMS is long overdue in piwik.The seeming over-reliance on a specific store (namely MySql) also demonstrates a lack of objective data encapsulation and store in what piwik is striving to achieve - else a custom format and or a native file based store would not be alien where-it present. I also think that a proper ORM would expedite parsing and translations of existing CLF which are equally as related and pertinent as the support of different stores.

@augustf commented on March 20th 2015

@Jon-IB I hadn't carefully read the PDO docs earlier, so didn't realize that only the drivers for the various databases are provided, not anything further. Of course, in an ideal world, in using PDO, one would avoid most non-standard behaviors, but given the nature of piwik, that would seem unlikely. I certainly would love something along the lines of what @aphorise is proposing - a full ORM - but I don't really see the development team coming together on that right away. Every attempt to move away from proprietary MySQL queries in one big jump seems to have failed. Whatever is done will need to be incremental and functional at each step.

But in any scenario, I think that data access abstractions are a necessary first step before further steps are taken. Because, as I've pointed out elsewhere, this is really a matter of future-proofing and maintainability. Sure, piwik may not be able to just pick up the current best practices for data access or ORM, but it can at least move away from the really awful practice of having MySQL-proprietary queries sprinkled about. Even modest incremental steps in this direction could at the very least make other database ports maintainable over time.

@antoine-pous commented on May 27th 2015

Hi,

I only have postgres, what is the fork which you speak?

@rohdef commented on August 3rd 2015

+1 for postgres support

@piraz commented on August 26th 2015

+1 for postgres support

@gsenzer commented on August 28th 2015

+1 for postgres support. Especially important since MySQL is no longer truly open source. As a potential enterprise Piwik user, I would need to purchase MySQL licenses from Oracle... Boo.

@strugee commented on August 29th 2015

@gsenzer or you could just install MariaDB.

@augustf commented on August 29th 2015

In the end, the real reason to be compatible with Postgres (or any other relational database) is that being tightly coupled to the non-standard idiosyncrasies of a single engine is extremely bad design and ultimately unsustainable as time goes on.

@cwygoda commented on September 28th 2015

+1 on having a choice.

@fazalmajid commented on October 9th 2015

+1 for PostgreSQL. I am trying to get rid of MariaDB (moving from Wordpress to Ghost+PostgreSQL) and would like to ditch Mint for something that doesn't have dependencies on MySQL (and ideally not on PHP as well).
.

@artemrizhov commented on October 11th 2015

+1 for postgresql

@j0inty commented on November 10th 2015

+1 * 1Mio

Today I wanted to install piwik on my root server but there is no PostGreSQL support so there is no piwik installation available. To install and administration a MySQL server instance for only Piwik is not an option for me.

regards

@ecraven commented on November 25th 2015

+1 for postgresql support

I'd love to install Piwik, but we won't install MySQL just to get Piwik (and we have PostgreSQL and are happy with it).

@johannbg commented on December 1st 2015

Piwik should support postgresql as a backend database.

@vtalbot commented on December 18th 2015

+1

@jhass commented on December 18th 2015

@mattab Can you please lock this thread until there's worthwhile news? All these +1's are terribly annoying yet I keep want to be subscribed to the issue to watch the progress.

Everybody else: There's a subscribe button on the the right. Use it. +1 is meaningless and just causing notifications for about 30 people atm.

@smscotten commented on December 30th 2015

As @Jon-IB mentioned, "PDO doesn't handle generation of the correct SQL for each DBMS. It only generalizes the API for interacting with database objects." However, it does provide a number of structures which make creating cross-database queries a bit less painful. Using prepared statements, you still have to use syntax that works on both PG and MySQL (eg the parentheses syntax for INSERT or UPDATE statements) but you sidestep the need for handling a bunch of single- vs double-quoted values.

Any time I've had to deal with substituting backticks for double-quotes or vice-versa it's because I've made an embarrassing lapse in judgment naming tables. But there may well be edge cases I haven't considered.

@dtordable commented on March 6th 2016

+1 for postgres piwik

@rasebo commented on March 17th 2016

Sorry to contribute to the ever growing number of +1s to add postgres support on piwik, but this is the only app that requires and uses mysql in our infra, and tbh it's a bit silly needing to manage a separate database for just one app.

@gaumondp commented on March 30th 2016

Frankly, MySQL is a bit like Ron Popeil Rotisserie : "Set it and forget it ".

We are a big Oracle DB shop here (many hundreds terabytes) but Piwik-MySQL didn't required any DBA expertise so in the end, we just installed it... Now We have 81 millions records in our Piwik DB and it just works.

As long no one is willing to pay for DB abstraction in Piwik I don't think those +1 will change anything. With already more than 1 million websites using Piwik, there is no big incentive to rewrite the Core of Piwik.

@GreenReaper commented on March 30th 2016

We did the same; but it's two sets of binaries, two executables, two entirely different backup plans…

Ultimately I suspect you're right, in a way - someone will decide the cost is worth it and work to port it.

@thfree commented on June 20th 2016

+1

@jmurciego commented on July 5th 2016

+1

@a200612 commented on July 13th 2016

+1 for postgresql

@ildoc commented on July 25th 2016

+1 for postgres support

@sebastianpicklum commented on August 15th 2016

+1 for Postgres

It’s never a good idea to bet on just one horse.

@someone-somenet-org commented on August 18th 2016

Its 2016 ... and THIS is this still an issue?
DBMS-Agnostic features like PDO have been there for like ages and no sane person uses mysql (or maria) anymore.
Its either fully relational (oracle, mssql, pgsql) or nosql nowadays.

+1 for PDO (or pgsql for that case)

@mattab commented on August 22nd 2016 Member

Hi everyone,

if you need Piwik to support Postgresql and your company or business can sponsor this improvement, please get in touch with the Piwik core team here: https://piwik.org/development/ - we would love to build Postgresql support in Piwik but we would need serious monetary or engineering support to make this project happen and maintain Postgresql going forward.

@law commented on October 5th 2016

+1 for Postgresql from me, as well.

@eydunn commented on October 19th 2016

+1 for postgresql :-)

@fazalmajid commented on October 19th 2016

@mattab: how much money are we talking about? I doubt any one entity would front something like this, but it might be possible to crowdfund it.

@law commented on October 19th 2016

I'd chip in for this.

@advn commented on October 31st 2016

@mattab It costs money to implement support for postrresql. We all get it. But how much money? 10k? 100k? More? Can anyone give a cost estimate?

@augustf commented on October 31st 2016

@advn It's been awhile since I jumped into this crazy +1-happy thread, but my guess is money is only a minor part of the issue. To make this happen, the product would need to essentially be torn apart and re-architected to remove all of the MySQL-proprietary evilness. And that's not something they're willing to do for really any price at all.

@fazalmajid commented on October 31st 2016

@augustf that's not quite the impression I got from mattab's comment, which sounds guardedly open as long as there is some funding. Obviously rewriting, e.g. to use PDO, is a significant engineering effort and I would understand if the Piwik team closed this ticket with a WONTFIX status code.

@tsteur commented on November 1st 2016 Member

I would say it is about funding, having the resources and priority. It's not only making Piwik itself compatible because it even affects the Piwik eco system with plugins etc. It makes everything a bit more complicated as plugin developers suddenly need to mark whether their plugin is compatible with Postgres or MySQL. Users need to filter plugins by database backend etc. It's quite hard to estimate something like this without having a proper look which would take a lot of time. Just to mention a rough number because you asked I would roughly estimate like a mid five digit number but as said, it's really hard to say something about it.

@karo-github commented on February 2nd 2017

Can some one explain why it is not enough to rewrite (or write another class) like Piwik/Db, Piwik/DbHelper, \Piwik\Db\Schema\Pgsql and Piwik\Db\Adapter\Pdo\Pgsql to have piwik integrated with PostgreSQL?

@sgiehl commented on February 2nd 2017 Member

SQL queries written for MySQL might not work on PostgeSQL

@karo-github commented on February 2nd 2017

But why is it necessary to use native sql queries? We can havean DataAccess layer that encapsulates any sql logic in itself and provides an API for executing any db query without using sql queries at all.
I mean something like ORM in Java

@sgiehl commented on February 2nd 2017 Member

Most of our queries are optimized to run faster on MySQL. That isn't really possible when using ORMs that create the queries dynamicaly. And as speed is something that matters quite much in that kind of application, it's imho not a good option to loose it only to support multiple databases.

@karo-github commented on February 2nd 2017

I agree with you that " not a good option to loose it only to support multiple databases", but it would be great to have one or several classes , where all these queries are accumulated , but not hardcode queries in code . It really make a big problem for me as programmer to integrate piwik with another DB.

@smscotten commented on February 2nd 2017

Of course, performance at scale is why many of us prefer PostgreSQL over MySQL.

My suggestion: drop MySQL support entirely and make everyone happier.

Yes, I'm kidding. If MySQL optimization is your specialty it will take you a little while to get PostgreSQL queries to purr like you want.

The part I'm not kidding about is: we're all on the side of better performance.

@kvordf commented on May 3rd 2017

Hello everyone,
Where can I get the latest PIWIK/PostgreSQL version for testing.

Thanks
Khanh

@sgiehl commented on May 3rd 2017 Member

There is no official PostgreSQL version

@kvordf commented on May 3rd 2017

I understand that there is no official version for PostgreSQL. I thought that there was a beta or some version out there. Is this it? https://github.com/klando/piwik ?

@RuslanAyupov commented on March 12th 2018

+1 for postgresql support

@wenerme commented on March 27th 2018

Matomo now, the first thing that I hope to happen is support PG, kind of frustrated. Is there any possibility that this may happen, for 9ys, we all know PG is good, so what?

@clement-igonet commented on April 8th 2018

My (big) company support PS but not MySQL, because of policy and ressources constraints.
So, Matomo would be welcome if PS supported, but currently excluded until the day it will supports PS...

@siva538 commented on August 22nd 2018 Contributor

+1 PostgreSQL

@fazalmajid commented on September 17th 2018

Never mind, i switched to Fathom
https://usefathom.com

Much more basic, but I like the UI better and it’s written in Go, not PHP/

@clement-igonet commented on September 18th 2018

Thx a lot !

On Mon, Sep 17, 2018, 18:15 Fazal Majid <notifications@github.com> wrote:

Never mind, i switched to Fathom
https://usefathom.com

Much more basic, but I like the UI better and it’s written in Go, not PHP/


You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/matomo-org/matomo/issues/500#issuecomment-422076896,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AHVAkysq50o_B8K9ffvaFBAew_3D8sutks5ub8qbgaJpZM4CvPrj
.

@rasebo commented on September 18th 2018

Hey, fathom looks nice, it's worth a try if one only needs basic visitor tracking (which I do). Thanks, I'll look into it and consider switching to it if it means I'll be able to remove the overhead generated by having both mysql and postgres on my infrastructure.

@ofaurax commented on October 26th 2018

Can the travis-ci tests be run on an instance with postgresql?
Using the unit tests could help to ensure that a postgresql branch is functional.

@Jon-IB commented on November 13th 2018

I'm running the old 2.0.b4 fork for Postgres on about 15k low-traffic sites. Thanks to the EOL on PHP 5.6, I need to migrate these sites to Matomo 3.x on MySQL. Postgres is running fine with about 550 million rows in the log_link_action table. But I don't have time to port the Postgres fork to the 3.x branch.

@justinclift commented on February 6th 2019

Never mind, i switched to Fathom

Thanks, that looks interesting. Trying it out now, but it seems to require JS running in a users browser, and can't read server logs. As a Google Analytics replacement it seems functional. As an accurate web server stats processor though... not so much. :frowning:

Still it is very simple to set up, and doesn't rely on MySQL, so there is that. :smile:

@acsfer commented on April 30th 2019

Here's another hint why Postgres can be a good choice too: full-text search.
http://rachbelaid.com/postgres-full-text-search-is-good-enough/

@justinclift commented on April 30th 2019

Never mind, i switched to Fathom

We did too. Unfortunately, Fathom have decided to go Open Core with their next release. We'll be looking for a new alternative when that happen. Probably just write something ourselves.

@skid9000 commented on July 3rd 2019

+1 Postgresql support

@wenerme commented on July 4th 2019

Gitlab give up mysql today, and focus on pg support.

@snblitz commented on August 4th 2019

If you implement postgres support you get hadoop, cassandra and mongodb for free. In other words, postgresql-11 allows you to run schema-less pure text or json if you want and you get to keep your SQL query engine.

@ahachete commented on January 23rd 2020

+1 for PostgreSQL support

@frankgerhardt commented on January 28th 2020

Cool, this ticket is 11 years old.

@mattab commented on January 28th 2020 Member

Update 2020: it's still unlikely to happen. see the FAQ for more info https://matomo.org/faq/how-to-install/faq_55/

@mikaelmeulle commented on February 19th 2020

+1 for PostgreSQL support

@DBotThePony commented on March 10th 2020
@dotMavriQ commented on March 10th 2020

Amen for PostgreSQL instead of mySQL, or at least support

@ptman commented on March 11th 2020

I care about this as much as the next PostgreSQL user. However, comments saying "+1" or "me too" don't add anything to the discussion. They only risk getting this issue locked. Use the reaction buttons to vote. Or contribute code. Or offer matomo devs incentive to develop this (i.e. money).

@WyriHaximus commented on March 11th 2020

On a serious note then: What would it take to implement this? I know @mattab said they won't but depending on how much work it is we might collaborate with a few developers in helping matomo out here.

@Findus23 commented on March 11th 2020 Member

What would it take to implement this?

I'd say:

  • create a Database class, so that SQL queries are sent to PostgreSQL instead of MySQL (that should be easy with PDO and you could maybe even reuse (https://github.com/matomo-org/matomo/blob/4.x-dev/core/Db/Adapter/Pdo/Pgsql.php)
  • check and potentially rewrite every SQL query used in Matomo to make it work with the slight differences in PostgreSQL
  • (not to be underestimated) optimize all queries to the way PostgreSQL works. Many parts of Matomo were tuned over the years to be as fast as possible in MySQL/MariaDB. But the same solution doesn't have to be the best and fastest in PostgreSQL.

And the most important part:
Not only do it once (as in https://github.com/sri-soham/piwik), but repeat this for every single change in Matomo (every bug fix, every performance optimization, etc.)

As much as I like PostgreSQL and even if Matomo might be better/faster/more stable with it, I don't think supporting multiple database systems is really possible and Matomo might not be where it is now if it didn't support MySQL as it is more widespread in use for "beginners".

I don't want to discourage anyone from trying, but I don't think any change is possible.

@SuperSandro2000 commented on March 12th 2020

If you don't do stupid stuff postgres scales really well and does not need necessarily need the live MySQL does.

This does also not need to be repeated for every change. Only the ones related to queries are affected also git has you covered to do that reasonable.

Supporting multiple DBs is possible and one of the main reasons database abstractions exist and I am going so far to say that matomo would be at an even better place if it would have gone with postgres instead of MySQL since the beginning. It is one of two products I am using which does not support postgres and MySQL is using quadruple the ram, has longer start up times and just does not work as well with containers.

And change is always possible if someone wants to do it. Right now we are finding excuses to not do it rather than solving problems.

@dotMavriQ commented on March 17th 2020

What would it take to implement this?

I'd say:

* create a Database class, so that SQL queries are sent to PostgreSQL instead of MySQL (that should be easy with PDO and you could maybe even reuse (https://github.com/matomo-org/matomo/blob/4.x-dev/core/Db/Adapter/Pdo/Pgsql.php)

* check and potentially rewrite every SQL query used in Matomo to make it work with the slight differences in PostgreSQL

* (not to be underestimated) optimize all queries to the way PostgreSQL works. Many parts of Matomo were tuned over the years to be as fast as possible in MySQL/MariaDB. But the same solution doesn't have to be the best and fastest in PostgreSQL.

And the most important part:
Not only do it once (as in https://github.com/sri-soham/piwik), but repeat this for every single change in Matomo (every bug fix, every performance optimization, etc.)

As much as I like PostgreSQL and even if Matomo might be better/faster/more stable with it, I don't think supporting multiple database systems is really possible and Matomo might not be where it is now if it didn't support MySQL as it is more widespread in use for "beginners".

I don't want to discourage anyone from trying, but I don't think any change is possible.

I don't mean to mind you as I don't necessarily have a contention to what you said.

I would just like to add that the up and coming generation opt for Postgres over mySQL and as I am 27 with a couple of years in the game I can say that it's more than just a focus on edge cases.

Not considering the switch could also spell a shorter life span for matomo.

@arjan-s commented on September 21st 2020

Just found this out now, and really disappointed this still isn't a thing in 2020.
The FAQ mentions a fork supporting PostgreSQL. Is that fork supported?

@Findus23 commented on September 21st 2020 Member

@arjan-s The fork (https://github.com/sri-soham/piwik) was last updated 5 years ago, so I doubt it will work anymore.

@mattab commented on September 22nd 2020 Member

Thanks for the feedback @arjan-s the FAQ has been updated to clarify the situation: at: https://matomo.org/faq/how-to-install/faq_55/ (and removed mention of the fork)

@sanchezzzhak commented on February 24th 2021

Postgres can be implemented if the project does not use manual sql query.
To do this, all requests must be executed through the query builder.

example cycle orm

@Findus23 commented on February 24th 2021 Member

@sanchezzzhak,

Correct, but Matomo does build all sql queries manually and doesn't use a query builder. And switching Matomo to an ORM is around the same order of magnitude work than also supporting Postgresql.

@sgiehl commented on February 24th 2021 Member

Also with query builders its not easily possible to tweak queries for performance, which is essential for us.

@rg2609 commented on April 8th 2021

+1 for postgres support

@geekdenz commented on May 8th 2021 Contributor

I'm interested in implementing this as I have extensive experience with PostgreSQL and PostGIS. I'd just need to find or make time to do this. Also, I'll need to get familiar with the code base. If you have any questions about PostgreSQL if you're doing this please don't hesitate to get in touch. I'll let you know once I've read all the comments and code base in context and I can start so we don't double up efforts.

@geekdenz commented on May 8th 2021 Contributor

This gives me a crazy idea:
An Open Source MySQL API to PostgreSQL thin middleware. I have googled it but did not find it.

This would mean, instead of having to port applications from MySQL to PostgreSQL, we "just" write a middleware that translates MySQL queries to PostgreSQL queries and then forwards the translation to PostgreSQL and returns the result using the MySQL client/server protocol.

This could even be optimised by caching the translations in memory, redis or even PostgreSQL itself.

It is similar to the Linux Wine or Cedega ideas that translate system calls for example from DirectX to OpenGL and could be quite fast if implemented in a low-level language such as C++.

I got the idea because I have used Sphinx for fulltext search engines and it uses a MySQL-like server. One can simply connect to it with a MySQL client application, even with MySQL libraries and even though it has a different SQL language than MySQL.

It could be a PostgreSQL extension that runs on a different port that you connect to with the MySQL library.

This would solve this problem for all applications that only support the MySQL protocol and could boost PostgreSQL up in usage.

@geekdenz commented on May 8th 2021 Contributor

Maybe the code for these could be leveraged somehow:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers

@geekdenz commented on May 10th 2021 Contributor

Easier might be to write classes in PHP that extend PDO and override methods and translate MySQL to PostgreSQL on that level and make it a PHP library that Matomo could leverage. The translations could be limited to the ones used by Matomo or another client application for that matter.

@Findus23 commented on May 10th 2021 Member

Hi @geekdenz,

As interesting as this idea sounds, I think implementing it would be an enormous efford.
MySQL and PostgreSQL differ in more than just syntax, so even if you can modify it (which already means supporting a lot of SQL specific things), there might still be MySQL features Matomo is using, that don't exist in PostgreSQL. And while something similar might exist that would work in Matomo, I am not sure if this could always be used simply by rewriting queries

@geekdenz commented on May 10th 2021 Contributor

Hi @Findus23

Thanks for your reply.

In my last comment, I meant to say that vendors such as Matomo could provide translations for just their query patterns. That would make the module quite simple actually.

Wine is by no means perfect, but works for quite a few games and apps. It would be an incremental effort supporting some queries from the start but not all. Matomo could be the first vendor to work with the module.

PostgreSQL is the most advanced Open Source DBMS with the best standard support of SQL. I am pretty sure it supports at least what MySQL supports and therefore believe it is a super set. That means anything in MySQL can be implemented in a PostgreSQL structure, but maybe not the other way around.

@mkobel commented on May 11th 2021

Hi @geekdenz & @Findus23

I already ported applications from MySQL to PostgreSQL. According to my experience, a good part of simple queries are easy to migrate. But special performance critical queries might be completely rewritten, because optimizations for MySQL might have opposite effects with PostgreSQL. Furthermore, if multiple queries are required for one task, a rewrite of the application logic might be required.
One reason for the complexity of this task might be the fact, SQL is a language where you basically define WHAT you would like to get from the database and not HOW to get it. The how is determined by the query planner. So optimizing a query for one engine are not in general applicable for another engine by just mapping commands.

Don't get me wrong, I would really like to see PostgreSQL support in Matomo!

@geekdenz commented on May 14th 2021 Contributor

Doesn't that mean a PDO extension allowing prepared statements to be mapped to translations would still work if an application such as Matomo implements their own mappings? It would be a super simple module that Matomo could use and someone could monitor the queries that go through with testing the functionality and then they could be written.

@lafriks commented on May 14th 2021

imho the most correct way would be to move all db access to some kind of interface with defined methods and returnable structures and allow to implement other database support as plugins

@sgiehl commented on May 14th 2021 Member

@lafriks we are using database adapters to connect to the database. Those could in theory simply be replaced with a postgre adapter. But that was actually never the problem. We are building a lot queries that are optimized for mysql, some even use features that won't be available in another database. All those queries are done all across over the code. The most common solution for something like this would for sure be using a database query builder. For simpler queries that wouldn't be a problem. But we have a lot very complex queries, where a query builder would not even work. So that would actually mean we would need to write each query for each database we would like to support, which would make everything a lot more complex.
Moving database support to a plugin might not be a good solution as we would likely break the plugin with each new complex query we add somewhere and the plugin wouldn't know how to handle it.

@geekdenz commented on June 7th 2021 Contributor

imho even complex queries should have a mapping from any RDBMS to any other. There are bound to be complex queries that will need to get translated and optimised but it should be possible according to relational algebra, which I believe can be proven mathematically. I had a look through some of the source code. See
https://github.com/matomo-org/matomo/blob/4.x-dev/core/Db.php#L240-L241
and
https://github.com/matomo-org/matomo/blob/4.x-dev/core/Db.php#L271-L276

It says

<?php
return self::get()->query($sql, $parameters);

and earlier

<?php
/** <a class='mention' href='https://github.com/var'>@var</a> \Zend_Db_Adapter_Abstract $db */
$db = self::get();

which implies the Zend_Db_Adapter_Abstract class is used.

My proposition is to create an intermediary class

<?php
class MySQL_PG_Translator
{
    // this map can be stored in a DB or memcached compatible middleware like redis
    // if it gets big and would still have O(1) access speed as it would be a map op
    private $queries = [
        'SELECT * FROM `Table1` t1 JOIN `Table2` t2 ON (t1.id=t2.t1_id) WHERE prop1=?'
        =>
        'SELECT * FROM "Table1" AS t1 JOIN "Table2" AS t2 ON (t1.id=t2.t1_id) WHERE prop1=?',
        // ...
    ];
    public function translate($query)
    {
        return $this->queries[$query];
    }
}

class MySQL_PG_Translator_Zend_Db_Adapter extends Zend_Db_Adapter_Abstract
{
    // DI Translator
    protected $translator;
    // ...
    public function query($query, $params = [])
    {
        $translatedQuery = $this->translator->translate($query);
        // ... other maybe necessary stuff
        return parent::query($translatedQuery, $params);
    }
    // other methods like fetchAll etc
}

// use in Db, something like
$db = <a class='mention' href='https://github.com/Adapter'>@Adapter</a>::factory($dbConfig['adapter'], $dbConfig);
}

The amount of queries that need to be translated should be reasonably finite, which would make the MySQL_PG_Translator class fairly managable even with a lot of queries.

The large amount of work would then be to find all the queries that are run and then rewrite them.

One could put the adapter in place, just translate to itself and still use MySQL, run all the tests and log the queries to a file. Then they can be translated in isolation.

Of course there could be generated queries where this does not quite work, because in theory there might be infinite possibilities for them. But they would probably have a pattern that could be matched with a regex.

In the worst case a transformation engine could be used such as ANTLR:
https://www.antlr.org/
or a PHP alternative if it exists.

@sanchezzzhak commented on June 7th 2021

I don't use matomo completely. but I would like the product to be really cool.

Why don't I like mysql?

  • terribly slow, constant search for solutions at the code level. (yes, sometimes it's fun)

  • it is difficult to maintain large data (for example, when you delete a piece of data from a table, the table does not collapse until you perform optimization)
  • selecting data from large tables is a very long and complex process.
    with this volume size 500~GB, I can't use mysql
    image

  • not the fastest update ( for example, we have a balance log, and we need to update the balance very often), here mysql will lose out compared to other databases, even handlersocket will not cope. We solved this problem with the Tarantul layer as a proxy over mysql, I didn't want to rewrite the code.

Therefore, my choice when creating an analytical product
Clickhouse-as the main storage for statistics and building various reports
Mysql/Pgsql-as a repository of lists and other data for the site

ORM has enough functionality for queries, even if you think that they are special-purpose.

@tsteur commented on June 7th 2021 Member

FYI: Not sure how important it is but Matomo has an infinite amount of SQL queries because of features like Segmentation and https://matomo.org/custom-report/ . They don't just change where conditions or anything but they might change queries quite a bit with various subqueries, different joins etc. Translating query by query wouldn't work there potentially.

@geekdenz commented on June 8th 2021 Contributor

Yes, @tsteur I agree it has to be weighed how important this issue is and I agree with @mattab 's initial decision to not do this as it will be a big effort. We would have to do some serious work either way. Since there are an infinite amount of possible queries the only way along these lines would be to write an actual translator for SQL.

Would it maybe be easier to reason with users that installing a free Middleware such as MySQL or MariaDB additional to PostgreSQL is a small price to pay for a great product?

@tsteur commented on June 8th 2021 Member

I guess in the end the effort you put into it, and the advantage you get with PostgreSQL is minimal and it might be a different story if you were to think about an alternative storage that scales a lot better say 10-100 times etc and brings more unique advantages/benefits

@lafriks commented on June 9th 2021

I guess in the end the effort you put into it, and the advantage you get with PostgreSQL is minimal and it might be a different story if you were to think about an alternative storage that scales a lot better say 10-100 times etc and brings more unique advantages/benefits

Hence my proposal was to have interface with all needed storage functions/abstraction that could be implemented as plugin. This way it could be possible even to let's say combine db for users and config + some other, ex. let's say time series database for metric data

Powered by GitHub Issue Mirror