From PostgreSQL to TimescaleDB: How an IoT Startup Cut Query Latency by 94% and Monthly DB Costs by $8,000 — A Vietnam Offshore Case Study

Your time-series data is growing faster than your queries can handle. You’ve tried indexing. You’ve tried partitioning. You’ve tried throwing more money at RDS. None of it works.

I know that pain intimately. We just helped a US-based IoT startup escape it.

Why Smart CTOs Hire Vietnamese Developers: A Data-Driven Guide to Offshore Engineering

TL;DR: Vietnam is emerging as the top offshore engineering destination for 2024-2025. Lower costs than India, higher retention… ...

Read more

Recently, a client in the smart building space came to us with a mess. They were ingesting sensor data from 50,000+ devices across 1,200 commercial buildings. Every 15 seconds, each device emitted temperature, humidity, energy consumption, and occupancy metrics. That’s roughly 2 million writes per day into a single PostgreSQL instance.

Their application queries were simple: “Give me the average temperature for building X over the last 7 days.” But with 200TB of raw time-series data, that query took 2.2 seconds. Average. Dead simple queries were timing out at 15 seconds.

Designing Resilient Multi-Agent AI Systems: Why Prompt Chaining Fails and Event-Driven Loops Win

Designing Resilient Multi-Agent AI Systems: Why Prompt Chaining Fails and Event-Driven Loops Win TL;DR: Simple prompt chaining breaks… ...

Read more

Their monthly RDS bill? $12,000. Mostly for IOPS we couldn’t escape.

Here’s how we fixed it with a team of 5 Vietnamese engineers from our Can Tho hub, using the ECOA AI Platform ACP to automate the dirty work.

The Core Problem: PostgreSQL Was Never Built for This

PostgreSQL is an incredible general-purpose database. But it’s not a time-series database. The fundamental issue is row-level indexing. Every query hits the index, then does heap lookups. With time-series, you’re almost always querying by a time range on recent data. PostgreSQL doesn’t optimize for that.

We were seeing:

• P95 query latency: 6.8 seconds for range queries over 30 days
• Storage amplification: 3.2x due to MVCC overhead on constantly updated rows
• Autovacuum hell: The worker was constantly running, consuming 40% of CPU

The client had tried manual partitioning by month. It helped marginally. But partitioned tables in PostgreSQL still don’t give you the columnar compression or chunk-level pruning that a purpose-built time-series engine provides.

Why TimescaleDB? Not a Hard Choice

Actually, we evaluated three options:

1. InfluxDB: Great write throughput, but terrible JOIN support with relational data
2. ClickHouse: Blazing fast analytics, but complex operations and limited tooling
3. TimescaleDB: PostgreSQL extension — same SQL, same tooling, but hypertables for time-series

TimescaleDB won because the client’s schema had 12 relational tables (buildings, devices, tenants, maintenance logs) that needed to JOIN with time-series data. Rewriting those into a non-relational store would have taken months.

TimescaleDB gave us native PostgreSQL compatibility with chunked storage, automatic partitioning, and columnar compression. No application code changes needed for the JOINs.

The Migration: 8 Weeks, Zero Downtime

Here’s the exact strategy we executed with the Vietnamese team.

Phase 1: Schema Conversion (Week 1-2)

TimescaleDB uses hypertables. We took the main `sensor_readings` table and converted it:

sql
-- Before (PostgreSQL)
CREATE TABLE sensor_readings (
    id BIGSERIAL,
    device_id INTEGER NOT NULL,
    sensor_type VARCHAR(20) NOT NULL,
    value DOUBLE PRECISION NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL,
    building_id INTEGER NOT NULL
);
CREATE INDEX idx_readings_device_time ON sensor_readings(device_id, recorded_at);

-- After (TimescaleDB)
CREATE TABLE sensor_readings (
    recorded_at TIMESTAMPTZ NOT NULL,
    device_id INTEGER NOT NULL,
    sensor_type VARCHAR(20) NOT NULL,
    value DOUBLE PRECISION NOT NULL,
    building_id INTEGER NOT NULL
);
SELECT create_hypertable('sensor_readings', 'recorded_at', chunk_time_interval => INTERVAL '1 day');
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');

Honestly, the schema change was minimal. The hypertable definition and compression policy took 15 minutes. The real work was in the data migration.

Phase 2: Data Migration Without Tears (Week 3-5)

Two hundred terabytes doesn’t move overnight. We built a dual-write pipeline:

1. Keep existing PostgreSQL live — all reads and writes continued against the old database
2. Set up TimescaleDB as a read replica — we used `pg_dump` with custom chunking to export 1TB batches
3. Double-write all new data — application layer wrote to both PostgreSQL and TimescaleDB simultaneously
4. Backfill historical data — using `COPY` commands with batch sizes of 100,000 rows

The ECOA AI Platform ACP orchestrated the backfill process. We defined agent workflows:

• Ingestion Agent: Pulled data from PostgreSQL in chunks
• Validation Agent: Checked data integrity against source (row counts, checksums)
• Migration Agent: Executed batch `COPY` into TimescaleDB
• Alerting Agent: Flagged any mismatches above 0.01% threshold

This was critical. Manual backfills are error-prone. The AI layer caught 47 data integrity issues in the first 3 days — everything from missing timestamps to duplicate device IDs in the source data.

