When One Table Serves Two Masters: CQRS at the Database Level

Series: Building Backarch — Engineering decisions from building backarch.com

"What's the current price of RDS db.t3.micro in us-east-1?" is a point lookup — one row, one index hit, done in microseconds.

"How has that price changed over the last 30 days?" is a range scan — bounded by time, ordered by timestamp, potentially thousands of rows.

These are not different queries on the same data. They are different query shapes with opposite indexing requirements. Building one table to serve both shapes optimises for neither.

The query that looks fine until the table grows

On a single pricing table with a recorded_at timestamp, the naive current-price query looks like this:

SELECT price
FROM pricing
WHERE provider = 'aws'
  AND service = 'rds'
  AND region = 'us-east-1'
  AND instance_type = 'db.t3.micro'
ORDER BY recorded_at DESC
LIMIT 1;

That ORDER BY recorded_at DESC LIMIT 1 is the problem. The planner uses an index on (provider, service, recorded_at DESC), but it still scans all matching rows for this provider/service combination to find the latest one. As the table grows — weekly cron runs, 40+ service types, 10+ regions per service — the scan gets wider.

In Backarch, the cron refreshes pricing data on Sunday morning at 02:00 UTC — the exact moment clients in different time zones are fetching cost estimates for their diagrams. Add concurrent upserts from the weekly pricing cron, and you get lock contention on the same index pages during the most read-heavy period. The table that looked fine at a few hundred rows starts misbehaving once you have years of weekly snapshots across multiple clouds.

The split: one table per query shape

cloud_pricing_history is append-only. Every cron run inserts new rows with the current timestamp. Composite primary key on (time, provider, service, region, instance_type). B-tree index on (provider, service, time DESC) for range scans. Nobody updates rows here — they only insert.

cloud_pricing_latest is a single-row-per-SKU read model. One row per unique (provider, service, region, instance_type, unit) combination, enforced by a UNIQUE constraint. Every cron run upserts this table — same SKU, row overwritten with the fresh price. The entire table fits in PostgreSQL's shared buffer cache. It never grows beyond one row per SKU.

# app/features/pricing/models.py
 
class PricingRecord(Base):
    __tablename__ = "cloud_pricing_history"
    __table_args__ = (
        PrimaryKeyConstraint("time", "provider", "service", "region", "instance_type"),
        Index("ix_pricing_history_lookup", "provider", "service", "time"),
    )
    time: Mapped[datetime]
    provider: Mapped[str]
    service: Mapped[str]
    region: Mapped[str]
    instance_type: Mapped[str]
    price: Mapped[Decimal]
    unit: Mapped[str]
 
class PricingLatest(Base):
    __tablename__ = "cloud_pricing_latest"
    __table_args__ = (
        UniqueConstraint(
            "provider", "service", "region", "instance_type", "unit",
            name="uq_cloud_pricing_latest_service",
        ),
    )
    id: Mapped[UUID]
    provider: Mapped[str]
    service: Mapped[str]
    region: Mapped[str]
    instance_type: Mapped[str]
    price: Mapped[Decimal]
    unit: Mapped[str]
    recorded_at: Mapped[datetime]

Point lookup vs history query

Point lookup — "current price of RDS db.t3.micro in us-east-1":

SELECT price FROM cloud_pricing_latest
WHERE provider = 'aws'
  AND service = 'rds'
  AND region = 'us-east-1'
  AND instance_type = 'db.t3.micro';

Single hit on the UNIQUE constraint index. O(1). No sort. No range scan. The entire cloud_pricing_latest table is likely in shared buffer cache — it's small and read constantly.

History query — "how has this price changed over 30 days":

SELECT price, time FROM cloud_pricing_history
WHERE provider = 'aws'
  AND service = 'rds'
  AND region = 'us-east-1'
  AND instance_type = 'db.t3.micro'
  AND time > now() - interval '30 days'
ORDER BY time DESC;

Bounded range scan on a dedicated index. Fast, because cloud_pricing_history serves only this one query shape — it doesn't need to also handle point lookups.

Write side: two writes, one transaction

The cron job writes to both tables in the same transaction:

# app/features/pricing/repo.py — cron write path
async def refresh_pricing(db: AsyncSession, records: list[PricingRecord]) -> None:
    async with db.begin():
        # Append to history — idempotent on duplicate composite key
        await db.execute(
            insert(PricingRecord)
            .values([r.__dict__ for r in records])
            .on_conflict_do_nothing()
        )
        # Overwrite latest — always reflects freshest data
        await db.execute(
            insert(PricingLatest)
            .values([r.__dict__ for r in records])
            .on_conflict_do_update(
                index_elements=["provider", "service", "region", "instance_type", "unit"],
                set_={"price": excluded.price, "recorded_at": excluded.recorded_at},
            )
        )

Both writes commit together. You never have a window where cloud_pricing_latest has data that cloud_pricing_history doesn't — they're always in sync at the transaction boundary.

90-day retention: five lines, no extra job

At the end of every cron run:

async def purge_old_records(db: AsyncSession) -> int:
    result = await db.execute(
        delete(PricingRecord).where(
            PricingRecord.time < datetime.utcnow() - timedelta(days=90)
        )
    )
    return result.rowcount

90 days is sufficient for pricing trend analysis. Pricing trends beyond that window aren't useful for architecture cost estimation — cloud pricing rarely changes by more than single-digit percentages over a quarter, and when it does, a current snapshot tells you more than a year of history. Five lines. No separate cron. No TTL worker. It runs inline after every data refresh.

cloud_pricing_latest has no TTL because it doesn't need one — it has exactly one row per SKU, always. Only the history table grows, and the 90-day purge keeps it bounded.

Why "CQRS" applies at table level, not just service level

CQRS — Command Query Responsibility Segregation — is usually described as an application architecture pattern, with separate write models and read models that might live in different services or even different databases. The core idea applies directly here: separate the shape optimised for writes from the shape optimised for reads.

cloud_pricing_history is the write model. Append-only, time-ordered, indexed for range scans. The cron writes to it.

cloud_pricing_latest is the read model. Flat, indexed for point lookups, always current. The cost estimation engine reads from it.

The "command" is the cron refreshing pricing data. The "query" is the cost engine computing diagram estimates. They run against different tables with different schemas optimised for different access patterns. The pattern is CQRS — just expressed at the table level instead of the service level.

If your analytics queries and your live-lookup queries share the same table, one of them is paying for the other's access pattern. The split removes that tax.