What to Do After Postgres Hardware Upgrade Fails
Your hardware upgrade didn't fix slow queries. Here's why vertical scaling fails and what actually works for analytical workloads.
Andrew Grosser
May 15, 2026 • 11 min read
What to Do After Postgres Hardware Upgrade Fails
Your hardware upgrade didn't fix slow queries. Here's why vertical scaling fails and what actually works for analytical workloads.
You just spent $15,000 upgrading your Postgres server from 32GB to 128GB RAM. You doubled your CPU cores. Your database vendor promised this would solve your slow analytical queries. Three weeks later, your GROUP BY queries still take 45 seconds. Your dashboards still time out. Your data team is still complaining. The hardware upgrade failed because you're using the wrong tool for the job.
Postgres is an excellent transactional database. It handles OLTP workloads brilliantly—thousands of concurrent small reads and writes, ACID compliance, complex transactions. But when you ask it to scan 50 million rows and aggregate them by 12 dimensions, you're asking a sports car to tow a trailer. It wasn't built for that.
Sourcetable's AI data analyst connects to both Postgres and ClickHouse, letting you query both from natural language. Try it free now.
Why Your Postgres Hardware Upgrade Didn't Work
Postgres stores data in rows. When you run an analytical query that needs to count events by user_id and date, Postgres reads every single row from disk, pulls all columns into memory, then filters and aggregates. If each row is 500 bytes and you have 80 million rows, that's 40GB of data transfer just to count events.
More RAM helps Postgres cache more rows. More CPU cores help it process rows in parallel. But the fundamental architecture remains: row-by-row scanning. At 50 million rows, queries that touch multiple columns start taking 15-60 seconds. At 200 million rows, they take minutes. At a billion rows, they become unusable.
| Database Size | Postgres Query Time | ClickHouse Query Time | Speedup |
|---|---|---|---|
| 10M rows | 2.3 seconds | 0.18 seconds | 12.8x faster |
| 50M rows | 18.7 seconds | 0.41 seconds | 45.6x faster |
| 200M rows | 94.2 seconds | 1.1 seconds | 85.6x faster |
| 1B rows | timeout (300s+) | 2.3 seconds | 130x+ faster |
These numbers come from real-world testing on identical hardware: 16-core CPU, 64GB RAM, NVMe SSD. The query was a simple GROUP BY with COUNT and AVG aggregations across three dimensions. Postgres hit the wall at 200 million rows. ClickHouse handled a billion rows in 2.3 seconds.
How ClickHouse Achieves 100x Faster Analytical Queries
ClickHouse stores data in columns, not rows. When you query for user_id and event_count, ClickHouse reads only those two columns from disk. If your table has 30 columns but you only need 2, ClickHouse touches 1/15th the data that Postgres would.
Column storage also enables extreme compression. User IDs compress from 16 bytes to 2-4 bytes using dictionary encoding. Timestamps compress 3-5x using delta encoding. A billion-row events table that would consume 500GB in Postgres fits in 80GB in ClickHouse. Less data to read means faster queries.
ClickHouse Optimization Techniques
- Vectorized execution: Processes 64KB blocks at a time using SIMD CPU instructions, 10-20x faster than row-by-row processing
- Primary key ordering: Data sorted by primary key enables binary search and range skipping, eliminating 90%+ of data scans
- Sparse indexing: Index every 8,192 rows instead of every row, reducing index size by 99% while maintaining query speed
- Codec compression: Automatic selection of LZ4, ZSTD, Delta, DoubleDelta, Gorilla, and T64 based on column data type
- Partition pruning: Queries filtered by date automatically skip entire monthly partitions, reducing scan volume by 95%+
A real example: One Sourcetable customer had a 340M row events table in Postgres. Their daily revenue dashboard query took 78 seconds. After migrating to ClickHouse with the same schema, the identical query completed in 1.4 seconds—a 55.7x improvement. They didn't change their data model. They didn't add indexes. They just moved the data to a database built for analytical queries.
When to Keep Postgres vs When to Migrate to ClickHouse
Postgres excels at transactional workloads. If you're running an application that needs to update individual user records, process payments, manage inventory, or handle concurrent writes with ACID guarantees, Postgres is the right choice. ClickHouse is terrible at updates and deletes—it's optimized for insert-only append workloads.
| Workload Type | Use Postgres | Use ClickHouse | Why |
|---|---|---|---|
| User profiles | ✅ | ❌ | Frequent updates, row-level locking needed |
| Event logs | ❌ | ✅ | Insert-only, analytical queries, billions of rows |
| Order processing | ✅ | ❌ | Complex transactions, updates, ACID requirements |
| Analytics dashboards | ❌ | ✅ | GROUP BY aggregations over millions of rows |
| Time-series metrics | ❌ | ✅ | High insert rate, range queries, retention policies |
| Product catalog | ✅ | ❌ | Frequent updates, complex joins, low volume |
Most organizations end up running both. Postgres handles the operational database—user accounts, orders, inventory. ClickHouse handles the analytical database—event logs, metrics, aggregations. You replicate data from Postgres to ClickHouse using change data capture (CDC) tools like Debezium or native replication.
How to Migrate from Postgres to ClickHouse Without Downtime
The safest migration path runs both databases in parallel for 2-4 weeks. You keep Postgres running for production queries while you backfill ClickHouse and validate query results. Here's the step-by-step process we've seen work across 50+ migrations:
Migration Process (14-21 Days)
- Day 1-2: Schema design. Map your Postgres tables to ClickHouse. Choose primary keys based on your most common query filters (usually timestamp + user_id or timestamp + entity_id). Select partition keys (typically monthly or weekly date partitions).
- Day 3-5: Historical backfill. Export data from Postgres using pg_dump or COPY TO CSV. Import into ClickHouse using clickhouse-client or the INSERT INTO SELECT FROM file() function. A 100M row table typically backfills in 10-30 minutes.
- Day 6-7: Set up CDC replication. Configure Debezium or pg_chameleon to stream changes from Postgres to ClickHouse in real-time. Test that inserts in Postgres appear in ClickHouse within 1-5 seconds.
- Day 8-14: Parallel validation. Run your analytical queries against both databases. Compare row counts, aggregation results, and query performance. Fix any schema mismatches or data type issues.
- Day 15-18: Migrate dashboards. Update your BI tools (Tableau, Looker, Metabase) to point at ClickHouse instead of Postgres. Test all dashboards with real users.
- Day 19-21: Monitor and optimize. Watch query performance. Add materialized views for frequently-accessed aggregations. Adjust partition keys if needed.
The biggest mistake teams make is trying to replicate Postgres schemas exactly. ClickHouse works best when you denormalize aggressively. Instead of joining 5 tables, create a wide table with all the columns you need. Joins are expensive in ClickHouse—denormalized tables are fast.
Connecting ClickHouse to Sourcetable for Natural Language Queries
Sourcetable now supports ClickHouse as a first-class data source alongside Postgres, MySQL, and Supabase. You can connect your ClickHouse database in under 60 seconds and immediately query it using natural language or SQL.
From the Sourcetable connectors page, click 'Add Database' and select ClickHouse. Enter your connection details: host, port (default 9000 for native protocol or 8123 for HTTP), database name, username, and password. Sourcetable tests the connection and imports your table schemas automatically. All your ClickHouse tables appear in the @ picker with full column type information.
What You Can Do With ClickHouse in Sourcetable
- Natural language to SQL: Type 'Show me daily active users for the last 90 days' and Sourcetable generates the ClickHouse query, executes it, and returns results in the spreadsheet
- Cross-database joins: Join your ClickHouse events table with a Postgres users table in a single query using federated SQL
- Python analysis: Pull ClickHouse data into pandas DataFrames, run statistical analysis, and write results back to the spreadsheet
- Auto-updating dashboards: Build charts that refresh every hour by re-querying ClickHouse automatically
- AI-powered exploration: Ask 'What caused the spike in errors on May 3?' and the AI queries ClickHouse, identifies patterns, and explains the root cause
The SQL syntax is identical to Postgres for 95% of queries. SELECT, WHERE, GROUP BY, ORDER BY, and JOIN all work the same way. ClickHouse-specific features like SAMPLE, PREWHERE, and materialized views are available when you need them, but you can start with standard SQL and optimize later.
Real-World Performance Comparison: Postgres vs ClickHouse
We benchmarked a typical analytical workload: calculating daily active users, session duration, and conversion rate from a 280 million row events table. The table schema included user_id, event_type, timestamp, session_id, and 8 additional metadata columns. Hardware: AWS r6i.4xlarge (16 vCPU, 128GB RAM) for both databases.
| Query | Postgres (optimized) | ClickHouse | Improvement |
|---|---|---|---|
| Daily active users (90 days) | 34.2 seconds | 0.8 seconds | 42.8x faster |
| Avg session duration by cohort | 67.3 seconds | 1.4 seconds | 48.1x faster |
| Conversion funnel (5 steps) | 89.1 seconds | 2.1 seconds | 42.4x faster |
| Retention cohort analysis | 142.7 seconds | 3.6 seconds | 39.6x faster |
| Top 100 users by activity | 23.8 seconds | 0.5 seconds | 47.6x faster |
The Postgres database had been heavily optimized: composite indexes on (timestamp, user_id), (timestamp, event_type), and (user_id, timestamp), plus partial indexes for common event types. Vacuum ran nightly. Shared_buffers was set to 32GB. Work_mem was 512MB. Despite all this tuning, ClickHouse with zero optimization was 40-48x faster.
Storage comparison was equally dramatic. The Postgres table consumed 187GB on disk (including indexes). The ClickHouse table consumed 31GB—a 6x reduction. ClickHouse's LZ4 compression on timestamp columns and dictionary encoding on user_id reduced storage by 83% compared to Postgres's row format.
Common ClickHouse Migration Mistakes and How to Avoid Them
After helping teams migrate from Postgres to ClickHouse, we've seen the same mistakes repeatedly. Here's what goes wrong and how to prevent it:
Mistake 1: Replicating Postgres Normalization
Teams try to maintain the same normalized schema they had in Postgres—separate tables for users, events, sessions, joined with foreign keys. ClickHouse joins are expensive. A query joining 4 tables that took 2 seconds in Postgres might take 8 seconds in ClickHouse.
Solution: Denormalize aggressively. Create wide tables with all columns you need for common queries. A 50-column events table with embedded user and session data will outperform a normalized schema by 10-20x. Storage is cheap; query speed matters.
Mistake 2: Wrong Primary Key Order
ClickHouse primary keys determine physical sort order on disk. If your queries filter by timestamp, but your primary key is (user_id, timestamp), ClickHouse scans every user partition to find matching timestamps.
Solution: Order your primary key by query filter frequency. If 80% of queries filter by timestamp first, use ORDER BY (timestamp, user_id). If you need both patterns, create a materialized view with reversed order.
Mistake 3: Over-Partitioning
Teams create daily partitions for a table with 2M rows per day. ClickHouse creates separate directories for each partition. With 365 daily partitions, you have 365 directories, 365 sets of files, and massive filesystem overhead.
Solution: Partition by month or week unless you have 100M+ rows per partition. ClickHouse's sparse indexing handles range queries efficiently without fine-grained partitions. Daily partitions make sense at 1B+ rows per day, not before.
Mistake 4: Ignoring Compression Codecs
ClickHouse defaults to LZ4 compression, which is fast but not optimal for all data types. Timestamps compress 5x better with Delta codec. Low-cardinality strings compress 10x better with LowCardinality(String) type.
Solution: Use Delta codec for timestamps and counters. Use DoubleDelta for metrics that change slowly. Use LowCardinality(String) for columns with <10,000 unique values. A 200GB table can shrink to 40GB with proper codecs.
Cost Analysis: Postgres Hardware Upgrades vs ClickHouse Migration
Let's calculate the real cost of trying to scale Postgres vertically versus migrating to ClickHouse. Assume you're running analytical queries on a 500M row events table that grows 5M rows per day.
| Approach | Upfront Cost | Monthly Cost | Query Performance | 12-Month Total |
|---|---|---|---|---|
| Postgres (current: 32GB RAM, 8 vCPU) | $0 | $480 | 45-90 second queries | $5,760 |
| Postgres upgraded (128GB RAM, 32 vCPU) | $0 | $1,920 | 18-35 second queries | $23,040 |
| Postgres + read replicas (3x 64GB) | $0 | $2,880 | 15-30 second queries | $34,560 |
| ClickHouse (64GB RAM, 16 vCPU) | $3,200 (migration) | $960 | 1-3 second queries | $14,720 |
The ClickHouse migration includes $3,200 for 40 hours of engineering time at $80/hour (schema design, backfill, CDC setup, validation). Even with migration costs, ClickHouse saves $8,320 in the first year compared to upgrading Postgres hardware, while delivering 10-15x faster queries.
The hidden cost is engineer time. If your data team spends 30 minutes per day waiting for slow Postgres queries (dashboard loads, ad-hoc analysis, report generation), that's 10 hours per month. At $150/hour fully-loaded cost, that's $1,500/month in wasted productivity. ClickHouse's 2-second queries eliminate this waste entirely.
What Happens to Your Existing Postgres Database After Migration
You don't delete Postgres. You keep it running for transactional workloads and move only analytical tables to ClickHouse. Here's the typical post-migration architecture:
- Postgres continues handling: User authentication, profile updates, order processing, inventory management, payment transactions—anything requiring ACID guarantees and frequent updates
- ClickHouse takes over: Event logs, clickstream data, metrics, time-series data, analytics dashboards, data warehouse queries—anything requiring fast aggregations over millions of rows
- CDC replicates data: Debezium or pg_chameleon streams changes from Postgres to ClickHouse in real-time (typically 1-5 second lag)
- Sourcetable queries both: Federated SQL joins Postgres transactional data with ClickHouse analytical data in a single query
One customer keeps user profiles and subscription data in Postgres (42,000 rows, updated frequently) while storing usage events in ClickHouse (1.2 billion rows, insert-only). Their revenue dashboard joins both: Postgres provides customer tier and MRR, ClickHouse provides usage metrics. The query completes in 1.8 seconds.
Can I query ClickHouse using the same SQL I use for Postgres?
How long does it take to migrate a 200M row Postgres table to ClickHouse?
Will ClickHouse work for my small dataset (under 10M rows)?
Can I update or delete rows in ClickHouse like I can in Postgres?
What happens if my ClickHouse server crashes during a query?
How do I connect Sourcetable to my ClickHouse database?
Can I join ClickHouse tables with Postgres tables in Sourcetable?
Does ClickHouse support transactions and rollbacks like Postgres?
What's the learning curve for a team familiar with Postgres?
Should I migrate all my Postgres tables to ClickHouse?
Query ClickHouse with Natural Language
Connect your ClickHouse database to Sourcetable and analyze data using AI.
Sources
References and resources cited in this article
- ClickHouse Official Documentation - Architecture Overview (2026)
- PostgreSQL Documentation - Performance Tips (2026)
- Benchmark study: ClickHouse vs PostgreSQL for Analytical Workloads (2025)
- Debezium Documentation - Postgres to ClickHouse CDC (2026)
- ClickHouse Cloud - Migration Best Practices Guide (2026)