Tuning Postgres for Analytics

Imagine the following, common scenario:

Your database is configured for the needs of your day-to-day (OLTP) application activity; many concurrent user connections each performing multiple short select, insert and update statements.

This OLTP activity constantly generates data, which has built up in your database over time, and is now seen as a valuable business resource. The organisation wants to use this data to answer real-world business questions such as “what percentage increase in sales did we see as a result of this marketing campaign?”.

This means you need to run analytics queries (OLAP activity) against the data; complex queries that work on large data sets and are therefore very resource intensive.

How can you do that without compromising the performance of your application?

Let’s look at some of the ways that you can design your environment and tune your database to work with this hybrid (OLTP + OLAP) workload. The goal is to make sure you've got performant analytics queries that have minimal impact on your day-to-day database activity.