TimescaleDB database engine

TimescaleDB engine for Piko's database service. It is a thin Postgres-derived engine that teaches Piko's build-time SQL parser the TimescaleDB DDL forms on top of the PostgreSQL engine, bundled into a single EngineConfig.

Overview

TimescaleDB is a Postgres extension for time-series data. It adds hypertables, continuous aggregates, native compression, retention policies, and a library of hyperfunctions (time_bucket, first, last, gap-filling, and friends). Most of its SQL surface is plain Postgres.

The split that defines this integration is build-time against runtime. The TimescaleDB-aware parser exists for piko generate sql. At build time it teaches Piko's code generator the time-series DDL forms so codegen resolves typed queries against your migrations without choking. At runtime the engine is plain Postgres. You get the same wire protocol, the same migrations, and the same transactions and advisory locks. Operating a TimescaleDB database in Piko is indistinguishable from operating a Postgres one.

The build-time engine extends the Postgres engine through its public hooks instead of forking the parser. It inherits all Postgres DDL, DML, and type coverage and stays in lockstep with Postgres. On top of that base it contributes three things. It registers the TimescaleDB hyperfunction library (around fifty functions). It adds type aliases for the opaque aggregate-state types (counter_summary, candlestick, tdigest, and so on) so codegen does not error when a query returns one. It parses the time-series DDL, including the CREATE HYPERTABLE forms, continuous-aggregate materialized views, the compression and policy management calls, and the modern CREATE TABLE ... WITH (tsdb.hypertable, ...) form. There is no live-database catalogue introspection. The schema Piko knows is the one your migrations declare.

Reach for TimescaleDB when you have time-series or event data. This covers metrics, IoT readings, financial ticks, and application telemetry that you want to live inside Postgres next to your relational data. You keep Postgres joins, constraints, and the extension ecosystem (PostGIS, pgvector) while gaining automatic partitioning, columnar compression, and rollups. Reach for plain PostgreSQL when you do not need the time-series machinery. Reach for ClickHouse when ingest volume and pure analytical scan throughput dominate and you do not need transactional Postgres alongside it. Reach for CockroachDB when horizontal scale on the Postgres dialect matters more than time-series features.

Configuration

The constructor takes no arguments. A single no-arg TimescaleDB() call returns a fully wired EngineConfig that drops into the same DatabaseRegistration as every other engine, so you write no glue. The returned EngineConfig bundles three pieces. They are the Postgres-derived codegen engine, the postgres driver name, and the Postgres migration dialect. TimescaleDB is wire-compatible with Postgres, so the config uses the postgres driver name. It reuses the Postgres migration dialect unchanged, so you keep Postgres transactions and advisory-lock-based migration coordination.

import (
    "piko.sh/piko/wdk/db"
    "piko.sh/piko/wdk/db/db_engine_timescaledb"
)

engineConfig := db_engine_timescaledb.TimescaleDB()

Bootstrap

TimescaleDB speaks the Postgres wire protocol, so use a Postgres driver such as pgx.

import (
    "database/sql"
    "os"

    _ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver

    "piko.sh/piko"
    "piko.sh/piko/wdk/db"
    "piko.sh/piko/wdk/db/db_engine_timescaledb"
)

connection, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
    return err
}

ssr := piko.New(
    piko.WithDatabase("metrics", &db.DatabaseRegistration{
        DB:           connection,
        DriverName:   "pgx",
        EngineConfig: db_engine_timescaledb.TimescaleDB(),
        MigrationFS:  migrationsFS,
    }),
)

This example pre-opens the *sql.DB and passes it as DB, so Piko never opens a connection itself. The postgres driver name baked into the EngineConfig is a fallback that Piko reads only when it opens the connection for you (for example when you set DSN instead of DB). The database/sql pgx driver registers itself as pgx, not postgres, so set the registration DriverName to pgx to match the driver you imported.

Tradeoffs

TimescaleDB is Postgres, so it inherits Postgres operational characteristics. You get single-primary writes, the same replication and high-availability story, and the same single-node performance ceiling. It buys you time-series ergonomics, not a different scaling model. Consider the case where raw analytical scan throughput over enormous, append-only datasets is the dominant requirement and you do not need transactional Postgres alongside it. There, a purpose-built columnar store like ClickHouse out-scans it. Reach for TimescaleDB when the win is keeping time-series and relational data in one Postgres you already operate.

See also

Other database engines:

  • PostgreSQL, the host engine; TimescaleDB is a superset of its parser and type system.
  • ClickHouse, columnar OLAP for high-ingest analytics without a transactional store.
  • CockroachDB, distributed Postgres-compatible scale.
  • MySQL, widely available managed transactional alternative.

Framework docs:

External: