Why you should use Postgres over MySQL for analytics purpose

Huy Nguyen
Jun 22, 2016 · 4 min read

(this post originally published on the Holistics Blog)

Update: Confused about the complex analytics landscape? Check out our book: The Analytics Setup Guidebook.

Being an analytics/reporting product company, we’ve worked with a fair amount of customers who run Postgres/Redshift for their analytics, and the same fair amount who uses MySQL.

And from our experience with both, I’d prefer our new customers to use Postgres all the time. For reporting/analytics/data-warehouse purpose, Postgres wins over MySQL hands-down.

In this post I lay down a few practical reasons why working with Postgres is so much better than MySQL from a data analyst perspective.

1- MySQL doesn’t support CTE.

Unlike production application where your queries should be as simple and straight-forward as possible, queries for analytics are usually quite complex. And very often you need to build intermediate resultset to be used multiple times across your queries.

The query below in Postgres will get the employed people together with their supervisor.

# Postgres
with employees as (
select
id,
first_name || ' ' || last_name as full_name,
supervisor_id
from people
where person_type = 'employed'
)
select
E1.id,
E1.full_name,
E2.id as supervisor_id
E2.full_name as supervisor_full_name
from employees E1
left join employees E2 ON E1.supervisor_id = E2.id

With MySQL, the query will need to be more verbose and difficult to maintain, because you have to repeat employees into subquery. See the difference.

2- MySQL doesn’t support window functions

I can’t stress enough how useful this is when it comes to doing analytics. Window functions bring Postgres’ capabilities to a whole new level, allowing a lot of complex, sophisticated requests to be done. There have been countless times that I need to use window functions to do a certain report.

Learn about Window functions

3- MySQL doesn’t support schema (namespacing)

Your analytics database don’t usually contain just data from one source. You want to pull multiple data sources into one place. Things like click-stream events (in Hadoop), 3rd-party data sources like Adwords or Google Analytics, multiple production databases (when your apps are doing service-oriented architecture).

When these happen, namespacing your data from different data sources become a must-do. And MySQL doesn’t have an elegant way to do it! I’ve seen people doing it the prefix way

soa1__users
soa2__bookings
adwords__clicks_by_country

It’s like having one folder with over 100 files, and no subfolders. It’s difficult to manage, it’s overwhelming, and there’s no native way to work with all tables from the same data source.

Postgres saves you all these pains with schemas, so that you can put data in different data sources into one dedicated schema.

Update: Edwin pointed out in the comment that my MySQL I can do cross-database joins, thus getting the same functionality of Postgres’ schemas. While this is posible, it doesn’t feel right to be to maintain different databases just for the sake of categorizing them.

4- Postgres has better interface when dealing with datetimes

Dealing with dates/datetimes is a major topic when doing analysis. With my experience so far, Postgres has a far more consistent interface over MySQL.

Take a simple example of converting a timestamp field to day, week (first day of week), and month (first date of month).

In Postgres:

  • DATE_TRUNC(ts, ‘DAY’)
  • DATE_TRUNC(ts, ‘WEEK’)
  • DATE_TRUNC(ts, ‘MONTH’)

In MySQL:

  • DATE(ts)
  • DATE_ADD(ts, INTERVAL (1-DAYOFWEEK(ts) DAY))
  • DATE_FORMAT(ts,’%Y-%m-01')

5- Other smaller things:

I try to make this post sort, so below are some smaller things in Postgres is better over MySQL, that I find immensely useful.

MySQL doesn’t support full outer join: I know this might not be a big deal, but it is a big deal when you need it and it isn’t there. Learn more why here.

Wrong group by doesn’t throw error in MySQL: This query will give an error in Postgres, but not in MySQL (it’ll give strange results). I’d rather get an error thrown at me, than getting a wrong result.

# error in Postgres, and give wrong results in MySQL!
SELECT listing_type, count(1) FROM listings

Postgres supports VALUES for manual values list: In Postgres to generate a manual table, I can use VALUES, with MySQL, I have to use UNION ALL

# Postgres
values
('2015-01-01', 100, 200),
('2015-01-02', 200, 400),
('2015-01-03', 300, 600)
# MySQL
select '2015-01-01', 100, 200 union all
select '2015-01-02', 200, 400 union all
select 2015-01-03', 300, 600

Postgres has generate_series This has helped me countless time from generating number series, sample data, and date ranges!

Postgres has materialized views, to help you with pre-calculating data (complex queries) so that querying them will be quicker later on.

Wrapping Up

MySQL is indeed the most popular database, and is a fine and battle-tested one. But over the past few years, Postgres has taken over and become my favourite database, both for building production applications, and doing data analytics. I do hope MySQL will catch up with the missing features, so that it’ll make some (if not a lot) of our lives easier.

The above list is nowhere near exhaustive, so feel free to share in the comments if you have anything to add.

— — — — — — — — — — — —
Having problems finding a simple and affordable data reporting system for your startups? Check us out at
holistics.io.

Holistics.io

Using data to transform how we work.

Huy Nguyen

Written by

CTO of Holistics.io (self-service BI platform) — Confused about BI/analytics landscape? Read this book: https://www.holistics.io/books/setup-analytics/

Holistics.io

Using data to transform how we work. Letting data teams automate repetitive data operations, with our data reporting and preparation software. Works with your data infrastructure, no training required.

Huy Nguyen

Written by

CTO of Holistics.io (self-service BI platform) — Confused about BI/analytics landscape? Read this book: https://www.holistics.io/books/setup-analytics/

Holistics.io

Using data to transform how we work. Letting data teams automate repetitive data operations, with our data reporting and preparation software. Works with your data infrastructure, no training required.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store