Please 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.
[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.
Source：https://postgr.es/p/9zJ
