TigerData

https://www.tigerdata.com/docs
Admin
TigerData enhances PostgreSQL for real-time analytics and AI workloads, offering a faster cloud...
Tokens:709,776
Snippets:8,898
Trust Score:10
Update:5 months ago
Show doc for...
Tokens:
Raw
### Start Eon Setup Script

Source: https://www.tigerdata.com/docs/build/examples/aggregate-organizational-data-with-ai

Navigate into the cloned directory and execute the setup script to begin the Tiger Eon installation process. This script guides you through the configuration.

```bash
cd tiger-eon
./setup-tiger-eon.sh
```

--------------------------------

### Install and Start PostgreSQL with Homebrew

Source: https://www.tigerdata.com/docs/get-started/choose-your-path/install-timescaledb

These commands install PostgreSQL version 17 using Homebrew, clean up any old installations, and start the PostgreSQL service.

```bash
brew install postgresql@17
brew cleanup --prune-prefix
brew services start postgresql@17
```

--------------------------------

### Example Output of pg_available_extensions

Source: https://www.tigerdata.com/docs/deploy/self-hosted/troubleshooting

This is an example of the expected output when querying `pg_available_extensions` for `timescaledb_toolkit`, showing the default and installed versions.

```text
-[ RECORD 1 ]-----+
name              | timescaledb_toolkit
default_version   | 1.6.0
installed_version | 1.6.0
comment           | Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities
```

--------------------------------

### Test Your Connection

Source: https://www.tigerdata.com/docs/get-started/quickstart/connect-your-app

Start the Node.js application to test the basic web server setup. Navigate to http://localhost:3000 in your browser to see the 'Hello World!' message.

```bash
node index.js
```

--------------------------------

### Install timescaledb-parallel-copy

Source: https://www.tigerdata.com/docs/deploy/self-hosted/migration/schema-then-data

Install the timescaledb-parallel-copy tool using go get. This tool is recommended for faster data restoration.

```bash
go get github.com/timescale/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy
```

--------------------------------

### Enable and Start PostgreSQL (ArchLinux)

Source: https://www.tigerdata.com/docs/get-started/choose-your-path/install-timescaledb

Enable the PostgreSQL service to start on boot and then start it.

```bash
sudo systemctl enable postgresql.service
sudo systemctl start postgresql.service
```

--------------------------------

### PGRX Development Prerequisites

Source: https://www.tigerdata.com/docs/reference/pgvectorscale/testing

Before developing with PGRX, start a PostgreSQL instance with `cargo pgrx start` and install the extension using `cargo pgrx install`. These commands prepare the environment for custom development.

```bash
cd pgvectorscale && cargo pgrx start pg17
```

```bash
cargo pgrx install --features pg17
```

--------------------------------

### Run TimescaleDB Setup Script

Source: https://www.tigerdata.com/docs/get-started/choose-your-path/install-timescaledb

Executes a setup script to finalize the TimescaleDB installation. This command dynamically finds the installed version and runs the necessary configuration steps.

```bash
$(brew --prefix)/Cellar/timescaledb/$(brew list --versions timescaledb | awk '{print $2}')/bin/timescaledb_move.sh
```

--------------------------------

### Install timescaledb-tune

Source: https://www.tigerdata.com/docs/deploy/self-hosted/configuration/timescaledb-tune

Install the timescaledb-tune tool using the 'go install' command. This is an alternative if TimescaleDB was not installed from a binary release.

```bash
go install github.com/timescale/timescaledb-tune/cmd/timescaledb-tune@latest
```

--------------------------------

### Expected Extension Versions

Source: https://www.tigerdata.com/docs/build/examples/hybrid-search

This is an example of the expected output after verifying the extension installation. Actual version numbers may vary.

```text
   extname     | extversion
---------------+------------
 vector        | 0.8.0
 pg_textsearch | 1.0.0
 vectorscale   | 0.7.0
```

--------------------------------

### View installed files

Source: https://www.tigerdata.com/docs/integrate/query-administration/psql

Lists all files installed by the libpqxx package. This can be useful for troubleshooting or understanding the installation contents.

```bash
port contents libpqxx
```

--------------------------------

### Enable and Start PostgreSQL Service

Source: https://www.tigerdata.com/docs/get-started/choose-your-path/install-timescaledb

Enable the PostgreSQL service to start automatically on boot and start the service immediately.

```bash
sudo systemctl enable postgresql-18
sudo systemctl start postgresql-18
```

--------------------------------

### Install Rust and Build pgvectorscale from Source

Source: https://www.tigerdata.com/docs/reference/pgvectorscale

This snippet outlines the steps to install Rust, clone the pgvectorscale repository, install cargo-pgrx, initialize it for PostgreSQL 18, and then build and install pgvectorscale in release mode.

```bash
# install rust
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh


# download pgvectorscale
cd /tmp
git clone --branch <version> https://github.com/timescale/pgvectorscale
cd pgvectorscale/pgvectorscale
# install cargo-pgrx with the same version as pgrx
cargo install --locked cargo-pgrx --version $(cargo metadata --format-version 1 | jq -r '.packages[] | select(.name == "pgrx") | .version')
cargo pgrx init --pg18 pg_config
# build and install pgvectorscale
cargo pgrx install --release

```

