GCP Database & Logging Cost Optimisation

The Zimi platform stores years of device telemetry and control history in Cloud SQL for PostgreSQL, and it sends application logs to Cloud Logging. Both grow every day. I treated that growth as a data-lifecycle problem: what must stay hot, what can be archived, and what should never have been written.

The storage bill was a data-lifecycle problem

Time-series tables do not plateau. Every power sample and state change lands in the operational database, so storage, backups, and query plans all get heavier even when nobody is asking about last year.

Retention as DELETE FROM … WHERE time < … made that worse. Removing millions of rows is a long write that generates WAL, dead tuples, bloat, and autovacuum pressure. Ordinary VACUUM generally makes that space reusable inside PostgreSQL; it does not return the table’s disk allocation to the operating system. Meanwhile the queries operators actually run — last hour, last day, this month — were still scanning a table that contained the whole history.

Logging had the same shape. Repetitive, low-signal lines were cheap to console.log and expensive to ingest and retain.

What changed

I converted the hot time-series tables to monthly PostgreSQL range partitions on Cloud SQL. New months are created before they are needed; inserts land in the current month; older months are physical tables we can manage on their own.

The retention path is then boring on purpose:

1. Archive a month that has left the active window.
2. Check the archive is complete.
3. DROP the partition from Cloud SQL.

Dropping an expired partition avoids row-by-row deletion and the resulting vacuum overhead. It is fast, but PostgreSQL takes an ACCESS EXCLUSIVE lock on the parent table for the operation, so the retention job runs in a controlled window and keeps the lock brief.

On the logging side I went through what the services emitted and kept lines that help with monitoring, incidents, state changes, and audit. Noise that never appeared in those workflows stopped going to Cloud Logging.

Why drop a month, not delete rows

Monthly boundaries match how this data is used. Support and operations almost always ask about recent time. Partition pruning lets PostgreSQL ignore months outside the query range, so the hot path stays a few partitions even as the estate ages.

A month is also a sane ops unit: large enough that we are not managing hundreds of tiny tables, small enough to archive and drop on a schedule. Daily partitions would have been more moving parts; yearly partitions would have left too much history in the active query path.

The important product decision sits next to the SQL: how long is “active,” where does the archive live, and who is allowed to read it. Partitioning without that agreement just moves the mess.

After

Active Cloud SQL size is now governed by the retention window, not by how long the platform has been running. Recent telemetry queries work against a smaller set of partitions. Retention no longer depends on a multi-hour delete job.

Cloud Logging ingest and retain less, without dropping the context needed to run the platform.

Approved before-and-after storage, query, and logging figures are not published here.

Related: the same data stores sit in the C4 model — document snapshot, Redis hot path, partitioned PostgreSQL history, archive.