I used to think running large-scale analytics workloads on Postgres was like trying to use a screwdriver as a hammer - technically possible but probably not a great idea. But after years of running both OLTP and OLAP workloads on Postgres at scale, I've learned it's much more nuanced than that. Postgres can actually handle analytics workloads quite well, if you know its limits and how to work within them.
In my previous articles, we looked at handling growing data volumes and increasing user concurrency. Analytics workloads add another dimension of complexity - instead of simple lookups and CRUD operations, you're now dealing with complex aggregations across millions or billions of rows, joins across multiple large tables, and queries that need to scan huge chunks of your data.
The good news? With the right approach, Postgres actually can handle many analytics use cases, from basic reporting to complex business intelligence queries. I've seen it work well for datasets up to several terabytes, providing query response times from seconds to minutes. The bad news? You'll need to make some specific optimizations and architectural choices to get there, and it does require a lot of oversight, planning, and investment.
Before we get into specific optimizations, let's understand what makes analytics queries fundamentally different from the transactional queries we've discussed in previous articles. Having run both types at scale, I can tell you the differences go way beyond just query complexity.
Here's what we're dealing with:
I think this table neatly summarizes why we need different strategies for OLAP workloads in Postgres. When your analytics queries start competing with your OLTP workloads for resources, you'll need to get creative with your approach - which is exactly what we'll cover next.
The first rule of running analytics on Postgres: don't run analytics queries on your primary OLTP database. This isn't just best practice - it's survival. One complex analytics query scanning millions of rows can bring your entire application to a halt. I've seen it happen, and the incident reviews aren't fun.
Instead, set up a dedicated analytics replica. But not just any replica - one specifically configured for analytical workloads. Here's what this gets you:
Here's a basic setup using streaming replication:
Notice the differences from the suggested settings in the “Outgrowing Postgres: Handling increased user concurrency” article. The key difference is that you’ll want to optimize these replicas for analytics workloads. Skip to the Performance tuning for analytics section below for more find-tuned settings for your replicas.
Key things you’ll want to watch out for:
A common question I get is "How much replication lag is acceptable?" For most analytics workloads, a few seconds or even minutes of lag is fine. But if you need real-time analytics, you'll need to either accept higher costs from a more powerful replica setup or start looking at specialized analytics solutions.
Your schema design can make or break analytics performance. Let's look at several techniques that can dramatically improve query speed, starting with the basics and moving to more advanced approaches.
If you've read my first article in this series on handling growing data volumes, you're already familiar with materialized views. They're particularly valuable for analytics workloads because they let you pre-calculate expensive aggregations:
For a more thorough treatment of materialized views, including concurrent refreshes and maintenance strategies, check out the materialized views section of that article.
Partitioning is another fundamental technique we covered in the data volumes article. For analytics workloads, time-based partitioning is particularly effective:
Take a look at the table partitioning section of that article for detailed partitioning strategies and maintenance approaches.
Here's where things get interesting for analytics workloads. Vertical partitioning - splitting tables by columns rather than rows - can significantly improve analytics performance. While we touched on this in that same data volumes article, it deserves special attention for analytics.
Consider a wide events table:
You can split this into separate tables based on query patterns:
This approach has several benefits for analytics:
But - and this is a big but - vertical partitioning isn't free. Here's what you're signing up for:
Write complexity
Read complexity
Maintenance headaches
When should you actually use vertical partitioning? In my experience, it works best when:
If you're just starting out with analytics on Postgres, I'd recommend exhausting simpler options first. Materialized views and regular partitioning can take you quite far with less complexity. But when you're hitting scan performance issues and you see clear patterns in how columns are accessed, vertical partitioning might be worth the trade-offs.
Postgres has a powerful extension system that lets you add new capabilities to your database. Think of extensions like browser plugins - they extend functionality but run with the same privileges as Postgres itself. That's both powerful and potentially risky.
Extensions are shared libraries that can:
They're typically written in C and need to be compiled against your specific Postgres version. While the official Postgres extension repository (PGXN) has some quality controls, you should still evaluate extensions carefully - they run with full database privileges.
For analytics workloads, the following columnar storage extensions stand out. However, teams eventually considering dedicated OLAP databases should also evaluate alternatives like ClickHouse®; see our detailed comparison of ClickHouse® vs PostgreSQL with extensions to understand performance trade-offs and migration strategies.
The battle-tested option, developed by Citus Data (now part of Microsoft):
Pros:
Cons:
pg_analytics (formerly pg_lakehouse)
A DuckDB-powered option by ParadeDB focused on data lake integration:
Pros:
Cons:
A relative newcomer by Hydra and MotherDuck that also embeds DuckDB in Postgres:
Pros:
Cons:
The newest kid on the block by Mooncake Labs combines DuckDB execution with Iceberg/Delta Lake storage and is available on Neon:
Pros:
Cons:
There are definitely some risks you need to consider before going down this route:
Despite these challenges, columnar storage can be worth it when:
If you're on a managed platform that doesn't support these extensions, you might need to:
Remember: adding extensions is a significant architectural decision. Start with standard Postgres features first, and only add extensions when you have clear evidence they'll solve specific problems in your workload.
While these advanced techniques can give you a huge boost in analytics performance, they also add complexity to your system. Start with the basics (materialized views, partitioning, and analytics replicas) and only move to columnar storage when you have clear evidence they'll help your specific workload.
Up to this point I’ve focused on infrastructure and schema changes - in many situations however, smart query optimization often beats brute force.
Analytics queries are fundamentally different from OLTP queries. They often:
Now I’ll walk you through how to optimize each of these patterns.
Window functions are incredibly powerful for analytics. They let you perform calculations across sets of rows without the overhead of self-joins or complex subqueries:
Pro tip: Window functions are processed after aggregations. This means you can combine them with GROUP BY:
Common Table Expressions (CTEs) are often seen as just a way to make queries more readable. But they're also a powerful optimization tool because of how they work in Postgres but, that also makes them a double-edged sword:
The other side of the sword: By default, CTEs in Postgres are materialized - they're calculated once and reused. This is usually great for analytics but can backfire if your CTE returns a large result set that's only used for a small subset of rows.
Postgres can parallelize many analytics operations, but you need to tune it right:
Some operations that benefit most from parallelism:
Keep in mind: While parallel queries use more total CPU, they can finish much faster. Monitor your system resources carefully when enabling parallelism.
When optimizing analytics queries, EXPLAIN ANALYZE is your best friend. These are some of the specific things to watch for:
ORDER BY in subqueriesDISTINCT ON for latest valuesQuery optimization is iterative. Start with the basics, measure, then optimize further based on real usage patterns. And always test with realistic data volumes - what works for 1M rows might fall apart at 100M. Lastly, it’s amazing how far you can get by simply following “The 5 rules of writing faster SQL queries.”
BRIN indexes are Postgres’ secret weapon for analytical workloads. Unlike B-tree indexes that track every single row, BRIN (Block Range INdex) creates a tiny index that stores metadata about blocks of data. Think of it like chapter summaries in a book instead of a detailed index of every word.
For time-series data or any naturally ordered columns (like IDs that only go up), BRIN shines. It's perfect when physical storage order and the indexed column are highly correlated for your data.
In Crunchy Data's testing, on a 42MB table, the B-tree indexes were 21MB while the BRIN indexes were just 24KB! And for large result sets (100K+ rows), BRIN consistently outperformed B-tree indexes.
Here's how to create one:
The pages_per_range parameter is key - it defaults to 128 but tuning it can really boost performance. For narrow queries (returning ~100 rows), smaller values like 8 or 16 work better. For broader analytical queries, stick with larger values.
But BRIN isn't magic. Skip it when:
UPDATEs in the middleThe sweet spot? Analytical queries that scan large chunks of naturally ordered data. Bonus points if you're tight on disk space - BRIN indexes are tiny.
Want to check if BRIN makes sense? Look at the correlation between storage order and your column:
The closer to 1.0, the better BRIN will perform.
No matter how well you write your queries, your postgres instance needs proper tuning to handle analytics workloads efficiently. While I covered basic configuration in previous articles, analytics workloads have specific needs that require different trade-offs.
Memory configuration is where analytics workloads differ most from OLTP. You'll need larger memory allocations for complex operations, but you'll also need to be careful not to overallocate:
Analytics queries often read large amounts of data. These settings help optimize I/O:
The query planner needs different settings for analytics:
Large analytics tables need different vacuum settings:
Monitoring becomes even more critical with analytics workloads:
Here's what this might look like in practice. Let's say you have:
Your configuration might look like:
Don't just copy these numbers and YOLO it. Monitor your actual memory usage using tools like pg_stat_statements and adjust based on your workload. Below I’ll call out some gotchas and guidance for testing your settings.
Watch out for these common issues:
Always test configuration changes:
Analytics workloads often reveal configuration problems that OLTP workloads miss. What works for millions of small transactions might fail spectacularly for one large analytical query.
Even with all these optimizations, you'll eventually hit limits running analytics on Postgres. Watch for:
When you see these signs, it's time to start thinking about dedicated analytics solutions. But that's a topic for an upcoming article!
Postgres can handle many analytics workloads if you:
It's not a perfect analytics database. But with the right approach, it can take you surprisingly far. Just don't forget to keep an eye on those limits - they'll sneak up on you faster than an angry DBA during a production incident.
In the next article, I’ll walk you through when and how to move your analytics workloads off Postgres. Because sometimes, you really do need a hammer instead of that screwdriver.
Need to move your analytics off Postgres?
Tinybird is data infrastructure for software teams. You can query your Postgres tables with SQL and publish queries as dynamic, scalable REST API Endpoints to power in-product reporting, real-time dashboards, and more.
You can try Tinybird for free with no time limit. Sign up here.