TimescaleDB

TimescaleDB Testing Guide: Hypertable Testing, Continuous Aggregate Validation & Time-Bucket Queries

HelpMeTest

17 May 2026 — 9 min read

TimescaleDB extends PostgreSQL with time-series superpowers—automatic partitioning via hypertables, continuous aggregates, and time-bucket functions. Since it's built on PostgreSQL, most Postgres testing tools work. But TimescaleDB-specific features require specific testing approaches. This guide covers all of them.

TimescaleDB Testing Stack

TimescaleDB runs as a PostgreSQL extension, which simplifies setup:

Testcontainers: Official TimescaleDB image from Docker Hub
JDBC: Standard PostgreSQL JDBC driver works
Flyway/Liquibase: Standard database migration tools work
Spring Boot: @DataJpaTest with TimescaleDB-specific configuration

Setting Up TimescaleDB with Testcontainers

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

@Testcontainers
class TimescaleDBIntegrationTest {

    @Container
    static PostgreSQLContainer<?> timescale = new PostgreSQLContainer<>(
        DockerImageName.parse("timescale/timescaledb:latest-pg16")
            .asCompatibleSubstituteFor("postgres")
    )
    .withDatabaseName("test_db")
    .withUsername("test")
    .withPassword("test");

    static DataSource dataSource;
    static Connection connection;

    @BeforeAll
    static void setup() throws Exception {
        dataSource = DataSourceBuilder.create()
            .url(timescale.getJdbcUrl())
            .username(timescale.getUsername())
            .password(timescale.getPassword())
            .build();

        connection = dataSource.getConnection();

        // Enable TimescaleDB extension
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("CREATE EXTENSION IF NOT EXISTS timescaledb");
        }
    }

    @AfterAll
    static void tearDown() throws Exception {
        if (connection != null) connection.close();
    }

    @BeforeEach
    void resetSchema() throws Exception {
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("DROP TABLE IF EXISTS metrics CASCADE");
            stmt.execute("DROP TABLE IF EXISTS sensor_readings CASCADE");
        }
    }
}

Hypertable Testing

Creating and Validating Hypertables

@Test
void shouldCreateHypertableWithCorrectPartitioning() throws Exception {
    try (Statement stmt = connection.createStatement()) {
        // Create regular table first
        stmt.execute("""
            CREATE TABLE metrics (
                time        TIMESTAMPTZ NOT NULL,
                device_id   TEXT NOT NULL,
                metric_name TEXT NOT NULL,
                value       DOUBLE PRECISION,
                tags        JSONB
            )
        """);

        // Convert to hypertable partitioned by time
        stmt.execute(
            "SELECT create_hypertable('metrics', 'time', " +
            "chunk_time_interval => INTERVAL '1 day')"
        );

        // Verify it's a hypertable
        ResultSet rs = stmt.executeQuery(
            "SELECT hypertable_name, num_dimensions FROM timescaledb_information.hypertables " +
            "WHERE hypertable_name = 'metrics'"
        );

        assertTrue(rs.next(), "metrics should be registered as a hypertable");
        assertEquals("metrics", rs.getString("hypertable_name"));
        assertEquals(1, rs.getInt("num_dimensions"));
    }
}

@Test
void shouldCreateSpacePartitionedHypertable() throws Exception {
    try (Statement stmt = connection.createStatement()) {
        stmt.execute("""
            CREATE TABLE sensor_readings (
                time      TIMESTAMPTZ NOT NULL,
                sensor_id INT NOT NULL,
                value     DOUBLE PRECISION
            )
        """);

        // Space+time partitioned hypertable (better for multi-tenant workloads)
        stmt.execute(
            "SELECT create_hypertable('sensor_readings', 'time', " +
            "'sensor_id', number_partitions => 4, " +
            "chunk_time_interval => INTERVAL '7 days')"
        );

        ResultSet rs = stmt.executeQuery(
            "SELECT num_dimensions FROM timescaledb_information.hypertables " +
            "WHERE hypertable_name = 'sensor_readings'"
        );

        assertTrue(rs.next());
        assertEquals(2, rs.getInt("num_dimensions")); // time + space
    }
}

Testing Automatic Chunk Creation

