Optimising your database for analytics
Karen Jex • September 11, 2024 • Sarajevo, Bosnia and Herzegovina • Talk

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.

EuRuKo 2024