--------------------------------

### Example PostgreSQL Configuration File Path

Source: https://www.tigerdata.com/docs/reference/timescaledb/configuration/tiger-postgres

An example output showing the path to the postgresql.conf file.

```text
--------------------------------------------
/home/postgres/pgdata/data/postgresql.conf
(1 row)
```

--------------------------------

### Install Tiger CLI

Source: https://www.tigerdata.com/docs/build/examples/hybrid-search

Installs the Tiger CLI tool. Run this script to get started with the command-line interface for Tiger Cloud.

```bash
curl -sL https://assets.tigerdata.com/releases/install-tiger-cli.sh | sh
```

--------------------------------

### Run Setup Script

Source: https://www.tigerdata.com/docs/build/examples/hybrid-search

Execute the setup.sql script to quickly set up extensions, tables, data, and indexes. This is a shortcut to skip manual setup steps.

```bash
psql -h localhost -U postgres -f setup.sql
```

--------------------------------

### Calculate irate_left() on Gauge Aggregates

Source: https://www.tigerdata.com/docs/reference/toolkit/counters-and-gauges/gauge_agg/irate_left

Get the instantaneous rate of change at the start of each 15-minute gauge aggregate. This example demonstrates how to use irate_left() with gauge_agg() and time_bucket().

```sql
SELECT
    id,
    bucket,
    irate_left(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t
```

--------------------------------

### Navigate and Set Up Hybrid Search Environment

Source: https://www.tigerdata.com/docs/build/examples/hybrid-search

Navigate to the hybrid search directory, set up a virtual environment, install dependencies, and configure environment variables.

```bash
cd cookbook-search/Hybrid-search
uv venv
source .venv/bin/activate   # On Windows: .venv\Scripts\activate
uv pip install -r requirements.txt
cd ..
cp .env.example .env
cd Hybrid-search
```

--------------------------------

### Get instantaneous change at the start of each 15-minute aggregate

Source: https://www.tigerdata.com/docs/reference/toolkit/counters-and-gauges/counter_agg/idelta_left

Use idelta_left() to find the change at the beginning of each time bucket. This example aggregates counter data into 15-minute intervals and then calculates the left-side delta for each interval.

```sql
SELECT
    id,
    bucket,
    idelta_left(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        counter_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t
```

--------------------------------

### Calculate Instantaneous Rate of Change with irate_left

Source: https://www.tigerdata.com/docs/reference/toolkit/counters-and-gauges/counter_agg/irate_left

This example demonstrates how to get the instantaneous rate of change at the start of each 15-minute counter aggregate using irate_left. It first aggregates counter data using counter_agg and then applies irate_left to the summary.

```sql
SELECT
    id,
    bucket,
    irate_left(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        counter_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t
```

--------------------------------

### Connect to Database with psql and verify-full

Source: https://www.tigerdata.com/docs/deploy/tiger-cloud/tiger-cloud-aws/security/strict-ssl

Example of connecting to your database using `psql` with the `sslmode=verify-full` parameter. Ensure the certificate chain is correctly stored.

```bash
psql "postgres://tsdbadmin@$SERVICE_URL_WITH_PORT/tsdb?sslmode=verify-full"
```

--------------------------------

### Example output: Forking service

Source: https://www.tigerdata.com/docs/deploy/tiger-cloud/tiger-cloud-aws/service-management/fork-services

Example output after initiating a service fork. Includes confirmation, new service ID, password save status, default service setting, and status update.

```bash
🍴 Forking service 'tgrservice' to create 'bob' at current state...
✅ Fork request accepted!
📋 New Service ID: <service_id>
🔐 Password saved to system keyring for automatic authentication
🎯 Set service '<service_id>' as default service.
⏳ Service is being forked. Use 'tiger service list' to check status.
┌───────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────┐
│     PROPERTY      │                                              VALUE                                               │
├───────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Service ID        │ <service_id>                                                                                       │
│ Name              │ bob                                                                                              │
│ Status            │                                                                                                  │
│ Type              │ TIMESCALEDB                                                                                      │
│ Region            │ eu-central-1                                                                                     │
```

--------------------------------

### Get the size of a specific index on a hypertable

Source: https://www.tigerdata.com/docs/reference/timescaledb/hypertables/hypertable_index_size

This example demonstrates how to get the disk space used by an index named 'second_index' on a hypertable.

```APIDOC
## hypertable_index_size()

### Description
Get the disk space used by an index on a hypertable, including the disk space needed to provide the index on all chunks. The size is reported in bytes.

### Syntax
```sql
SELECT hypertable_index_size('<index_name>');
```

### Arguments
#### Path Parameters
- **index_name** (REGCLASS) - Required - Name of the index on a hypertable

### Returns
#### Success Response (200)
- **hypertable_index_size** (BIGINT) - Returns the disk space used by the index in bytes.

If the function is executed on a non-hypertable relation, `NULL` is returned.

### Request Example
```sql
SELECT hypertable_index_size('second_index');
```

### Response Example
```json
{
  "hypertable_index_size": 163840
}
```

### Example with pg_size_pretty
```sql
SELECT pg_size_pretty(hypertable_index_size('second_index'));
```

### Response Example
```json
{
  "pg_size_pretty": "160 kB"
}
```
```

