Optimising your database for analytics

Your database is configured for the needs of your day-to-day application activity, but what if you need to run complex analytics queries against your application data? Let’s look at how you can optimise your database for an analytics workload without compromising the performance of your application.

Data analytics still isn’t always done in a dedicated analytics database. The business wants to glean insights and value from the data that’s generated over time by your OLTP applications, and the simplest way to do that is often just to run analytics queries directly on your application database.

Of course, this almost certainly involves running complex queries, joining data from multiple tables, and working on large data sets. If your database and code are optimised for performance of your day-to-day application activity, you’re likely to slow down your application and find yourself with analytics queries that take far too long to run.

In this talk, we’ll discuss the challenges associated with running data analytics on an existing application database. We’ll look at some of the impacts this type of workload could have on the application, and why it could cause the analytics queries themselves to perform poorly.

We’ll then look at a number of different strategies, tools and techniques that can prevent the two workloads from impacting each other. We will look at things such as architecture choices, configuration parameters, materialized views and external tools.

The focus will be on PostgreSQL, but most of the concepts are relevant to other database systems.

Bio

Karen was a database administrator for over 20 years and was once described as “quite personable for a DBA”, which she decided to take as a compliment! She’s now a Senior Solutions Architect, helping her customers to design and manage their (PostgreSQL) database environments.

Karen gives talks about databases at PostgreSQL and developer conferences because she loves sharing knowledge. She is also on the PostgreSQL Europe board and helps to organise various PostgreSQL community events.

When she’s not playing with databases, Karen can either be found crocheting cute gifts for her friends, or out on one of her bikes enjoying the mountains where she lives with her family.