Planet PostgreSQL
Open Karen Jex: Tuning Postgres for Analytics - PGConf.EUPlease enjoy the slides and transcript from
Tuning Postgres for Analytics
at PostgreSQL Conference Europe (PGConf.EU) in Valencia, Spain on 20 Oct 2026.
I'll add a link to the recording once it's available.
I've included just very brief alt-text for each slide image,
but I've tried to add any links or other essential information into the body of the blog post.
Obviously, I don't know the specifics of your particular Postgres environment.
But, as always, there are some generalisations that apply to most systems.
So, just for today, we're going to imagine that you're running a small chain of popular coffee shops in Valencia.
If you've seen any of my previous talks, you might start to notice a coffee and cake theme in lots of my talks, demos and examples!
You've implemented a state of the art system to manage your sales, stock, customer loyalty program etc.
and you've carefully configured your production database for the needs of your day-to-day application workload.
Let's talk about what happens if you then need to run analytics queries against your application data.
How can you do that without compromising the performance of your application?
First, a quick "about me":
I’ve spent my whole career working with databases; for the first 20 years or so as a database administrator.
I’m currently a Senior Software Engineer on the Postgres extensions team at Snowflake,
working mainly on the pg_lake set of extensions.
I’m actively involved in PostgreSQL Europe; amongst other things, leading the PGEU Diversity Committee
and helping to organise pgconf.eu.
I also write and present talks (about databases) at Postgres and Developer conferences.
If you want ideas for non-database-related conversation topics when you meet me in the hallway:
I love being creative; crocheting cute things, sewing clothes and accessories, and 3D printing all sorts of random things.
I also enjoy getting out and about on one of my bikes in the mountains where I live.
Back to the presentation.
Let's do a quick recap of different types of database activity to help set the scene.
Most organisations have at least one application with a database backend
that’s constantly being queried, inserted into and updated by a number of application users.
Today, that's the operational systems for your chain of coffee shops,
but it could equally be something like a payroll app or banking system,
an online commerce site, or a whole host of other things.
This type of workload is usually referred to as OLTP (Online Transaction Processing),
or sometimes “operational” database activity.
It's characterised by:
Many concurrent users
Typically short queries
Often multiple select, update, insert statements per transaction
Your applications constantly generate data from this OLTP activity,
which gradually builds up in your database.
It's a huge source of important data about your business and, at some point,
you'll want to get value from that data. You'll use it to glean insights into past activity:
Which stores were most profitable last year?
Which products are the most popular?
What kinds of trends are we seeing over time?
and make predictions about the future:
We've heard that Postgres folks drink a lot of coffee - should we open a new shop near the Valencia conference center?
What promotion will bring the best returns in July?
Which product line should we add to the menu this winter?
This is where analytics comes in. Sometimes referred to as reporting, decision support (DS) or business intelligence (BI).
Basically, activity that’s designed to answer real-world business questions.
The analytics activity is what's known as OLAP or Online Analytical Processing.
OLAP workloads typically involve:
Complex queries.
Joining data from multiple tables.
Working on large data sets.
Therefore potentially using high amounts of CPU, memory and IO.
Once upon a time, there were OLTP databases, and there were analytics/reporting databases,
and the two were very much separate.
Data would somehow be extracted from the various OLTP systems and loaded into the OLAP system
(sometimes known as the data warehouse) often via a nightly or weekly batch process.
This still happens in some cases, and in some organisations.
There can be good reasons for doing things that way, not least being able to take advantage of specialised analytics systems
that structure and access data in the most efficient way for that type of activity.
Of course, I have to give a little nod to Snowflake there, because that's their original, core business.
But imagine that your chain of coffee shops, like many other businesses,
doesn't have a dedicated, specialised analytics system.
You simply want to run analytics against your data where it is, in real-time, directly from your OLTP system.
Where you’ve got both OLTP and OLAP activity in the same database,
you’ll often hear it described as a “Mixed” or “Hybrid” workload.
This isn't a new idea - Gartner came up with the term HTAP (Hybrid transactional/analytical processing) back in around 2010.
It’s been described in other places as “Translytical” or HOAP (Hybrid operational and analytical processing).
“Hybrid transaction/analytical processing (HTAP) is an emerging application architecture that "breaks the wall"
between transaction processing and analytics. It enables more informed and "in business real time" decision making.”
Gartner: https://en.wikipedia.org/wiki/Hybrid_transactional/analytical_processing
So what’s the problem with this?
It’s well established in academic circles that this type of workload presents performance challenges.
There are papers from back in 2010/2011 where researchers identified the fact that running mixed workloads causes performance issues,
and there are ongoing discussions around the best types of benchmarks for this type of activity.
But today's talk isn't an academic discussion. I’d rather focus on the practicalities of what you can do if you’re in this situation,
to make sure you can run performant analytics with minimal impact on your operational database activity.
[1] Cole, Richard et al. "The mixed workload CH-benCHmark". Proceedings of the Fourth International Workshop on Testing Database Systems (2011).
[2] Bogyeong Kim, Kyoseung Koo, Undraa Enkhbat, Sohyun Kim, Juhun Kim, and Bongki Moon. "M2Bench: A Database Benchmark for Multi-Model Analytic Workloads". Proceedings of the VLDB Endowment (2022)
To recap:
Your database is optimised for your day-to-day (OLTP) application activity (many concurrent users running lots of short selects, inserts and updates).
Adding analytics activity into the mix (complex, long-running queries that consume lots of resources) means you can end up with the worst of both worlds:
inefficient analytics queries
that also slow down your existing application.
Some of the reasons that long-running analytics queries can impact your application activity:
Full table scans of large tables can flush hot OLTP pages from the buffer-cache, meaning your OLTP hits disk instead of memory.
Analytics queries using multiple parallel workers and performing aggregations etc. can compete for CPU with your OLTP backends.
Competition for I/O bandwidth.
Long-running analytics transactions can hold onto locks and block vacuum.
Let's set up an environment to test things out as we start to look at ways to run your analytics
as efficiently as possible and with as little impact as possible on your day-to-day operations.
I'm sure your coffee shop database contains more than one table,
but for today's purposes we'll focus on just this table that records transaction information for each store.
CREATE TABLE cafe_sales (
transaction_id NUMERIC,
transaction_date DATE,
transaction_time TIME,
transaction_qty NUMERIC,
store_id NUMERIC,
store_location TEXT,
product_id NUMERIC,
unit_price NUMERIC,
product_category TEXT,
product_type TEXT,
product_detail TEXT,
sales_tax_pct NUMERIC);
It contains:
A transaction ID
When and where the sale happened.
What was bought.
The total sale amount.
I used this Coffee Shop Sales dataset
as a starting point to populate the cafe_sales table,
modifying the data slightly and expanding it to get several million rows.
This represents the sales data that you've collected over the years through your OLTP activity.
Clearly, you've been extremely successful over the years that you've been running your business,
because during that time you've generated a couple of million rows of data for each of your 3 stores.
Of course, you ANALYZE your cafe_sales table after a big data load,
to make sure the Postgres optimizer/planner has the most up-to-date statistics available to it.
“Which Valencia location had the highest total sales for each month of last year, counting only transactions with a value greater than €20?”
Now, to help you in deciding where to focus your energies for a new promotion,
you decide you want to know which of your stores had the highest total sales for each month of last year,
only taking into account transactions with a value greater than 20 euros.
To help you answer that question, you write this query. The details here aren’t important
(in fact, I’d rather any SQL experts in the audience don’t look too closely because it’s likely not the most elegant or efficient query out there).
The important things for us to note are that it performs some of the usual kinds of things that we'd expect in an analytics query on the data in the cafe sales table.
Things like:
Filters (WHERE clause looking for data just from last year)
Calculated and concatenated values (adds sales tax to the unit price and multiplies by quantity, concatenates store id and location)
Aggregates (SUM of sales per month and per store)
Ranks (to find the store with the highest sales each month
This is the (somewhat convoluted) analytics query that we're going to use as a demo through the presentation.
WITH ranked_sales AS (
SELECT
store_id::text||': '||store_location AS store,
DATE_TRUNC('month', transaction_date) AS month,
SUM(unit_price*(1+sales_tax_pct/100)*transaction_qty) AS total_monthly_sales,
RANK() OVER (PARTITION BY DATE_TRUNC('month', transaction_date)
ORDER BY SUM(unit_price*(1+sales_tax_pct/100)*transaction_qty) DESC) AS sales_rank
FROM cafe_sales
WHERE date_trunc('year',transaction_date) = date_trunc('year',now()) - interval '1 year'
AND unit_price*(1+sales_tax_pct/100)*transaction_qty>20
GROUP BY 1, 2)
SELECT
to_char(month, 'YYYY-MM') AS month,
store,
round(total_monthly_sales,2) AS total_monthly_sales
FROM ranked_sales
WHERE sales_rank = 1
ORDER BY month;
The query returns one row per month showing the month, the winning store's store_id and name, and its total monthly sales.
It takes around 5 seconds to run.
You want to see what the query's doing behind the scenes, and record how long it takes to run,
so you choose a quiet time for your database and use EXPLAIN ANALYZE to get its execution plan.
A couple of notes:
The ANALYZE keyword is optional, but it tells PG to actually run the query, so we get the real stats rather than an estimation.
To anticipate the question “why didn’t you use the buffers option?” - I don’t need it for the purposes of this talk,
and I wanted to keep the information on the next screen to a minimum. But yes, it’s a very useful option, and definitely worth adding.
I've included a link to the EXPLAIN documentation so you have details of all of the options.
This is the execution plan that you get back.
Note: it takes 2 slides to display, even in tiny text, so please don't bother trying to read it! There's a summary on the next slide.
Amongst other things, you see:
a (parallel) full table scan on the cafe_sales table (Seq Scan on cafe_sales) so you know it's reading through all of the rows.
a filter that removes 2.8 million rows
a sort that's spilled to disk because it's too big to be done in memory (Sort Method: external merge Disk: 9288kB).
and an execution time of just under 5 seconds.
So that's the analytics activity that you use as your benchmark. That's how the query behaves on an otherwise quiet database, without any tuning.
Of course, your analytics query won't always be running on its own.
You'll often have day-to-day cafe activity going on at the same time.
To represent that, I'll use pgbench
- a simple but really useful client tool available by default with Postgres - to simulate some OLTP activity on the database.
To use it, first you initialize it (pgbench -i) to create and populate the 4 tables shown on the slide:
pgbench_branches, pgbench_tellers, pgbench_accounts and pgbench_history.
The numbers shown here are the number of rows you get in each table with the default scale factor of 1.
Here, I've initialized in the cafe database with a scale factor of 50, so we get 50 times the number of rows shown on the slide.
i.e. 50 branches, 500 tellers and 5 million rows (~750MB) in the accounts table.
The history table starts out empty at the beginning of each run and gradually fills.
Once you've initialized pgbench, you run it, telling it things like how many concurrent connections you want and how long it should run for.
By default, it repeatedly runs the transaction shown here, which is loosely based on the TPC-B benchmark.
Each transaction contains several SQL statements; 3 updates, a select and an insert against the 4 tables we just populated.
BEGIN;
UPDATE pgbench_accounts
SET abalance = abalance + :delta
WHERE aid = :aid;
SELECT abalance
FROM pgbench_accounts
WHERE aid = :aid;
UPDATE pgbench_tellers
SET tbalance = tbalance + :delta
WHERE tid = :tid;
UPDATE pgbench_branches
SET bbalance = bbalance + :delta
WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
You can alternatively have it run queries that more closely represent your own database activity.
Here, I'm running it with:
The default workload.
8 connections (-c)(the number of clients or concurrent database users simulated.
4 pgbench worker threads or jobs (-j).
The clients are distributed as evenly as possible among available threads.
for 120 seconds (-T).
What does running pg_bench tell you?
...
duration: 30 s
number of transactions actually processed: 127
number of failed transactions: 0 (0.000%)
latency average = 1834.636 ms
initial connection time = 6109.777 ms
tps = 4.360538 (without initial connection time)
This is the output from a 30 second run.
You can see 127 transactions processed, latency of just under 2 seconds, and 4.3 transactions processed per second.
The actual numbers aren't necessarily the most important thing. The thing to note is how the numbers change as you make changes.
As we discussed, you don't have a dedicated analytics environment for your coffee shop systems.
All activity is currently happening on your application database.
You want to be able to run analytics queries as efficiently as possible,
and have as little impact as possible on your application.
So let's look at some of the different techniques you can use to help you do that.
First, database configuration parameters.
When you first started out, you carefully set your database configuration parameters to optimise your day to day database activity.
Hopefully, you've kept an eye on things since then and tuned them to make sure things are still running smoothly.
If not, it's a good time to go back and have a look at them.
In case you're interested in an overview of some of the most impactful configuration parameters,
in an act of shameless self-promotion, I've included a link to one of my previous talks:
Tuning PostgreSQL to Work Even Better
Some configuration parameters have to be set across your entire Postgres instance
(for example parameters that control WAL, logging, autovacuum, checkpointing etc).
For those, of course, you need to find a compromise that works for your combined database activity.
But many parameters can be overridden at a database level, or can be set for specific users.
Sometimes it makes sense to set them for individual sessions or even within a transaction.
We’ll just look at a few of the most relevant parameters here.
A high number of database connections has a huge impact on database performance.
This is because each of those connections has a high database overhead, and the rapid creation and destruction of sessions is expensive.
max_connections specifies the maximum number of concurrent client connections that are allowed for your entire instance.
It's set to 100 by default, and on most systems and you don’t want to increase it too much higher than that. Generally in the low hundreds.
If you're on a small server, or you're sharing resources with other databases, you may need to set it lower than 100.
But what do you do if you have more users than that?
What if you want to limit the impact your analytics users can have on day-to-day operations?
You could use a connection pooler, such as pgbouncer, to help manage database connections for you.
A connection pooler sits between the client and the database, reusing and buffering connections between the database and application,
and reducing the creation and destruction of processes.
You can create different pools of connections, with different sizes and different limits.
You probably won’t have too many concurrent analytics connections but, as we've seen,
those connections are likely to be using a lot of resources so you'll want to keep them under control.
To do that you could use a separate, smaller, pool for analytics connections.
work_mem
is the maximum amount of memory that can be used by a query operation before it spills to disk and creates a temporary file.
The default value is 4MB.
We saw the Sort Method: external merge Disk: 9288kB in the execution plan earlier. You can also monitor for the creation of temp files
by setting log_temp_files to 0, to let you know if temp files are being created, indicating that work_mem may be too low.
You'll generally want a larger value for complex queries that perform large sort or hash operations.
Just be aware that a complex query might run multiple sort or hash operations in parallel,
and that several running sessions could be doing that type of operation concurrently. That means you can end up using a lot of memory.
To avoid using too much memory, set work_mem to a high value just for specific analytics sessions, and limit the number of them.
This slide shows how you might set work_mem within a specific transaction.
If you’re concerned that certain queries might run for too long, you can set
statement_timeout,
which will abort any statements that run for longer than the specified amount of time.
By default it’s set to zero, which means it’s disabled.
You can set statement_timeout either for the whole instance, or just for specific sessions.
It generally makes sense to set it to an appropriate value for different types of session;
certain analytics queries might be OK running for many minutes,
but you might expect statements executed via your application to take milliseconds.
If you want to make sure any statement that is aborted due to the timeout is logged in the Postgres logs,
make sure log_min_error_statement
is set to ERROR or lower.
You can see the valid values on the slide, from least severe (DEBUG5) to most severe (PANIC).
If log_min_error_statement is set to ERROR, statements causing errors, log messages, fatal errors, or panics will be logged.
You decide to create a dedicated analytics role with appropriate parameter values.
For example, increased work_mem and a statement timeout that represents the longest time you want analytics queries to run for.
You can also create a separate analytics connection pool for this role, and limit the number of connections.
You’ll probably need to create specific indexes for some of your analytics queries.
Just be aware, of course, that indexes need to be maintained.
If you create too many indexes on your application tables, you’ll start slowing down your inserts and updates.
You need to get the right balance between the needs of your application, and the requirements for your analytics activity.
And that doesn't just apply to indexes. It's relevant for all of the things we're talking about today.
There’s no point tuning an analytics query to lightning speed if no-one needs the results that quickly, and it ends up making your application unusable.
As well as creating indexes on one or more table columns,
you can also create indexes on functions or scalar expressions
computed from your table columns.
I created an index on the total_sale_amount expression that’s used in our query.
Re-running the EXPLAIN ANALYZE, we see Bitmap Index Scan on cs_total_sale_amt in the execution plan,
showing that the index is used, and the execution time goes down to just over a second.
Most analytics queries aggregate, sort and calculate large quantities of data; totals, averages, comparisons, groupings etc.
These are resource-intensive operations, and tend to make up the bulk of the time and resources used by the analytics activity.
If you can pre-calculate and/or pre-sort some of the data, you can make the queries much less expensive at run-time.
One of the things you can do it to create a generated column, whose value is calculated automatically to match an expression used in your queries.
For example, we might want a total_sale_amt column in the cafe_sales table with the default value unit_price*(1+sales_tax_pct/100)*transaction_qty.
You can do this using the GENERATED ALWAYS AS keywords. The STORED keyword says that it should be physically stored in the table
rather than just calculated at query-time.
You can also create indexes on generated columns. This can improve query performance but it will, of course, affect inserts into the table.
Now, we can use the calculated column total_sale_amt in our query
instead of the expression unit_price*(1+sales_tax_pct/100)*transaction_qty.
A materialized view
is a physical copy of the results of a query that can be queried like a normal table.
Materialized views are a useful tool for pre-aggregating data.
They can be particularly useful when you don’t need the data to be completely up to date;
data from an hour ago, a day ago or even a week ago might be good enough for your analytics.
You can also create indexes on materialized views
For example, we can create a materialized view for the ranked sales part of our query.
It’s as simple as adding CREATE MATERIALIZED VIEW before the query that we want to store the results of.
Wherever that block would have been used as part of a bigger query, for example in your original analytics query,
you can now select from the materialized view instead, where the data has already been calculated, aggregated and sorted.
In this case, when it’s selecting from the materialized view, the analytics query completes in a couple of milliseconds instead of several seconds.
Note that you’ll need to schedule a periodic refresh of the materialized view,
which will re-execute the query and re-populate the materialized view with up to date information.
The frequency will depend on how up to date you need the data to be.
If you can, you’ll schedule it during quieter periods on the database, to minimise impact on application activity.
If you use pg_cron, you can set up a periodic refresh in PostgreSQL itself.
This example schedules an hourly refresh of the ranked sales mv materialized view.
Of course, the best way to avoid impacting your application database is probably by not running analytics against it at all.
Even if you don’t have a completely separate analytics environment, there are still some options that might be available to you.
Most database environments already have a high availability architecture in place.
That usually means you have one or more standby databases that are kept in sync with the primary via physical replication.
Those replica databases are available for read-only transactions, so you could send your analytics workload to one of them to
take load off your primary application database.
Just be aware that physical replication gives you an exact copy of primary DB instance.
That means you can’t create separate users, you can’t create indexes, and you can’t change the layout of the schema.
Any changes have to be made on the primary and propagated across to the replica.
Another option is logical replication.
You can use this to replicate just certain schemas or tables from a publisher database (your application database)
to a subscriber database (that you'll use for analytics).
It’s more complicated to set up and maintain than physical replication, but it's more flexible:
You can replicate just a selection of objects, you can replicate to/from multiple targets/sources, and your subscriber database is available for read-write.
That means you can create indexes, users, materialized views etc. just on your subscriber (analytics) database.
What if you have external data in data lake formats (Iceberg, Parquet…) that you want to be able to query as well?
Do you need a separate system for that?
Do you need to set up data pipelines etc. to get the data from where it lives to where you need it for querying?
Wouldn’t it be great if you could “Just use Postgres”?
Postgres doesn’t natively talk to Iceberg, but you can use (for example) the pg_lake extension,
which was created by the folks at Crunchy Data and open-sourced last year by Snowflake,
to effectively turn your Postgres instance into a stand-alone lakehouse system.
Amongst other things, pg_lake allows you to:
create and modify Iceberg tables directly from PostgreSQL.
query and import data files in object storage in Parquet, CSV, JSON, and Iceberg format.
export query results back to object storage in Parquet, CSV, or JSON formats.
Combine heap, Iceberg, and external Parquet/CSV/JSON files in the same SQL queries
with full transactional guarantees, using standard SQL.
It uses Postgres as the Iceberg catalog, and DuckDB’s query engine for fast execution within Postgres itself.
To use pg_lake, you just need to enable the pg_lake extension.
You need to use “CASCADE” in CREATE EXTENSION because it’s actually a set of interdependent extensions.
You can then create a table with the USING iceberg clause.
This tells Postgres to create the table in columnar, Iceberg format using Postgres as the Iceberg catalog.
You can insert into the table, query and modify it like any other table.
Alternatively, you can create the iceberg table from a file.
You can also query across the live heap table plus the Iceberg file.
How do you decide which of the options to choose if you have analytics queries that are impacting the performance of your application?
Is your analytics activity mainly of a set of known, repeated queries, or queries that are based mainly on a set of repeatable building blocks?
In that case, you’ll probably be able to identify data that should be pre-calculated and pre-aggregated
using generated columns and materialized views.
You should also be able to identify columns or expressions where indexes would improve performance of your analytics -
being careful, of course, to balance performance of your analytics with performance of your day-to-day application.
Do you need the data used by your analytics queries to be completely up to date, or can you tolerate some lag?
Generated columns and indexes on your application tables let you run your analytics directly against the live, completely up-to-date data,
but they do impact performance of your day-to-day activity.
If you can tolerate some lag, you could run the analytics queries almost entirely against materialized views.
Instead of creating indexes on the base tables, you can create them on your materialized views.
And you can just refresh the materialized views as often as you need to, whether that's hourly, daily or weekly.
Do you need to isolate the application from the analytics workload?
Is your application database already at capacity?
If you just need some isolation, you can create a separate analytics user with its own configuration parameters and a separate connection pool.
If you need to take load completely off your application database, and if you can,
look at using a physical or logical replica to create a separate database for your analytics workload.
Do you have data in Iceberg files that you need to include in your queries?
In that case, you could try using pg_lake which will let you join online and lakehouse data, take advantage of the columnar, lakehouse format,
and the DuckDB engine.
Your application database is configured for the needs of your day-to-day application activity - it wasn’t designed to run analytics.
Analytics queries generally run over huge amounts of data and perform complex operations, so they can slow down your application.
You may not have a dedicated analytics environment, but you still want to get business insights out of the data in your database.
Fortunately, there are various techniques that you can use to reduce the load on your database,
and many of the techniques can be combined so you get the results you need.
Thank you for reading!
Get in touch, or follow me for more database chat:
LinkedIn: https://www.linkedin.com/in/karenhjex/
Mastodon: @karenhjex@mastodon.online
Bluesky: @karenhjex.bsky.social