--------------------------------

### Run PostgreSQL Package Setup Script on Debian/Ubuntu

Source: https://www.tigerdata.com/docs/get-started/choose-your-path/install-timescaledb

Executes the script to set up the PostgreSQL APT repository.

```bash
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
```

--------------------------------

### Start Vectorizer Worker (Self-Hosted)

Source: https://www.tigerdata.com/docs/reference/pgai/vectorizer/quick-start-openai

For self-hosted installations, start the pgai vectorizer worker using Docker Compose. This process handles the embedding generation.

```bash
docker compose up -d vectorizer-worker
```

--------------------------------

### Setup and Run Python Tests

Source: https://www.tigerdata.com/docs/reference/pgvectorscale/testing

Set up the Python testing environment using `make test-python-setup`. Then, run all Python tests with `make test-python` or specific test categories using `pytest` with markers like `concurrency` or `integration`.

```bash
# Setup (creates .venv virtual environment)
make test-python-setup
```

```bash
# Run all Python tests
make test-python
```

```bash
# Run specific categories
pytest tests/ -m concurrency -v    # Multi-process concurrency tests
```

```bash
pytest tests/ -m integration -v    # Basic integration tests
```

```bash
# For PGRX development (custom port)
DB_PORT=28817 ./scripts/run-python-tests.sh
```

--------------------------------

### Create a Basic Java Main File

Source: https://www.tigerdata.com/docs/get-started/quickstart/connect-your-app

Create a simple Java file with a main method that prints 'Hello, World!'. This serves as the initial application structure.

```java
package com.timescale.java;


public class Main {


    public static void main(String... args) {
        System.out.println("Hello, World!");
    }
}
```

--------------------------------

### Configure Vectorizer with TimescaleDB Specific Start Time and Timezone

Source: https://www.tigerdata.com/docs/reference/pgai/vectorizer/api-reference

Set a specific initial start time and timezone for TimescaleDB scheduled vectorizer runs. This example schedules runs every 30 minutes, starting at a defined time in a specific timezone.

```sql
SELECT ai.create_vectorizer(
    'my_table'::regclass,
    scheduling => ai.scheduling_timescaledb(
      interval '30 minutes',
      initial_start => '2024-01-01 00:00:00'::timestamptz,
      timezone => 'America/New_York'
    ),
    -- other parameters...
);

```

--------------------------------

### Example Usage of delta()

Source: https://www.tigerdata.com/docs/reference/toolkit/counters-and-gauges/counter_agg/delta

Get the change in each counter over the entire time interval in table `foo`.

```APIDOC
```sql
SELECT
    id,
    delta(summary)
FROM (
    SELECT
        id,
        counter_agg(ts, val) AS summary
    FROM foo
    GROUP BY id
) t
```
```

--------------------------------

### Initialize PostgreSQL Instance (ArchLinux)

Source: https://www.tigerdata.com/docs/get-started/choose-your-path/install-timescaledb

Initialize a new PostgreSQL instance with specified locale, encoding, data directory, and checksums.

```bash
sudo -u postgres initdb --locale=en_US.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums
```

--------------------------------

### Example: Get the latest temperature every 5 minutes

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/last

This example demonstrates how to use the last() function to retrieve the latest temperature reading for each device within 5-minute time buckets over the past day.

```APIDOC
## Samples
Get the temperature every 5 minutes for each device over the past day:
```sql
SELECT device_id, time_bucket('5 minutes', time) AS interval,
  last(temp, time)
FROM metrics
WHERE time > now () - INTERVAL '1 day'
GROUP BY device_id, interval
ORDER BY interval DESC;
```
```

--------------------------------

### Get instantaneous rate of change with irate_right

Source: https://www.tigerdata.com/docs/reference/toolkit/counters-and-gauges/gauge_agg/irate_right

Get the instantaneous rate of change at the end of each 15-minute gauge aggregate. This example demonstrates how to use `irate_right` with `gauge_agg` to find the rate of change for time-bucketed gauge data.

```sql
SELECT
    id,
    bucket,
    irate_right(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t
```

--------------------------------

### Example output: No services found

Source: https://www.tigerdata.com/docs/deploy/tiger-cloud/tiger-cloud-aws/service-management/fork-services

Example output from 'tiger service list' when no services are found in the project. Suggests creating a new service.

```bash
🏜️  No services found! Your project is looking a bit empty.
🚀 Ready to get started? Create your first service with: tiger service create
```

--------------------------------

### Example: Create Read-Only MST Replica

Source: https://www.tigerdata.com/docs/deploy/mst/aiven-client

An example demonstrating the creation of a read-only replica with specific parameters for project, plan, cloud, and source service.

```bash
avn service create replica-fork --project fork-project\
-t pg --plan timescale-basic-100-compute-optimized\
--cloud timescale-aws-us-east-1 -c pg_read_replica=true\
-c service_to_fork_from=timescaledb -c\
pg_version=11 -c variant=timescale
```

--------------------------------

### Get columnstore statistics for a hypertable

Source: https://www.tigerdata.com/docs/reference/timescaledb/hypercore/hypertable_columnstore_stats

This example demonstrates how to retrieve the compression status and size statistics for the 'conditions' hypertable.

```APIDOC
## hypertable_columnstore_stats()

### Description
Retrieves columnstore statistics for a specified hypertable.

### Method
SQL Function

### Endpoint
N/A (SQL Function)

### Arguments
#### Path Parameters
- **hypertable** (REGCLASS) - Required - The name of the hypertable for which to retrieve statistics.

### Returns
#### Success Response
- **total_chunks** (BIGINT) - The total number of chunks for the hypertable.
- **number_compressed_chunks** (BIGINT) - The number of compressed chunks.
- **before_compression_table_bytes** (BIGINT) - Size of the heap before compression.
- **before_compression_index_bytes** (BIGINT) - Size of all indexes before compression.
- **before_compression_toast_bytes** (BIGINT) - Size of the TOAST table before compression.
- **before_compression_total_bytes** (BIGINT) - Total size of the table before compression.
- **after_compression_table_bytes** (BIGINT) - Size of the heap after compression.
- **after_compression_index_bytes** (BIGINT) - Size of all indexes after compression.
- **after_compression_toast_bytes** (BIGINT) - Size of the TOAST table after compression.
- **after_compression_total_bytes** (BIGINT) - Total size of the table after compression.
- **node_name** (NAME) - The node name for distributed hypertables.

### Request Example
```sql
SELECT * FROM hypertable_columnstore_stats('conditions');
```

### Response Example
```
-[ RECORD 1 ]------------------+------
total_chunks                   | 4
number_compressed_chunks       | 1
before_compression_table_bytes | 8192
before_compression_index_bytes | 32768
before_compression_toast_bytes | 0
before_compression_total_bytes | 40960
after_compression_table_bytes  | 8192
after_compression_index_bytes  | 32768
after_compression_toast_bytes  | 8192
after_compression_total_bytes  | 49152
node_name                      | 
```
```

--------------------------------

### Initialize PostgreSQL Instance

Source: https://www.tigerdata.com/docs/get-started/choose-your-path/install-timescaledb

Initialize the PostgreSQL data directory for the specified version.

```bash
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
```

--------------------------------

### Combine state aggregates and calculate duration in START state

Source: https://www.tigerdata.com/docs/reference/toolkit/state-tracking/state_agg/rollup

This example demonstrates how to combine state aggregates from 1-minute buckets into a single aggregate using rollup() and then calculate the duration spent in the 'START' state within those combined buckets.

```sql
WITH buckets AS (
    SELECT
        time_bucket('1 minute', ts) as dt,
        state_agg(ts, state) AS sa
    FROM states_test
    GROUP BY time_bucket('1 minute', ts))
SELECT duration_in(
    'START',
    rollup(buckets.sa)
)
FROM buckets;
```

--------------------------------

### Get Outflux Help

Source: https://www.tigerdata.com/docs/deploy/self-hosted/migration/migrate-influxdb

Run this command to see available options and usage instructions for Outflux.

```bash
./outflux --help
```

--------------------------------

### live_ranges()

Source: https://www.tigerdata.com/docs/reference/toolkit/state-tracking/heartbeat_agg/live_ranges

Get the live intervals from a heartbeat_agg. This function returns a table with start and end timestamps indicating when the system was live.

```APIDOC
## live_ranges()

### Description
Get the live intervals from a heartbeat_agg. Returns a set of (start_time, end_time) pairs representing when the underlying system was live during the interval of the aggregate.

### Arguments
- **agg** (HeartbeatAgg) - Required - A heartbeat aggregate to get the liveness data from.

### Returns
- **live_ranges** (TABLE (start TIMESTAMPTZ, end TIMESTAMPTZ)) - The (start, end) pairs of when the system was live.

### Sample Usage
```sql
SELECT live_ranges(health)
FROM liveness
WHERE date = '01-9-2022 UTC'
```

### Sample Response
```
                 live_ranges
-----------------------------------------------------
("2022-01-09 00:00:30+00","2022-01-12 15:27:22+00")
("2022-01-12 15:31:17+00","2022-01-16 00:00:00+00")
```
```

--------------------------------

### Bootstrap Build System

Source: https://www.tigerdata.com/docs/get-started/choose-your-path/install-timescaledb

Initialize the build system for TimescaleDB. Use `bootstrap` for Linux and `bootstrap.bat` for Windows.

```bash
./bootstrap
```

```bat
bootstrap.bat
```

--------------------------------

### Get Earliest Temperature by Device ID

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/first

This example demonstrates how to retrieve the earliest temperature reading for each device_id within a dataset.

```sql
SELECT device_id, first(temp, time)
FROM metrics
GROUP BY device_id;
```

--------------------------------

### Create a Simple Web Page

Source: https://www.tigerdata.com/docs/get-started/quickstart/connect-your-app

Create an index.js file to set up a basic Express server. This server listens on port 3000 and responds with 'Hello World!' to root requests.

```javascript
const express = require('express')
const app = express()
const port = 3000;


app.use(express.json());
app.get('/', (req, res) => res.send('Hello World!'))
app.listen(port, () => console.log(`Example app listening at http://localhost:${port}`))
```

--------------------------------

### dead_ranges() Function

Source: https://www.tigerdata.com/docs/reference/toolkit/state-tracking/heartbeat_agg/dead_ranges

Get the down intervals from a heartbeat_agg. This function returns a table of start and end timestamps representing periods of system downtime.

```APIDOC
## dead_ranges()

### Description
Get the down intervals from a heartbeat_agg. Returns a set of (start_time, end_time) pairs representing when the underlying system did not have a valid heartbeat during the interval of the aggregate.

### Syntax
```sql
dead_ranges(
    agg HEARTBEATAGG
) RETURNS TABLE (
    start TIMESTAMPTZ,
    end TIMESTAMPTZ
)
```

### Arguments
#### Path Parameters
- **agg** (HeartbeatAgg) - Required - A heartbeat aggregate to get the liveness data from.

### Returns
#### Success Response (TABLE)
- **start** (TIMESTAMPTZ) - The start timestamp of a downtime period.
- **end** (TIMESTAMPTZ) - The end timestamp of a downtime period.

### Sample Usage
```sql
-- Given a table called `liveness` containing weekly heartbeat aggregates in column `health` with timestamp column `date`,
-- use the following to get the intervals where the system was down during the week of Jan 9, 2022.
SELECT dead_ranges(health)
FROM liveness
WHERE date = '01-9-2022 UTC'
```

### Sample Response
```
              dead_ranges
-----------------------------------------------------
("2022-01-09 00:00:00+00","2022-01-09 00:00:30+00")
("2022-01-12 15:27:22+00","2022-01-12 15:31:17+00")
```
```

--------------------------------

### Configure PostgreSQL Exporter for Local Installation

Source: https://www.tigerdata.com/docs/integrate/observability-alerting/prometheus

Set the DATA_SOURCE_NAME environment variable to connect the PostgreSQL Exporter to your local TimescaleDB instance and start the exporter.

```bash
export DATA_SOURCE_NAME="postgres://<user>:<password>@<host>:<port>/<database>?sslmode=<sslmode>"
./postgres_exporter

```

--------------------------------

### Full Sample: Connect, Query, and Process Results

Source: https://www.tigerdata.com/docs/get-started/quickstart/connect-your-app

A complete Go program demonstrating connection to TimescaleDB, executing a query with parameters, and iterating through the results. This serves as a comprehensive example for integrating TimescaleDB into your application.

```go
package main


import (
    "context"
    "fmt"
    "os"
    "time"


    "github.com/jackc/pgx/v5/pgxpool"
)


func main() {
    ctx := context.Background()
    connStr := "yourConnectionStringHere"
    dbpool, err := pgxpool.New(ctx, connStr)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }
    defer dbpool.Close()


    /********************************************/
    /* Execute a query                          */
    /********************************************/


    // Formulate query in SQL
    // Note the use of prepared statement placeholders $1 and $2
    queryTimebucketFiveMin := `
        SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
        FROM sensor_data
        JOIN sensors ON sensors.id = sensor_data.sensor_id
        WHERE sensors.location = $1 AND sensors.type = $2
        GROUP BY five_min
        ORDER BY five_min DESC;
        `


    //Execute query on TimescaleDB
    rows, err := dbpool.Query(ctx, queryTimebucketFiveMin, "ceiling", "a")
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to execute query %v\n", err)
        os.Exit(1)
    }
    defer rows.Close()


    fmt.Println("Successfully executed query")


    //Do something with the results of query
    // Struct for results
    type result2 struct {
        Bucket time.Time
        Avg    float64
    }


    // Print rows returned and fill up results slice for later use
    var results []result2
    for rows.Next() {
        var r result2
        err = rows.Scan(&r.Bucket, &r.Avg)
        if err != nil {
            fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
            os.Exit(1)
        }
        results = append(results, r)
        fmt.Printf("Time bucket: %s | Avg: %f\n", &r.Bucket, r.Avg)
    }
    // Any errors encountered by rows.Next or rows.Scan are returned here
    if rows.Err() != nil {
        fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
        os.Exit(1)
    }
}
```

--------------------------------

### Create and migrate database

Source: https://www.tigerdata.com/docs/get-started/quickstart/connect-your-app

Use these commands to create the database for your project and run pending migrations.

```bash
rails db:create
```

```bash
rails db:migrate
```

--------------------------------

### Shifting bucket alignment with origin

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/time_bucket

This example shows how to shift the alignment of weekly buckets using the `origin` parameter, setting the start of the week to Sunday.

```APIDOC
## Shifting bucket alignment with origin

### Description
Calculates the average CPU usage, bucketing data by week and shifting the alignment to start on Sunday using the `origin` parameter.

### Method
SELECT

### Endpoint
time_bucket()

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
SELECT time_bucket('1 week', timetz, TIMESTAMPTZ '2017-12-31')
  AS one_week, avg(cpu)
FROM metrics
GROUP BY one_week
WHERE time > TIMESTAMPTZ '2017-12-01'  AND time < TIMESTAMPTZ '2018-01-03'
ORDER BY one_week DESC LIMIT 10;
```

### Response
#### Success Response (200)
- **one_week** (TIMESTAMPTZ) - The start of the weekly bucket, aligned to Sunday.
- **avg(cpu)** (NUMERIC) - The average CPU usage within the bucket.

#### Response Example
```json
[
  {
    "one_week": "2017-12-31T00:00:00Z",
    "avg_cpu": 0.80
  }
]
```
```

--------------------------------

### Get state history from a state aggregate

Source: https://www.tigerdata.com/docs/reference/toolkit/state-tracking/state_agg/state_timeline

Use this query to retrieve the state, start time, and end time for each state recorded in the state aggregate.

```sql
SELECT state, start_time, end_time
  FROM state_timeline(
    (SELECT state_agg(ts, state) FROM states_test)
  );
```

--------------------------------

### Install TimescaleDB Toolkit (Homebrew)

Source: https://www.tigerdata.com/docs/deploy/self-hosted/tooling/install-toolkit

Install the TimescaleDB Toolkit using the Homebrew package manager after tapping the correct repository and updating Homebrew.

```bash
brew install timescaledb-toolkit
```

--------------------------------

### Configure and Run Apache Kafka

Source: https://www.tigerdata.com/docs/integrate/data-engineering-etl/apache-kafka

Formats the Kafka storage and starts the Kafka server. Use the -daemon flag to run in the background.

```bash
KAFKA_CLUSTER_ID="$(bin/kafka-storage.sh random-uuid)"
./bin/kafka-storage.sh format --standalone -t $KAFKA_CLUSTER_ID -c config/kraft/reconfig-server.properties
./bin/kafka-server-start.sh config/kraft/reconfig-server.properties
```

--------------------------------

### Update local repository list (apt)

Source: https://www.tigerdata.com/docs/deploy/self-hosted/tooling/install-toolkit

Before installing or updating packages, refresh your local package list to ensure you get the latest available versions.

```bash
apt update
```

--------------------------------

### Example Aiven Project List Output

Source: https://www.tigerdata.com/docs/deploy/mst/aiven-client

This is an example output of the `avn project list` command, showing available projects and their details.

```text
 PROJECT_NAME       DEFAULT_CLOUD            CREDIT_CARD
 =============     =======================   ===================
 project-xxxx      timescale-aws-us-east-1   xxxx-xxxx-xxxx-xxxx
 project-yyyy      timescale-aws-us-east-1   xxxx-xxxx-xxxx-xxxx
 project-zzzz      timescale-aws-us-east-1   xxxx-xxxx-xxxx-xxxx

```

--------------------------------

### Example Usage of rollup()

Source: https://www.tigerdata.com/docs/reference/toolkit/state-tracking/state_agg/rollup

This sample demonstrates how to use the rollup() function to combine state aggregates from 1-minute buckets and then calculate the duration spent in the 'START' state.

```APIDOC
WITH buckets AS (
    SELECT
        time_bucket('1 minute', ts) as dt,
        state_agg(ts, state) AS sa
    FROM states_test
    GROUP BY time_bucket('1 minute', ts)
)
SELECT duration_in(
    'START',
    rollup(buckets.sa)
)
FROM buckets;
```

--------------------------------

### Reschedule job to a specific next start time

Source: https://www.tigerdata.com/docs/api/latest/jobs-automation/alter_job

This example reschedules job ID `1000` to have its next execution at a precise date and time: '2020-03-15 09:00:00.0+00'.

```APIDOC
## alter_job() - Specific Next Start

### Description
Reschedule a job to start at a specific future date and time.

### Method
SQL (TimescaleDB function)

### Endpoint
N/A (Function call)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
SELECT alter_job(1000, next_start => '2020-03-15 09:00:00.0+00');
```

### Response
#### Success Response (200)
Confirms the job has been rescheduled.

#### Response Example
(Specific response structure depends on TimescaleDB version and configuration)
```

--------------------------------

### Create Project Directory

Source: https://www.tigerdata.com/docs/build/examples/hybrid-search

Creates a new directory for the hybrid search project and navigates into it. This sets up the workspace for the tutorial.

```bash
mkdir hybrid-search-tutorial
cd hybrid-search-tutorial
```

--------------------------------

### CREATE TABLE with Hypertable Options

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_table

This example demonstrates the basic syntax for creating a hypertable with standard PostgreSQL CREATE TABLE syntax and TimescaleDB-specific WITH options.

```APIDOC
## CREATE TABLE <table_name>

### Description
Creates a new hypertable for time-series data or a standard PostgreSQL relational table.

### Method
SQL Command

### Endpoint
N/A

### Parameters
#### WITH Options
- **tsdb.hypertable** (BOOLEAN) - Required: ✖ - Description: Create a new hypertable for time-series data rather than a standard PostgreSQL relational table. Default: `true`.
- **tsdb.columnstore** (BOOLEAN) - Required: ✖ - Description: Enable or disable columnstore on the hypertable. Default: `true`.
- **tsdb.partition_column** (TEXT) - Required: ✖ - Description: Set the time column to automatically partition your time-series data by. Default: The first `TIMESTAMP` or `TIMESTAMPTZ` column in the table.
- **tsdb.chunk_interval** (TEXT) - Required: ✖ - Description: Change this to better suit your needs. For example, if you set `chunk_interval` to 1 day, each chunk stores data from the same day. Data from different days is stored in different chunks. Default: `7 days`.
- **tsdb.create_default_indexes** (BOOLEAN) - Required: ✖ - Description: Set to `false` to not automatically create indexes. Default: `true`.
- **tsdb.associated_schema** (TEXT) - Required: ✖ - Description: Set the schema name for internal hypertable tables. Default: `_timescaledb_internal`.
- **tsdb.associated_table_prefix** (TEXT) - Required: ✖ - Description: Set the prefix for the names of internal hypertable chunks. Default: `_hyper`.
- **tsdb.orderby** (TEXT) - Required: ✖ - Description: The order in which items are used in the columnstore. Specified in the same way as an `ORDER BY` clause in a `SELECT` query. Default: Descending order on the time column in `table_name`.
- **tsdb.segmentby** (TEXT) - Required: ✖ - Description: Set the list of columns used to segment data in the columnstore for `table`. Default: TimescaleDB evaluates `pg_stats` to determine an appropriate column.
- **tsdb.sparse_index** (TEXT) - Required: ✖ - Description: Configure the sparse indexes for compressed chunks. Requires setting `tsdb.orderby`. Supported index types are `bloom(<column>, …)` for equality filters and `minmax(<column>)` for range filters; use a comma-separated list to set multiple. Set to an empty string to disable sparse indexes. Default: TimescaleDB evaluates existing indexes and data types to create sparse indexes.

### Request Example
```sql
CREATE TABLE conditions (
    time        timestamptz PRIMARY KEY,
    device      integer NOT NULL,
    condition   text
);

SELECT create_hypertable('conditions', 'time', 'device');
```

### Response
#### Success Response (Command Tag)
- **CREATE TABLE**: Command completed successfully.

#### Error Response
- **partition column could not be determined**: No timestamp column found for automatic partitioning. Use `tsdb.partition_column` to specify the partitioning column.
- **column "<name>" does not exist**: The specified partition column does not exist in the table.
- **timescaledb options requires hypertable option**: TimescaleDB options used without setting `tsdb.hypertable=true`.
- **invalid input syntax for type <type>**: Invalid value for `tsdb.chunk_interval` for the partition column type.
- **invalid value for tsdb.create_default_indexes '<value>'**: Value for `tsdb.create_default_indexes` must be a boolean.
- **unrecognized parameter "<param>"**: Invalid TimescaleDB parameter specified.
- **functionality not supported under the current "apache" license**: Feature requires a TimescaleDB license with additional capabilities.
```

--------------------------------

### Combine State Aggregates and Calculate Duration

Source: https://www.tigerdata.com/docs/reference/toolkit/state-tracking/compact_state_agg/rollup

This example demonstrates combining state aggregates from minute buckets into a single aggregate and then calculating the duration spent in the 'START' state.

```sql
WITH buckets AS (SELECT
    time_bucket('1 minute', ts) as dt,
    toolkit_experimental.compact_state_agg(ts, state) AS sa
FROM states_test
GROUP BY time_bucket('1 minute', ts))
SELECT toolkit_experimental.duration_in(
    'START',
    toolkit_experimental.rollup(buckets.sa)
)
FROM buckets;
```

--------------------------------

### Create a job with configuration parameters

Source: https://www.tigerdata.com/docs/reference/timescaledb/jobs-automation

This example shows how to create a job that accepts configuration parameters via JSONB, allowing for dynamic behavior.

```APIDOC
## Create a job with configuration parameters

This example shows how to create a job that accepts configuration parameters via JSONB, allowing for dynamic behavior.

### SQL Procedure Definition

```sql
CREATE OR REPLACE PROCEDURE aggregate_metrics(job_id int, config jsonb)
LANGUAGE PLPGSQL AS
$$
DECLARE
  threshold int := config->>'threshold';
BEGIN
  INSERT INTO daily_summary
  SELECT time_bucket('1 day', time), location, AVG(value)
  FROM metrics
  WHERE value > threshold
  GROUP BY 1, 2;
END
$$;
```

### Scheduling the Job with Configuration

```sql
SELECT add_job(
  'aggregate_metrics',
  '1 day',
  config => '{"threshold": 100}'
);
```
```

--------------------------------

### Test authenticated connection to Tiger REST API

Source: https://www.tigerdata.com/docs/get-started/quickstart/cli-rest-api

Use curl to send a GET request to the services endpoint, authenticating with your access and secret keys. This verifies your setup.

```bash
curl -X GET "${API_BASE_URL}/projects/${TIGERDATA_PROJECT_ID}/services" \
   -u "${TIGERDATA_ACCESS_KEY}:${TIGERDATA_SECRET_KEY}" \
   -H "Content-Type: application/json"
```

--------------------------------

### Download and Install timescaledb-backfill

Source: https://www.tigerdata.com/docs/migrate/dual-write-and-backfill/timescaledb-backfill

Download the timescaledb-backfill binary and install it to your system's PATH. This tool is best executed in an instance located close to the target database, ideally in the same region.

```bash
wget https://assets.timescale.com/releases/timescaledb-backfill-x86_64-linux.tar.gz
tar xf timescaledb-backfill-x86_64-linux.tar.gz
sudo mv timescaledb-backfill /usr/local/bin/
```

--------------------------------

### Get Azure AD Tenant ID

Source: https://www.tigerdata.com/docs/deploy/mst/vpc-peering/vpc-peering-azure

Retrieve your Azure AD tenant ID, which is required for authentication during the peering setup. Make note of the 'tenantId' field from the output.

```bash
az account list
```

--------------------------------

### Install psql with Homebrew

Source: https://www.tigerdata.com/docs/integrate/query-administration/psql

Installs the libpq library, which includes the psql client, using the Homebrew package manager on macOS.

```bash
brew install libpq
```

--------------------------------

### Start a Service using TypeScript

Source: https://www.tigerdata.com/docs/reference/tiger-cloud-rest/typescript/resources/projects/subresources/services/methods/start

Initiates a service for a specified project. Ensure you have the Tiger Cloud SDK installed and your API key configured. The `project_id` parameter in the options is required.

```typescript
import TigerCloud from 'tiger-cloud';

const client = new TigerCloud({
  apiKey: process.env['TIGER_CLOUD_API_KEY'], // This is the default and can be omitted
});

const service = await client.projects.services.start('d1k5vk7hf2', { project_id: 'rp1pz7uyae' });

console.log(service.project_id);
```

--------------------------------

### Insert sample data into products table

Source: https://www.tigerdata.com/docs/deploy/tiger-cloud/tiger-cloud-aws/tiger-cloud-extensions/pg-textsearch

Populate the products table with sample records to demonstrate full-text search capabilities. This data will be indexed.

```sql
INSERT INTO products (name, description, category, price) VALUES
('Mechanical Keyboard', 'Durable mechanical switches with RGB backlighting for gaming and productivity', 'Electronics', 149.99),
('Ergonomic Mouse', 'Wireless mouse with ergonomic design to reduce wrist strain during long work sessions', 'Electronics', 79.99),
('Standing Desk', 'Adjustable height desk for better posture and productivity throughout the workday', 'Furniture', 599.99);
```

--------------------------------

### Enable Connection Pooler TypeScript Example

Source: https://www.tigerdata.com/docs/reference/tiger-cloud-rest/typescript/resources/projects/subresources/services/methods/enable_pooler

Use this TypeScript code to enable the connection pooler for a service. Ensure you have the Tiger Cloud SDK installed and your API key configured.

```typescript
import TigerCloud from 'tiger-cloud';

const client = new TigerCloud({
  apiKey: process.env['TIGER_CLOUD_API_KEY'], // This is the default and can be omitted
});

const response = await client.projects.services.enablePooler('d1k5vk7hf2', {
  project_id: 'rp1pz7uyae',
});

console.log(response.message);
```

```json
{
  "message": "Action completed successfully."
}
```

--------------------------------

### Get human-readable size of a specific index on a hypertable

Source: https://www.tigerdata.com/docs/api/latest/hypertable/hypertable_index_size

This example shows how to use `pg_size_pretty()` in conjunction with `hypertable_index_size()` to display the index size in a human-readable format (e.g., KB, MB).

```sql
SELECT pg_size_pretty(hypertable_index_size('second_index'));
```

--------------------------------

### Install TimescaleDB Toolkit

Source: https://www.tigerdata.com/docs/deploy/mst/create-mst-service

Run this SQL command to install the TimescaleDB Toolkit extension on your database.

```sql
CREATE EXTENSION timescaledb_toolkit;
```

--------------------------------

### Calculate Gauge Change Over Time

Source: https://www.tigerdata.com/docs/reference/toolkit/counters-and-gauges/gauge_agg/delta

Get the change in each gauge over the entire time interval in table 'foo'. This example demonstrates how to use delta() with gauge_agg() to find the net change for each ID.

```sql
SELECT
    id,
    delta(summary)
FROM (
    SELECT
        id,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id
) t
```

--------------------------------

### Create and connect to a new database

Source: https://www.tigerdata.com/docs/deploy/mst/ingest-data

Create a new database for your data and then connect to it using `psql`.

```sql
CREATE DATABASE new_db;
\c new_db;
```
Tokens:
Raw