Six Signs That Postgres Tuning Won't Fix Your Performance Problems

You've added indexes. You've partitioned tables. You've tuned autovacuum within an inch of its life. Performance improves for a few months, and then the dashboards go red again. Sound familiar?

If so, you're probably not doing anything wrong. You're running a workload that vanilla Postgres was never designed for, and no amount of configuration will change that.

It's not transactional. It's not a data warehouse. It's analytics on live data: high-frequency ingestion that stays operationally queryable. If you're running this pattern, you've already been through the cycle: add indexes, partition tables, tune autovacuum, upgrade instances. Each fix buys a few months. Then the metrics climb again.

The longer you wait, the harder the migration. At 10M rows it takes days. At 500M rows, weeks. At 1B+, months. Recognizing the pattern early is the highest-leverage decision you can make.

This post describes six characteristics that define this workload. If four or five apply to your system, the friction is architectural, not operational. (For a deeper look at how purpose-built time-series architecture addresses these constraints, see the Tiger Data architecture whitepaper.)

Continuous High-Frequency Ingestion

The database is absorbing thousands to hundreds of thousands of inserts per second. Not in bursts. Not during a nightly ETL window. Continuously, 24/7.

Consider a semiconductor fab with 8,000 CNC machines and inspection stations on the floor, each reporting vibration, temperature, spindle speed, and tool wear every 2 seconds. That's 4,000 inserts/sec from a single facility. Add process control events, quality inspection results, and environmental monitoring across three plants, and you're at 30-50K inserts/sec before accounting for growth.

-- What a single station's insert stream looks like
INSERT INTO machine_telemetry (ts, station_id, metric, value)
VALUES
  (now(), 'CNC-4401', 'vibration_mm_s', 2.34),
  (now(), 'CNC-4401', 'spindle_rpm', 12045),
  (now(), 'CNC-4401', 'coolant_temp_c', 31.2),
  (now(), 'CNC-4401', 'tool_wear_pct', 67.8);
-- Multiply by 8,000 stations × 0.5 Hz × 3 facilities

This matters because Postgres needs breathing room to run maintenance. Autovacuum, index maintenance, statistics collection. Continuous ingestion means maintenance always competes with writes. There is no off-peak window.

Queries Revolve Around Time

Nearly every row has a timestamp, and nearly every query filters on a time range. Last 30 minutes. This week versus last week. Everything between two dates.

A trading platform captures every order, fill, and cancellation across multiple venues. The operations team monitors execution quality in real time. The compliance team audits historical patterns. Both teams write queries that look like this:

-- Operations: real-time execution quality
SELECT venue, avg(fill_latency_us), percentile_cont(0.99)
  WITHIN GROUP (ORDER BY fill_latency_us)
FROM executions
WHERE ts > now() - interval '15 minutes'
GROUP BY venue;

-- Compliance: historical pattern detection
SELECT account_id, count(*) as cancel_count
FROM order_events
WHERE ts BETWEEN '2025-01-01' AND '2025-03-31'
  AND event_type = 'cancel'
  AND cancel_reason = 'client_requested'
GROUP BY account_id
HAVING count(*) > 500;

Time is the primary axis for both storage and retrieval. General-purpose B-tree indexes aren't built for this access pattern, which is why teams end up building manual partitioning schemes and custom tooling to get time-range queries to perform.

Data Is Append-Only

Once a row lands, it doesn't change. Sensor readings are immutable. Financial transactions don't get updated. Log entries are permanent. When data gets removed, it happens in bulk: drop an entire month's partition, not individual rows.

A wind farm operator collects turbine performance data: blade pitch, rotor speed, power output, nacelle orientation. Once recorded, these readings are facts. They never get corrected or overwritten.

-- This is the entire write pattern. INSERT. No UPDATE. No single-row DELETE.
INSERT INTO turbine_readings
  (ts, turbine_id, blade_pitch_deg, rotor_rpm, power_kw, wind_speed_ms)
VALUES
  (now(), 'WT-112', 12.4, 14.2, 2840, 11.3);

-- Data removal is always bulk
DROP TABLE turbine_readings_2023_q1;

Every row you insert carries 23 bytes of MVCC transaction metadata, on data you will never update. Autovacuum scans these tables constantly, cleaning up dead tuples that were never created through updates. At 50K inserts/sec, that's MVCC overhead on 4.3 billion rows per day that will never be modified. You're paying the full cost of a concurrency model designed for workloads that look nothing like yours.

Retention Is Measured in Months or Years

