The TimescaleDB Extension RDS Wouldn't Install (And What We Used Instead)

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

The migration worked perfectly in local Docker. On RDS, it failed with extension "timescaledb" is not available. After removing that line, it failed again — this time with index "pricing_records_time_idx" does not exist. A migration that had nothing to do with indexes was trying to drop one.

Two failure modes. Same root cause: a local environment that had TimescaleDB installed silently creating schema objects that RDS had never heard of.

What RDS PostgreSQL actually supports

RDS PostgreSQL 16 ships with around 50 available extensions. uuid-ossp, pg_trgm, pgvector, btree_gin, pg_stat_statements — these are there. TimescaleDB is not. Neither is PostGIS by default.

The canonical check before designing around any extension: the AWS RDS PostgreSQL supported extensions list. That document exists precisely because this trip-up is common. The extension that runs fine in your Docker image may not exist in your managed database.

The pricing feature needed time-series storage for historical cloud pricing data. TimescaleDB's create_hypertable was the local-dev choice: automatic chunk management, time-based partitioning, clean query interface. The migration originally included:

op.execute("CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE")
op.create_table("cloud_pricing_history", ...)
op.execute("SELECT create_hypertable('cloud_pricing_history', 'time')")

Line one fails on RDS with:

ERROR: extension "timescaledb" is not available
DETAIL: Could not open extension control file "/.../timescaledb.control": No such file or directory

The hidden second failure: autogenerate pollution

After removing the CREATE EXTENSION line, a subtler problem appeared — and it only surfaced because the migration had already run locally with TimescaleDB active.

When create_hypertable runs, TimescaleDB silently creates internal named indexes on the table. On the local database, pricing_records_time_idx now exists as a TimescaleDB-managed index. When alembic revision --autogenerate was run for a subsequent migration (one that modified the users table), Alembic compared the current database state against the ORM models. It saw an index that wasn't declared anywhere in the models. It emitted:

op.drop_index("pricing_records_time_idx", table_name="cloud_pricing_history")

This line — dropping a TimescaleDB internal index — landed inside a migration that was supposed to only add a username column to users. Running that migration on RDS fails with:

ERROR: index "pricing_records_time_idx" does not exist

Because TimescaleDB never ran on RDS, the index was never created there. The cleanup required removing the orphaned drop_index calls from the migration and re-verifying the full chain against a fresh RDS instance.

The lesson: any extension that creates implicit schema objects locally — indexes, constraint triggers, partitions — will pollute Alembic autogenerate output. The next migration will contain drop statements for objects that don't exist in any environment except the local one with the extension active.

What plain PostgreSQL handles at this scale

The fix is a composite primary key and one index:

op.create_table(
    "cloud_pricing_history",
    sa.Column("time", sa.DateTime(timezone=True), nullable=False),
    sa.Column("provider", sa.String(50), nullable=False),
    sa.Column("service", sa.String(100), nullable=False),
    sa.Column("region", sa.String(50), nullable=False),
    sa.Column("instance_type", sa.String(100), nullable=False),
    sa.Column("unit", sa.String(50), nullable=False),
    sa.Column("price_usd", sa.Numeric(18, 9), nullable=False),
    sa.PrimaryKeyConstraint("time", "provider", "service", "region", "instance_type"),
)
op.create_index(
    "ix_pricing_history_provider_service_time",
    "cloud_pricing_history",
    ["provider", "service", sa.text("time DESC")],
)

In Backarch, the current dataset is roughly 5,500 cloud pricing rows across AWS, GCP, and Azure. That's not time-series at scale — it's a few thousand rows with a timestamp column. A B-tree index on (provider, service, time DESC) handles the range queries for the pricing history endpoint without TimescaleDB's overhead or its portability cost.

INSERT ... ON CONFLICT DO NOTHING handles idempotent refresh — the weekly cron re-inserts all pricing, silently skipping any row that already exists at the same composite key. No TimescaleDB compression, no automatic partitioning, no extension management. It runs on Docker, CI, and RDS identically.

The portability dividend is the real win. Local Docker, CI containers, and RDS run the exact same schema. When a new developer clones the repo and runs docker compose up && alembic upgrade head, they get production-equivalent behaviour. There are no environment-specific branches, no "it worked on my machine" migrations, no extension flags in the Alembic env.

Before you build your schema around a database extension: does your managed database support it, and does enabling it locally create implicit schema objects that Alembic will try to manage?

Next: Repository functions vs repository classes — why self.db in a constructor isn't buying you anything FastAPI's Depends() doesn't already provide.