@Test
void shouldAutoCreateChunksOnInsert() throws Exception {
    createMetricsHypertable();

    Instant now = Instant.now();

    // Insert data across 3 different days
    try (PreparedStatement ps = connection.prepareStatement(
        "INSERT INTO metrics (time, device_id, metric_name, value) VALUES (?, ?, ?, ?)"
    )) {
        for (int day = 0; day < 3; day++) {
            Timestamp ts = Timestamp.from(now.minus(day, ChronoUnit.DAYS));
            ps.setTimestamp(1, ts);
            ps.setString(2, "device-1");
            ps.setString(3, "temperature");
            ps.setDouble(4, 20.0 + day);
            ps.addBatch();
        }
        ps.executeBatch();
    }

    // Each day should be in a separate chunk
    ResultSet chunks = connection.createStatement().executeQuery(
        "SELECT count(*) as chunk_count FROM timescaledb_information.chunks " +
        "WHERE hypertable_name = 'metrics'"
    );
    chunks.next();
    int chunkCount = chunks.getInt("chunk_count");

    assertTrue(chunkCount >= 3, "Should have at least 3 chunks for 3 days of data");
}

Time-Bucket Query Testing

time_bucket() is TimescaleDB's core function for grouping time-series data:

Testing Hourly Aggregations

@Test
void shouldAggregateSensorDataByHour() throws Exception {
    createMetricsHypertable();

    Instant baseTime = Instant.parse("2024-01-15T00:00:00Z");

    // Insert 60 readings, one per minute for 1 hour
    try (PreparedStatement ps = connection.prepareStatement(
        "INSERT INTO metrics (time, device_id, metric_name, value) VALUES (?, ?, ?, ?)"
    )) {
        for (int minute = 0; minute < 60; minute++) {
            ps.setTimestamp(1, Timestamp.from(baseTime.plus(minute, ChronoUnit.MINUTES)));
            ps.setString(2, "device-1");
            ps.setString(3, "cpu_percent");
            ps.setDouble(4, 40.0 + (minute % 10));  // Values 40-49
            ps.addBatch();
        }
        ps.executeBatch();
    }

    // Aggregate by 15-minute buckets
    ResultSet rs = connection.createStatement().executeQuery("""
        SELECT
            time_bucket('15 minutes', time) AS bucket,
            avg(value) AS avg_value,
            min(value) AS min_value,
            max(value) AS max_value,
            count(*) AS reading_count
        FROM metrics
        WHERE device_id = 'device-1' AND metric_name = 'cpu_percent'
        GROUP BY bucket
        ORDER BY bucket
    """);

    int bucketCount = 0;
    while (rs.next()) {
        assertEquals(15, rs.getInt("reading_count"),
            "Each 15-min bucket should have 15 readings");
        assertTrue(rs.getDouble("min_value") >= 40.0);
        assertTrue(rs.getDouble("max_value") < 50.0);
        bucketCount++;
    }
    assertEquals(4, bucketCount, "60 minutes / 15 minutes = 4 buckets");
}

Testing Daily and Weekly Aggregations

@Test
void shouldAggregateToDailyBuckets() throws Exception {
    createMetricsHypertable();

    // Insert 100 readings per day for 7 days
    Instant now = Instant.parse("2024-01-22T12:00:00Z");
    try (PreparedStatement ps = connection.prepareStatement(
        "INSERT INTO metrics (time, device_id, metric_name, value) VALUES (?, ?, ?, ?)"
    )) {
        for (int day = 0; day < 7; day++) {
            for (int reading = 0; reading < 100; reading++) {
                ps.setTimestamp(1, Timestamp.from(
                    now.minus(day, ChronoUnit.DAYS)
                       .minus(reading, ChronoUnit.MINUTES)
                ));
                ps.setString(2, "server-1");
                ps.setString(3, "requests_per_sec");
                ps.setDouble(4, 100.0 + reading);
                ps.addBatch();
            }
        }
        ps.executeBatch();
    }

    ResultSet rs = connection.createStatement().executeQuery("""
        SELECT
            time_bucket('1 day', time) AS day,
            avg(value) AS daily_avg,
            sum(value) AS daily_sum,
            count(*) AS reading_count
        FROM metrics
        WHERE device_id = 'server-1'
        GROUP BY day
        ORDER BY day DESC
    """);

    int dayCount = 0;
    while (rs.next()) {
        assertEquals(100, rs.getInt("reading_count"),
            "Each day should have 100 readings");
        dayCount++;
    }
    assertEquals(7, dayCount);
}

@Test
void shouldCalculateWeekOverWeekComparison() throws Exception {
    createMetricsHypertable();

    // Seed two weeks of data: week 1 avg=100, week 2 avg=120
    insertWeekOfData("2024-01-08", 100.0);
    insertWeekOfData("2024-01-15", 120.0);

    ResultSet rs = connection.createStatement().executeQuery("""
        SELECT
            time_bucket('1 week', time) AS week,
            avg(value) AS weekly_avg
        FROM metrics
        WHERE device_id = 'server-1'
        GROUP BY week
        ORDER BY week
    """);

    assertTrue(rs.next());
    assertEquals(100.0, rs.getDouble("weekly_avg"), 0.1);

    assertTrue(rs.next());
    double week2Avg = rs.getDouble("weekly_avg");
    assertEquals(120.0, week2Avg, 0.1);

    // Verify 20% growth
    assertEquals(20.0, ((week2Avg - 100.0) / 100.0) * 100, 0.1);
}

Continuous Aggregate Validation

Continuous aggregates pre-compute and materialize time_bucket results for fast queries:

Creating and Testing Continuous Aggregates

@Test
void shouldCreateAndQueryContinuousAggregate() throws Exception {
    createMetricsHypertable();

    // Create continuous aggregate view
    try (Statement stmt = connection.createStatement()) {
        stmt.execute("""
            CREATE MATERIALIZED VIEW hourly_metrics
            WITH (timescaledb.continuous) AS
            SELECT
                time_bucket('1 hour', time) AS bucket,
                device_id,
                metric_name,
                avg(value) AS avg_value,
                min(value) AS min_value,
                max(value) AS max_value,
                count(*) AS reading_count
            FROM metrics
            GROUP BY bucket, device_id, metric_name
            WITH NO DATA
        """);
    }

    // Insert data
    Instant now = Instant.now().truncatedTo(ChronoUnit.HOURS);
    insertReadings("device-1", "temperature", now.minus(2, ChronoUnit.HOURS), 60, 22.5);
    insertReadings("device-1", "temperature", now.minus(1, ChronoUnit.HOURS), 60, 24.0);

    // Refresh the continuous aggregate
    try (Statement stmt = connection.createStatement()) {
        stmt.execute(
            "CALL refresh_continuous_aggregate('hourly_metrics', " +
            "NOW() - INTERVAL '3 hours', NOW())"
        );
    }

    // Query the materialized view
    ResultSet rs = connection.createStatement().executeQuery("""
        SELECT bucket, avg_value, reading_count
        FROM hourly_metrics
        WHERE device_id = 'device-1' AND metric_name = 'temperature'
        ORDER BY bucket DESC
        LIMIT 2
    """);

    assertTrue(rs.next());
    assertEquals(60, rs.getInt("reading_count"));
    assertEquals(24.0, rs.getDouble("avg_value"), 0.1);

    assertTrue(rs.next());
    assertEquals(60, rs.getInt("reading_count"));
    assertEquals(22.5, rs.getDouble("avg_value"), 0.1);
}

Testing Continuous Aggregate Real-Time Updates

@Test
void shouldReflectRecentDataWithRealTimePolicy() throws Exception {
    createMetricsHypertable();

    // Create continuous aggregate with real-time enabled (default in recent versions)
    try (Statement stmt = connection.createStatement()) {
        stmt.execute("""
            CREATE MATERIALIZED VIEW realtime_hourly
            WITH (timescaledb.continuous, timescaledb.materialized_only = false) AS
            SELECT
                time_bucket('1 hour', time) AS bucket,
                device_id,
                avg(value) AS avg_value,
                count(*) AS cnt
            FROM metrics
            GROUP BY bucket, device_id
        """);
    }

    // Insert old data (will be materialized)
    Instant twoHoursAgo = Instant.now().minus(2, ChronoUnit.HOURS);
    insertReadings("device-a", "cpu", twoHoursAgo, 30, 50.0);

    // Refresh for old data
    try (Statement stmt = connection.createStatement()) {
        stmt.execute(
            "CALL refresh_continuous_aggregate('realtime_hourly', " +
            "NOW() - INTERVAL '3 hours', NOW() - INTERVAL '1 hour')"
        );
    }

    // Insert recent data (will be picked up in real-time mode without refresh)
    Instant fiveMinutesAgo = Instant.now().minus(5, ChronoUnit.MINUTES);
    insertReadings("device-a", "cpu", fiveMinutesAgo, 10, 75.0);

    // Query should return both old (materialized) and recent (real-time) data
    ResultSet rs = connection.createStatement().executeQuery("""
        SELECT bucket, avg_value, cnt
        FROM realtime_hourly
        WHERE device_id = 'device-a'
        ORDER BY bucket DESC
        LIMIT 2
    """);

    int rowCount = 0;
    while (rs.next()) rowCount++;

    // With real-time mode, recent inserts appear without explicit refresh
    assertTrue(rowCount >= 1, "Should see at least recent data in real-time mode");
}

Testing Data Compression

TimescaleDB's column compression dramatically reduces storage:

@Test
void shouldCompressOldChunks() throws Exception {
    createMetricsHypertable();

    // Enable compression
    try (Statement stmt = connection.createStatement()) {
        stmt.execute("""
            ALTER TABLE metrics SET (
                timescaledb.compress,
                timescaledb.compress_segmentby = 'device_id',
                timescaledb.compress_orderby = 'time DESC'
            )
        """);

        // Add compression policy (compress chunks older than 7 days)
        stmt.execute(
            "SELECT add_compression_policy('metrics', INTERVAL '7 days')"
        );
    }

    // Insert data from 8 days ago
    Instant eightDaysAgo = Instant.now().minus(8, ChronoUnit.DAYS);
    insertReadings("device-1", "temp", eightDaysAgo, 1000, 25.0);

    // Manually compress (policy runs on schedule; force it in tests)
    try (Statement stmt = connection.createStatement()) {
        stmt.execute(
            "SELECT compress_chunk(c) FROM show_chunks('metrics') c"
        );
    }

    // Verify chunks are compressed
    ResultSet rs = connection.createStatement().executeQuery("""
        SELECT count(*) as compressed_count
        FROM timescaledb_information.chunks
        WHERE hypertable_name = 'metrics'
        AND is_compressed = true
    """);
    rs.next();
    assertTrue(rs.getInt("compressed_count") > 0, "At least one chunk should be compressed");

    // Verify data is still queryable
    ResultSet data = connection.createStatement().executeQuery(
        "SELECT count(*) FROM metrics WHERE device_id = 'device-1'"
    );
    data.next();
    assertEquals(1000, data.getInt(1), "All data should remain queryable after compression");
}

Testing Retention Policies

@Test
void shouldExpireOldDataAccordingToRetentionPolicy() throws Exception {
    createMetricsHypertable();

    // Add retention policy: drop data older than 30 days
    try (Statement stmt = connection.createStatement()) {
        stmt.execute(
            "SELECT add_retention_policy('metrics', INTERVAL '30 days')"
        );
    }

    // Insert old data (35 days ago)
    Instant oldData = Instant.now().minus(35, ChronoUnit.DAYS);
    insertReadings("device-1", "temp", oldData, 100, 25.0);

    // Insert recent data (today)
    insertReadings("device-1", "temp", Instant.now(), 100, 25.0);

    // Manually run retention job (policy runs on schedule)
    try (Statement stmt = connection.createStatement()) {
        stmt.execute(
            "SELECT drop_chunks('metrics', TIMESTAMPTZ 'NOW() - INTERVAL 30 days')"
        );
    }

    // Old data should be gone
    ResultSet old = connection.createStatement().executeQuery(
        "SELECT count(*) FROM metrics WHERE time < NOW() - INTERVAL '30 days'"
    );
    old.next();
    assertEquals(0, old.getInt(1), "Data older than 30 days should be deleted");

    // Recent data should remain
    ResultSet recent = connection.createStatement().executeQuery(
        "SELECT count(*) FROM metrics WHERE time >= NOW() - INTERVAL '1 day'"
    );
    recent.next();
    assertEquals(100, recent.getInt(1), "Recent data should be preserved");
}

Spring Boot Integration Testing

@DataJpaTest
@Testcontainers
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class MetricsRepositoryTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> timescale = new PostgreSQLContainer<>(
        DockerImageName.parse("timescale/timescaledb:latest-pg16")
            .asCompatibleSubstituteFor("postgres")
    );

    @Autowired
    MetricsRepository metricsRepository;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @BeforeAll
    static void enableTimescale(@Autowired JdbcTemplate jdbc) {
        jdbc.execute("CREATE EXTENSION IF NOT EXISTS timescaledb");
        jdbc.execute(
            "SELECT create_hypertable('metrics', 'time', " +
            "if_not_exists => TRUE)"
        );
    }

    @Test
    void shouldFindLatestReadingPerDevice() {
        Instant now = Instant.now();
        metricsRepository.saveAll(List.of(
            new MetricReading("device-1", "cpu", 45.0, now.minus(10, ChronoUnit.MINUTES)),
            new MetricReading("device-1", "cpu", 72.0, now),
            new MetricReading("device-2", "cpu", 30.0, now)
        ));

        List<MetricReading> latest = metricsRepository.findLatestPerDevice("cpu");

        assertEquals(2, latest.size());
        MetricReading device1 = latest.stream()
            .filter(r -> r.getDeviceId().equals("device-1"))
            .findFirst()
            .orElseThrow();

        assertEquals(72.0, device1.getValue(), 0.01);
    }

    @Test
    void shouldCalculateHourlyAveragesViaNativeQuery() {
        // Use time_bucket in native query
        Instant baseTime = Instant.now().truncatedTo(ChronoUnit.HOURS);
        insertTestData(baseTime);

        List<Object[]> results = metricsRepository.findHourlyAverages(
            "device-1", "temperature",
            Timestamp.from(baseTime.minus(3, ChronoUnit.HOURS)),
            Timestamp.from(baseTime)
        );

        assertEquals(3, results.size());
    }
}

Repository with TimescaleDB Queries

@Repository
public interface MetricsRepository extends JpaRepository<MetricReading, Long> {

    @Query(value = """
        SELECT DISTINCT ON (device_id)
            device_id, metric_name, value, time
        FROM metrics
        WHERE metric_name = :metricName
        ORDER BY device_id, time DESC
        """, nativeQuery = true)
    List<MetricReading> findLatestPerDevice(@Param("metricName") String metricName);

    @Query(value = """
        SELECT
            time_bucket('1 hour', time) AS bucket,
            avg(value) AS avg_value
        FROM metrics
        WHERE device_id = :deviceId
          AND metric_name = :metricName
          AND time BETWEEN :start AND :end
        GROUP BY bucket
        ORDER BY bucket
        """, nativeQuery = true)
    List<Object[]> findHourlyAverages(
        @Param("deviceId") String deviceId,
        @Param("metricName") String metricName,
        @Param("start") Timestamp start,
        @Param("end") Timestamp end
    );
}

TimescaleDB with HelpMeTest

For time-series dashboards and monitoring UIs backed by TimescaleDB:

*** Test Cases ***
Metrics Dashboard Shows Last 24 Hours
    As    Admin
    Go To    https://app.example.com/monitoring
    Wait Until Element Is Visible    [data-testid=metrics-chart]    timeout=10s
    Select Time Range    Last 24 Hours
    Chart Data Points Should Cover    24 hours
    Check For Visual Flaws

Anomaly Detection Alert Is Triggered
    As    Admin
    Go To    https://app.example.com/alerts
    Wait Until Element Is Visible    [data-testid=alert-list]
    Inject High Value Metric    cpu_percent    99.0
    Wait Until Element Contains    [data-testid=alert-list]    High CPU    timeout=30s

HelpMeTest catches issues like empty charts from failed time-bucket queries, incorrect timezone handling in date displays, or alerts not triggering due to continuous aggregate refresh lag.

Common Pitfalls

1. Forgetting to call CREATE EXTENSION IF NOT EXISTS timescaledb TimescaleDB isn't enabled by default even with the correct Docker image. Always enable the extension in your test setup.

2. Querying continuous aggregates without refreshing Continuous aggregates only materialize data up to the watermark. For tests with recent data, call refresh_continuous_aggregate or use materialized_only = false.

3. Testing time-bucket results without timezone awareness time_bucket('1 day', time) uses UTC by default. Use time_bucket('1 day', time AT TIME ZONE 'America/New_York') if your application is timezone-aware.

4. Not testing chunk boundaries Data near chunk boundaries (midnight UTC for daily chunks) can expose partition key edge cases. Insert data straddling midnight in your tests.

Summary

TimescaleDB testing builds on standard PostgreSQL testing with time-series specific additions:

Hypertable creation tests to validate partitioning configuration
Time-bucket query tests for hourly, daily, and weekly aggregations
Continuous aggregate tests with explicit refresh and real-time mode validation
Compression tests to verify storage optimization doesn't corrupt data
Retention policy tests to ensure old data is correctly expired
Spring Data integration tests using native queries with time_bucket()

The combination of PostgreSQL compatibility and time-series-specific features makes TimescaleDB both powerful and approachable—your test suite should reflect both.