Menu
Does anyone have experience of using PostgreSQL for an OLAP setup, using cubes against the database etc. Having come across a number of idiosyncracies when using MySQL for OLAP, are there reasons in favour of using PostgreSQL instead (assuming that I want to go the open source route)?
633
PostgreSQL is a powerful database, and for OLAP workloads, it can certainly meet expectations. With a good deal of planning and tuning, the database engine will be able to deliver analytics at scale.
PostgreSQL is a popular open-source OLTP database for systems of record. It's the fourth most-widely used database in the world, and its popularity has grown more than any other database for three of the last four years in a row, according to the recent DB-Engines database rankings.
PostgreSQL is fully ACID compliant and therefore ideal for OLTP (Online Transaction Processing) workloads. However, PostgreSQL is not only a good choice for its superior OLTP capabilities – it is also a highly capable analytical database and can be integrated nicely with mathematical software such as Matlab and R.
There are a number of data warehousing software vendors that are based on Postgresql (and contribute OLAP related changes back to core fairly regularly). Check out https://greenplum.org/. You'll find that PG works a lot better (for nearly any workload, OLAP especially) than MySQL. Greenplum and other similar solutions should work a bit better than PG depending on your data sets and use cases.
181
PGSQL is much better suited for Data Warehousing compared to MySQL. We had thought initially to go with MySQL, but it performs poorly in aggregations if data grows to a few million rows. PGSQL performs almost 20 times faster in caparison with MySQL for 20 million records for a single fact table on same hardware setup. If for some reason you choose to go with MySQL, then you should use MyISAM storage engine for fact tables rather then InnoDB; you will see slightly better performance.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