Seven years of financial records for compliance. Quarters of manufacturing data for root cause analysis. Two-plus years of training data for ML pipelines.

A pharmaceutical manufacturer tracks environmental conditions (temperature, humidity, particulate count) across cleanroom facilities to meet FDA 21 CFR Part 11 requirements. When a batch fails quality control six months after production, the investigation pulls environmental data from the exact time window the batch was in each room.

-- Root cause investigation: what were cleanroom conditions
-- during a batch produced 6 months ago?
SELECT room_id, avg(temp_c), max(particulate_count),
  bool_or(humidity_pct > 45) as humidity_excursion
FROM cleanroom_environment
WHERE ts BETWEEN '2025-08-14 06:00' AND '2025-08-14 18:00'
  AND facility = 'building_3'
GROUP BY room_id;

Short retention hides architectural problems because old data ages out. Long retention removes that escape valve. At 50K inserts per second, that's 1.5 billion rows per year. After three years: 4.5 billion rows.

Queries Are Latency-Sensitive

This data isn't sitting in cold storage waiting for a weekly report. It's being queried actively, under latency constraints.

A SaaS observability platform collects metrics from thousands of customer deployments. The product serves real-time dashboards, automated alerting, and deep-dive investigation, all from the same database. Latency expectations form a gradient:

-- Dashboard widget: last 5 minutes, needs < 100ms response
SELECT host_id, avg(cpu_pct), max(mem_used_bytes)
FROM host_metrics
WHERE ts > now() - interval '5 minutes'
  AND customer_id = 'cust_8821'
GROUP BY host_id;

-- Alert evaluation: last hour, needs < 500ms
SELECT host_id, avg(cpu_pct)
FROM host_metrics
WHERE ts > now() - interval '1 hour'
  AND customer_id = 'cust_8821'
GROUP BY host_id
HAVING avg(cpu_pct) > 90;

-- Incident investigation: last 3 months, seconds acceptable
SELECT date_trunc('hour', ts), avg(cpu_pct), avg(mem_used_bytes)
FROM host_metrics
WHERE ts > now() - interval '90 days'
  AND host_id = 'host-a3f9c'
GROUP BY 1 ORDER BY 1;

Data warehouse scope with operational latency requirements. All from a single system.

Growth Is Sustained

Data volume growing 50-100%+ year over year on a predictable curve. Static workloads can be over-provisioned once and left alone. Growing workloads demand constant re-optimization.

A logistics company tracks GPS position, engine diagnostics, and cargo conditions across a fleet of refrigerated trucks. They started with 200 trucks. Expansion added 150 trucks in year one, another 300 in year two. Each truck reports every 10 seconds.

Year 1:  200 trucks ×  6 readings/min × 1,440 min/day = 1.7M rows/day
Year 2:  350 trucks ×  6 readings/min × 1,440 min/day = 3.0M rows/day
Year 3:  650 trucks ×  6 readings/min × 1,440 min/day = 5.6M rows/day

Cumulative after 3 years: ~3.8 billion rows

Every optimization you ship today is solving for a table size you'll blow past in six months. The treadmill doesn't stop.

What to Do With This

Count how many of these characteristics describe your system. If it's two or three, standard Postgres optimization should have a real impact. The architecture fits your workload. Better indexes, smarter queries, autovacuum tuning. The usual playbook works.

If it's four or five, however, the friction is architectural, not operational. You don't need to abandon Postgres. Tiger Data extends vanilla Postgres to handle exactly this workload. You keep SQL, your extensions, your team's expertise, and the entire Postgres ecosystem. What changes is the storage engine, partitioning, and query planning underneath.

The numbers bear this out. In benchmarks against vanilla PostgreSQL at one billion rows, TimescaleDB delivered up to 1,000x faster query performance while reducing storage by 90% through native compression. Ingest throughput stays constant past 10 billion rows, while PostgreSQL's performance degrades as indexed tables outgrow memory (throughput that starts at 100K+ rows/sec can crash to hundreds). On Azure infrastructure running RTABench workloads, Tiger Cloud was 1,200x faster than vanilla PostgreSQL across 40 real-time analytics queries. These aren't synthetic edge cases. They're the exact query patterns this post describes: time-range filters, aggregations, selective scans on growing datasets.

This post is part of a series on Postgres performance limits for high-frequency data workloads. The full analysis, including a workload scoring framework and migration complexity breakdown at different scales, is in the anchor essay: Understanding Postgres Performance Limits for Analytics on Live Data. Ready to test it on your own data? Start a free Tiger Data trial.