PostgreSQL for high volume transactions and for Data warehousing

In response to your reply to my comment, I mentioned some of the areas in which PostgreSQL shines for DW work - notably CTEs (Common Table Expressions - AKA the WITH clause) and Window functions (AKA the OVER clause).

Without these in MySQL, you'll end up writing your own hacks to emulate these (and, no disrespect, but it'll take a while for your code to be bug free), whereas with PostgreSQL you get them out of the box. PostgreSQL also has superior GIS support (should you require it) and better JSON - only experimental in MySQL.

Take a look here

PostgreSQL has traditionally focused on reliability, data-integrity and integrated developer-focused features. It has an extremely sophisticated query planner, which is capable of joining relatively large numbers of tables efficiently.

MySQL, on the other hand, has traditionally focused on read-mostly web apps, usually written in PHP, where the principal concern is with optimising simple queries.

DW type apps need far more of the former than the latter.

Also, check this out - a very recent comparion of the two systems. My take on this is that PostgreSQL is the better of the two, esp. for DW work.

You could peruse these pages for PostgreSQL and MySQL features. There is a certain "religious war" element here, and I'm mindful of the fact that you already have MySQL experience which may be a clincher for you. I wish you all the best with your DW project.

[EDIT in response to OP's comment]

I would certainly urge you to have separate databases for your OLTP and your OLAP work - if that is within your budget. OLTP and OLAP are fundamentally different and if you mix them you'll have many conflicts - I know the pain caused by "managers" insisting on running reports against a live system during the day - so if I were you, I would have MySQL as my OLTP system and PostgreSQL as my OLAP.

But whatever configuration you choose, I strongly suggest separate servers.

If I were forced to choose one database and one only, it would be PostgreSQL without hesitation. MySQL was essentially lucky and in the right place at the right time with the right software during the first internet boom. IMHO, PostgreSQL is undoubtedly technically superior.

I bear MySQL no ill-will - I have used it a lot (clients!) and find that if you can live with or workaround its quirks, it can be quite performant and has some interesting features - notably the storage engine choices one can make (which one can't with PostgreSQL).

In the DW sphere for MySQL there are two interesting developments which may bode well for it in this area in the future - these are columnar store engines - Infinidb and Infobright. The problem at the moment is that Infinidb appears to have died - even though the code is still available, the installation guide has disappeared and ICE (Infobright Community Edition) is essentially crippleware. You may wish to evaluate the Entreprise edition.

I urge you to set up a few test cases - imagine what sort of reports you will be running and try them on both systems. Think especially about the complex reports required for OLAP systems - I believe that you will find the far richer PostgreSQL SQL language to be a persuasive argument in its favour.

For reference, here's the solution that I came up with:

• Create a new schema dwh
• Build the new version of the DWH in that shema
• Drop the old public schema
• Rename the new dwh schema to public

(this assumes that your main schema is named public, of course)

This method works quite reliably and without interrupting/killing existing database connections, so I prefer the solution over my initial "create and rename database" approach. Here's the SQL that I use for building the DWH (it assumes that you're connected to the DWH database):

--we drop the temporary schema if it already exists
DROP SCHEMA IF EXISTS dwh CASCADE;
--we create the temporary schema
CREATE SCHEMA dwh;

--we modify the search path for this session
SET search_path to dwh;

--create your DWH here...

--now we commit the new version

--make sure you're connected to the correct database when executing this ;)
DROP SCHEMA IF EXISTS public CASCADE;

--we rename the DWH schema to public
ALTER SCHEMA dwh RENAME TO public;

--you might need to restore permissions on the new public schema...
GRANT USAGE ON SCHEMA public TO dwh_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dwh_user;

Apart from needing to manually restore permissions and having to recreate extensions in the new schema, I found this solution to be quite optimal as it does not require superuser privileges for the DWH user, and works without creating/dropping any databases. I have not yet figured out a way to copy permissions from one schema to another, which is my only minor issue with this approach.