PostgreSQL gets tuning playbook to run analytics without sacrificing OLTP

Karen Jex, from Snowflake's PostgreSQL extensions team, detailed at PGConf.EU configuration techniques to isolate heavy analytical queries from day-to-day transactional traffic.

Karen Jex, a senior engineer on Snowflake's PostgreSQL extensions team (she works mainly on the pg_lake extension set), presented the talk "Tuning Postgres for Analytics" at PGConf.EU in Valencia on October 20, 2026. The slides and transcript were published on Planet PostgreSQL on October 22. The talk's focus is practical: what to do when you need to run heavy analytical queries directly on the database that also serves your application, without a separate data warehouse.

It's a problem that any team running Postgres in production has already faced: the product manager asks for a report, someone writes a query with GROUP BY, JOIN across several tables and a window function, and suddenly the application starts slowing down without anyone knowing exactly why.

OLTP and OLAP coexisting in the same database

Jex revisits the classic distinction. OLTP (Online Transaction Processing) is the day-to-day workload: many concurrent users, short queries, many transactions with SELECT, UPDATE, and INSERT per second. OLAP (Online Analytical Processing) is the opposite: complex queries, JOIN across several tables, large data volumes, heavy CPU, memory, and I/O consumption.

When both workloads run on the same instance, the technical name is HTAP (Hybrid Transactional/Analytical Processing), a term coined by Gartner around 2010:

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, via Wikipedia

Jex's caveat is that the performance challenge of mixed workloads has been well documented in academic literature since 2010-2011, but the talk isn't a theoretical discussion: it's a configuration playbook applicable today.

Why the analytical query takes down the transactional database

Jex lists four concrete mechanisms through which a poorly isolated analytical query hurts the application sharing the same database:

• A Seq Scan on a large table evicts hot OLTP pages from the buffer cache, forcing transactional traffic to read from disk instead of memory.
• Parallel aggregation workers compete for CPU with the backends serving the application.
• There's direct contention for I/O bandwidth between the analytical scan and OLTP writes.
• Long analytical transactions hold locks and block VACUUM, which degrades table health over time.

The test environment: a coffee shop chain

To illustrate, Jex sets up a didactic scenario: a fictional chain of coffee shops in Valencia with a cafe_sales table containing a few million rows per store (transaction_id, transaction_date, store_id, product_category, unit_price, sales_tax_pct, among other columns). The transactional load is simulated with pgbench, the benchmarking tool that ships with Postgres by default.

With pgbench -i and a scale factor of 50, she generates about 5 million rows (~750MB) in the pgbench_accounts table, simulating transactions based on the TPC-B benchmark (three UPDATEs, one SELECT, and one INSERT per transaction). This is the background OLTP load that runs in parallel with the analytical queries during the tests.

The reference analytical query answers a real business question: which store had the highest revenue in each month of the past year, considering only sales above 20 euros. The query combines a CTE, aggregation with SUM, and RANK() OVER (PARTITION BY ...) to pick the winning store for each month:

WITH ranked_sales AS (
 SELECT
 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;

What EXPLAIN ANALYZE reveals without tuning

With no optimization, on an idle database, this query takes about 5 seconds. Running EXPLAIN ANALYZE on it, Jex shows three symptoms that any reader who has read an execution plan will immediately recognize: a parallel Seq Scan on the cafe_sales table (a full read, without using an index), a filter that discards 2.8 million rows after they're read, and a sort that doesn't fit in memory and spills to disk (Sort Method: external merge Disk: 9288kB).

With pgbench running in parallel simulating the application load (8 connections, 4 threads, 120 seconds), the impact becomes clear: in a 30-second window, the benchmark processes 127 transactions, with average latency of 1,834 ms and a rate of just 4.36 transactions per second. Jex is explicit about what matters in this number: it's not the absolute value that counts, it's how it changes as you apply each configuration adjustment.

The configuration levers she recommends adjusting first

The practical part of the talk goes through parameters that already exist in Postgres and that, according to Jex, rarely get attention after the database's initial tuning. First, connections: max_connections has a high per-session overhead, and the default value of 100 shouldn't be raised much beyond that in most systems. To separate analytical traffic from operational traffic, the recommendation is to use a pooler like pgbouncer, creating a smaller pool dedicated only to analytics connections, limiting their blast radius.

The second central adjustment is work_mem, the memory limit a query operation can use before writing to disk (the default is 4MB, exactly the scenario that produced the Sort Method: external merge seen in the plan above). Jex recommends raising this value only for specific analytical sessions, not for the entire instance, since a complex query can run several sort and hash operations in parallel and multiply memory consumption. One of the slides shows how to do this inside a specific transaction, without changing the server's global configuration:

BEGIN;
SET LOCAL work_mem = '<value above the 4MB default>';
-- analytical query here
COMMIT;

To monitor whether work_mem is too low, she suggests enabling log_temp_files = 0, which logs every time an operation needs to create a temporary file on disk. The slides go on to statement_timeout, used to cut off analytical queries that stray from the expected pattern and threaten to run indefinitely, but the material published so far cuts off before detailing the recommended values for this parameter.

What changes for those who write the query, not just those who administer the server

For those developing on a Postgres instance shared between the application and reporting, the practical takeaway is twofold. First, before asking for more hardware or migrating to a data warehouse, it's worth confirming whether the analytical session's work_mem is adequate and whether the query is running in a connection pool isolated via pgbouncer. Second, the execution plan always tells the real story: a Seq Scan together with a Sort Method: external merge Disk is a sign that the query (or the session configuration) has room to improve before even considering rewriting the schema.

One caveat is worth noting: these techniques relieve symptoms, they don't solve the underlying problem if analytical data volume keeps growing continuously. Jex comes from a context where this is central to her work: Snowflake, where she works on the extensions team behind pg_lake, was built precisely to separate analytical workloads from operational databases. For analytical loads that outgrow what an isolated session and a larger work_mem can handle, the conversation shifts to dedicated read replicas or specialized analytical systems, something the talk itself acknowledges as a valid alternative when it exists, except not every team has that budget or that operational complexity available right now.