Phase 3: Application Layer (Week 6-7)

The client’s ORM (SQLAlchemy) needed zero changes. TimescaleDB speaks PostgreSQL wire protocol. We just updated the connection string.

But queries needed optimization. We refactored the most expensive calls:

Before (PostgreSQL, 2.2s):

sql
SELECT AVG(value)
FROM sensor_readings
WHERE building_id = 542
  AND sensor_type = 'temperature'
  AND recorded_at >= NOW() - INTERVAL '7 days';

After (TimescaleDB, 0.13s):

sql
SELECT AVG(value)
FROM sensor_readings
WHERE building_id = 542
  AND sensor_type = 'temperature'
  AND recorded_at >= NOW() - INTERVAL '7 days'
ORDER BY recorded_at DESC
LIMIT 1;

Wait, that’s the same query. The speedup came entirely from the hypertable structure. The chunk pruning at the data level cut the scan size from gigabytes to megabytes.

Phase 4: Cutover and Validation (Week 8)

We ran a full week of dual-write validation. Every query fired against both databases. We used the ECOA platform’s observability layer to compare:

• Query latency distributions (P50, P95, P99)
• Error rates
• Data freshness
• Cost per query

The results spoke for themselves.

The Numbers That Matter

The cost reduction wasn’t just from cheaper storage. TimescaleDB’s native compression reduced the dataset by 76%. We moved from `db.r6g.8xlarge` (32 vCPU, 256GB RAM) to `db.r6g.4xlarge` (16 vCPU, 128GB RAM). Way less IOPS provisioning needed.

The Vietnamese Team: Why It Worked

Let’s talk about the actual people who did this.

We staffed 5 engineers from our Can Tho hub:

• 1 Senior Database Engineer (10 years PostgreSQL experience, 3 years TimescaleDB)
• 2 Middle Backend Engineers (Python, SQLAlchemy, async task queues)
• 2 Junior DevOps Engineers (Terraform, Ansible, monitoring)

Total monthly cost: $10,000. That’s less than the client’s *database bill alone* before the migration.

But more importantly, these engineers had real experience with high-throughput IoT pipelines. One of our leads had previously managed a 500TB cluster for a Vietnamese smart city project. This wasn’t their first rodeo.

The async work was done via our Can Tho hub in the GMT+7 timezone. We overlapped 4 hours with the US East Coast team. The rest was asynchronous with well-documented PRs and the ECOA agent orchestration handling the continuous data validation.

How ECOA AI Platform ACP Made This Possible

We didn’t just throw people at the problem. The AI orchestration was critical.

The migration involved:

• 37 custom Python scripts for data extraction, transformation, and validation
• 12 Terraform modules for infrastructure provisioning
• 15 Hasura metadata changes for GraphQL APIs

The ECOA ACP automated the execution of these scripts in parallel across 8 worker nodes. It tracked dependencies, retried failures, and alerted on anomalies. Without it, the backfill alone would have taken 5 weeks instead of 3.

And the cost savings? The client’s monthly bill dropped from $12,000 to $4,200. That’s a $93,600 annual savings. The migration paid for itself in 2 months.

Key Takeaways

1. Don’t force PostgreSQL to do time-series — it’s not built for it. TimescaleDB (or ClickHouse) is the right tool.
2. Zero-downtime migration is possible with dual-write pipelines and chunked backfills. It just takes planning.
3. AI orchestration matters — manual migration scripts are error-prone. Automated validation catches edge cases humans miss.
4. Vietnamese engineering teams deliver — the talent in Can Tho and Ho Chi Minh City rivals any North American team at 1/3 the cost.

Is your PostgreSQL database buckling under time-series load? You’re not alone. But you don’t have to live with it.

—

Frequently Asked Questions

Q: Is TimescaleDB fully compatible with PostgreSQL for migration?

A: Yes, it’s a PostgreSQL extension. All your existing queries, ORM tools, and connection pools work without changes. The main migration work is converting your time-series tables to hypertables and backfilling historical data. Our team handled 200TB in 8 weeks with zero application code changes.

Q: What’s the cost benefit of switching to TimescaleDB for IoT workloads?

A: We typically see 60-80% storage reduction due to native compression, plus 50-70% compute cost savings because queries are faster and need less resources. In this case, the client went from $12,000/month to $4,200/month — a 65% reduction. ROI is usually under 3 months.

Q: Can a Vietnamese offshore team handle complex database migrations?

A: Absolutely. Our Can Tho engineers have deep experience with PostgreSQL, TimescaleDB, ClickHouse, and MongoDB. The key is vetting for specific domain expertise — not just SQL knowledge but experience with high-throughput, time-series data pipelines. We screen for that explicitly.

Q: Do we need to rewrite our application queries for TimescaleDB?

A: No. Standard SQL works. But you’ll want to optimize queries to leverage time-based pruning. In our case, just switching the database cut query latency by 94% without any SQL changes. Additional optimization (like `ORDER BY` + `LIMIT`) can push it even further.

Related reading: Why You Should Hire Vietnamese Developers: A CTO’s Honest Take

Related reading: Vietnam Outsourcing: Why Smart Tech Leaders Are Betting on This Southeast Asian Powerhouse