===== PAGE: https://docs.tigerdata.com/getting-started/try-key-features-timescale-products/ ===== # Try the key features in Tiger Data products Tiger Cloud offers managed database services that provide a stable and reliable environment for your applications. Each Tiger Cloud service is a single optimised Postgres instance extended with innovations such as TimescaleDB in the database engine, in a cloud infrastructure that delivers speed without sacrifice. A radically faster Postgres for transactional, analytical, and agentic workloads at scale. Tiger Cloud scales Postgres to ingest and query vast amounts of live data. Tiger Cloud provides a range of features and optimizations that supercharge your queries while keeping the costs down. For example: * The hypercore row-columnar engine in TimescaleDB makes queries up to 350x faster, ingests 44% faster, and reduces storage by 90%. * Tiered storage in Tiger Cloud seamlessly moves your data from high performance storage for frequently accessed data to low cost bottomless storage for rarely accessed data. The following figure shows how TimescaleDB optimizes your data for superfast real-time analytics: ![Main features and tiered data](https://assets.timescale.com/docs/images/mutation.png ) This page shows you how to rapidly implement the features in Tiger Cloud that enable you to ingest and query data faster while keeping the costs low. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. ## Optimize time-series data in hypertables with hypercore Time-series data represents the way a system, process, or behavior changes over time. Hypertables are Postgres tables that help you improve insert and query performance by automatically partitioning your data by time. Each hypertable is made up of child tables called chunks. Each chunk is assigned a range of time, and only contains data from that range. When you run a query, TimescaleDB identifies the correct chunk and runs the query on it, instead of going through the entire table. You can also tune hypertables to increase performance even more. ![Hypertable structure](https://assets.timescale.com/docs/images/hypertable-structure.png) [Hypercore][hypercore] is the hybrid row-columnar storage engine in TimescaleDB used by hypertables. Traditional databases force a trade-off between fast inserts (row-based storage) and efficient analytics (columnar storage). Hypercore eliminates this trade-off, allowing real-time analytics without sacrificing transactional capabilities. Hypercore dynamically stores data in the most efficient format for its lifecycle: * **Row-based storage for recent data**: the most recent chunk (and possibly more) is always stored in the rowstore, ensuring fast inserts, updates, and low-latency single record queries. Additionally, row-based storage is used as a writethrough for inserts and updates to columnar storage. * **Columnar storage for analytical performance**: chunks are automatically compressed into the columnstore, optimizing storage efficiency and accelerating analytical queries. Unlike traditional columnar databases, hypercore allows data to be inserted or modified at any stage, making it a flexible solution for both high-ingest transactional workloads and real-time analytics—within a single database. Hypertables exist alongside regular Postgres tables. You use regular Postgres tables for relational data, and interact with hypertables and regular Postgres tables in the same way. This section shows you how to create regular tables and hypertables, and import relational and time-series data from external files. 1. **Import some time-series data into hypertables** 1. Unzip [crypto_sample.zip](https://assets.timescale.com/docs/downloads/candlestick/crypto_sample.zip) to a ``. This test dataset contains: - Second-by-second data for the most-traded crypto-assets. This time-series data is best suited for optimization in a [hypertable][hypertables-section]. - A list of asset symbols and company names. This is best suited for a regular relational table. To import up to 100 GB of data directly from your current Postgres-based database, [migrate with downtime][migrate-with-downtime] using native Postgres tooling. To seamlessly import 100GB-10TB+ of data, use the [live migration][migrate-live] tooling supplied by Tiger Data. To add data from non-Postgres data sources, see [Import and ingest data][data-ingest]. 1. Upload data into a hypertable: To more fully understand how to create a hypertable, how hypertables work, and how to optimize them for performance by tuning chunk intervals and enabling chunk skipping, see [the hypertables documentation][hypertables-section]. The Tiger Cloud Console data upload creates hypertables and relational tables from the data you are uploading: 1. In [Tiger Cloud Console][portal-ops-mode], select the service to add data to, then click `Actions` > `Import data` > `Upload .CSV`. 1. Click to browse, or drag and drop `/tutorial_sample_tick.csv` to upload. 1. Leave the default settings for the delimiter, skipping the header, and creating a new table. 1. In `Table`, provide `crypto_ticks` as the new table name. 1. Enable `hypertable partition` for the `time` column and click `Process CSV file`. The upload wizard creates a hypertable containing the data from the CSV file. 1. When the data is uploaded, close `Upload .CSV`. If you want to have a quick look at your data, press `Run` . 1. Repeat the process with `/tutorial_sample_assets.csv` and rename to `crypto_assets`. There is no time-series data in this table, so you don't see the `hypertable partition` option. 1. In Terminal, navigate to `` and connect to your service. ```bash psql -d "postgres://:@:/" ``` You use your [connection details][connection-info] to fill in this Postgres connection string. 2. Create tables for the data to import: - For the time-series data: 1. In your sql client, create a hypertable: Create a [hypertable][hypertables-section] for your time-series data using [CREATE TABLE][hypertable-create-table]. For [efficient queries][secondary-indexes], remember to `segmentby` the column you will use most often to filter your data. For example: ```sql CREATE TABLE crypto_ticks ( "time" TIMESTAMPTZ, symbol TEXT, price DOUBLE PRECISION, day_volume NUMERIC ) WITH ( tsdb.hypertable, tsdb.partition_column='time', tsdb.segmentby = 'symbol' ); ``` If you are self-hosting TimescaleDB v2.19.3 and below, create a [Postgres relational table][pg-create-table], then convert it using [create_hypertable][create_hypertable]. You then enable hypercore with a call to [ALTER TABLE][alter_table_hypercore]. - For the relational data: In your sql client, create a normal Postgres table: ```sql CREATE TABLE crypto_assets ( symbol TEXT NOT NULL, name TEXT NOT NULL ); ``` 1. Speed up data ingestion: When you set `timescaledb.enable_direct_compress_copy` your data gets compressed in memory during ingestion with `COPY` statements. By writing the compressed batches immediately in the columnstore, the IO footprint is significantly lower. Also, the [columnstore policy][add_columnstore_policy] you set is less important, `INSERT` already produces compressed chunks. Please note that this feature is a **tech preview** and not production-ready. Using this feature could lead to regressed query performance and/or storage ratio, if the ingested batches are not correctly ordered or are of too high cardinality. To enable in-memory data compression during ingestion: ```sql SET timescaledb.enable_direct_compress_copy=on; ``` **Important facts** - High cardinality use cases do not produce good batches and lead to degreaded query performance. - The columnstore is optimized to store 1000 records per batch, which is the optimal format for ingestion per segment by. - WAL records are written for the compressed batches rather than the individual tuples. - Currently only `COPY` is support, `INSERT` will eventually follow. - Best results are achieved for batch ingestion with 1000 records or more, upper boundary is 10.000 records. - Continous Aggregates are **not** supported at the moment. 3. Upload the dataset to your service: ```sql \COPY crypto_ticks from './tutorial_sample_tick.csv' DELIMITER ',' CSV HEADER; ``` ```sql \COPY crypto_assets from './tutorial_sample_assets.csv' DELIMITER ',' CSV HEADER; ``` 1. **Have a quick look at your data** You query hypertables in exactly the same way as you would a relational Postgres table. Use one of the following SQL editors to run a query and see the data you uploaded: - **Data mode**: write queries, visualize data, and share your results in [Tiger Cloud Console][portal-data-mode] for all your Tiger Cloud services. This feature is not available under the Free pricing plan. - **SQL editor**: write, fix, and organize SQL faster and more accurately in [Tiger Cloud Console][portal-ops-mode] for a Tiger Cloud service. - **psql**: easily run queries on your Tiger Cloud services or self-hosted TimescaleDB deployment from Terminal. ## Enhance query performance for analytics Hypercore is the TimescaleDB hybrid row-columnar storage engine, designed specifically for real-time analytics and powered by time-series data. The advantage of hypercore is its ability to seamlessly switch between row-oriented and column-oriented storage. This flexibility enables TimescaleDB to deliver the best of both worlds, solving the key challenges in real-time analytics. ![Move from rowstore to columstore in hypercore](https://assets.timescale.com/docs/images/hypercore.png ) When TimescaleDB converts chunks from the rowstore to the columnstore, multiple records are grouped into a single row. The columns of this row hold an array-like structure that stores all the data. Because a single row takes up less disk space, you can reduce your chunk size by up to 98%, and can also speed up your queries. This helps you save on storage costs, and keeps your queries operating at lightning speed. hypercore is enabled by default when you call [CREATE TABLE][hypertable-create-table]. Best practice is to compress data that is no longer needed for highest performance queries, but is still accessed regularly in the columnstore. For example, yesterday's market data. 1. **Add a policy to convert chunks to the columnstore at a specific time interval** For example, yesterday's data: ``` sql CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '1d'); ``` If you have not configured a `segmentby` column, TimescaleDB chooses one for you based on the data in your hypertable. For more information on how to tune your hypertables for the best performance, see [efficient queries][secondary-indexes]. 1. **View your data space saving** When you convert data to the columnstore, as well as being optimized for analytics, it is compressed by more than 90%. This helps you save on storage costs and keeps your queries operating at lightning speed. To see the amount of space saved, click `Explorer` > `public` > `crypto_ticks`. ![Columnstore data savings](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-columstore-data-savings.png ) ## Write fast and efficient analytical queries Aggregation is a way of combing data to get insights from it. Average, sum, and count are all examples of simple aggregates. However, with large amounts of data, aggregation slows things down, quickly. Continuous aggregates are a kind of hypertable that is refreshed automatically in the background as new data is added, or old data is modified. Changes to your dataset are tracked, and the hypertable behind the continuous aggregate is automatically updated in the background. ![Reduced data calls with continuous aggregates](https://assets.timescale.com/docs/images/continuous-aggregate.png) You create continuous aggregates on uncompressed data in high-performance storage. They continue to work on [data in the columnstore][test-drive-enable-compression] and [rarely accessed data in tiered storage][test-drive-tiered-storage]. You can even create [continuous aggregates on top of your continuous aggregates][hierarchical-caggs]. You use time buckets to create a continuous aggregate. Time buckets aggregate data in hypertables by time interval. For example, a 5-minute, 1-hour, or 3-day bucket. The data grouped in a time bucket uses a single timestamp. Continuous aggregates minimize the number of records that you need to look up to perform your query. This section shows you how to run fast analytical queries using time buckets and continuous aggregate in Tiger Cloud Console. You can also do this using psql. This feature is not available under the Free pricing plan. 1. **Connect to your service** In [Tiger Cloud Console][portal-data-mode], select your service in the connection drop-down in the top right. 1. **Create a continuous aggregate** For a continuous aggregate, data grouped using a time bucket is stored in a Postgres `MATERIALIZED VIEW` in a hypertable. `timescaledb.continuous` ensures that this data is always up to date. In data mode, use the following code to create a continuous aggregate on the real-time data in the `crypto_ticks` table: ```sql CREATE MATERIALIZED VIEW assets_candlestick_daily WITH (timescaledb.continuous) AS SELECT time_bucket('1 day', "time") AS day, symbol, max(price) AS high, first(price, time) AS open, last(price, time) AS close, min(price) AS low FROM crypto_ticks srt GROUP BY day, symbol; ``` This continuous aggregate creates the [candlestick chart][charts] data you use to visualize the price change of an asset. 1. **Create a policy to refresh the view every hour** ```sql SELECT add_continuous_aggregate_policy('assets_candlestick_daily', start_offset => INTERVAL '3 weeks', end_offset => INTERVAL '24 hours', schedule_interval => INTERVAL '3 hours'); ``` 1. **Have a quick look at your data** You query continuous aggregates exactly the same way as your other tables. To query the `assets_candlestick_daily` continuous aggregate for all assets: 1. **In [Tiger Cloud Console][portal-ops-mode], select the service you uploaded data to** 1. **Click `Explorer` > `Continuous Aggregates` > `Create a Continuous Aggregate` next to the `crypto_ticks` hypertable** 1. **Create a view called `assets_candlestick_daily` on the `time` column with an interval of `1 day`, then click `Next step`** ![continuous aggregate wizard](https://assets.timescale.com/docs/images/tiger-cloud-console/continuous-aggregate-wizard-tiger-console.png ) 1. **Update the view SQL with the following functions, then click `Run`** ```sql CREATE MATERIALIZED VIEW assets_candlestick_daily WITH (timescaledb.continuous) AS SELECT time_bucket('1 day', "time") AS bucket, symbol, max(price) AS high, first(price, time) AS open, last(price, time) AS close, min(price) AS low FROM "public"."crypto_ticks" srt GROUP BY bucket, symbol; ``` 1. **When the view is created, click `Next step`** 1. **Define a refresh policy with the following values:** - `How far back do you want to materialize?`: `3 weeks` - `What recent data to exclude?`: `24 hours` - `How often do you want the job to run?`: `3 hours` 1. **Click `Next step`, then click `Run`** Tiger Cloud creates the continuous aggregate and displays the aggregate ID in Tiger Cloud Console. Click `DONE` to close the wizard. To see the change in terms of query time and data returned between a regular query and a continuous aggregate, run the query part of the continuous aggregate ( `SELECT ...GROUP BY day, symbol;` ) and compare the results. ## Slash storage charges In the previous sections, you used continuous aggregates to make fast analytical queries, and hypercore to reduce storage costs on frequently accessed data. To reduce storage costs even more, you create tiering policies to move rarely accessed data to the object store. The object store is low-cost bottomless data storage built on Amazon S3. However, no matter the tier, you can [query your data when you need][querying-tiered-data]. Tiger Cloud seamlessly accesses the correct storage tier and generates the response. ![Tiered storage](https://assets.timescale.com/docs/images/tiered-storage.png ) To set up data tiering: 1. **Enable data tiering** 1. In [Tiger Cloud Console][portal-ops-mode], select the service to modify. 1. In `Explorer`, click `Storage configuration` > `Tiering storage`, then click `Enable tiered storage`. ![Enable tiered storage](https://assets.timescale.com/docs/images/tiger-cloud-console/enable-tiered-storage-tiger-console.png) When tiered storage is enabled, you see the amount of data in the tiered object storage. 1. **Set the time interval when data is tiered** In Tiger Cloud Console, click `Data` to switch to the data mode, then enable data tiering on a hypertable with the following query: ```sql SELECT add_tiering_policy('assets_candlestick_daily', INTERVAL '3 weeks'); ``` 1. **Query tiered data** You enable reads from tiered data for each query, for a session or for all future sessions. To run a single query on tiered data: 1. Enable reads on tiered data: ```sql set timescaledb.enable_tiered_reads = true ``` 1. Query the data: ```sql SELECT * FROM crypto_ticks srt LIMIT 10 ``` 1. Disable reads on tiered data: ```sql set timescaledb.enable_tiered_reads = false; ``` For more information, see [Querying tiered data][querying-tiered-data]. ## Reduce the risk of downtime and data loss By default, all Tiger Cloud services have rapid recovery enabled. However, if your app has very low tolerance for downtime, Tiger Cloud offers high-availability replicas. HA replicas are exact, up-to-date copies of your database hosted in multiple AWS availability zones (AZ) within the same region as your primary node. HA replicas automatically take over operations if the original primary data node becomes unavailable. The primary node streams its write-ahead log (WAL) to the replicas to minimize the chances of data loss during failover. 1. In [Tiger Cloud Console][cloud-login], select the service to enable replication for. 1. Click `Operations`, then select `High availability`. 1. Choose your replication strategy, then click `Change configuration`. ![Tiger Cloud service replicas](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-ha-replicas.png) 1. In `Change high availability configuration`, click `Change config`. For more information, see [High availability][high-availability]. What next? See the [use case tutorials][tutorials], interact with the data in your Tiger Cloud service using [your favorite programming language][connect-with-code], integrate your Tiger Cloud service with a range of [third-party tools][integrations], plain old [Use Tiger Data products][use-timescale], or dive into [the API][use-the-api]. ===== PAGE: https://docs.tigerdata.com/getting-started/start-coding-with-timescale/ ===== # Start coding with Tiger Data Easily integrate your app with Tiger Cloud or self-hosted TimescaleDB. Use your favorite programming language to connect to your Tiger Cloud service, create and manage hypertables, then ingest and query data. # "Quick Start: Ruby and TimescaleDB" ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. * Install [Rails][rails-guide]. ## Connect a Rails app to your service Every Tiger Cloud service is a 100% Postgres database hosted in Tiger Cloud with Tiger Data extensions such as TimescaleDB. You connect to your Tiger Cloud service from a standard Rails app configured for Postgres. 1. **Create a new Rails app configured for Postgres** Rails creates and bundles your app, then installs the standard Postgres Gems. ```bash rails new my_app -d=postgresql cd my_app ``` 1. **Install the TimescaleDB gem** 1. Open `Gemfile`, add the following line, then save your changes: ```ruby gem 'timescaledb' ``` 1. In Terminal, run the following command: ```bash bundle install ``` 1. **Connect your app to your Tiger Cloud service** 1. In `/config/database.yml` update the configuration to read securely connect to your Tiger Cloud service by adding `url: <%= ENV['DATABASE_URL'] %>` to the default configuration: ```yaml default: &default adapter: postgresql encoding: unicode pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> url: <%= ENV['DATABASE_URL'] %> ``` 1. Set the environment variable for `DATABASE_URL` to the value of `Service URL` from your [connection details][connection-info] ```bash export DATABASE_URL="value of Service URL" ``` 1. Create the database: - **Tiger Cloud**: nothing to do. The database is part of your Tiger Cloud service. - **Self-hosted TimescaleDB**, create the database for the project: ```bash rails db:create ``` 1. Run migrations: ```bash rails db:migrate ``` 1. Verify the connection from your app to your Tiger Cloud service: ```bash echo "\dx" | rails dbconsole ``` The result shows the list of extensions in your Tiger Cloud service | Name | Version | Schema | Description | | -- | -- | -- | -- | | pg_buffercache | 1.5 | public | examine the shared buffer cache| | pg_stat_statements | 1.11 | public | track planning and execution statistics of all SQL statements executed| | plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language| | postgres_fdw | 1.1 | public | foreign-data wrapper for remote Postgres servers| | timescaledb | 2.18.1 | public | Enables scalable inserts and complex queries for time-series data (Community Edition)| | timescaledb_toolkit | 1.19.0 | public | Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities| ## Optimize time-series data in hypertables Hypertables are Postgres tables designed to simplify and accelerate data analysis. Anything you can do with regular Postgres tables, you can do with hypertables - but much faster and more conveniently. In this section, you use the helpers in the TimescaleDB gem to create and manage a [hypertable][about-hypertables]. 1. **Generate a migration to create the page loads table** ```bash rails generate migration create_page_loads ``` This creates the `/db/migrate/_create_page_loads.rb` migration file. 1. **Add hypertable options** Replace the contents of `/db/migrate/_create_page_loads.rb` with the following: ```ruby class CreatePageLoads < ActiveRecord::Migration[8.0] def change hypertable_options = { time_column: 'created_at', chunk_time_interval: '1 day', compress_segmentby: 'path', compress_orderby: 'created_at', compress_after: '7 days', drop_after: '30 days' } create_table :page_loads, id: false, primary_key: [:created_at, :user_agent, :path], hypertable: hypertable_options do |t| t.timestamptz :created_at, null: false t.string :user_agent t.string :path t.float :performance end end end ``` The `id` column is not included in the table. This is because TimescaleDB requires that any `UNIQUE` or `PRIMARY KEY` indexes on the table include all partitioning columns. In this case, this is the time column. A new Rails model includes a `PRIMARY KEY` index for id by default: either remove the column or make sure that the index includes time as part of a "composite key." For more information, check the Roby docs around [composite primary keys][rails-compostite-primary-keys]. 1. **Create a `PageLoad` model** Create a new file called `/app/models/page_load.rb` and add the following code: ```ruby class PageLoad < ApplicationRecord extend Timescaledb::ActsAsHypertable include Timescaledb::ContinuousAggregatesHelper acts_as_hypertable time_column: "created_at", segment_by: "path", value_column: "performance" scope :chrome_users, -> { where("user_agent LIKE ?", "%Chrome%") } scope :firefox_users, -> { where("user_agent LIKE ?", "%Firefox%") } scope :safari_users, -> { where("user_agent LIKE ?", "%Safari%") } scope :performance_stats, -> { select("stats_agg(#{value_column}) as stats_agg") } scope :slow_requests, -> { where("performance > ?", 1.0) } scope :fast_requests, -> { where("performance < ?", 0.1) } continuous_aggregates scopes: [:performance_stats], timeframes: [:minute, :hour, :day], refresh_policy: { minute: { start_offset: '3 minute', end_offset: '1 minute', schedule_interval: '1 minute' }, hour: { start_offset: '3 hours', end_offset: '1 hour', schedule_interval: '1 minute' }, day: { start_offset: '3 day', end_offset: '1 day', schedule_interval: '1 minute' } } end ``` 1. **Run the migration** ```bash rails db:migrate ``` ## Insert data your service The TimescaleDB gem provides efficient ways to insert data into hypertables. This section shows you how to ingest test data into your hypertable. 1. **Create a controller to handle page loads** Create a new file called `/app/controllers/application_controller.rb` and add the following code: ```ruby class ApplicationController < ActionController::Base around_action :track_page_load private def track_page_load start_time = Time.current yield end_time = Time.current PageLoad.create( path: request.path, user_agent: request.user_agent, performance: (end_time - start_time) ) end end ``` 1. **Generate some test data** Use `bin/console` to join a Rails console session and run the following code to define some random page load access data: ```ruby def generate_sample_page_loads(total: 1000) time = 1.month.ago paths = %w[/ /about /contact /products /blog] browsers = [ "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.114 Safari/537.36", "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0", "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Safari/605.1.15" ] total.times.map do time = time + rand(60).seconds { path: paths.sample, user_agent: browsers.sample, performance: rand(0.1..2.0), created_at: time, updated_at: time } end end ``` 1. **Insert the generated data into your Tiger Cloud service** ```bash PageLoad.insert_all(generate_sample_page_loads, returning: false) ``` 1. **Validate the test data in your Tiger Cloud service** ```bash PageLoad.count PageLoad.first ``` ## Reference This section lists the most common tasks you might perform with the TimescaleDB gem. ### Query scopes The TimescaleDB gem provides several convenient scopes for querying your time-series data. - Built-in time-based scopes: ```ruby PageLoad.last_hour.count PageLoad.today.count PageLoad.this_week.count PageLoad.this_month.count ``` - Browser-specific scopes: ```ruby PageLoad.chrome_users.last_hour.count PageLoad.firefox_users.last_hour.count PageLoad.safari_users.last_hour.count PageLoad.slow_requests.last_hour.count PageLoad.fast_requests.last_hour.count ``` - Query continuous aggregates: This query fetches the average and standard deviation from the performance stats for the `/products` path over the last day. ```ruby PageLoad::PerformanceStatsPerMinute.last_hour PageLoad::PerformanceStatsPerHour.last_day PageLoad::PerformanceStatsPerDay.last_month stats = PageLoad::PerformanceStatsPerHour.last_day.where(path: '/products').select("average(stats_agg) as average, stddev(stats_agg) as stddev").first puts "Average: #{stats.average}" puts "Standard Deviation: #{stats.stddev}" ``` ### TimescaleDB features The TimescaleDB gem provides utility methods to access hypertable and chunk information. Every model that uses the `acts_as_hypertable` method has access to these methods. #### Access hypertable and chunk information - View chunk or hypertable information: ```ruby PageLoad.chunks.count PageLoad.hypertable.detailed_size ``` - Compress/Decompress chunks: ```ruby PageLoad.chunks.uncompressed.first.compress! # Compress the first uncompressed chunk PageLoad.chunks.compressed.first.decompress! # Decompress the oldest chunk PageLoad.hypertable.compression_stats # View compression stats ``` #### Access hypertable stats You collect hypertable stats using methods that provide insights into your hypertable's structure, size, and compression status: - Get basic hypertable information: ```ruby hypertable = PageLoad.hypertable hypertable.hypertable_name # The name of your hypertable hypertable.schema_name # The schema where the hypertable is located ``` - Get detailed size information: ```ruby hypertable.detailed_size # Get detailed size information for the hypertable hypertable.compression_stats # Get compression statistics hypertable.chunks_detailed_size # Get chunk information hypertable.approximate_row_count # Get approximate row count hypertable.dimensions.map(&:column_name) # Get dimension information hypertable.continuous_aggregates.map(&:view_name) # Get continuous aggregate view names ``` #### Continuous aggregates The `continuous_aggregates` method generates a class for each continuous aggregate. - Get all the continuous aggregate classes: ```ruby PageLoad.descendants # Get all continuous aggregate classes ``` - Manually refresh a continuous aggregate: ```ruby PageLoad.refresh_aggregates ``` - Create or drop a continuous aggregate: Create or drop all the continuous aggregates in the proper order to build them hierarchically. See more about how it works in this [blog post][ruby-blog-post]. ```ruby PageLoad.create_continuous_aggregates PageLoad.drop_continuous_aggregates ``` ## Next steps Now that you have integrated the ruby gem into your app: * Learn more about the [TimescaleDB gem](https://github.com/timescale/timescaledb-ruby). * Check out the [official docs](https://timescale.github.io/timescaledb-ruby/). * Follow the [LTTB][LTTB], [Open AI long-term storage][open-ai-tutorial], and [candlesticks][candlesticks] tutorials. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. * Install the `psycopg2` library. For more information, see the [psycopg2 documentation][psycopg2-docs]. * Create a [Python virtual environment][virtual-env]. [](#) ## Connect to TimescaleDB In this section, you create a connection to TimescaleDB using the `psycopg2` library. This library is one of the most popular Postgres libraries for Python. It allows you to execute raw SQL queries efficiently and safely, and prevents common attacks such as SQL injection. 1. Import the psycogpg2 library: ```python import psycopg2 ``` 1. Locate your TimescaleDB credentials and use them to compose a connection string for `psycopg2`. You'll need: * password * username * host URL * port * database name 1. Compose your connection string variable as a [libpq connection string][pg-libpq-string], using this format: ```python CONNECTION = "postgres://username:password@host:port/dbname" ``` If you're using a hosted version of TimescaleDB, or generally require an SSL connection, use this version instead: ```python CONNECTION = "postgres://username:password@host:port/dbname?sslmode=require" ``` Alternatively you can specify each parameter in the connection string as follows ```python CONNECTION = "dbname=tsdb user=tsdbadmin password=secret host=host.com port=5432 sslmode=require" ``` This method of composing a connection string is for test or development purposes only. For production, use environment variables for sensitive details like your password, hostname, and port number. 1. Use the `psycopg2` [connect function][psycopg2-connect] to create a new database session and create a new [cursor object][psycopg2-cursor] to interact with the database. In your `main` function, add these lines: ```python CONNECTION = "postgres://username:password@host:port/dbname" with psycopg2.connect(CONNECTION) as conn: cursor = conn.cursor() # use the cursor to interact with your database # cursor.execute("SELECT * FROM table") ``` Alternatively, you can create a connection object and pass the object around as needed, like opening a cursor to perform database operations: ```python CONNECTION = "postgres://username:password@host:port/dbname" conn = psycopg2.connect(CONNECTION) cursor = conn.cursor() # use the cursor to interact with your database cursor.execute("SELECT 'hello world'") print(cursor.fetchone()) ``` ## Create a relational table In this section, you create a table called `sensors` which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called `sensor_data` which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors. 1. Compose a string which contains the SQL statement to create a relational table. This example creates a table called `sensors`, with columns `id`, `type` and `location`: ```python query_create_sensors_table = """CREATE TABLE sensors ( id SERIAL PRIMARY KEY, type VARCHAR(50), location VARCHAR(50) ); """ ``` 1. Open a cursor, execute the query you created in the previous step, and commit the query to make the changes persistent. Afterward, close the cursor to clean up: ```python cursor = conn.cursor() # see definition in Step 1 cursor.execute(query_create_sensors_table) conn.commit() cursor.close() ``` ## Create a hypertable When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable. 1. Create a string variable that contains the `CREATE TABLE` SQL statement for your hypertable. Notice how the hypertable has the compulsory time column: ```python # create sensor data hypertable query_create_sensordata_table = """CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER, temperature DOUBLE PRECISION, cpu DOUBLE PRECISION, FOREIGN KEY (sensor_id) REFERENCES sensors (id) ); """ ``` 2. Formulate a `SELECT` statement that converts the `sensor_data` table to a hypertable. You must specify the table name to convert to a hypertable, and the name of the time column as the two arguments. For more information, see the [`create_hypertable` docs][create-hypertable-docs]: ```python query_create_sensordata_hypertable = "SELECT create_hypertable('sensor_data', by_range('time'));" ``` The `by_range` dimension builder is an addition to TimescaleDB 2.13. 3. Open a cursor with the connection, execute the statements from the previous steps, commit your changes, and close the cursor: ```python cursor = conn.cursor() cursor.execute(query_create_sensordata_table) cursor.execute(query_create_sensordata_hypertable) # commit changes to the database to make changes persistent conn.commit() cursor.close() ``` ## Insert rows of data You can insert data into your hypertables in several different ways. In this section, you can use `psycopg2` with prepared statements, or you can use `pgcopy` for a faster insert. 1. This example inserts a list of tuples, or relational data, called `sensors`, into the relational table named `sensors`. Open a cursor with a connection to the database, use prepared statements to formulate the `INSERT` SQL statement, and then execute that statement: ```python sensors = [('a', 'floor'), ('a', 'ceiling'), ('b', 'floor'), ('b', 'ceiling')] cursor = conn.cursor() for sensor in sensors: try: cursor.execute("INSERT INTO sensors (type, location) VALUES (%s, %s);", (sensor[0], sensor[1])) except (Exception, psycopg2.Error) as error: print(error.pgerror) conn.commit() ``` 1. [](#)Alternatively, you can pass variables to the `cursor.execute` function and separate the formulation of the SQL statement, `SQL`, from the data being passed with it into the prepared statement, `data`: ```python SQL = "INSERT INTO sensors (type, location) VALUES (%s, %s);" sensors = [('a', 'floor'), ('a', 'ceiling'), ('b', 'floor'), ('b', 'ceiling')] cursor = conn.cursor() for sensor in sensors: try: data = (sensor[0], sensor[1]) cursor.execute(SQL, data) except (Exception, psycopg2.Error) as error: print(error.pgerror) conn.commit() ``` If you choose to use `pgcopy` instead, install the `pgcopy` package [using pip][pgcopy-install], and then add this line to your list of `import` statements: ```python from pgcopy import CopyManager ``` 1. Generate some random sensor data using the `generate_series` function provided by Postgres. This example inserts a total of 480 rows of data (4 readings, every 5 minutes, for 24 hours). In your application, this would be the query that saves your time-series data into the hypertable: ```python # for sensors with ids 1-4 for id in range(1, 4, 1): data = (id,) # create random data simulate_query = """SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time, %s as sensor_id, random()*100 AS temperature, random() AS cpu; """ cursor.execute(simulate_query, data) values = cursor.fetchall() ``` 1. Define the column names of the table you want to insert data into. This example uses the `sensor_data` hypertable created earlier. This hypertable consists of columns named `time`, `sensor_id`, `temperature` and `cpu`. The column names are defined in a list of strings called `cols`: ```python cols = ['time', 'sensor_id', 'temperature', 'cpu'] ``` 1. Create an instance of the `pgcopy` CopyManager, `mgr`, and pass the connection variable, hypertable name, and list of column names. Then use the `copy` function of the CopyManager to insert the data into the database quickly using `pgcopy`. ```python mgr = CopyManager(conn, 'sensor_data', cols) mgr.copy(values) ``` 1. Commit to persist changes: ```python conn.commit() ``` 1. [](#)The full sample code to insert data into TimescaleDB using `pgcopy`, using the example of sensor data from four sensors: ```python # insert using pgcopy def fast_insert(conn): cursor = conn.cursor() # for sensors with ids 1-4 for id in range(1, 4, 1): data = (id,) # create random data simulate_query = """SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time, %s as sensor_id, random()*100 AS temperature, random() AS cpu; """ cursor.execute(simulate_query, data) values = cursor.fetchall() # column names of the table you're inserting into cols = ['time', 'sensor_id', 'temperature', 'cpu'] # create copy manager with the target table and insert mgr = CopyManager(conn, 'sensor_data', cols) mgr.copy(values) # commit after all sensor data is inserted # could also commit after each sensor insert is done conn.commit() ``` 1. [](#)You can also check if the insertion worked: ```python cursor.execute("SELECT * FROM sensor_data LIMIT 5;") print(cursor.fetchall()) ``` ## Execute a query This section covers how to execute queries against your database. The first procedure shows a simple `SELECT *` query. For more complex queries, you can use prepared statements to ensure queries are executed safely against the database. For more information about properly using placeholders in `psycopg2`, see the [basic module usage document][psycopg2-docs-basics]. For more information about how to execute more complex queries in `psycopg2`, see the [psycopg2 documentation][psycopg2-docs-basics]. ### Execute a query 1. Define the SQL query you'd like to run on the database. This example is a simple `SELECT` statement querying each row from the previously created `sensor_data` table. ```python query = "SELECT * FROM sensor_data;" ``` 1. Open a cursor from the existing database connection, `conn`, and then execute the query you defined: ```python cursor = conn.cursor() query = "SELECT * FROM sensor_data;" cursor.execute(query) ``` 1. To access all resulting rows returned by your query, use one of `pyscopg2`'s [results retrieval methods][results-retrieval-methods], such as `fetchall()` or `fetchmany()`. This example prints the results of the query, row by row. Note that the result of `fetchall()` is a list of tuples, so you can handle them accordingly: ```python cursor = conn.cursor() query = "SELECT * FROM sensor_data;" cursor.execute(query) for row in cursor.fetchall(): print(row) cursor.close() ``` 1. [](#)If you want a list of dictionaries instead, you can define the cursor using [`DictCursor`][dictcursor-docs]: ```python cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) ``` Using this cursor, `cursor.fetchall()` returns a list of dictionary-like objects. For more complex queries, you can use prepared statements to ensure queries are executed safely against the database. ### Execute queries using prepared statements 1. Write the query using prepared statements: ```python # query with placeholders cursor = conn.cursor() query = """ 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 = %s AND sensors.type = %s GROUP BY five_min ORDER BY five_min DESC; """ location = "floor" sensor_type = "a" data = (location, sensor_type) cursor.execute(query, data) results = cursor.fetchall() ``` ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. * Install [Node.js][node-install]. * Install the Node.js package manager [npm][npm-install]. ## Connect to TimescaleDB In this section, you create a connection to TimescaleDB with a common Node.js ORM (object relational mapper) called [Sequelize][sequelize-info]. 1. At the command prompt, initialize a new Node.js app: ```bash npm init -y ``` This creates a `package.json` file in your directory, which contains all of the dependencies for your project. It looks something like this: ```json { "name": "node-sample", "version": "1.0.0", "description": "", "main": "index.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1" }, "keywords": [], "author": "", "license": "ISC" } ``` 1. Install Express.js: ```bash npm install express ``` 1. Create a simple web page to check the connection. Create a new file called `index.js`, with this content: ```java 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}`)) ``` 1. Test your connection by starting the application: ```bash node index.js ``` In your web browser, navigate to `http://localhost:3000`. If the connection is successful, it shows "Hello World!" 1. Add Sequelize to your project: ```bash npm install sequelize sequelize-cli pg pg-hstore ``` 1. Locate your TimescaleDB credentials and use them to compose a connection string for Sequelize. You'll need: * password * username * host URL * port * database name 1. Compose your connection string variable, using this format: ```java 'postgres://:@:/' ``` 1. Open the `index.js` file you created. Require Sequelize in the application, and declare the connection string: ```java const Sequelize = require('sequelize') const sequelize = new Sequelize('postgres://:@:/', { dialect: 'postgres', protocol: 'postgres', dialectOptions: { ssl: { require: true, rejectUnauthorized: false } } }) ``` Make sure you add the SSL settings in the `dialectOptions` sections. You can't connect to TimescaleDB using SSL without them. 1. You can test the connection by adding these lines to `index.js` after the `app.get` statement: ```java sequelize.authenticate().then(() => { console.log('Connection has been established successfully.'); }).catch(err => { console.error('Unable to connect to the database:', err); }); ``` Start the application on the command line: ```bash node index.js ``` If the connection is successful, you'll get output like this: ```bash Example app listening at http://localhost:3000 Executing (default): SELECT 1+1 AS result Connection has been established successfully. ``` ## Create a relational table In this section, you create a relational table called `page_loads`. 1. Use the Sequelize command line tool to create a table and model called `page_loads`: ```bash npx sequelize model:generate --name page_loads \ --attributes userAgent:string,time:date ``` The output looks similar to this: ```bash Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11] New model was created at . New migration was created at . ``` 1. Edit the migration file so that it sets up a migration key: ```java 'use strict'; module.exports = { up: async (queryInterface, Sequelize) => { await queryInterface.createTable('page_loads', { userAgent: { primaryKey: true, type: Sequelize.STRING }, time: { primaryKey: true, type: Sequelize.DATE } }); }, down: async (queryInterface, Sequelize) => { await queryInterface.dropTable('page_loads'); } }; ``` 1. Migrate the change and make sure that it is reflected in the database: ```bash npx sequelize db:migrate ``` The output looks similar to this: ```bash Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11] Loaded configuration file "config/config.json". Using environment "development". == 20200528195725-create-page-loads: migrating ======= == 20200528195725-create-page-loads: migrated (0.443s) ``` 1. Create the `PageLoads` model in your code. In the `index.js` file, above the `app.use` statement, add these lines: ```java let PageLoads = sequelize.define('page_loads', { userAgent: {type: Sequelize.STRING, primaryKey: true }, time: {type: Sequelize.DATE, primaryKey: true } }, { timestamps: false }); ``` 1. Instantiate a `PageLoads` object and save it to the database. ## Create a hypertable When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable. 1. Create a migration to modify the `page_loads` relational table, and change it to a hypertable by first running the following command: ```bash npx sequelize migration:generate --name add_hypertable ``` The output looks similar to this: ```bash Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11] migrations folder at already exists. New migration was created at /20200601202912-add_hypertable.js . ``` 1. In the `migrations` folder, there is now a new file. Open the file, and add this content: ```js 'use strict'; module.exports = { up: (queryInterface, Sequelize) => { return queryInterface.sequelize.query("SELECT create_hypertable('page_loads', by_range('time'));"); }, down: (queryInterface, Sequelize) => { } }; ``` The `by_range` dimension builder is an addition to TimescaleDB 2.13. 1. At the command prompt, run the migration command: ```bash npx sequelize db:migrate ``` The output looks similar to this: ```bash Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11] Loaded configuration file "config/config.json". Using environment "development". == 20200601202912-add_hypertable: migrating ======= == 20200601202912-add_hypertable: migrated (0.426s) ``` ## Insert rows of data This section covers how to insert data into your hypertables. 1. In the `index.js` file, modify the `/` route to get the `user-agent` from the request object (`req`) and the current timestamp. Then, call the `create` method on `PageLoads` model, supplying the user agent and timestamp parameters. The `create` call executes an `INSERT` on the database: ```java app.get('/', async (req, res) => { // get the user agent and current time const userAgent = req.get('user-agent'); const time = new Date().getTime(); try { // insert the record await PageLoads.create({ userAgent, time }); // send response res.send('Inserted!'); } catch (e) { console.log('Error inserting data', e) } }) ``` ## Execute a query This section covers how to execute queries against your database. In this example, every time the page is reloaded, all information currently in the table is displayed. 1. Modify the `/` route in the `index.js` file to call the Sequelize `findAll` function and retrieve all data from the `page_loads` table using the `PageLoads` model: ```java app.get('/', async (req, res) => { // get the user agent and current time const userAgent = req.get('user-agent'); const time = new Date().getTime(); try { // insert the record await PageLoads.create({ userAgent, time }); // now display everything in the table const messages = await PageLoads.findAll(); res.send(messages); } catch (e) { console.log('Error inserting data', e) } }) ``` Now, when you reload the page, you should see all of the rows currently in the `page_loads` table. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. - Install [Go][golang-install]. - Install the [PGX driver for Go][pgx-driver-github]. ## Connect to your Tiger Cloud service In this section, you create a connection to Tiger Cloud using the PGX driver. PGX is a toolkit designed to help Go developers work directly with Postgres. You can use it to help your Go application interact directly with TimescaleDB. 1. Locate your TimescaleDB credentials and use them to compose a connection string for PGX. You'll need: * password * username * host URL * port number * database name 1. Compose your connection string variable as a [libpq connection string][libpq-docs], using this format: ```go connStr := "postgres://username:password@host:port/dbname" ``` If you're using a hosted version of TimescaleDB, or if you need an SSL connection, use this format instead: ```go connStr := "postgres://username:password@host:port/dbname?sslmode=require" ``` 1. [](#)You can check that you're connected to your database with this hello world program: ```go package main import ( "context" "fmt" "os" "github.com/jackc/pgx/v5" ) //connect to database using a single connection func main() { /***********************************************/ /* Single Connection to TimescaleDB/ PostgreSQL */ /***********************************************/ ctx := context.Background() connStr := "yourConnectionStringHere" conn, err := pgx.Connect(ctx, connStr) if err != nil { fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err) os.Exit(1) } defer conn.Close(ctx) //run a simple query to check our connection var greeting string err = conn.QueryRow(ctx, "select 'Hello, Timescale!'").Scan(&greeting) if err != nil { fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err) os.Exit(1) } fmt.Println(greeting) } ``` If you'd like to specify your connection string as an environment variable, you can use this syntax to access it in place of the `connStr` variable: ```go os.Getenv("DATABASE_CONNECTION_STRING") ``` Alternatively, you can connect to TimescaleDB using a connection pool. Connection pooling is useful to conserve computing resources, and can also result in faster database queries: 1. To create a connection pool that can be used for concurrent connections to your database, use the `pgxpool.New()` function instead of `pgx.Connect()`. Also note that this script imports `github.com/jackc/pgx/v5/pgxpool`, instead of `pgx/v5` which was used to create a single connection: ```go package main import ( "context" "fmt" "os" "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() //run a simple query to check our connection var greeting string err = dbpool.QueryRow(ctx, "select 'Hello, Tiger Data (but concurrently)'").Scan(&greeting) if err != nil { fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err) os.Exit(1) } fmt.Println(greeting) } ``` ## Create a relational table In this section, you create a table called `sensors` which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called `sensor_data` which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors. 1. Compose a string that contains the SQL statement to create a relational table. This example creates a table called `sensors`, with columns for ID, type, and location: ```go queryCreateTable := `CREATE TABLE sensors (id SERIAL PRIMARY KEY, type VARCHAR(50), location VARCHAR(50));` ``` 1. Execute the `CREATE TABLE` statement with the `Exec()` function on the `dbpool` object, using the arguments of the current context and the statement string you created: ```go package main import ( "context" "fmt" "os" "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() /********************************************/ /* Create relational table */ /********************************************/ //Create relational table called sensors queryCreateTable := `CREATE TABLE sensors (id SERIAL PRIMARY KEY, type VARCHAR(50), location VARCHAR(50));` _, err = dbpool.Exec(ctx, queryCreateTable) if err != nil { fmt.Fprintf(os.Stderr, "Unable to create SENSORS table: %v\n", err) os.Exit(1) } fmt.Println("Successfully created relational table SENSORS") } ``` ## Generate a hypertable When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable. 1. Create a variable for the `CREATE TABLE SQL` statement for your hypertable. Notice how the hypertable has the compulsory time column: ```go queryCreateTable := `CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER, temperature DOUBLE PRECISION, cpu DOUBLE PRECISION, FOREIGN KEY (sensor_id) REFERENCES sensors (id)); ` ``` 1. Formulate the `SELECT` statement to convert the table into a hypertable. You must specify the table name to convert to a hypertable, and its time column name as the second argument. For more information, see the [`create_hypertable` docs][create-hypertable-docs]: ```go queryCreateHypertable := `SELECT create_hypertable('sensor_data', by_range('time'));` ``` The `by_range` dimension builder is an addition to TimescaleDB 2.13. 1. Execute the `CREATE TABLE` statement and `SELECT` statement which converts the table into a hypertable. You can do this by calling the `Exec()` function on the `dbpool` object, using the arguments of the current context, and the `queryCreateTable` and `queryCreateHypertable` statement strings: ```go package main import ( "context" "fmt" "os" "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() /********************************************/ /* Create Hypertable */ /********************************************/ // Create hypertable of time-series data called sensor_data queryCreateTable := `CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER, temperature DOUBLE PRECISION, cpu DOUBLE PRECISION, FOREIGN KEY (sensor_id) REFERENCES sensors (id)); ` queryCreateHypertable := `SELECT create_hypertable('sensor_data', by_range('time'));` //execute statement _, err = dbpool.Exec(ctx, queryCreateTable+queryCreateHypertable) if err != nil { fmt.Fprintf(os.Stderr, "Unable to create the `sensor_data` hypertable: %v\n", err) os.Exit(1) } fmt.Println("Successfully created hypertable `sensor_data`") } ``` ## Insert rows of data You can insert rows into your database in a couple of different ways. Each of these example inserts the data from the two arrays, `sensorTypes` and `sensorLocations`, into the relational table named `sensors`. The first example inserts a single row of data at a time. The second example inserts multiple rows of data. The third example uses batch inserts to speed up the process. 1. Open a connection pool to the database, then use the prepared statements to formulate an `INSERT` SQL statement, and execute it: ```go package main import ( "context" "fmt" "os" "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() /********************************************/ /* INSERT into relational table */ /********************************************/ //Insert data into relational table // Slices of sample data to insert // observation i has type sensorTypes[i] and location sensorLocations[i] sensorTypes := []string{"a", "a", "b", "b"} sensorLocations := []string{"floor", "ceiling", "floor", "ceiling"} for i := range sensorTypes { //INSERT statement in SQL queryInsertMetadata := `INSERT INTO sensors (type, location) VALUES ($1, $2);` //Execute INSERT command _, err := dbpool.Exec(ctx, queryInsertMetadata, sensorTypes[i], sensorLocations[i]) if err != nil { fmt.Fprintf(os.Stderr, "Unable to insert data into database: %v\n", err) os.Exit(1) } fmt.Printf("Inserted sensor (%s, %s) into database \n", sensorTypes[i], sensorLocations[i]) } fmt.Println("Successfully inserted all sensors into database") } ``` Instead of inserting a single row of data at a time, you can use this procedure to insert multiple rows of data, instead: 1. This example uses Postgres to generate some sample time-series to insert into the `sensor_data` hypertable. Define the SQL statement to generate the data, called `queryDataGeneration`. Then use the `.Query()` function to execute the statement and return the sample data. The data returned by the query is stored in `results`, a slice of structs, which is then used as a source to insert data into the hypertable: ```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() // Generate data to insert //SQL query to generate sample data queryDataGeneration := ` SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time, floor(random() * (3) + 1)::int as sensor_id, random()*100 AS temperature, random() AS cpu ` //Execute query to generate samples for sensor_data hypertable rows, err := dbpool.Query(ctx, queryDataGeneration) if err != nil { fmt.Fprintf(os.Stderr, "Unable to generate sensor data: %v\n", err) os.Exit(1) } defer rows.Close() fmt.Println("Successfully generated sensor data") //Store data generated in slice results type result struct { Time time.Time SensorId int Temperature float64 CPU float64 } var results []result for rows.Next() { var r result err = rows.Scan(&r.Time, &r.SensorId, &r.Temperature, &r.CPU) if err != nil { fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err) os.Exit(1) } results = append(results, r) } // 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) } // Check contents of results slice fmt.Println("Contents of RESULTS slice") for i := range results { var r result r = results[i] fmt.Printf("Time: %s | ID: %d | Temperature: %f | CPU: %f |\n", &r.Time, r.SensorId, r.Temperature, r.CPU) } } ``` 1. Formulate an SQL insert statement for the `sensor_data` hypertable: ```go //SQL query to generate sample data queryInsertTimeseriesData := ` INSERT INTO sensor_data (time, sensor_id, temperature, cpu) VALUES ($1, $2, $3, $4); ` ``` 1. Execute the SQL statement for each sample in the results slice: ```go //Insert contents of results slice into TimescaleDB for i := range results { var r result r = results[i] _, err := dbpool.Exec(ctx, queryInsertTimeseriesData, r.Time, r.SensorId, r.Temperature, r.CPU) if err != nil { fmt.Fprintf(os.Stderr, "Unable to insert sample into TimescaleDB %v\n", err) os.Exit(1) } defer rows.Close() } fmt.Println("Successfully inserted samples into sensor_data hypertable") ``` 1. [](#)This example `main.go` generates sample data and inserts it into the `sensor_data` hypertable: ```go package main import ( "context" "fmt" "os" "time" "github.com/jackc/pgx/v5/pgxpool" ) func main() { /********************************************/ /* Connect using Connection Pool */ /********************************************/ 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() /********************************************/ /* Insert data into hypertable */ /********************************************/ // Generate data to insert //SQL query to generate sample data queryDataGeneration := ` SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time, floor(random() * (3) + 1)::int as sensor_id, random()*100 AS temperature, random() AS cpu ` //Execute query to generate samples for sensor_data hypertable rows, err := dbpool.Query(ctx, queryDataGeneration) if err != nil { fmt.Fprintf(os.Stderr, "Unable to generate sensor data: %v\n", err) os.Exit(1) } defer rows.Close() fmt.Println("Successfully generated sensor data") //Store data generated in slice results type result struct { Time time.Time SensorId int Temperature float64 CPU float64 } var results []result for rows.Next() { var r result err = rows.Scan(&r.Time, &r.SensorId, &r.Temperature, &r.CPU) if err != nil { fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err) os.Exit(1) } results = append(results, r) } // 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) } // Check contents of results slice fmt.Println("Contents of RESULTS slice") for i := range results { var r result r = results[i] fmt.Printf("Time: %s | ID: %d | Temperature: %f | CPU: %f |\n", &r.Time, r.SensorId, r.Temperature, r.CPU) } //Insert contents of results slice into TimescaleDB //SQL query to generate sample data queryInsertTimeseriesData := ` INSERT INTO sensor_data (time, sensor_id, temperature, cpu) VALUES ($1, $2, $3, $4); ` //Insert contents of results slice into TimescaleDB for i := range results { var r result r = results[i] _, err := dbpool.Exec(ctx, queryInsertTimeseriesData, r.Time, r.SensorId, r.Temperature, r.CPU) if err != nil { fmt.Fprintf(os.Stderr, "Unable to insert sample into TimescaleDB %v\n", err) os.Exit(1) } defer rows.Close() } fmt.Println("Successfully inserted samples into sensor_data hypertable") } ``` Inserting multiple rows of data using this method executes as many `insert` statements as there are samples to be inserted. This can make ingestion of data slow. To speed up ingestion, you can batch insert data instead. Here's a sample pattern for how to do so, using the sample data you generated in the previous procedure. It uses the pgx `Batch` object: 1. This example batch inserts data into the database: ```go package main import ( "context" "fmt" "os" "time" "github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgxpool" ) func main() { /********************************************/ /* Connect using Connection Pool */ /********************************************/ 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() // Generate data to insert //SQL query to generate sample data queryDataGeneration := ` SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time, floor(random() * (3) + 1)::int as sensor_id, random()*100 AS temperature, random() AS cpu ` //Execute query to generate samples for sensor_data hypertable rows, err := dbpool.Query(ctx, queryDataGeneration) if err != nil { fmt.Fprintf(os.Stderr, "Unable to generate sensor data: %v\n", err) os.Exit(1) } defer rows.Close() fmt.Println("Successfully generated sensor data") //Store data generated in slice results type result struct { Time time.Time SensorId int Temperature float64 CPU float64 } var results []result for rows.Next() { var r result err = rows.Scan(&r.Time, &r.SensorId, &r.Temperature, &r.CPU) if err != nil { fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err) os.Exit(1) } results = append(results, r) } // 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) } // Check contents of results slice /*fmt.Println("Contents of RESULTS slice") for i := range results { var r result r = results[i] fmt.Printf("Time: %s | ID: %d | Temperature: %f | CPU: %f |\n", &r.Time, r.SensorId, r.Temperature, r.CPU) }*/ //Insert contents of results slice into TimescaleDB //SQL query to generate sample data queryInsertTimeseriesData := ` INSERT INTO sensor_data (time, sensor_id, temperature, cpu) VALUES ($1, $2, $3, $4); ` /********************************************/ /* Batch Insert into TimescaleDB */ /********************************************/ //create batch batch := &pgx.Batch{} //load insert statements into batch queue for i := range results { var r result r = results[i] batch.Queue(queryInsertTimeseriesData, r.Time, r.SensorId, r.Temperature, r.CPU) } batch.Queue("select count(*) from sensor_data") //send batch to connection pool br := dbpool.SendBatch(ctx, batch) //execute statements in batch queue _, err = br.Exec() if err != nil { fmt.Fprintf(os.Stderr, "Unable to execute statement in batch queue %v\n", err) os.Exit(1) } fmt.Println("Successfully batch inserted data") //Compare length of results slice to size of table fmt.Printf("size of results: %d\n", len(results)) //check size of table for number of rows inserted // result of last SELECT statement var rowsInserted int err = br.QueryRow().Scan(&rowsInserted) fmt.Printf("size of table: %d\n", rowsInserted) err = br.Close() if err != nil { fmt.Fprintf(os.Stderr, "Unable to closer batch %v\n", err) os.Exit(1) } } ``` ## Execute a query This section covers how to execute queries against your database. 1. Define the SQL query you'd like to run on the database. This example uses a SQL query that combines time-series and relational data. It returns the average CPU values for every 5 minute interval, for sensors located on location `ceiling` and of type `a`: ```go // 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; ` ``` 1. Use the `.Query()` function to execute the query string. Make sure you specify the relevant placeholders: ```go //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") ``` 1. Access the rows returned by `.Query()`. Create a struct with fields representing the columns that you expect to be returned, then use the `rows.Next()` function to iterate through the rows returned and fill `results` with the array of structs. This uses the `rows.Scan()` function, passing in pointers to the fields that you want to scan for results. This example prints out the results returned from the query, but you might want to use those results for some other purpose. Once you've scanned through all the rows returned you can then use the results array however you like. ```go //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) } // use results here… ``` 1. [](#)This example program runs a query, and accesses the results of that query: ```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) } } ``` ## Next steps Now that you're able to connect, read, and write to a TimescaleDB instance from your Go application, be sure to check out these advanced TimescaleDB tutorials: * Refer to the [pgx documentation][pgx-docs] for more information about pgx. * Get up and running with TimescaleDB with the [Getting Started][getting-started] tutorial. * Want fast inserts on CSV data? Check out [TimescaleDB parallel copy][parallel-copy-tool], a tool for fast inserts, written in Go. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. * Install the [Java Development Kit (JDK)][jdk]. * Install the [PostgreSQL JDBC driver][pg-jdbc-driver]. All code in this quick start is for Java 16 and later. If you are working with older JDK versions, use legacy coding techniques. ## Connect to your Tiger Cloud service In this section, you create a connection to your service using an application in a single file. You can use any of your favorite build tools, including `gradle` or `maven`. 1. Create a directory containing a text file called `Main.java`, with this content: ```java package com.timescale.java; public class Main { public static void main(String... args) { System.out.println("Hello, World!"); } } ``` 1. From the command line in the current directory, run the application: ```bash java Main.java ``` If the command is successful, `Hello, World!` line output is printed to your console. 1. Import the PostgreSQL JDBC driver. If you are using a dependency manager, include the [PostgreSQL JDBC Driver][pg-jdbc-driver-dependency] as a dependency. 1. Download the [JAR artifact of the JDBC Driver][pg-jdbc-driver-artifact] and save it with the `Main.java` file. 1. Import the `JDBC Driver` into the Java application and display a list of available drivers for the check: ```java package com.timescale.java; import java.sql.DriverManager; public class Main { public static void main(String... args) { DriverManager.drivers().forEach(System.out::println); } } ``` 1. Run all the examples: ```bash java -cp *.jar Main.java ``` If the command is successful, a string similar to `org.postgresql.Driver@7f77e91b` is printed to your console. This means that you are ready to connect to TimescaleDB from Java. 1. Locate your TimescaleDB credentials and use them to compose a connection string for JDBC. You'll need: * password * username * host URL * port * database name 1. Compose your connection string variable, using this format: ```java var connUrl = "jdbc:postgresql://:/?user=&password="; ``` For more information about creating connection strings, see the [JDBC documentation][pg-jdbc-driver-conn-docs]. This method of composing a connection string is for test or development purposes only. For production, use environment variables for sensitive details like your password, hostname, and port number. ```java package com.timescale.java; import java.sql.DriverManager; import java.sql.SQLException; public class Main { public static void main(String... args) throws SQLException { var connUrl = "jdbc:postgresql://:/?user=&password="; var conn = DriverManager.getConnection(connUrl); System.out.println(conn.getClientInfo()); } } ``` 1. Run the code: ```bash java -cp *.jar Main.java ``` If the command is successful, a string similar to `{ApplicationName=PostgreSQL JDBC Driver}` is printed to your console. ## Create a relational table In this section, you create a table called `sensors` which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called `sensor_data` which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors. 1. Compose a string which contains the SQL statement to create a relational table. This example creates a table called `sensors`, with columns `id`, `type` and `location`: ```sql CREATE TABLE sensors ( id SERIAL PRIMARY KEY, type TEXT NOT NULL, location TEXT NOT NULL ); ``` 1. Create a statement, execute the query you created in the previous step, and check that the table was created successfully: ```java package com.timescale.java; import java.sql.DriverManager; import java.sql.SQLException; public class Main { public static void main(String... args) throws SQLException { var connUrl = "jdbc:postgresql://:/?user=&password="; var conn = DriverManager.getConnection(connUrl); var createSensorTableQuery = """ CREATE TABLE sensors ( id SERIAL PRIMARY KEY, type TEXT NOT NULL, location TEXT NOT NULL ) """; try (var stmt = conn.createStatement()) { stmt.execute(createSensorTableQuery); } var showAllTablesQuery = "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'"; try (var stmt = conn.createStatement(); var rs = stmt.executeQuery(showAllTablesQuery)) { System.out.println("Tables in the current database: "); while (rs.next()) { System.out.println(rs.getString("tablename")); } } } } ``` ## Create a hypertable When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable. 1. Create a `CREATE TABLE` SQL statement for your hypertable. Notice how the hypertable has the compulsory time column: ```sql CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER REFERENCES sensors (id), value DOUBLE PRECISION ); ``` 1. Create a statement, execute the query you created in the previous step: ```sql SELECT create_hypertable('sensor_data', by_range('time')); ``` The `by_range` and `by_hash` dimension builder is an addition to TimescaleDB 2.13. 1. Execute the two statements you created, and commit your changes to the database: ```java package com.timescale.java; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.List; public class Main { public static void main(String... args) { final var connUrl = "jdbc:postgresql://:/?user=&password="; try (var conn = DriverManager.getConnection(connUrl)) { createSchema(conn); insertData(conn); } catch (SQLException ex) { System.err.println(ex.getMessage()); } } private static void createSchema(final Connection conn) throws SQLException { try (var stmt = conn.createStatement()) { stmt.execute(""" CREATE TABLE sensors ( id SERIAL PRIMARY KEY, type TEXT NOT NULL, location TEXT NOT NULL ) """); } try (var stmt = conn.createStatement()) { stmt.execute(""" CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER REFERENCES sensors (id), value DOUBLE PRECISION ) """); } try (var stmt = conn.createStatement()) { stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))"); } } } ``` ## Insert data You can insert data into your hypertables in several different ways. In this section, you can insert single rows, or insert by batches of rows. 1. Open a connection to the database, use prepared statements to formulate the `INSERT` SQL statement, then execute the statement: ```java final List sensors = List.of( new Sensor("temperature", "bedroom"), new Sensor("temperature", "living room"), new Sensor("temperature", "outside"), new Sensor("humidity", "kitchen"), new Sensor("humidity", "outside")); for (final var sensor : sensors) { try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) { stmt.setString(1, sensor.type()); stmt.setString(2, sensor.location()); stmt.executeUpdate(); } } ``` If you want to insert a batch of rows by using a batching mechanism. In this example, you generate some sample time-series data to insert into the `sensor_data` hypertable: 1. Insert batches of rows: ```java final var sensorDataCount = 100; final var insertBatchSize = 10; try (var stmt = conn.prepareStatement(""" INSERT INTO sensor_data (time, sensor_id, value) VALUES ( generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'), floor(random() * 4 + 1)::INTEGER, random() ) """)) { for (int i = 0; i < sensorDataCount; i++) { stmt.addBatch(); if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) { stmt.executeBatch(); } } } ``` ## Execute a query This section covers how to execute queries against your database. ## Execute queries on TimescaleDB 1. Define the SQL query you'd like to run on the database. This example combines time-series and relational data. It returns the average values for every 15 minute interval for sensors with specific type and location. ```sql SELECT time_bucket('15 minutes', time) AS bucket, avg(value) FROM sensor_data JOIN sensors ON sensors.id = sensor_data.sensor_id WHERE sensors.type = ? AND sensors.location = ? GROUP BY bucket ORDER BY bucket DESC; ``` 1. Execute the query with the prepared statement and read out the result set for all `a`-type sensors located on the `floor`: ```java try (var stmt = conn.prepareStatement(""" SELECT time_bucket('15 minutes', time) AS bucket, avg(value) FROM sensor_data JOIN sensors ON sensors.id = sensor_data.sensor_id WHERE sensors.type = ? AND sensors.location = ? GROUP BY bucket ORDER BY bucket DESC """)) { stmt.setString(1, "temperature"); stmt.setString(2, "living room"); try (var rs = stmt.executeQuery()) { while (rs.next()) { System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2)); } } } ``` If the command is successful, you'll see output like this: ```bash 2021-05-12 23:30:00.0: 0,508649 2021-05-12 23:15:00.0: 0,477852 2021-05-12 23:00:00.0: 0,462298 2021-05-12 22:45:00.0: 0,457006 2021-05-12 22:30:00.0: 0,568744 ... ``` ## Next steps Now that you're able to connect, read, and write to a TimescaleDB instance from your Java application, and generate the scaffolding necessary to build a new application from an existing TimescaleDB instance, be sure to check out these advanced TimescaleDB tutorials: * [Continuous Aggregates][continuous-aggregates] * [Migrate Your own Data][migrate] ## Complete code samples This section contains complete code samples. ### Complete code sample ```java package com.timescale.java; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.List; public class Main { public static void main(String... args) { final var connUrl = "jdbc:postgresql://:/?user=&password="; try (var conn = DriverManager.getConnection(connUrl)) { createSchema(conn); insertData(conn); } catch (SQLException ex) { System.err.println(ex.getMessage()); } } private static void createSchema(final Connection conn) throws SQLException { try (var stmt = conn.createStatement()) { stmt.execute(""" CREATE TABLE sensors ( id SERIAL PRIMARY KEY, type TEXT NOT NULL, location TEXT NOT NULL ) """); } try (var stmt = conn.createStatement()) { stmt.execute(""" CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER REFERENCES sensors (id), value DOUBLE PRECISION ) """); } try (var stmt = conn.createStatement()) { stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))"); } } private static void insertData(final Connection conn) throws SQLException { final List sensors = List.of( new Sensor("temperature", "bedroom"), new Sensor("temperature", "living room"), new Sensor("temperature", "outside"), new Sensor("humidity", "kitchen"), new Sensor("humidity", "outside")); for (final var sensor : sensors) { try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) { stmt.setString(1, sensor.type()); stmt.setString(2, sensor.location()); stmt.executeUpdate(); } } final var sensorDataCount = 100; final var insertBatchSize = 10; try (var stmt = conn.prepareStatement(""" INSERT INTO sensor_data (time, sensor_id, value) VALUES ( generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'), floor(random() * 4 + 1)::INTEGER, random() ) """)) { for (int i = 0; i < sensorDataCount; i++) { stmt.addBatch(); if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) { stmt.executeBatch(); } } } } private record Sensor(String type, String location) { } } ``` ### Execute more complex queries ```java package com.timescale.java; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.List; public class Main { public static void main(String... args) { final var connUrl = "jdbc:postgresql://:/?user=&password="; try (var conn = DriverManager.getConnection(connUrl)) { createSchema(conn); insertData(conn); executeQueries(conn); } catch (SQLException ex) { System.err.println(ex.getMessage()); } } private static void createSchema(final Connection conn) throws SQLException { try (var stmt = conn.createStatement()) { stmt.execute(""" CREATE TABLE sensors ( id SERIAL PRIMARY KEY, type TEXT NOT NULL, location TEXT NOT NULL ) """); } try (var stmt = conn.createStatement()) { stmt.execute(""" CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER REFERENCES sensors (id), value DOUBLE PRECISION ) """); } try (var stmt = conn.createStatement()) { stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))"); } } private static void insertData(final Connection conn) throws SQLException { final List sensors = List.of( new Sensor("temperature", "bedroom"), new Sensor("temperature", "living room"), new Sensor("temperature", "outside"), new Sensor("humidity", "kitchen"), new Sensor("humidity", "outside")); for (final var sensor : sensors) { try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) { stmt.setString(1, sensor.type()); stmt.setString(2, sensor.location()); stmt.executeUpdate(); } } final var sensorDataCount = 100; final var insertBatchSize = 10; try (var stmt = conn.prepareStatement(""" INSERT INTO sensor_data (time, sensor_id, value) VALUES ( generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'), floor(random() * 4 + 1)::INTEGER, random() ) """)) { for (int i = 0; i < sensorDataCount; i++) { stmt.addBatch(); if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) { stmt.executeBatch(); } } } } private static void executeQueries(final Connection conn) throws SQLException { try (var stmt = conn.prepareStatement(""" SELECT time_bucket('15 minutes', time) AS bucket, avg(value) FROM sensor_data JOIN sensors ON sensors.id = sensor_data.sensor_id WHERE sensors.type = ? AND sensors.location = ? GROUP BY bucket ORDER BY bucket DESC """)) { stmt.setString(1, "temperature"); stmt.setString(2, "living room"); try (var rs = stmt.executeQuery()) { while (rs.next()) { System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2)); } } } } private record Sensor(String type, String location) { } } ``` You are not limited to these languages. Tiger Cloud is based on Postgres, you can interface with TimescaleDB and Tiger Cloud using any [Postgres client driver][postgres-drivers]. ===== PAGE: https://docs.tigerdata.com/getting-started/services/ ===== # Create your first Tiger Cloud service Tiger Cloud is the modern Postgres data platform for all your applications. It enhances Postgres to handle time series, events, real-time analytics, and vector search—all in a single database alongside transactional workloads. You get one system that handles live data ingestion, late and out-of-order updates, and low latency queries, with the performance, reliability, and scalability your app needs. Ideal for IoT, crypto, finance, SaaS, and a myriad other domains, Tiger Cloud allows you to build data-heavy, mission-critical apps while retaining the familiarity and reliability of Postgres. ## What is a Tiger Cloud service? A Tiger Cloud service is a single optimised Postgres instance extended with innovations in the database engine and cloud infrastructure to deliver speed without sacrifice. A Tiger Cloud service is 10-1000x faster at scale! It is ideal for applications requiring strong data consistency, complex relationships, and advanced querying capabilities. Get ACID compliance, extensive SQL support, JSON handling, and extensibility through custom functions, data types, and extensions. Each service is associated with a project in Tiger Cloud. Each project can have multiple services. Each user is a [member of one or more projects][rbac]. You create free and standard services in Tiger Cloud Console, depending on your [pricing plan][pricing-plans]. A free service comes at zero cost and gives you limited resources to get to know Tiger Cloud. Once you are ready to try out more advanced features, you can switch to a paid plan and convert your free service to a standard one. ![Tiger Cloud pricing plans](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-pricing.svg) The Free pricing plan and services are currently in beta. To the Postgres you know and love, Tiger Cloud adds the following capabilities: - **Standard services**: - _Real-time analytics_: store and query [time-series data][what-is-time-series] at scale for real-time analytics and other use cases. Get faster time-based queries with hypertables, continuous aggregates, and columnar storage. Save money by compressing data into the columnstore, moving cold data to low-cost bottomless storage in Amazon S3, and deleting old data with automated policies. - _AI-focused_: build AI applications from start to scale. Get fast and accurate similarity search with the pgvector and pgvectorscale extensions. - _Hybrid applications_: get a full set of tools to develop applications that combine time-based data and AI. All standard Tiger Cloud services include the tooling you expect for production and developer environments: [live migration][live-migration], [automatic backups and PITR][automatic-backups], [high availability][high-availability], [read replicas][readreplica], [data forking][operations-forking], [connection pooling][connection-pooling], [tiered storage][data-tiering], [usage-based storage][how-plans-work], secure in-Tiger Cloud Console [SQL editing][in-console-editors], service [metrics][metrics] and [insights][insights], [streamlined maintenance][maintain-upgrade], and much more. Tiger Cloud continuously monitors your services and prevents common Postgres out-of-memory crashes. - **Free services**: _Postgres with TimescaleDB and vector extensions_ Free services offer limited resources and a basic feature scope, perfect to get to know Tiger Cloud in a development environment. You manage your Tiger Cloud services and interact with your data in Tiger Cloud Console using the following modes: | **Ops mode** | **Data mode** | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | ![Tiger Cloud Console ops mode][ops-mode] | ![Tiger Cloud Console data mode][data-mode] | | **You use the ops mode to:**
  • Ensure data security with high availability and read replicas
  • Save money with columnstore compression and tiered storage
  • Enable Postgres extensions to add extra functionality
  • Increase security using VPCs
  • Perform day-to-day administration
| **Powered by PopSQL, you use the data mode to:**
  • Write queries with autocomplete
  • Visualize data with charts and dashboards
  • Schedule queries and dashboards for alerts or recurring reports
  • Share queries and dashboards
  • Interact with your data on auto-pilot with SQL assistant
This feature is not available under the Free pricing plan. | To start using Tiger Cloud for your data: 1. [Create a Tiger Data account][create-an-account]: register to get access to Tiger Cloud Console as a centralized point to administer and interact with your data. 1. [Create a Tiger Cloud service][create-a-service]: that is, a Postgres database instance, powered by [TimescaleDB][timescaledb], built for production, and extended with cloud features like transparent data tiering to object storage. 1. [Connect to your Tiger Cloud service][connect-to-your-service]: to run queries, add and migrate your data from other sources. ## Create a Tiger Data account You create a Tiger Data account to manage your services and data in a centralized and efficient manner in Tiger Cloud Console. From there, you can create and delete services, run queries, manage access and billing, integrate other services, contact support, and more. You create a standalone account to manage Tiger Cloud as a separate unit in your infrastructure, which includes separate billing and invoicing. To set up Tiger Cloud: 1. **Sign up for a 30-day free trial** Open [Sign up for Tiger Cloud][timescale-signup] and add your details, then click `Start your free trial`. You receive a confirmation email in your inbox. 1. **Confirm your email address** In the confirmation email, click the link supplied. 1. **Select the [pricing plan][pricing-plans]** You are now logged into Tiger Cloud Console. You can change the pricing plan to better accommodate your growing needs on the [`Billing` page][console-billing]. To have Tiger Cloud as a part of your AWS infrastructure, you create a Tiger Data account through AWS Marketplace. In this case, Tiger Cloud is a line item in your AWS invoice. To set up Tiger Cloud via AWS: 1. **Open [AWS Marketplace][aws-marketplace] and search for `Tiger Cloud`** You see two pricing options, [pay-as-you-go][aws-paygo] and [annual commit][aws-annual-commit]. 1. **Select the pricing option that suits you and click `View purchase options`** 1. **Review and configure the purchase details, then click `Subscribe`** 1. **Click `Set up your account` at the top of the page** You are redirected to Tiger Cloud Console. 1. **Sign up for a 30-day free trial** Add your details, then click `Start your free trial`. If you want to link an existing Tiger Data account to AWS, log in with your existing credentials. 1. **Select the [pricing plan][pricing-plans]** You are now logged into Tiger Cloud Console. You can change the pricing plan later to better accommodate your growing needs on the [`Billing` page][console-billing]. 1. **In `Confirm AWS Marketplace connection`, click `Connect`** Your Tiger Cloud and AWS accounts are now connected. ## Create a Tiger Cloud service Now that you have an active Tiger Data account, you create and manage your services in Tiger Cloud Console. When you create a service, you effectively create a blank Postgres database with additional Tiger Cloud features available under your pricing plan. You then add or migrate your data into this database. To create a free or standard service: 1. In the [service creation page][create-service], click `+ New service`. Follow the wizard to configure your service depending on its type. 1. Click `Create service`. Your service is constructed and ready to use in a few seconds. 1. Click `Download the config` and store the configuration information you need to connect to this service in a secure location. This file contains the passwords and configuration information you need to connect to your service using the Tiger Cloud Console data mode, from the command line, or using third-party database administration tools. If you choose to go directly to the service overview, [Connect to your service][connect-to-your-service] shows you how to connect. ## Connect to your service To run queries and perform other operations, connect to your service: 1. **Check your service is running correctly** In [Tiger Cloud Console][services-portal], check that your service is marked as `Running`. ![Check service is running](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-services-view.png) 1. **Connect to your service** Connect using data mode or SQL editor in Tiger Cloud Console, or psql in the command line: This feature is not available under the Free pricing plan. 1. In Tiger Cloud Console, toggle `Data`. 1. Select your service in the connection drop-down in the top right. ![Select a connection](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-data-mode-connection-dropdown.png) 1. Run a test query: ```sql SELECT CURRENT_DATE; ``` This query gives you the current date, you have successfully connected to your service. And that is it, you are up and running. Enjoy developing with Tiger Data. 1. In Tiger Cloud Console, select your service. 1. Click `SQL editor`. ![Check a service is running](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-ops-mode-sql-editor.png) 1. Run a test query: ```sql SELECT CURRENT_DATE; ``` This query gives you the current date, you have successfully connected to your service. And that is it, you are up and running. Enjoy developing with Tiger Data. 1. Install [psql][psql]. 1. Run the following command in the terminal using the service URL from the config file you have saved during service creation: ``` psql "" ``` 1. Run a test query: ```sql SELECT CURRENT_DATE; ``` This query returns the current date. You have successfully connected to your service. And that is it, you are up and running. Enjoy developing with Tiger Data. Quick recap. You: - Manage your services in the [ops mode][portal-ops-mode] in Tiger Cloud Console: add read replicas and enable high availability, compress data into the columnstore, change parameters, and so on. - Analyze your data in the [data mode][portal-data-mode] in Tiger Cloud Console: write queries with autocomplete, save them in folders, share them, create charts/dashboards, and much more. - Store configuration and security information in your config file. What next? [Try the key features offered by Tiger Data][try-timescale-features], see the [tutorials][tutorials], interact with the data in your Tiger Cloud service using [your favorite programming language][connect-with-code], integrate your Tiger Cloud service with a range of [third-party tools][integrations], plain old [Use Tiger Data products][use-timescale], or dive into the [API reference][use-the-api]. ===== PAGE: https://docs.tigerdata.com/getting-started/get-started-devops-as-code/ ===== # "DevOps as code with Tiger" Tiger Data supplies a clean, programmatic control layer for Tiger Cloud. This includes RESTful APIs and CLI commands that enable humans, machines, and AI agents easily provision, configure, and manage Tiger Cloud services programmatically. Tiger CLI is a command-line interface that you use to manage Tiger Cloud resources including VPCs, services, read replicas, and related infrastructure. Tiger CLI calls Tiger REST API to communicate with Tiger Cloud. This page shows you how to install and set up secure authentication for Tiger CLI, then create your first service. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Data account][create-account]. ## Install and configure Tiger CLI 1. **Install Tiger CLI** Use the terminal to install the CLI: ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.deb.sh | sudo os=any dist=any bash sudo apt-get install tiger-cli ``` ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.deb.sh | sudo os=any dist=any bash sudo apt-get install tiger-cli ``` ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.rpm.sh | sudo os=rpm_any dist=rpm_any bash sudo yum install tiger-cli ``` ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.rpm.sh | sudo os=rpm_any dist=rpm_any bash sudo yum install tiger-cli ``` ```shell brew install --cask timescale/tap/tiger-cli ``` ```shell curl -fsSL https://cli.tigerdata.com | sh ``` 1. **Set up API credentials** 1. Log Tiger CLI into your Tiger Data account: ```shell tiger auth login ``` Tiger CLI opens Console in your browser. Log in, then click `Authorize`. You can have a maximum of 10 active client credentials. If you get an error, open [credentials][rest-api-credentials] and delete an unused credential. 1. Select a Tiger Cloud project: ```terminaloutput Auth URL is: https://console.cloud.timescale.com/oauth/authorize?client_id=lotsOfURLstuff Opening browser for authentication... Select a project: > 1. Tiger Project (tgrproject) 2. YourCompany (Company wide project) (cpnproject) 3. YourCompany Department (dptproject) Use ↑/↓ arrows or number keys to navigate, enter to select, q to quit ``` If only one project is associated with your account, this step is not shown. Where possible, Tiger CLI stores your authentication information in the system keychain/credential manager. If that fails, the credentials are stored in `~/.config/tiger/credentials` with restricted file permissions (600). By default, Tiger CLI stores your configuration in `~/.config/tiger/config.yaml`. 1. **Test your authenticated connection to Tiger Cloud by listing services** ```bash tiger service list ``` This call returns something like: - No services: ```terminaloutput 🏜️ No services found! Your project is looking a bit empty. 🚀 Ready to get started? Create your first service with: tiger service create ``` - One or more services: ```terminaloutput ┌────────────┬─────────────────────┬────────┬─────────────┬──────────────┬──────────────────┐ │ SERVICE ID │ NAME │ STATUS │ TYPE │ REGION │ CREATED │ ├────────────┼─────────────────────┼────────┼─────────────┼──────────────┼──────────────────┤ │ tgrservice │ tiger-agent-service │ READY │ TIMESCALEDB │ eu-central-1 │ 2025-09-25 16:09 │ └────────────┴─────────────────────┴────────┴─────────────┴──────────────┴──────────────────┘ ``` ## Create your first Tiger Cloud service Create a new Tiger Cloud service using Tiger CLI: 1. **Submit a service creation request** By default, Tiger CLI creates a service for you that matches your [pricing plan][pricing-plans]: * **Free plan**: shared CPU/memory and the `time-series` and `ai` capabilities * **Paid plan**: 0.5 CPU and 2 GB memory with the `time-series` capability ```shell tiger service create ``` Tiger Cloud creates a Development environment for you. That is, no delete protection, high-availability, spooling or read replication. You see something like: ```terminaloutput 🚀 Creating service 'db-11111' (auto-generated name)... ✅ Service creation request accepted! 📋 Service ID: tgrservice 🔐 Password saved to system keyring for automatic authentication 🎯 Set service 'tgrservice' as default service. ⏳ Waiting for service to be ready (wait timeout: 30m0s)... 🎉 Service is ready and running! 🔌 Run 'tiger db connect' to connect to your new service ┌───────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────┐ │ PROPERTY │ VALUE │ ├───────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Service ID │ tgrservice │ │ Name │ db-11111 │ │ Status │ READY │ │ Type │ TIMESCALEDB │ │ Region │ us-east-1 │ │ CPU │ 0.5 cores (500m) │ │ Memory │ 2 GB │ │ Direct Endpoint │ tgrservice.tgrproject.tsdb.cloud.timescale.com:39004 │ │ Created │ 2025-10-20 20:33:46 UTC │ │ Connection String │ postgresql://tsdbadmin@tgrservice.tgrproject.tsdb.cloud.timescale.com:0007/tsdb?sslmode=require │ │ Console URL │ https://console.cloud.timescale.com/dashboard/services/tgrservice │ └───────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┘ ``` This service is set as default by the CLI. 1. **Check the CLI configuration** ```shell tiger config show ``` You see something like: ```terminaloutput api_url: https://console.cloud.timescale.com/public/api/v1 console_url: https://console.cloud.timescale.com gateway_url: https://console.cloud.timescale.com/api docs_mcp: true docs_mcp_url: https://mcp.tigerdata.com/docs project_id: tgrproject service_id: tgrservice output: table analytics: true password_storage: keyring debug: false config_dir: /Users//.config/tiger ``` And that is it, you are ready to use Tiger CLI to manage your services in Tiger Cloud. ## Commands You can use the following commands with Tiger CLI. For more information on each command, use the `-h` flag. For example: `tiger auth login -h` | Command | Subcommand | Description | |---------|----------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | auth | | Manage authentication and credentials for your Tiger Data account | | | login | Create an authenticated connection to your Tiger Data account | | | logout | Remove the credentials used to create authenticated connections to Tiger Cloud | | | status | Show your current authentication status and project ID | | version | | Show information about the currently installed version of Tiger CLI | | config | | Manage your Tiger CLI configuration | | | show | Show the current configuration | | | set `` `` | Set a specific value in your configuration. For example, `tiger config set debug true` | | | unset `` | Clear the value of a configuration parameter. For example, `tiger config unset debug` | | | reset | Reset the configuration to the defaults. This also logs you out from the current Tiger Cloud project | | service | | Manage the Tiger Cloud services in this project | | | create | Create a new service in this project. Possible flags are:
  • `--name`: service name (auto-generated if not provided)
  • `--addons`: addons to enable (time-series, ai, or none for PostgreSQL-only)
  • `--region`: region code where the service will be deployed
  • `--cpu-memory`: CPU/memory allocation combination
  • `--replicas`: number of high-availability replicas
  • `--no-wait`: don't wait for the operation to complete
  • `--wait-timeout`: wait timeout duration (for example, 30m, 1h30m, 90s)
  • `--no-set-default`: don't set this service as the default service
  • `--with-password`: include password in output
  • `--output, -o`: output format (`json`, `yaml`, table)

Possible `cpu-memory` combinations are:
  • shared/shared
  • 0.5 CPU/2 GB
  • 1 CPU/4 GB
  • 2 CPU/8 GB
  • 4 CPU/16 GB
  • 8 CPU/32 GB
  • 16 CPU/64 GB
  • 32 CPU/128 GB
| | | delete `` | Delete a service from this project. This operation is irreversible and requires confirmation by typing the service ID | | | fork `` | Fork an existing service to create a new independent copy. Key features are:
  • Timing options: `--now`, `--last-snapshot`, `--to-timestamp`
  • Resource configuration: `--cpu-memory`
  • Naming: `--name `. Defaults to `{source-service-name}-fork`
  • Wait behavior: `--no-wait`, `--wait-timeout`
  • Default service: `--no-set-default`
| | | get `` (aliases: describe, show) | Show detailed information about a specific service in this project | | | list | List all the services in this project | | | update-password `` | Update the master password for a service | | db | | Database operations and management | | | connect `` | Connect to a service | | | connection-string `` | Retrieve the connection string for a service | | | save-password `` | Save the password for a service | | | test-connection `` | Test the connectivity to a service | | mcp | | Manage the Tiger Model Context Protocol Server for AI Assistant integration | | | install `[client]` | Install and configure Tiger Model Context Protocol Server for a specific client (`claude-code`, `cursor`, `windsurf`, or other). If no client is specified, you'll be prompted to select one interactively | | | start | Start the Tiger Model Context Protocol Server. This is the same as `tiger mcp start stdio` | | | start stdio | Start the Tiger Model Context Protocol Server with stdio transport (default) | | | start http | Start the Tiger Model Context Protocol Server with HTTP transport. Includes flags: `--port` (default: `8080`), `--host` (default: `localhost`) | ## Global flags You can use the following global flags with Tiger CLI: | Flag | Default | Description | |-------------------------------|-------------------|-----------------------------------------------------------------------------| | `--analytics` | `true` | Set to `false` to disable usage analytics | | `--color ` | `true` | Set to `false` to disable colored output | | `--config-dir` string | `.config/tiger` | Set the directory that holds `config.yaml` | | `--debug` | No debugging | Enable debug logging | | `--help` | - | Print help about the current command. For example, `tiger service --help` | | `--password-storage` string | keyring | Set the password storage method. Options are `keyring`, `pgpass`, or `none` | | `--service-id` string | - | Set the Tiger Cloud service to manage | | ` --skip-update-check ` | - | Do not check if a new version of Tiger CLI is available| ## Configuration parameters By default, Tiger CLI stores your configuration in `~/.config/tiger/config.yaml`. The name of these variables matches the flags you use to update them. However, you can override them using the following environmental variables: - **Configuration parameters** - `TIGER_CONFIG_DIR`: path to configuration directory (default: `~/.config/tiger`) - `TIGER_API_URL`: Tiger REST API base endpoint (default: https://console.cloud.timescale.com/public/api/v1) - `TIGER_CONSOLE_URL`: URL to Tiger Cloud Console (default: https://console.cloud.timescale.com) - `TIGER_GATEWAY_URL`: URL to the Tiger Cloud Console gateway (default: https://console.cloud.timescale.com/api) - `TIGER_DOCS_MCP`: enable/disable docs MCP proxy (default: `true`) - `TIGER_DOCS_MCP_URL`: URL to the Tiger MCP Server for Tiger Data docs (default: https://mcp.tigerdata.com/docs) - `TIGER_SERVICE_ID`: ID for the service updated when you call CLI commands - `TIGER_ANALYTICS`: enable or disable analytics (default: `true`) - `TIGER_PASSWORD_STORAGE`: password storage method (keyring, pgpass, or none) - `TIGER_DEBUG`: enable/disable debug logging (default: `false`) - `TIGER_COLOR`: set to `false` to disable colored output (default: `true`) - **Authentication parameters** To authenticate without using the interactive login, either: - Set the following parameters with your [client credentials][rest-api-credentials], then `login`: ```shell TIGER_PUBLIC_KEY= TIGER_SECRET_KEY= TIGER_PROJECT_ID=\ tiger auth login ``` - Add your [client credentials][rest-api-credentials] to the `login` command: ```shell tiger auth login --public-key= --secret-key= --project-id= ``` [Tiger REST API][rest-api-reference] is a comprehensive RESTful API you use to manage Tiger Cloud resources including VPCs, services, and read replicas. This page shows you how to set up secure authentication for the Tiger REST API and create your first service. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Data account][create-account]. * Install [curl][curl]. ## Configure secure authentication Tiger REST API uses HTTP Basic Authentication with access keys and secret keys. All API requests must include proper authentication headers. 1. **Set up API credentials** 1. In Tiger Cloud Console [copy your project ID][get-project-id] and store it securely using an environment variable: ```bash export TIGERDATA_PROJECT_ID="your-project-id" ``` 1. In Tiger Cloud Console [create your client credentials][create-client-credentials] and store them securely using environment variables: ```bash export TIGERDATA_ACCESS_KEY="Public key" export TIGERDATA_SECRET_KEY="Secret key" ``` 1. **Configure the API endpoint** Set the base URL in your environment: ```bash export API_BASE_URL="https://console.cloud.timescale.com/public/api/v1" ``` 1. **Test your authenticated connection to Tiger REST API by listing the services in the current Tiger Cloud project** ```bash curl -X GET "${API_BASE_URL}/projects/${TIGERDATA_PROJECT_ID}/services" \ -u "${TIGERDATA_ACCESS_KEY}:${TIGERDATA_SECRET_KEY}" \ -H "Content-Type: application/json" ``` This call returns something like: - No services: ```terminaloutput []% ``` - One or more services: ```terminaloutput [{"service_id":"tgrservice","project_id":"tgrproject","name":"tiger-eon", "region_code":"us-east-1","service_type":"TIMESCALEDB", "created":"2025-10-20T12:21:28.216172Z","paused":false,"status":"READY", "resources":[{"id":"104977","spec":{"cpu_millis":500,"memory_gbs":2,"volume_type":""}}], "metadata":{"environment":"DEV"}, "endpoint":{"host":"tgrservice.tgrproject.tsdb.cloud.timescale.com","port":11111}}] ``` ## Create your first Tiger Cloud service Create a new service using the Tiger REST API: 1. **Create a service using the POST endpoint** ```bash curl -X POST "${API_BASE_URL}/projects/${TIGERDATA_PROJECT_ID}/services" \ -u "${TIGERDATA_ACCESS_KEY}:${TIGERDATA_SECRET_KEY}" \ -H "Content-Type: application/json" \ -d '{ "name": "my-first-service", "addons": ["time-series"], "region_code": "us-east-1", "replica_count": 1, "cpu_millis": "1000", "memory_gbs": "4" }' ``` Tiger Cloud creates a Development environment for you. That is, no delete protection, high-availability, spooling or read replication. You see something like: ```terminaloutput {"service_id":"tgrservice","project_id":"tgrproject","name":"my-first-service", "region_code":"us-east-1","service_type":"TIMESCALEDB", "created":"2025-10-20T22:29:33.052075713Z","paused":false,"status":"QUEUED", "resources":[{"id":"105120","spec":{"cpu_millis":1000,"memory_gbs":4,"volume_type":""}}], "metadata":{"environment":"PROD"}, "endpoint":{"host":"tgrservice.tgrproject.tsdb.cloud.timescale.com","port":00001}, "initial_password":"notTellingYou", "ha_replicas":{"sync_replica_count":0,"replica_count":1}} ``` 1. Save `service_id` from the response to a variable: ```bash # Extract service_id from the JSON response export SERVICE_ID="service_id-from-response" ``` 1. **Check the configuration for the service** ```bash curl -X GET "${API_BASE_URL}/projects/${TIGERDATA_PROJECT_ID}/services/${SERVICE_ID}" \ -u "${TIGERDATA_ACCESS_KEY}:${TIGERDATA_SECRET_KEY}" \ -H "Content-Type: application/json" ``` You see something like: ```terminaloutput {"service_id":"tgrservice","project_id":"tgrproject","name":"my-first-service", "region_code":"us-east-1","service_type":"TIMESCALEDB", "created":"2025-10-20T22:29:33.052075Z","paused":false,"status":"READY", "resources":[{"id":"105120","spec":{"cpu_millis":1000,"memory_gbs":4,"volume_type":""}}], "metadata":{"environment":"DEV"}, "endpoint":{"host":"tgrservice.tgrproject.tsdb.cloud.timescale.com","port":11111}, "ha_replicas":{"sync_replica_count":0,"replica_count":1}} ``` And that is it, you are ready to use the [Tiger REST API][rest-api-reference] to manage your services in Tiger Cloud. ## Security best practices Follow these security guidelines when working with the Tiger REST API: - **Credential management** - Store API credentials as environment variables, not in code - Use credential rotation policies for production environments - Never commit credentials to version control systems - **Network security** - Use HTTPS endpoints exclusively for API communication - Implement proper certificate validation in your HTTP clients - **Data protection** - Use secure storage for service connection strings and passwords - Implement proper backup and recovery procedures for created services - Follow data residency requirements for your region ===== PAGE: https://docs.tigerdata.com/getting-started/run-queries-from-console/ ===== # Run your queries from Tiger Cloud Console As Tiger Cloud is based on Postgres, you can use lots of [different tools][integrations] to connect to your service and interact with your data. In Tiger Cloud Console you can use the following ways to run SQL queries against your service: - [Data mode][run-popsql]: a rich experience powered by PopSQL. You can write queries with autocomplete, save them in folders, share them, create charts/dashboards, and much more. - [SQL Assistant in the data mode][sql-assistant]: write, fix, and organize SQL faster and more accurately. - [SQL editor in the ops mode][run-sqleditor]: a simple SQL editor in the ops mode that lets you run ad-hoc ephemeral queries. This is useful for quick one-off tasks like creating an index on a small table or inspecting `pg_stat_statements`. If you prefer the command line to the ops mode SQL editor in Tiger Cloud Console, use [psql][install-psql]. ## Data mode You use the data mode in Tiger Cloud Console to write queries, visualize data, and share your results. ![Tiger Cloud Console data mode](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-data-mode.png) This feature is not available under the Free pricing plan. Available features are: - **Real-time collaboration**: work with your team directly in the data mode query editor with live presence and multiple cursors. - **[Schema browser][schema-browser]**: understand the structure of your service and see usage data on tables and columns. - **[SQL Assistant][sql-assistant]**: write, fix, and organize SQL faster and more accurately using AI. - **Autocomplete**: get suggestions as you type your queries. - **[Version history][version-history]**: access previous versions of a query from the built-in revision history, or connect to a git repo. - **[Charts][charts]**: visualize data from inside the UI rather than switch to Sheets or Excel. - **[Schedules][schedules]**: automatically refresh queries and dashboards to create push alerts. - **[Query variables][query-variables]**: use Liquid to parameterize your queries or use `if` statements. - **Cross-platform support**: work from [Tiger Cloud Console][portal-data-mode] or download the [desktop app][popsql-desktop] for macOS, Windows, and Linux. - **Easy connection**: connect to Tiger Cloud, Postgres, Redshift, Snowflake, BigQuery, MySQL, SQL Server, [and more][popsql-connections]. ### Connect to your Tiger Cloud service in the data mode To connect to a service: 1. **Check your service is running correctly** In [Tiger Cloud Console][services-portal], check that your service is marked as `Running`: ![Check Tiger Cloud service is running](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-services-view.png) 1. **Connect to your service** In the [data mode][portal-data-mode] in Tiger Cloud Console, select a service in the connection drop-down: ![Select a connection](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-data-mode-connection-dropdown.png) 1. **Run a test query** Type `SELECT CURRENT_DATE;` in `Scratchpad` and click `Run`: ![Run a simple query](https://assets.timescale.com/docs/images/tiger-cloud-console/run-query-in-scratchpad-tiger-console.png) Quick recap. You: - Manage your services in the [ops mode in Tiger Cloud Console][portal-ops-mode] - Manage your data in the [data mode in Tiger Cloud Console][portal-data-mode] - Store configuration and security information in your config file. Now you have used the data mode in Tiger Cloud Console, see how to easily do the following: - [Write a query][write-query] - [Share a query with your teammates][share-query] - [Create a chart from your data][create-chart] - [Create a dashboard of multiple query results][create-dashboard] - [Create schedules for your queries][create-schedule] ### Data mode FAQ #### What if my service is within a vpc? If your Tiger Cloud service runs inside a VPC, do one of the following to enable access for the PopSQL desktop app: - Use PopSQL's [bridge connector][bridge-connector]. - Use an SSH tunnel: when you configure the connection in PopSQL, under `Advanced Options` enable `Connect over SSH`. - Add PopSQL's static IPs (`23.20.131.72, 54.211.234.135`) to your allowlist. #### What happens if another member of my Tiger Cloud project uses the data mode? The number of data mode seats you are allocated depends on your [pricing plan][pricing-plan-features]. #### Will using the data mode affect the performance of my Tiger Cloud service? There are a few factors to consider: 1. What instance size is your service? 1. How many users are running queries? 1. How computationally intensive are the queries? If you have a small number of users running performant SQL queries against a service with sufficient resources, then there should be no degradation to performance. However, if you have a large number of users running queries, or if the queries are computationally expensive, best practice is to create a [read replica][read-replica] and send analytical queries there. If you'd like to prevent write operations such as insert or update, instead of using the `tsdbadmin` user, create a read-only user for your service and use that in the data mode. ## SQL Assistant SQL Assistant in [Tiger Cloud Console][portal-data-mode] is a chat-like interface that harnesses the power of AI to help you write, fix, and organize SQL faster and more accurately. Ask SQL Assistant to change existing queries, write new ones from scratch, debug error messages, optimize for query performance, add comments, improve readability—and really, get answers to any questions you can think of. This feature is not available under the Free pricing plan. ### Key capabilities SQL Assistant offers a range of features to improve your SQL workflow, including: - **Real-time help**: SQL Assistant provides in-context help for writing and understanding SQL. Use it to: - **Understand functions**: need to know how functions like `LAG()` or `ROW_NUMBER()` work? SQL Assistant explains it with examples. - **Interpret complex queries**: SQL Assistant breaks down dense queries, giving you a clear view of each part. - **Error resolution**: SQL Assistant diagnoses errors as they happen, you can resolve issues without leaving your editor. Features include: - **Error debugging**: if your query fails, SQL Assistant identifies the issue and suggests a fix. - **Performance tuning**: for slow queries, SQL Assistant provides optimization suggestions to improve performance immediately. - **Query organization**: to keep your query library organized, and help your team understand the purpose of each query, SQL Assistant automatically adds titles and summaries to your queries. - **Agent mode**: to get results with minimal involvement from you, SQL Assistant autopilots through complex tasks and troubleshoots its own problems. No need to go step by step, analyze errors, and try out solutions. Simply turn on the agent mode in the LLM picker and watch SQL Assistant do all the work for you. Recommended for use when your database connection is configured with read-only credentials. ### Supported LLMs SQL Assistant supports a large number of LLMs, including: - GPT-4o mini - GPT-4o - GPT-4.1 nano - GPT-4.1 mini - GPT-4.1 - o4-mini (low) - o4-mini - o4-mini (high) - o3 (low) - o3 - o3 (high) - Claude 3.5 Haiku - Claud 3.7 Sonnet - Claud 3.7 Sonnet (extended thinking) - Llama 3.3 70B Versatile - Llama 3.3 70B Instruct - Llama 3.1 405B Instruct - Llama 4 Scout - Llama 4 Maverick - DeepSeek R1 Distill - Llama 3.3. 70B - DeepSeek R1 - Gemini 2.0 Flash - Sonnet 4 - Sonnet 4 (extended thinking) - Opus 4 - Opus 4 (extended thinking) Choose the LLM based on the particular task at hand. For simpler tasks, try the smaller and faster models like Gemini Flash, Haiku, or o4-mini. For more complex tasks, try the larger reasoning models like Claude Sonnet, Gemini Pro, or o3. We provide a description of each model to help you decide. ### Limitations to keep in mind For best results with SQL Assistant: * **Schema awareness**: SQL Assistant references schema data but may need extra context in complex environments. Specify tables, columns, or joins as needed. * **Business logic**: SQL Assistant does not inherently know specific business terms such as active user. Define these terms clearly to improve results. ### Security, privacy, and data usage Security and privacy is prioritized in Tiger Cloud Console. In [data mode][portal-data-mode], project members manage SQL Assistant settings under [`User name` > `Settings` > `SQL Assistant`][sql-editor-settings]. ![SQL assistant settings](https://assets.timescale.com/docs/images/tiger-console-sql-editor-preferences.png) SQL Assistant settings are: * **Opt-in features**: all AI features are off by default. Only [members][project-members] of your Tiger Cloud project can enable them. * **Data protection**: your data remains private as SQL Assistant operates with strict security protocols. To provide AI support, Tiger Cloud Console may share your currently open SQL document, some basic metadata about your database, and portions of your database schema. By default, Tiger Cloud Console **does not include** any data from query results, but you can opt in to include this context to improve the results. * **Sample data**: to give the LLM more context so you have better SQL suggestions, enable sample data sharing in the SQL Assistant preferences. * **Telemetry**: to improve SQL Assistant, Tiger Data collects telemetry and usage data, including prompts, responses, and query metadata. ## Ops mode SQL editor SQL editor is an integrated secure UI that you use to run queries and see the results for a Tiger Cloud service. ![Tiger Cloud Console SQL editor](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-ops-mode-sql-editor.png) To enable or disable SQL editor in your service, click `Operations` > `Service management`, then update the setting for SQL editor. To use SQL editor: 1. **Open SQL editor from Tiger Cloud Console** In the [ops mode][portal-ops-mode] in Tiger Cloud Console, select a service, then click `SQL editor`. ![Check service is running](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-ops-mode-sql-editor-empty.png) 1. **Run a test query** Type `SELECT CURRENT_DATE;` in the UI and click `Run`. The results appear in the lower window: ![Run a simple query](https://assets.timescale.com/docs/images/tiger-cloud-console/run-a-query-in-tiger-ops-mode-sql-editor.png) ## Cloud SQL editor licenses * **SQL editor in the ops mode**: free for anyone with a [Tiger Data account][create-cloud-account]. * **Data mode**: the number of seats you are allocated depends on your [pricing plan][pricing-plan-features]. [SQL Assistant][sql-assistant] is currently free for all users. In the future, limits or paid options may be introduced as we work to build the best experience. * **PopSQL standalone**: there is a free plan available to everyone, as well as paid plans. See [PopSQL Pricing][popsql-pricing] for full details. What next? [Try the key features offered by Tiger Data][try-timescale-features], see the [tutorials][tutorials], interact with the data in your Tiger Cloud service using [your favorite programming language][connect-with-code], integrate your Tiger Cloud service with a range of [third-party tools][integrations], plain old [Use Tiger Data products][use-timescale], or dive into the [API reference][use-the-api]. ===== PAGE: https://docs.tigerdata.com/use-timescale/hypertables/ ===== # Hypertables Tiger Cloud supercharges your real-time analytics by letting you run complex queries continuously, with near-zero latency. Under the hood, this is achieved by using hypertables—Postgres tables that automatically partition your time-series data by time and optionally by other dimensions. When you run a query, Tiger Cloud identifies the correct partition, called chunk, and runs the query on it, instead of going through the entire table. ![Hypertable structure](https://assets.timescale.com/docs/images/hypertable.png) Hypertables offer the following benefits: - **Efficient data management with [automated partitioning by time][chunk-size]**: Tiger Cloud splits your data into chunks that hold data from a specific time range. For example, one day or one week. You can configure this range to better suit your needs. - **Better performance with [strategic indexing][hypertable-indexes]**: an index on time in the descending order is automatically created when you create a hypertable. More indexes are created on the chunk level, to optimize performance. You can create additional indexes, including unique indexes, on the columns you need. - **Faster queries with [chunk skipping][chunk-skipping]**: Tiger Cloud skips the chunks that are irrelevant in the context of your query, dramatically reducing the time and resources needed to fetch results. Even more—you can enable chunk skipping on non-partitioning columns. - **Advanced data analysis with [hyperfunctions][hyperfunctions]**: Tiger Cloud enables you to efficiently process, aggregate, and analyze significant volumes of data while maintaining high performance. To top it all, there is no added complexity—you interact with hypertables in the same way as you would with regular Postgres tables. All the optimization magic happens behind the scenes. Inheritance is not supported for hypertables and may lead to unexpected behavior. ## Partition by time Each hypertable is partitioned into child hypertables called chunks. Each chunk is assigned a range of time, and only contains data from that range. ### Time partitioning Typically, you partition hypertables on columns that hold time values. [Best practice is to use `timestamptz`][timestamps-best-practice] column type. However, you can also partition on `date`, `integer`, `timestamp` and [UUIDv7][uuidv7_functions] types. By default, each hypertable chunk holds data for 7 days. You can change this to better suit your needs. For example, if you set `chunk_interval` to 1 day, each chunk stores data for a single day. TimescaleDB divides time into potential chunk ranges, based on the `chunk_interval`. Each hypertable chunk holds data for a specific time range only. When you insert data from a time range that doesn't yet have a chunk, TimescaleDB automatically creates a chunk to store it. In practice, this means that the start time of your earliest chunk does not necessarily equal the earliest timestamp in your hypertable. Instead, there might be a time gap between the start time and the earliest timestamp. This doesn't affect your usual interactions with your hypertable, but might affect the number of chunks you see when inspecting it. ## Best practices for scaling and partitioning Best practices for maintaining a high performance when scaling include: - Limit the number of hypertables in your service; having tens of thousands of hypertables is not recommended. - Choose a strategic chunk size. Chunk size affects insert and query performance. You want a chunk small enough to fit into memory so you can insert and query recent data without reading from disk. However, having too many small and sparsely filled chunks can affect query planning time and compression. The more chunks in the system, the slower that process becomes, even more so when all those chunks are part of a single hypertable. Postgres builds the index on the fly during ingestion. That means that to build a new entry on the index, a significant portion of the index needs to be traversed during every row insertion. When the index does not fit into memory, it is constantly flushed to disk and read back. This wastes IO resources which would otherwise be used for writing the heap/WAL data to disk. The default chunk interval is 7 days. However, best practice is to set `chunk_interval` so that prior to processing, the indexes for chunks currently being ingested into fit within 25% of main memory. For example, on a system with 64 GB of memory, if index growth is approximately 2 GB per day, a 1-week chunk interval is appropriate. If index growth is around 10 GB per day, use a 1-day interval. You set `chunk_interval` when you [create a hypertable][hypertable-create-table], or by calling [`set_chunk_time_interval`][chunk_interval] on an existing hypertable. For a detailed analysis of how to optimize your chunk sizes, see the [blog post on chunk time intervals][blog-chunk-time]. To learn how to view and set your chunk time intervals, see [Optimize hypertable chunk intervals][change-chunk-intervals]. ## Hypertable indexes By default, indexes are automatically created when you create a hypertable. The default index is on time, descending. You can prevent index creation by setting the `create_default_indexes` option to `false`. Hypertables have some restrictions on unique constraints and indexes. If you want a unique index on a hypertable, it must include all the partitioning columns for the table. To learn more, see [Enforce constraints with unique indexes on hypertables][hypertables-and-unique-indexes]. You can prevent index creation by setting the `create_default_indexes` option to `false`. ## Partition by dimension Partitioning on time is the most common use case for hypertable, but it may not be enough for your needs. For example, you may need to scan for the latest readings that match a certain condition without locking a critical hypertable. The use case for a partitioning dimension is a multi-tenant setup. You isolate the tenants using the `tenant_id` space partition. However, you must perform extensive testing to ensure this works as expected, and there is a strong risk of partition explosion. You add a partitioning dimension at the same time as you create the hypertable, when the table is empty. The good news is that although you select the number of partitions at creation time, as your data grows you can change the number of partitions later and improve query performance. Changing the number of partitions only affects chunks created after the change, not existing chunks. To set the number of partitions for a partitioning dimension, call `set_number_partitions`. For example: 1. **Create the hypertable with the 1-day interval chunk interval** ```sql CREATE TABLE conditions( "time" timestamptz not null, device_id integer, temperature float ) WITH( timescaledb.hypertable, timescaledb.partition_column='time', timescaledb.chunk_interval='1 day' ); ``` 1. **Add a hash partition on a non-time column** ```sql select * from add_dimension('conditions', by_hash('device_id', 3)); ``` Now use your hypertable as usual, but you can also ingest and query efficiently by the `device_id` column. 1. **Change the number of partitions as you data grows** ```sql select set_number_partitions('conditions', 5, 'device_id'); ``` ===== PAGE: https://docs.tigerdata.com/use-timescale/hypercore/ ===== # Hypercore Hypercore is a hybrid row-columnar storage engine in TimescaleDB. It is designed specifically for real-time analytics and powered by time-series data. The advantage of hypercore is its ability to seamlessly switch between row-oriented and column-oriented storage, delivering the best of both worlds: ![Hypercore workflow](https://assets.timescale.com/docs/images/hypertable-with-hypercore-enabled.png) Hypercore solves the key challenges in real-time analytics: - High ingest throughput - Low-latency ingestion - Fast query performance - Efficient handling of data updates and late-arriving data - Streamlined data management Hypercore’s hybrid approach combines the benefits of row-oriented and column-oriented formats: - **Fast ingest with rowstore**: new data is initially written to the rowstore, which is optimized for high-speed inserts and updates. This process ensures that real-time applications easily handle rapid streams of incoming data. Mutability—upserts, updates, and deletes happen seamlessly. - **Efficient analytics with columnstore**: as the data **cools** and becomes more suited for analytics, it is automatically converted to the columnstore. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. - **Faster queries on compressed data in columnstore**: in the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries. Combined with [chunk skipping][chunk-skipping], this helps you save on storage costs and keeps your queries operating at lightning speed. - **Fast modification of compressed data in columnstore**: just use SQL to add or modify data in the columnstore. TimescaleDB is optimized for superfast INSERT and UPSERT performance. - **Full mutability with transactional semantics**: regardless of where data is stored, hypercore provides full ACID support. Like in a vanilla Postgres database, inserts and updates to the rowstore and columnstore are always consistent, and available to queries as soon as they are completed. For an in-depth explanation of how hypertables and hypercore work, see the [Data model][data-model]. This section shows the following: * [Optimize your data for real-time analytics][setup-hypercore] * [Improve query and upsert performance using secondary indexes][secondary-indexes] * [Compression methods in hypercore][compression-methods] * [Troubleshooting][troubleshooting] ===== PAGE: https://docs.tigerdata.com/use-timescale/continuous-aggregates/ ===== # Continuous aggregates From real-time dashboards to performance monitoring and historical trend analysis, data aggregation is a must-have for any sort of analytical application. To address this need, TimescaleDB uses continuous aggregates to precompute and store aggregate data for you. Using Postgres [materialized views][postgres-materialized-views], TimescaleDB incrementally refreshes the aggregation query in the background. When you do run the query, only the data that has changed needs to be computed, not the entire dataset. This means you always have the latest aggregate data at your fingertips—and spend as little resources on it, as possible. In this section you: * [Learn about continuous aggregates][about-caggs] to understand how it works before you begin using it. * [Create a continuous aggregate][cagg-create] and query it. * [Create a continuous aggregate on top of another continuous aggregate][cagg-on-cagg]. * [Add refresh policies][cagg-autorefresh] to an existing continuous aggregate. * [Manage time][cagg-time] in your continuous aggregates. * [Drop data][cagg-drop] from your continuous aggregates. * [Manage materialized hypertables][cagg-mat-hypertables]. * [Use real-time aggregates][cagg-realtime]. * [Convert continuous aggregates to the columnstore][cagg-compression]. * [Migrate your continuous aggregates][cagg-migrate] from old to new format. Continuous aggregates created in TimescaleDB v2.7 and later are in the new format, unless explicitly created in the old format. * [Troubleshoot][cagg-tshoot] continuous aggregates. ===== PAGE: https://docs.tigerdata.com/use-timescale/services/ ===== # About Tiger Cloud services Tiger Cloud is the modern Postgres data platform for all your applications. It enhances Postgres to handle time series, events, real-time analytics, and vector search—all in a single database alongside transactional workloads. You get one system that handles live data ingestion, late and out-of-order updates, and low latency queries, with the performance, reliability, and scalability your app needs. Ideal for IoT, crypto, finance, SaaS, and a myriad other domains, Tiger Cloud allows you to build data-heavy, mission-critical apps while retaining the familiarity and reliability of Postgres. A Tiger Cloud service is a single optimised Postgres instance extended with innovations in the database engine and cloud infrastructure to deliver speed without sacrifice. A Tiger Cloud service is 10-1000x faster at scale! It is ideal for applications requiring strong data consistency, complex relationships, and advanced querying capabilities. Get ACID compliance, extensive SQL support, JSON handling, and extensibility through custom functions, data types, and extensions. Each service is associated with a project in Tiger Cloud. Each project can have multiple services. Each user is a [member of one or more projects][rbac]. You create free and standard services in Tiger Cloud Console, depending on your [pricing plan][pricing-plans]. A free service comes at zero cost and gives you limited resources to get to know Tiger Cloud. Once you are ready to try out more advanced features, you can switch to a paid plan and convert your free service to a standard one. ![Tiger Cloud pricing plans](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-pricing.svg) The Free pricing plan and services are currently in beta. To the Postgres you know and love, Tiger Cloud adds the following capabilities: - **Standard services**: - _Real-time analytics_: store and query [time-series data][what-is-time-series] at scale for real-time analytics and other use cases. Get faster time-based queries with hypertables, continuous aggregates, and columnar storage. Save money by compressing data into the columnstore, moving cold data to low-cost bottomless storage in Amazon S3, and deleting old data with automated policies. - _AI-focused_: build AI applications from start to scale. Get fast and accurate similarity search with the pgvector and pgvectorscale extensions. - _Hybrid applications_: get a full set of tools to develop applications that combine time-based data and AI. All standard Tiger Cloud services include the tooling you expect for production and developer environments: [live migration][live-migration], [automatic backups and PITR][automatic-backups], [high availability][high-availability], [read replicas][readreplica], [data forking][operations-forking], [connection pooling][connection-pooling], [tiered storage][data-tiering], [usage-based storage][how-plans-work], secure in-Tiger Cloud Console [SQL editing][in-console-editors], service [metrics][metrics] and [insights][insights], [streamlined maintenance][maintain-upgrade], and much more. Tiger Cloud continuously monitors your services and prevents common Postgres out-of-memory crashes. - **Free services**: _Postgres with TimescaleDB and vector extensions_ Free services offer limited resources and a basic feature scope, perfect to get to know Tiger Cloud in a development environment. ## Learn more about Tiger Cloud Read about Tiger Cloud features in the documentation: * Create your first [hypertable][hypertable-info]. * Run your first query using [time_bucket()][time-bucket-info]. * Trying more advanced time-series functions, starting with [gap filling][gap-filling-info] or [real-time aggregates][aggregates-info]. ## Keep testing during your free trial You're now on your way to a great start with Tiger Cloud. You have an unthrottled, 30-day free trial with Tiger Cloud to continue to test your use case. Before the end of your trial, make sure you add your credit card information. This ensures a smooth transition after your trial period concludes. If you have any questions, you can [join our community Slack group][slack-info] or [contact us][contact-timescale] directly. ## Advanced configuration Tiger Cloud is a versatile hosting service that provides a growing list of advanced features for your Postgres and time-series data workloads. For more information about customizing your database configuration, see the [Configuration section][configuration]. The [TimescaleDB Terraform provider](https://registry.terraform.io/providers/timescale/timescale/latest/) provides configuration management resources for Tiger Cloud. You can use it to create, rename, resize, delete, and import services. For more information about the supported service configurations and operations, see the [Terraform provider documentation](https://registry.terraform.io/providers/timescale/timescale/latest/docs). ===== PAGE: https://docs.tigerdata.com/use-timescale/write-data/ ===== # Write data Writing data in TimescaleDB works the same way as writing data to regular Postgres. You can add and modify data in both regular tables and hypertables using `INSERT`, `UPDATE`, and `DELETE` statements. * [Learn about writing data in TimescaleDB][about-writing-data] * [Insert data][insert] into hypertables * [Update data][update] in hypertables * [Upsert data][upsert] into hypertables * [Delete data][delete] from hypertables For more information about using third-party tools to write data into TimescaleDB, see the [Ingest data from other sources][ingest-data] section. ===== PAGE: https://docs.tigerdata.com/use-timescale/query-data/ ===== # Query data Hypertables in TimescaleDB are Postgres tables. That means you can query them with standard SQL commands. * [About querying data][about-querying-data] * [Select data with `SELECT`][selecting-data] * [Get faster `DISTINCT` queries with SkipScan][skipscan] * [Perform advanced analytic queries][advanced-analytics] ===== PAGE: https://docs.tigerdata.com/use-timescale/time-buckets/ ===== # Time buckets Time buckets enable you to aggregate data in [hypertables][create-hypertable] by time interval. For example, you can group data into 5-minute, 1-hour, and 3-day buckets to calculate summary values. * [Learn how time buckets work][about-time-buckets] * [Use time buckets][use-time-buckets] to aggregate data ===== PAGE: https://docs.tigerdata.com/use-timescale/schema-management/ ===== # Schema management A database schema defines how the tables and indexes in your database are organized. Using a schema that is appropriate for your workload can result in significant performance improvements. * [Learn about schema management][about-schema] to understand how it works before you begin using it. * [Learn about indexing][about-indexing] to understand how it works before you begin using it. * [Learn about tablespaces][about-tablespaces] to understand how they work before you begin using them. * [Learn about constraints][about-constraints] to understand how they work before you begin using them. * [Alter a hypertable][schema-alter] to modify your schema. * [Create an index][schema-indexing] to speed up your queries. * [Create triggers][schema-triggers] to propagate your schema changes to chunks. * [Use JSON and JSONB][schema-json] for semi-structured data. * [Query external databases][foreign-data-wrappers] with foreign data wrappers. * [Troubleshoot][troubleshoot-schemas] your schemas. ===== PAGE: https://docs.tigerdata.com/use-timescale/configuration/ ===== # Configuration By default, Tiger Cloud uses the standard Postgres server configuration settings. However, in some cases, these settings are not appropriate, especially if you have larger servers that use more hardware resources such as CPU, memory, and storage. This section contains information about tuning your Tiger Cloud service. ===== PAGE: https://docs.tigerdata.com/use-timescale/alerting/ ===== # Alerting Early issue detecting and prevention, ensuring high availability, and performance optimization are only a few of the reasons why alerting plays a major role for modern applications, databases, and services. There are a variety of different alerting solutions you can use in conjunction with Tiger Cloud that are part of the Postgres ecosystem. Regardless of whether you are creating custom alerts embedded in your applications, or using third-party alerting tools to monitor event data across your organization, there are a wide selection of tools available. ## Grafana Grafana is a great way to visualize your analytical queries, and it has a first-class integration with Tiger Data products. Beyond data visualization, Grafana also provides alerting functionality to keep you notified of anomalies. Within Grafana, you can [define alert rules][define alert rules] which are time-based thresholds for your dashboard data (for example, "Average CPU usage greater than 80 percent for 5 minutes"). When those alert rules are triggered, Grafana sends a message via the chosen notification channel. Grafana provides integration with webhooks, email and more than a dozen external services including Slack and PagerDuty. To get started, first download and install [Grafana][Grafana-install]. Next, add a new [Postgres data source][PostgreSQL datasource] that points to your Tiger Cloud service. This data source was built by Tiger Data engineers, and it is designed to take advantage of the database's time-series capabilities. From there, proceed to your dashboard and set up alert rules as described above. Alerting is only available in Grafana v4.0 and later. ## Other alerting tools Tiger Cloud works with a variety of alerting tools within the Postgres ecosystem. Users can use these tools to set up notifications about meaningful events that signify notable changes to the system. Some popular alerting tools that work with Tiger Cloud include: * [DataDog][datadog-install] * [Nagios][nagios-install] * [Zabbix][zabbix-install] See the [integration guides][integration-docs] for details. ===== PAGE: https://docs.tigerdata.com/use-timescale/data-retention/ ===== # Data retention Data retention helps you save on storage costs by deleting old data. You can combine data retention with [continuous aggregates][caggs] to downsample your data. In this section: * [Learn about data retention][about-data-retention] before you start using it * [Learn about data retention with continuous aggregates][retention-with-caggs] for downsampling data * Create a [data retention policy][retention-policy] * [Manually drop chunks][manually-drop] of data * [Troubleshoot] data retention ===== PAGE: https://docs.tigerdata.com/use-timescale/data-tiering/ ===== # Storage in Tiger Tiered storage is a [hierarchical storage management architecture][hierarchical-storage] for [real-time analytics][create-service] services you create in [Tiger Cloud](https://console.cloud.timescale.com/). Engineered for infinite low-cost scalability, tiered storage consists of the following: * **High-performance storage tier**: stores the most recent and frequently queried data. This tier comes in two types, standard and enhanced, and provides you with up to 64 TB of storage and 32,000 IOPS. * **Object storage tier**: stores data that is rarely accessed and has lower performance requirements. For example, old data for auditing or reporting purposes over long periods of time, even forever. The object storage tier is low-cost and bottomless. No matter the tier your data is stored in, you can [query it when you need it][querying-tiered-data]. Tiger Cloud seamlessly accesses the correct storage tier and generates the response. You [define tiering policies][creating-data-tiering-policy] that automatically migrate data from the high-performance storage tier to the object tier as it ages. You use [retention policies][add-retention-policies] to remove very old data from the object storage tier. With tiered storage you don't need an ETL process, infrastructure changes, or custom-built, bespoke solutions to offload data to secondary storage and fetch it back in when needed. Kick back and relax, we do the work for you. In this section, you: * [Learn more about storage tiers][about-data-tiering]: understand how the tiers are built and how they differ. * [Manage storage and tiering][enabling-data-tiering]: configure high-performance storage, object storage, and data tiering. * [Query tiered data][querying-tiered-data]: query the data in the object storage. * [Learn about replicas and forks with tiered data][replicas-and-forks]: understand how tiered storage works with forks and replicas of your service. ===== PAGE: https://docs.tigerdata.com/use-timescale/metrics-logging/ ===== # Metrics and logging Find metrics and logs for your services in Tiger Cloud Console, or integrate with third-party monitoring services: * [Monitor][monitor] your services in Tiger Cloud Console. * Export metrics to [Datadog][datadog]. * Export metrics to [Amazon Cloudwatch][cloudwatch]. * Export metrics to [Prometheus][prometheus]. ===== PAGE: https://docs.tigerdata.com/use-timescale/ha-replicas/ ===== # High availability and read replication In Tiger Cloud, replicas are copies of the primary data instance in a Tiger Cloud service. If your primary becomes unavailable, Tiger Cloud automatically fails over to your HA replica. The replication strategies offered by Tiger Cloud are: - [High Availability(HA) replicas][ha-replica]: significantly reduce the risk of downtime and data loss due to system failure, and enable services to avoid downtime during routine maintenance. - [Read replicas][read-replica]: safely scale a service to power your read-intensive apps and business intelligence tooling and remove the load from the primary data instance. - For MST, see [Failover in Managed Service for TimescaleDB][mst-failover]. For self-hosted TimescaleDB, see [Replication and high availability][self-hosted-ha]. ## Rapid recovery By default, all services have rapid recovery enabled. Because compute and storage are handled separately in Tiger Cloud, services recover quickly from compute failures, but usually need a full recovery from backup for storage failures. - **Compute failure**: the most common cause of database failure. Compute failures can be caused by hardware failing, or through things like unoptimized queries, causing increased load that maxes out the CPU usage. In these cases, data on disk is unaffected and only the compute and memory needs replacing. Tiger Cloud recovery immediately provisions new compute infrastructure for the service and mounts the existing storage to the new node. Any WAL that was in memory then replays. This process typically only takes thirty seconds. However, depending on the amount of WAL that needs replaying this may take up to twenty minutes. Even in the worst-case scenario, Tiger Cloud recovery is an order of magnitude faster than a standard recovery from backup. - **Storage failure**: in the rare occurrence of disk failure, Tiger Cloud automatically [performs a full recovery from backup][backup-recovery]. If CPU usage for a service runs high for long periods of time, issues such as WAL archiving getting queued behind other processes can occur. This can cause a failure and could result in a larger data loss. To avoid data loss, services are monitored for this kind of scenario. ===== PAGE: https://docs.tigerdata.com/use-timescale/upgrades/ ===== # Maintenance and upgrades Tiger Cloud offers managed database services that provide a stable and reliable environment for your applications. Each service is based on a specific version of the Postgres database and the TimescaleDB extension. To ensure that you benefit from the latest features, performance and security improvements, it is important that your Tiger Cloud service is kept up to date with the latest versions of TimescaleDB and Postgres. Tiger Cloud has the following upgrade policies: * **Minor software upgrades**: handled automatically, you do not need to do anything. Upgrades are performed on your Tiger Cloud service during a maintenance window that you [define to suit your workload][define-maintenance-window]. You can also [manually upgrade TimescaleDB][minor-manual-upgrade]. * **Critical security upgrades**: installed outside normal maintenance windows when necessary, and sometimes require a short outage. Downtime is usually between 30 seconds and 5 minutes. Tiger Data aims to notify you by email if downtime is required, so that you can plan accordingly. However, in some cases this is not possible. * **Major upgrades**: such as a new version of Postgres are performed [manually by you][manual-upgrade], or [automatically by Tiger Cloud][automatic-upgrade]. After a maintenance upgrade, the DNS name remains the same. However, the IP address often changes. ## Minor software upgrades If you do not [manually upgrade TimescaleDB][minor-manual-upgrade] for non-critical upgrades, Tiger Cloud performs upgrades automatically in the next available maintenance window. The upgrade is first applied to your services tagged `#dev`, and three weeks later to those tagged `#prod`. [Subscribe][subscribe] to get an email notification before your `#prod` services are upgraded. You can upgrade your `#prod` services manually sooner, if needed. Most upgrades that occur during your maintenance windows do not require any downtime. This means that there is no service outage during the upgrade. However, all connections and transactions in progress during the upgrade are reset. Usually, the service connection is automatically restored after the reset. Some minor upgrades do require some downtime. This is usually between 30 seconds and 5 minutes. If downtime is required for an upgrade, Tiger Data endeavors to notify you by email ahead of the upgrade. However, in some cases, we might not be able to do so. Best practice is to [schedule your maintenance window][define-maintenance-window] so that any downtime disrupts your workloads as little as possible and [minimize downtime with replicas][minimize-downtime]. If there are no pending upgrades available during a regular maintenance window, no changes are performed. To track the status of maintenance events, see the Tiger Cloud [status page][status-page]. ### Minimize downtime with replicas Maintenance upgrades require up to two automatic failovers. Each failover takes less than a few seconds. Tiger Cloud services with [high-availability replicas and read replicas][replicas-docs] require minimal write downtime during maintenance, read-only queries keep working throughout. During a maintenance event, services with replicas perform maintenance on each node independently. When maintenance is complete on the primary node, it is restarted: - If the restart takes more than a minute, a replica node is promoted to primary, given that the replica has no replication lag. Maintenance now proceeds on the newly promoted replica, following the same sequence. If the newly promoted replica takes more than a minute to restart, the former primary is promoted back. In total, the process may result in up to two minutes of write downtime and two failover events. - If the maintenance on the primary node is completed within a minute and it comes back online, the replica remains the replica. ### Manually upgrade TimescaleDB for non-critical upgrades Non-critical upgrades are available before the upgrade is performed automatically by Tiger Cloud. To upgrade TimescaleDB manually: 1. **Connect to your service** In [Tiger Cloud Console][cloud-login], select the service you want to upgrade. 1. **Upgrade TimescaleDB** Either: - Click `SQL Editor`, then run `ALTEREXTENSION timescaledb UPDATE`. - Click `⋮`, then `Pause` and `Resume` the service. Upgrading to a newer version of Postgres allows you to take advantage of new features, enhancements, and security fixes. It also ensures that you are using a version of Postgres that's compatible with the newest version of TimescaleDB, allowing you to take advantage of everything it has to offer. For more information about feature changes between versions, see the [Tiger Cloud release notes][timescale-changelog], [supported systems][supported-systems], and the [Postgres release notes][postgres-relnotes]. ## Deprecations To ensure you benefit from the latest features, optimal performance, enhanced security, and full compatibility with TimescaleDB, Tiger Cloud supports a defined set of Postgres major versions. To reduce the maintenance burden and continue providing a high-quality managed experience, as Postgres and TimescaleDB evolve, Tiger Data periodically deprecates older Postgres versions. Tiger Data provides advance notification to allow you ample time to plan and perform your upgrade. The timeline deprecation is as follows: - **Deprecation notice period begins**: you receive email notification of the deprecation and the timeline for the upgrade. - **Customer self-service upgrade window**: best practice is to [manually upgrade to a new Postgres version][manual-upgrade] in this time. - **Automatic upgrade deadline**: Tiger Cloud performs an [automatic upgrade][automatic-upgrade] of your service. ## Manually upgrade Postgres for a service Upgrading to a newer version of Postgres enables you to take advantage of new features, enhancements, and security fixes. It also ensures that you are using a version of Postgres that's compatible with the newest version of TimescaleDB. For a smooth upgrade experience, make sure you: * **Plan ahead**: upgrades cause downtime, so ideally perform an upgrade during a low traffic time. * **Run a test upgrade**: [fork your service][operations-forking], then try out the upgrade on the fork before running it on your production system. This gives you a good idea of what happens during the upgrade, and how long it might take. * **Keep a copy of your service**: if you're worried about losing your data, [fork your service][operations-forking] without upgrading, and keep this duplicate of your service. To reduce cost, you can immediately pause this fork and only pay for storage until you are comfortable deleting it after the upgrade is complete. Tiger Cloud services with replicas cannot be upgraded. To upgrade a service with a replica, you must first delete the replica and then upgrade the service. The following table shows you the compatible versions of Postgres and TimescaleDB. | TimescaleDB version |Postgres 17|Postgres 16|Postgres 15|Postgres 14|Postgres 13|Postgres 12|Postgres 11|Postgres 10| |-----------------------|-|-|-|-|-|-|-|-| | 2.22.x |✅|✅|✅|❌|❌|❌|❌|❌|❌| | 2.21.x |✅|✅|✅|❌|❌|❌|❌|❌|❌| | 2.20.x |✅|✅|✅|❌|❌|❌|❌|❌|❌| | 2.17 - 2.19 |✅|✅|✅|✅|❌|❌|❌|❌|❌| | 2.16.x |❌|✅|✅|✅|❌|❌|❌|❌|❌|❌| | 2.13 - 2.15 |❌|✅|✅|✅|✅|❌|❌|❌|❌| | 2.12.x |❌|❌|✅|✅|✅|❌|❌|❌|❌| | 2.10.x |❌|❌|✅|✅|✅|✅|❌|❌|❌| | 2.5 - 2.9 |❌|❌|❌|✅|✅|✅|❌|❌|❌| | 2.4 |❌|❌|❌|❌|✅|✅|❌|❌|❌| | 2.1 - 2.3 |❌|❌|❌|❌|✅|✅|✅|❌|❌| | 2.0 |❌|❌|❌|❌|❌|✅|✅|❌|❌ | 1.7 |❌|❌|❌|❌|❌|✅|✅|✅|✅| We recommend not using TimescaleDB with Postgres 17.1, 16.5, 15.9, 14.14, 13.17, 12.21. These minor versions [introduced a breaking binary interface change][postgres-breaking-change] that, once identified, was reverted in subsequent minor Postgres versions 17.2, 16.6, 15.10, 14.15, 13.18, and 12.22. When you build from source, best practice is to build with Postgres 17.2, 16.6, etc and higher. Users of [Tiger Cloud](https://console.cloud.timescale.com/) and platform packages for Linux, Windows, MacOS, Docker, and Kubernetes are unaffected. For more information about feature changes between versions, see the [Postgres release notes][postgres-relnotes] and [TimescaleDB release notes][timescale-relnotes]. Your Tiger Cloud service is unavailable until the upgrade is complete. This can take up to 20 minutes. Best practice is to test on a fork first, so you can estimate how long the upgrade will take. To upgrade your service to a newer version of Postgres: 1. **Connect to your service** In [Tiger Cloud Console][cloud-login], select the service you want to upgrade. 1. **Disable high-availability replicas** 1. Click `Operations` > `High Availability`, then click `Change configuaration`. 1. Select `Non-production (No replica)`, then click `Change configuration`. 1. **Disable read replicas** 1. Click `Operations` > `Read scaling`, then click the trash icon next to all replica sets. 1. **Upgrade Postgres** 1. Click `Operations` > `Service Upgrades`. 1. Click `Upgrade service`, then confirm that you are ready to start the upgrade. Your Tiger Cloud service is unavailable until the upgrade is complete. This normally takes up to 20 minutes. However, it can take longer if you have a large or complex service. When the upgrade is finished, your service automatically resumes normal operations. If the upgrade is unsuccessful, the service returns to the state it was in before you started the upgrade. 1. **Enable high-availability replicas and replace your read replicas** ## Automatic Postgres upgrades for a service If you do not manually upgrade your services within the [customer self-service upgrade window][deprecation-window], Tiger Cloud performs an automatic upgrade. Automatic upgrades can result in downtime, best practice is to [manually upgrade your services][manual-upgrade] during a low-traffic period for your application. During an automatic upgrade: 1. Any configured [high-availability replicas][hareplica] or [read replicas][readreplica] are temporarily removed. 1. The primary service is upgraded. 1. High-availability replicas and read replicas are added back to the service. ## Define your maintenance window When you are considering your maintenance window schedule, best practice is to choose a day and time that usually has very low activity, such as during the early hours of the morning, or over the weekend. This helps minimize the impact of a short service interruption. Alternatively, you might prefer to have your maintenance window occur during office hours, so that you can monitor your system during the upgrade. To change your maintenance window: 1. **Connect to your service** In [Tiger Cloud Console][cloud-login], select the service you want to manage. 1. **Set your maintenance window** 1. Click `Operations` > `Environment`, then click `Change maintenance window`. ![Maintenance and upgrades](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-console-maintenance-upgrades.png) 1. Select the maintence window start time, then click `Apply`. Maintenance windows can run for up to four hours. ===== PAGE: https://docs.tigerdata.com/use-timescale/extensions/ ===== # Postgres extensions The following Postgres extensions are installed with each Tiger Cloud service: - [Tiger Data extensions][timescale-extensions] - [Postgres built-in extensions][built-ins] - [Third-party extensions][third-party] ## Tiger Data extensions | Extension | Description | Enabled by default | |---------------------------------------------|--------------------------------------------|-----------------------------------------------------------------------| | [pgai][pgai] | Helper functions for AI workflows | For [AI-focused][services] services | | [pg_textsearch][pg_textsearch] | [BM25][bm25-wiki]-based full-text search | Currently early access. For development and staging environments only | | [pgvector][pgvector] | Vector similarity search for Postgres | For [AI-focused][services] services | | [pgvectorscale][pgvectorscale] | Advanced indexing for vector data | For [AI-focused][services] services | | [timescaledb_toolkit][timescaledb-toolkit] | TimescaleDB Toolkit | For [Real-time analytics][services] services | | [timescaledb][timescaledb] | TimescaleDB | For all services | ## Postgres built-in extensions | Extension | Description | Enabled by default | |------------------------------------------|------------------------------------------------------------------------|-------------------------| | [autoinc][autoinc] | Functions for autoincrementing fields | - | | [amcheck][amcheck] | Functions for verifying relation integrity | - | | [bloom][bloom] | Bloom access method - signature file-based index | - | | [bool_plperl][bool-plper] | Transform between bool and plperl | - | | [btree_gin][btree-gin] | Support for indexing common datatypes in GIN | - | | [btree_gist][btree-gist] | Support for indexing common datatypes in GiST | - | | [citext][citext] | Data type for case-insensitive character strings | - | | [cube][cube] | Data type for multidimensional cubes | - | | [dict_int][dict-int] | Text search dictionary template for integers | - | | [dict_xsyn][dict-xsyn] | Text search dictionary template for extended synonym processing | - | | [earthdistance][earthdistance] | Calculate great-circle distances on the surface of the Earth | - | | [fuzzystrmatch][fuzzystrmatch] | Determine similarities and distance between strings | - | | [hstore][hstore] | Data type for storing sets of (key, value) pairs | - | | [hstore_plperl][hstore] | Transform between hstore and plperl | - | | [insert_username][insert-username] | Functions for tracking who changed a table | - | | [intagg][intagg] | Integer aggregator and enumerator (obsolete) | - | | [intarray][intarray] | Functions, operators, and index support for 1-D arrays of integers | - | | [isn][isn] | Data types for international product numbering standards | - | | [jsonb_plperl][jsonb-plperl] | Transform between jsonb and plperl | - | | [lo][lo] | Large object maintenance | - | | [ltree][ltree] | Data type for hierarchical tree-like structures | - | | [moddatetime][moddatetime] | Functions for tracking last modification time | - | | [old_snapshot][old-snapshot] | Utilities in support of `old_snapshot_threshold` | - | | [pgcrypto][pgcrypto] | Cryptographic functions | - | | [pgrowlocks][pgrowlocks] | Show row-level locking information | - | | [pgstattuple][pgstattuple] | Obtain tuple-level statistics | - | | [pg_freespacemap][pg-freespacemap] | Examine the free space map (FSM) | - | | [pg_prewarm][pg-prewarm] | Prewarm relation data | - | | [pg_stat_statements][pg-stat-statements] | Track execution statistics of all SQL statements executed | For all services | | [pg_trgm][pg-trgm] | Text similarity measurement and index searching based on trigrams | - | | [pg_visibility][pg-visibility] | Examine the visibility map (VM) and page-level visibility info | - | | [plperl][plperl] | PL/Perl procedural language | - | | [plpgsql][plpgsql] | SQL procedural language | For all services | | [postgres_fdw][postgres-fdw] | Foreign data wrappers | For all services | | [refint][refint] | Functions for implementing referential integrity (obsolete) | - | | [seg][seg] | Data type for representing line segments or floating-point intervals | - | | [sslinfo][sslinfo] | Information about SSL certificates | - | | [tablefunc][tablefunc] | Functions that manipulate whole tables, including crosstab | - | | [tcn][tcn] | Trigger change notifications | - | | [tsm_system_rows][tsm-system-rows] | `TABLESAMPLE` method which accepts the number of rows as a limit | - | | [tsm_system_time][tsm-system-time] | `TABLESAMPLE` method which accepts the time in milliseconds as a limit | - | | [unaccent][unaccent] | Text search dictionary that removes accents | - | | [uuid-ossp][uuid-ossp] | Generate universally unique identifiers (UUIDs) | - | ## Third-party extensions | Extension | Description | Enabled by default | |--------------------------------------------------|-------------------------------------------------------------------------|------------------------------------------------------| | [h3][h3] | H3 bindings for Postgres | - | | [pgaudit][pgaudit] | Detailed session and/or object audit logging | - | | [pgpcre][pgpcre] | Perl-compatible RegEx | - | | [pg_cron][pgcron] | SQL commands that you can schedule and run directly inside the database | [Contact us](mailto:support@tigerdata.com) to enable | | [pg_repack][pgrepack] | Table reorganization in Postgres with minimal locks | - | | [pgrouting][pgrouting] | Geospatial routing functionality | - | | [postgis][postgis] | PostGIS geometry and geography spatial types and functions | - | | [postgis_raster][postgis-raster] | PostGIS raster types and functions | - | | [postgis_sfcgal][postgis-sfcgal] | PostGIS SFCGAL functions | - | | [postgis_tiger_geocoder][postgis-tiger-geocoder] | PostGIS Tiger Cloud geocoder and reverse geocoder | - | | [postgis_topology][postgis-topology] | PostGIS topology spatial types and functions | - | | [unit][unit] | SI units for Postgres | - | ===== PAGE: https://docs.tigerdata.com/use-timescale/backup-restore/ ===== # Back up and recover your Tiger Cloud services Tiger Cloud provides comprehensive backup and recovery solutions to protect your data, including automatic daily backups, cross-region protection, and point-in-time recovery. ## Automatic backups Tiger Cloud automatically handles backup for your Tiger Cloud services using the `pgBackRest` tool. You don't need to perform backups manually. What's more, with [cross-region backup][cross-region], you are protected when an entire AWS region goes down. Tiger Cloud automatically creates one full backup every week, and incremental backups every day in the same region as your service. Additionally, all [Write-Ahead Log (WAL)][wal] files are retained back to the oldest full backup. This means that you always have a full backup available for the current and previous week: ![Backup in Tiger](https://assets.timescale.com/docs/images/database-backup-recovery.png) On [Scale and Performance][pricing-and-account-management] pricing plans, you can check the list of backups for the previous 14 days in Tiger Cloud Console. To do so, select your service, then click `Operations` > `Backup and restore` > `Backup history`. In the event of a storage failure, a service automatically recovers from a backup to the point of failure. If the whole availability zone goes down, your Tiger Cloud services are recovered in a different zone. In the event of a user error, you can [create a point-in-time recovery fork][create-fork]. ## Enable cross-region backup For added reliability, you can enable cross-region backup. This protects your data when an entire AWS region goes down. In this case, you have two identical backups of your service at any time, but one of them is in a different AWS region. Cross-region backups are updated daily and weekly in the same way as a regular backup. You can have one cross-region backup for a service. You enable cross-region backup when you create a service, or configure it for an existing service in Tiger Cloud Console: 1. In [Console][console], select your service and click `Operations` > `Backup & restore`. 1. In `Cross-region backup`, select the region in the dropdown and click `Enable backup`. ![Create cross-region backup](https://assets.timescale.com/docs/images/tiger-cloud-console/create-cross-region-backup-in-tiger-console.png) You can now see the backup, its region, and creation date in a list. You can have one cross-region backup per service. To change the region of your backup: 1. In [Console][console], select your service and click `Operations` > `Backup & restore`. 1. Click the trash icon next to the existing backup to disable it. ![Disable cross-region backup](https://assets.timescale.com/docs/images/tiger-cloud-console/cross-region-backup-list-in-tiger-console.png) 1. Create a new backup in a different region. ## Create a point-in-time recovery fork To recover your service from a destructive or unwanted action, create a point-in-time recovery fork. You can recover a service to any point within the period [defined by your pricing plan][pricing-and-account-management]. The provision time for the recovery fork is typically less than twenty minutes, but can take longer depending on the amount of WAL to be replayed. The original service stays untouched to avoid losing data created since the time of recovery. All tiered data remains recoverable during the PITR period. When restoring to any point-in-time recovery fork, your service contains all data that existed at that moment - whether it was stored in high-performance or low-cost storage. When you restore a recovery fork: - Data restored from a PITR point is placed into high-performance storage - The tiered data, as of that point in time, remains in tiered storage To avoid paying for compute for the recovery fork and the original service, pause the original to only pay storage costs. You initiate a point-in-time recovery from a same-region or cross-region backup in Tiger Cloud Console: 1. In [Tiger Cloud Console][console], from the `Services` list, ensure the service you want to recover has a status of `Running` or `Paused`. 1. Navigate to `Operations` > `Service management` and click `Create recovery fork`. 1. Select the recovery point, ensuring the correct time zone (UTC offset). 1. Configure the fork. ![Create recovery fork](https://assets.timescale.com/docs/images/tiger-cloud-console/create-recovery-fork-tiger-console.png) You can configure the compute resources, add an HA replica, tag your fork, and add a connection pooler. Best practice is to match the same configuration you had at the point you want to recover to. 1. Confirm by clicking `Create recovery fork`. A fork of the service is created. The recovered service shows in `Services` with a label specifying which service it has been forked from. 1. Update the connection strings in your app Since the point-in-time recovery is done in a fork, to migrate your application to the point of recovery, change the connection strings in your application to use the fork. [Contact us](mailto:support@tigerdata.com), and we will assist in recovering your service. ## Create a service fork To manage development forks: 1. **Install Tiger CLI** Use the terminal to install the CLI: ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.deb.sh | sudo os=any dist=any bash sudo apt-get install tiger-cli ``` ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.deb.sh | sudo os=any dist=any bash sudo apt-get install tiger-cli ``` ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.rpm.sh | sudo os=rpm_any dist=rpm_any bash sudo yum install tiger-cli ``` ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.rpm.sh | sudo os=rpm_any dist=rpm_any bash sudo yum install tiger-cli ``` ```shell brew install --cask timescale/tap/tiger-cli ``` ```shell curl -fsSL https://cli.tigerdata.com | sh ``` 1. **Set up API credentials** 1. Log Tiger CLI into your Tiger Data account: ```shell tiger auth login ``` Tiger CLI opens Console in your browser. Log in, then click `Authorize`. You can have a maximum of 10 active client credentials. If you get an error, open [credentials][rest-api-credentials] and delete an unused credential. 1. Select a Tiger Cloud project: ```terminaloutput Auth URL is: https://console.cloud.timescale.com/oauth/authorize?client_id=lotsOfURLstuff Opening browser for authentication... Select a project: > 1. Tiger Project (tgrproject) 2. YourCompany (Company wide project) (cpnproject) 3. YourCompany Department (dptproject) Use ↑/↓ arrows or number keys to navigate, enter to select, q to quit ``` If only one project is associated with your account, this step is not shown. Where possible, Tiger CLI stores your authentication information in the system keychain/credential manager. If that fails, the credentials are stored in `~/.config/tiger/credentials` with restricted file permissions (600). By default, Tiger CLI stores your configuration in `~/.config/tiger/config.yaml`. 1. **Test your authenticated connection to Tiger Cloud by listing services** ```bash tiger service list ``` This call returns something like: - No services: ```terminaloutput 🏜️ No services found! Your project is looking a bit empty. 🚀 Ready to get started? Create your first service with: tiger service create ``` - One or more services: ```terminaloutput ┌────────────┬─────────────────────┬────────┬─────────────┬──────────────┬──────────────────┐ │ SERVICE ID │ NAME │ STATUS │ TYPE │ REGION │ CREATED │ ├────────────┼─────────────────────┼────────┼─────────────┼──────────────┼──────────────────┤ │ tgrservice │ tiger-agent-service │ READY │ TIMESCALEDB │ eu-central-1 │ 2025-09-25 16:09 │ └────────────┴─────────────────────┴────────┴─────────────┴──────────────┴──────────────────┘ ``` 1. **Fork the service** ```shell tiger service fork tgrservice --now --no-wait --name bob ``` By default a fork matches the resource of the parent Tiger Cloud services. For paid plans specify `--cpu` and/or `--memory` for dedicated resources. You see something like: ```terminaloutput 🍴 Forking service 'tgrservice' to create 'bob' at current state... ✅ Fork request accepted! 📋 New Service ID: 🔐 Password saved to system keyring for automatic authentication 🎯 Set service '' as default service. ⏳ Service is being forked. Use 'tiger service list' to check status. ┌───────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────┐ │ PROPERTY │ VALUE │ ├───────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Service ID │ │ │ Name │ bob │ │ Status │ │ │ Type │ TIMESCALEDB │ │ Region │ eu-central-1 │ │ CPU │ 0.5 cores (500m) │ │ Memory │ 2 GB │ │ Direct Endpoint │ ..tsdb.cloud.timescale.com: │ │ Created │ 2025-10-08 13:58:07 UTC │ │ Connection String │ postgresql://tsdbadmin@..tsdb.cloud.timescale.com:/tsdb?sslmode=require │ └───────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┘ ``` 1. **When you are done, delete your forked service** 1. Use the CLI to request service delete: ```shell tiger service delete ``` 1. Validate the service delete: ```terminaloutput Are you sure you want to delete service ''? This operation cannot be undone. Type the service ID '' to confirm: ``` You see something like: ```terminaloutput 🗑️ Delete request accepted for service ''. ✅ Service '' has been successfully deleted. ``` ===== PAGE: https://docs.tigerdata.com/use-timescale/fork-services/ ===== # Fork services Modern development is highly iterative. Developers and AI agents need safe spaces to test changes before deploying them to production. Forkable services make this natural and easy. Spin up a branch, run your test, throw it away, or merge it back. A fork is an exact copy of a service at a specific point in time, with its own independent data and configuration, including: - The database data and schema - Configuration - An admin `tsdbadmin` user with a new password Forks are fully independent. Changes to the fork don't affect the parent service. You can query them, run migrations, add indexes, or test new features against the fork without affecting the original service. Forks are a powerful way to share production-scale data safely. Testing, BI and data science teams often need access to real datasets to build models or generate insights. With forkable services, you easily create fast, zero-copy branches of a production service that are isolated from production, but contain all the data needed for analysis. Rapid fork creation dramatically reduces friction getting insights from live data. ## Understand service forks You can use service forks for disaster recovery, CI/CD automation, and testing and development. For example, you can automatically test a major Postgres upgrade on a fork before applying it to your production service. Tiger Cloud offers the following fork strategies: - `now`: create a fresh fork of your database at the current time. Use when: - You need the absolute latest data - Recent changes must be included in the fork - `last-snapshot`: fork from the most recent [automatic backup or snapshot][automatic-backups]. Use when: - You want the fastest possible fork creation - Slightly behind current data is acceptable - `timestamp`: fork from a specific point in time within your [retention period][pricing]. Use when: - Disaster recovery from a known-good state - Investigating issues that occurred at a specific time - Testing "what-if" scenarios from historical data The retention period for point-in-time recovery and forking depends on your [pricing plan][pricing-plan-features]. ### Fork creation speed Fork creation speed depends on your type of service you want to create: - Free: ~30-90 seconds. Uses a Copy-on-Write storage architecture with zero-copy between a fork and the parent. - Paid: varies with the size of your service, typically 5-20+ minutes. Uses tradional storage architecture with backup restore + WAL replay. ### Billing You can fork a free service to a free or a paid service. However, you cannot fork a paid service to a free service. Billing on storage works in the following way: - High-performance storage: - Copy-on-Write: you are only billed for storage for the chunks that diverge from the parent service. - Traditional: you are billed for storage for the whole service. - Object storage tier: - [Tiered data][data-tiering] is shared across forks using copy-on-write and traditional storage: - Chunks in tiered storage are only billed once, regardless of the number of forks - Only new or modified chunks in a fork incur additional costs For details, see [Replicas and forks with tiered data][tiered-forks]. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. ## Manage forks using Tiger CLI To manage development forks: 1. **Install Tiger CLI** Use the terminal to install the CLI: ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.deb.sh | sudo os=any dist=any bash sudo apt-get install tiger-cli ``` ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.deb.sh | sudo os=any dist=any bash sudo apt-get install tiger-cli ``` ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.rpm.sh | sudo os=rpm_any dist=rpm_any bash sudo yum install tiger-cli ``` ```shell curl -s https://packagecloud.io/install/repositories/timescale/tiger-cli/script.rpm.sh | sudo os=rpm_any dist=rpm_any bash sudo yum install tiger-cli ``` ```shell brew install --cask timescale/tap/tiger-cli ``` ```shell curl -fsSL https://cli.tigerdata.com | sh ``` 1. **Set up API credentials** 1. Log Tiger CLI into your Tiger Data account: ```shell tiger auth login ``` Tiger CLI opens Console in your browser. Log in, then click `Authorize`. You can have a maximum of 10 active client credentials. If you get an error, open [credentials][rest-api-credentials] and delete an unused credential. 1. Select a Tiger Cloud project: ```terminaloutput Auth URL is: https://console.cloud.timescale.com/oauth/authorize?client_id=lotsOfURLstuff Opening browser for authentication... Select a project: > 1. Tiger Project (tgrproject) 2. YourCompany (Company wide project) (cpnproject) 3. YourCompany Department (dptproject) Use ↑/↓ arrows or number keys to navigate, enter to select, q to quit ``` If only one project is associated with your account, this step is not shown. Where possible, Tiger CLI stores your authentication information in the system keychain/credential manager. If that fails, the credentials are stored in `~/.config/tiger/credentials` with restricted file permissions (600). By default, Tiger CLI stores your configuration in `~/.config/tiger/config.yaml`. 1. **Test your authenticated connection to Tiger Cloud by listing services** ```bash tiger service list ``` This call returns something like: - No services: ```terminaloutput 🏜️ No services found! Your project is looking a bit empty. 🚀 Ready to get started? Create your first service with: tiger service create ``` - One or more services: ```terminaloutput ┌────────────┬─────────────────────┬────────┬─────────────┬──────────────┬──────────────────┐ │ SERVICE ID │ NAME │ STATUS │ TYPE │ REGION │ CREATED │ ├────────────┼─────────────────────┼────────┼─────────────┼──────────────┼──────────────────┤ │ tgrservice │ tiger-agent-service │ READY │ TIMESCALEDB │ eu-central-1 │ 2025-09-25 16:09 │ └────────────┴─────────────────────┴────────┴─────────────┴──────────────┴──────────────────┘ ``` 1. **Fork the service** ```shell tiger service fork tgrservice --now --no-wait --name bob ``` By default a fork matches the resource of the parent Tiger Cloud services. For paid plans specify `--cpu` and/or `--memory` for dedicated resources. You see something like: ```terminaloutput 🍴 Forking service 'tgrservice' to create 'bob' at current state... ✅ Fork request accepted! 📋 New Service ID: 🔐 Password saved to system keyring for automatic authentication 🎯 Set service '' as default service. ⏳ Service is being forked. Use 'tiger service list' to check status. ┌───────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────┐ │ PROPERTY │ VALUE │ ├───────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Service ID │ │ │ Name │ bob │ │ Status │ │ │ Type │ TIMESCALEDB │ │ Region │ eu-central-1 │ │ CPU │ 0.5 cores (500m) │ │ Memory │ 2 GB │ │ Direct Endpoint │ ..tsdb.cloud.timescale.com: │ │ Created │ 2025-10-08 13:58:07 UTC │ │ Connection String │ postgresql://tsdbadmin@..tsdb.cloud.timescale.com:/tsdb?sslmode=require │ └───────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┘ ``` 1. **When you are done, delete your forked service** 1. Use the CLI to request service delete: ```shell tiger service delete ``` 1. Validate the service delete: ```terminaloutput Are you sure you want to delete service ''? This operation cannot be undone. Type the service ID '' to confirm: ``` You see something like: ```terminaloutput 🗑️ Delete request accepted for service ''. ✅ Service '' has been successfully deleted. ``` ## Manage forks using Console To manage development forks: 1. In [Tiger Cloud Console][console], from the `Services` list, ensure the service you want to recover has a status of `Running` or `Paused`. 1. Navigate to `Operations` > `Service Management` and click `Fork service`. 1. Configure the fork, then click `Fork service`. A fork of the service is created. The forked service shows in `Services` with a label specifying which service it has been forked from. ![See the forked service](https://assets.timescale.com/docs/images/tsc-forked-service.webp) 1. Update the connection strings in your app to use the fork. ## Integrate service forks in your CI/CD pipeline To fork your Tiger Cloud service using GitHub actions: 1. **Store your Tiger Cloud API key as a GitHub Actions secret** 1. In [Tiger Cloud Console][rest-api-credentials], click `Create credentials`. 2. Save the `Public key` and `Secret key` locally, then click `Done`. 1. In your GitHub repository, click `Settings`, open `Secrets and variables`, then click `Actions`. 3. Click `New repository secret`, then set `Name` to `TIGERDATA_API_KEY` 4. Set `Secret` to your Tiger Cloud API key in the following format `:`, then click `Add secret`. 1. **Add the [GitHub Actions Marketplace][github-action] to your workflow YAML files** For example, the following workflow forks a service when a pull request is opened, running tests against the fork, then automatically cleans up. ```yaml name: Test on a service fork on: pull_request jobs: test: runs-on: ubuntu-latest steps: - uses: actions/checkout@v4 - name: Fork Database id: fork uses: timescale/fork-service@v1 with: project_id: ${{ secrets.TIGERDATA_PROJECT_ID }} service_id: ${{ secrets.TIGERDATA_SERVICE_ID }} api_key: ${{ secrets.TIGERDATA_API_KEY }} fork_strategy: last-snapshot cleanup: true name: pr-${{ github.event.pull_request.number }} - name: Run Integration Tests env: DATABASE_URL: postgresql://tsdbadmin:${{ steps.fork.outputs.initial_password }}@${{ steps.fork.outputs.host }}:${{ steps.fork.outputs.port }}/tsdb?sslmode=require run: | npm install npm test - name: Run Migrations env: DATABASE_URL: postgresql://tsdbadmin:${{ steps.fork.outputs.initial_password }}@${{ steps.fork.outputs.host }}:${{ steps.fork.outputs.port }}/tsdb?sslmode=require run: npm run migrate ``` For the full list of inputs, outputs, and configuration options, see the [Tiger Data - Fork Service][github-action] in GitHub marketplace. ===== PAGE: https://docs.tigerdata.com/use-timescale/jobs/ ===== # Jobs in TimescaleDB TimescaleDB natively includes some job-scheduling policies, such as: * [Continuous aggregate policies][caggs] to automatically refresh continuous aggregates * [Hypercore policies][setup-hypercore] to optimize and compress historical data * [Retention policies][retention] to drop historical data * [Reordering policies][reordering] to reorder data within chunks If these don't cover your use case, you can create and schedule custom-defined jobs to run within your database. They help you automate periodic tasks that aren't covered by the native policies. In this section, you see how to: * [Create and manage jobs][create-jobs] * Set up a [generic data retention][generic-retention] policy that applies across all hypertables * Implement [automatic moving of chunks between tablespaces][manage-storage] * Automatically [downsample and compress][downsample-compress] older chunks ===== PAGE: https://docs.tigerdata.com/use-timescale/security/ ===== # Security Learn how Tiger Cloud protects your data and privacy. * Learn about [security in Tiger Cloud][overview] * Restrict access to your [project][console-rbac] * Restrict access to the [data in your service][read-only] * Set up [multifactor][mfa] and [SAML][saml] authentication * Generate multiple [client credentials][client-credentials] instead of using your username and password * Connect with a [stricter SSL mode][ssl] * Secure your services with [VPC peering][vpc-peering] * Connect to your services from any cloud with [AWS Transit Gateway][transit-gateway] * Restrict access with an [IP address allow list][ip-allowlist] ===== PAGE: https://docs.tigerdata.com/use-timescale/limitations/ ===== # Limitations While TimescaleDB generally offers capabilities that go beyond what Postgres offers, there are some limitations to using hypertables. ## Hypertable limitations * Time dimensions (columns) used for partitioning cannot have NULL values. * Unique indexes must include all columns that are partitioning dimensions. * `UPDATE` statements that move values between partitions (chunks) are not supported. This includes upserts (`INSERT ... ON CONFLICT UPDATE`). * Foreign key constraints from a hypertable referencing another hypertable are not supported. ===== PAGE: https://docs.tigerdata.com/use-timescale/tigerlake/ ===== # Integrate data lakes with Tiger Cloud Tiger Lake enables you to build real-time applications alongside efficient data pipeline management within a single system. Tiger Lake unifies the Tiger Cloud operational architecture with data lake architectures. ![Tiger Lake architecture](https://assets.timescale.com/docs/images/tiger-cloud-console/tiger-lake-integration-tiger.svg) Tiger Lake is a native integration enabling synchronization between hypertables and relational tables running in Tiger Cloud services to Iceberg tables running in [Amazon S3 Tables][s3-tables] in your AWS account. Tiger Lake is currently in private beta. Please contact us to request access. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need your [connection details][connection-info]. ## Integrate a data lake with your Tiger Cloud service To connect a Tiger Cloud service to your data lake: 1. **Set the AWS region to host your table bucket** 1. In [AWS CloudFormation][cmc], select the current AWS region at the top-right of the page. 1. Set it to the Region you want to create your table bucket in. **This must match the region your Tiger Cloud service is running in**: if the regions do not match AWS charges you for cross-region data transfer. 1. **Create your CloudFormation stack** 1. Click `Create stack`, then select `With new resources (standard)`. 1. In `Amazon S3 URL`, paste the following URL, then click `Next`. ```http request https://tigerlake.s3.us-east-1.amazonaws.com/tigerlake-connect-cloudformation.yaml ``` 1. In `Specify stack details`, enter the following details, then click `Next`: * `Stack Name`: a name for this CloudFormation stack * `BucketName`: a name for this S3 table bucket * `ProjectID` and `ServiceID`: enter the [connection details][get-project-id] for your Tiger Lake service 1. In `Configure stack options` check `I acknowledge that AWS CloudFormation might create IAM resources`, then click `Next`. 1. In `Review and create`, click `Submit`, then wait for the deployment to complete. AWS deploys your stack and creates the S3 table bucket and IAM role. 1. Click `Outputs`, then copy all four outputs. 1. **Connect your service to the data lake** 1. In [Tiger Cloud Console][services-portal], select the service you want to integrate with AWS S3 Tables, then click `Connectors`. 1. Select the Apache Iceberg connector and supply the: - ARN of the S3Table bucket - ARN of a role with permissions to write to the table bucket Provisioning takes a couple of minutes. 1. **Create your CloudFormation stack** Replace the following values in the command, then run it from the terminal: * `Region`: region of the S3 table bucket * `StackName`: the name for this CloudFormation stack * `BucketName`: the name of the S3 table bucket to create * `ProjectID`: enter your Tiger Cloud service [connection details][get-project-id] * `ServiceID`: enter your Tiger Cloud service [connection details][get-project-id] ```shell aws cloudformation create-stack \ --capabilities CAPABILITY_IAM \ --template-url https://tigerlake.s3.us-east-1.amazonaws.com/tigerlake-connect-cloudformation.yaml \ --region \ --stack-name \ --parameters \ ParameterKey=BucketName,ParameterValue="" \ ParameterKey=ProjectID,ParameterValue="" \ ParameterKey=ServiceID,ParameterValue="" ``` Setting up the integration through Tiger Cloud Console in Tiger Cloud, provides a convenient copy-paste option with the placeholders populated. 1. **Connect your service to the data lake** 1. In [Tiger Cloud Console][services-portal], select the service you want to integrate with AWS S3 Tables, then click `Connectors`. 1. Select the Apache Iceberg connector and supply the: - ARN of the S3Table bucket - ARN of a role with permissions to write to the table bucket Provisioning takes a couple of minutes. 1. **Create a S3 Bucket** 1. Set the AWS region to host your table bucket 1. In [Amazon S3 console][s3-console], select the current AWS region at the top-right of the page. 2. Set it to the Region your you want to create your table bucket in. **This must match the region your Tiger Cloud service is running in**: if the regions do not match AWS charges you for cross-region data transfer. 1. In the left navigation pane, click `Table buckets`, then click `Create table bucket`. 1. Enter `Table bucket name`, then click `Create table bucket`. 1. Copy the `Amazon Resource Name (ARN)` for your table bucket. 1. **Create an ARN role** 1. In [IAM Dashboard][iam-dashboard], click `Roles` then click `Create role` 1. In `Select trusted entity`, click `Custom trust policy`, replace the **Custom trust policy** code block with the following: ```json { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::142548018081:root" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": "/" } } } ] } ``` `"Principal": { "AWS": "arn:aws:iam::123456789012:root" }` does not mean `root` access. This delegates permissions to the entire AWS account, not just the root user. 1. Replace `` and `` with the the [connection details][get-project-id] for your Tiger Lake service, then click `Next`. 1. In `Permissions policies`. click `Next`. 1. In `Role details`, enter `Role name`, then click `Create role`. 1. In `Roles`, select the role you just created, then click `Add Permissions` > `Create inline policy`. 1. Select `JSON` then replace the `Policy editor` code block with the following: ```json { "Version": "2012-10-17", "Statement": [ { "Sid": "BucketOps", "Effect": "Allow", "Action": [ "s3tables:*" ], "Resource": "" }, { "Sid": "BucketTableOps", "Effect": "Allow", "Action": [ "s3tables:*" ], "Resource": "/table/*" } ] } ``` 1. Replace `` with the `Amazon Resource Name (ARN)` for the table bucket you just created. 1. Click `Next`, then give the inline policy a name and click `Create policy`. 1. **Connect your service to the data lake** 1. In [Tiger Cloud Console][services-portal], select the service you want to integrate with AWS S3 Tables, then click `Connectors`. 1. Select the Apache Iceberg connector and supply the: - ARN of the S3Table bucket - ARN of a role with permissions to write to the table bucket Provisioning takes a couple of minutes. ## Stream data from your Tiger Cloud service to your data lake When you start streaming, all data in the table is synchronized to Iceberg. Records are imported in time order, from oldest to youngest. The write throughput is approximately 40.000 records / second. For larger tables, a full import can take some time. For Iceberg to perform update or delete statements, your hypertable or relational table must have a primary key. This includes composite primary keys. To stream data from a Postgres relational table, or a hypertable in your Tiger Cloud service to your data lake, run the following statement: ```sql ALTER TABLE SET ( tigerlake.iceberg_sync = true | false, tigerlake.iceberg_partitionby = '', tigerlake.iceberg_namespace = '', tigerlake.iceberg_table = '' ) ``` * `tigerlake.iceberg_sync`: `boolean`, set to `true` to start streaming, or `false` to stop the stream. A stream **cannot** resume after being stopped. * `tigerlake.iceberg_partitionby`: optional property to define a partition specification in Iceberg. By default the Iceberg table is partitioned as `day()`. This default behavior is only applicable to hypertables. For more information, see [partitioning][partitioning]. * `tigerlake.iceberg_namespace`: optional property to set a namespace, the default is `timescaledb`. * `tigerlake.iceberg_table`: optional property to specify a different table name. If no name is specified the Postgres table name is used. ### Partitioning intervals By default, the partition interval for an Iceberg table is one day(time-column) for a hypertable. Postgres table sync does not enable any partitioning in Iceberg for non-hypertables. You can set it using [tigerlake.iceberg_partitionby][samples]. The following partition intervals and specifications are supported: | Interval | Description | Source types | | ------------- |---------------------------------------------------------------------------| --- | | `hour` | Extract a date or timestamp day, as days from epoch. Epoch is 1970-01-01. | `date`, `timestamp`, `timestamptz` | | `day` | Extract a date or timestamp day, as days from epoch. | `date`, `timestamp`, `timestamptz` | | `month` | Extract a date or timestamp day, as days from epoch. | `date`, `timestamp`, `timestamptz` | | `year` | Extract a date or timestamp day, as days from epoch. | `date`, `timestamp`, `timestamptz` | | `truncate[W]` | Value truncated to width W, see [options][iceberg-truncate-options] | These partitions define the behavior using the [Iceberg partition specification][iceberg-partition-spec]: ### Sample code The following samples show you how to tune data sync from a hypertable or a Postgres relational table to your data lake: - **Sync a hypertable with the default one-day partitioning interval on the `ts_column` column** To start syncing data from a hypertable to your data lake using the default one-day chunk interval as the partitioning scheme to the Iceberg table, run the following statement: ```sql ALTER TABLE my_hypertable SET (tigerlake.iceberg_sync = true); ``` This is equivalent to `day(ts_column)`. - **Specify a custom partitioning scheme for a hypertable** You use the `tigerlake.iceberg_partitionby` property to specify a different partitioning scheme for the Iceberg table at sync start. For example, to enforce an hourly partition scheme from the chunks on `ts_column` on a hypertable, run the following statement: ```sql ALTER TABLE my_hypertable SET ( tigerlake.iceberg_sync = true, tigerlake.iceberg_partitionby = 'hour(ts_column)' ); ``` - **Set the partition to sync relational tables** Postgres relational tables do not forward a partitioning scheme to Iceberg, you must specify the partitioning scheme using `tigerlake.iceberg_partitionby` when you start the sync. For example, for a standard Postgres table to sync to the Iceberg table with daily partitioning , run the following statement: ```sql ALTER TABLE my_postgres_table SET ( tigerlake.iceberg_sync = true, tigerlake.iceberg_partitionby = 'day(timestamp_col)' ); ``` - **Stop sync to an Iceberg table for a hypertable or a Postgres relational table** ```sql ALTER TABLE my_hypertable SET (tigerlake.iceberg_sync = false); ``` - **Update or add the partitioning scheme of an Iceberg table** To change the partitioning scheme of an Iceberg table, you specify the desired partitioning scheme using the `tigerlake.iceberg_partitionby` property. For example. if the `samples` table has an hourly (`hour(ts)`) partition on the `ts` timestamp column, to change to daily partitioning, call the following statement: ```sql ALTER TABLE samples SET (tigerlake.iceberg_partitionby = 'day(ts)'); ``` This statement is also correct for Iceberg tables without a partitioning scheme. When you change the partition, you **do not** have to pause the sync to Iceberg. Apache Iceberg handles the partitioning operation in function of the internal implementation. **Specify a different namespace** By default, tables are created in the the `timescaledb` namespace. To specify a different namespace when you start the sync, use the `tigerlake.iceberg_namespace` property. For example: ```sql ALTER TABLE my_hypertable SET ( tigerlake.iceberg_sync = true, tigerlake.iceberg_namespace = 'my_namespace' ); ``` **Specify a different Iceberg table name** The table name in Iceberg is the same as the source table in Tiger Cloud. Some services do not allow mixed case, or have other constraints for table names. To define a different table name for the Iceberg table at sync start, use the `tigerlake.iceberg_table` property. For example: ```sql ALTER TABLE Mixed_CASE_TableNAME SET ( tigerlake.iceberg_sync = true, tigerlake.iceberg_table = 'my_table_name' ); ``` ## Limitations * Service requires Postgres 17.6 and above is supported. * Consistent ingestion rates of over 30000 records / second can lead to a lost replication slot. Burst can be feathered out over time. * [Amazon S3 Tables Iceberg REST][aws-s3-tables] catalog only is supported. * In order to collect deletes made to data in the columstore, certain columnstore optimizations are disabled for hypertables. * [Direct Compress][direct-compress] is not supported. * The `TRUNCATE` statement is not supported, and does not truncate data in the corresponding Iceberg table. * Data in a hypertable that has been moved to the [low-cost object storage tier][data-tiering] is not synced. * Writing to the same S3 table bucket from multiple services is not supported, bucket-to-service mapping is one-to-one. * Iceberg snapshots are pruned automatically if the amount exceeds 2500. ===== PAGE: https://docs.tigerdata.com/use-timescale/troubleshoot-timescaledb/ ===== # Troubleshooting TimescaleDB If you run into problems when using TimescaleDB, there are a few things that you can do. There are some solutions to common errors in this section as well as ways to output diagnostic information about your setup. If you need more guidance, you can join the community [Slack group][slack] or post an issue on the TimescaleDB [GitHub][github]. ## Common errors ### Error updating TimescaleDB when using a third-party Postgres administration tool The `ALTER EXTENSION timescaledb UPDATE` command must be the first command executed upon connection to a database. Some administration tools execute commands before this, which can disrupt the process. You might need to manually update the database with `psql`. See the [update docs][update-db] for details. ### Log error: could not access file "timescaledb" If your Postgres logs have this error preventing it from starting up, you should double-check that the TimescaleDB files have been installed to the correct location. The installation methods use `pg_config` to get Postgres's location. However, if you have multiple versions of Postgres installed on the same machine, the location `pg_config` points to may not be for the version you expect. To check which version of TimescaleDB is used: ```bash $ pg_config --version PostgreSQL 12.3 ``` If that is the correct version, double-check that the installation path is the one you'd expect. For example, for Postgres 11.0 installed via Homebrew on macOS it should be `/usr/local/Cellar/postgresql/11.0/bin`: ```bash $ pg_config --bindir /usr/local/Cellar/postgresql/11.0/bin ``` If either of those steps is not the version you are expecting, you need to either uninstall the incorrect version of Postgres if you can, or update your `PATH` environmental variable to have the correct path of `pg_config` listed first, that is, by prepending the full path: ```bash export PATH = /usr/local/Cellar/postgresql/11.0/bin:$PATH ``` Then, reinstall TimescaleDB and it should find the correct installation path. ### ERROR: could not access file "timescaledb-\": No such file or directory If the error occurs immediately after updating your version of TimescaleDB and the file mentioned is from the previous version, it is probably due to an incomplete update process. Within the greater Postgres server instance, each database that has TimescaleDB installed needs to be updated with the SQL command `ALTER EXTENSION timescaledb UPDATE;` while connected to that database. Otherwise, the database looks for the previous version of the `timescaledb` files. See [our update docs][update-db] for more info. ### Scheduled jobs stop running Your scheduled jobs might stop running for various reasons. On self-hosted TimescaleDB, you can fix this by restarting background workers: ```sql SELECT _timescaledb_internal.restart_background_workers(); ``` On Tiger Cloud and Managed Service for TimescaleDB, restart background workers by doing one of the following: * Run `SELECT timescaledb_pre_restore()`, followed by `SELECT timescaledb_post_restore()`. * Power the service off and on again. This might cause a downtime of a few minutes while the service restores from backup and replays the write-ahead log. ### Failed to start a background worker You might see this error message in the logs if background workers aren't properly configured: ```bash "": failed to start a background worker ``` To fix this error, make sure that `max_worker_processes`, `max_parallel_workers`, and `timescaledb.max_background_workers` are properly set. `timescaledb.max_background_workers` should equal the number of databases plus the number of concurrent background workers. `max_worker_processes` should equal the sum of `timescaledb.max_background_workers` and `max_parallel_workers`. For more information, see the [worker configuration docs][worker-config]. ### Cannot compress chunk You might see this error message when trying to compress a chunk if the permissions for the compressed hypertable are corrupt. ```sql tsdb=> SELECT compress_chunk('_timescaledb_internal._hyper_65_587239_chunk'); ERROR: role 149910 was concurrently dropped ``` This can be caused if you dropped a user for the hypertable before TimescaleDB 2.5. For this case, the user would be removed from `pg_authid` but not revoked from the compressed table. As a result, the compressed table contains permission items that refer to numerical values rather than existing users (see below for how to find the compressed hypertable from a normal hypertable): ```sql tsdb=> \dp _timescaledb_internal._compressed_hypertable_2 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+--------------+-------+---------------------+-------------------+---------- public | transactions | table | mats=arwdDxt/mats +| | | | | wizard=arwdDxt/mats+| | | | | 149910=r/mats | | (1 row) ``` This means that the `relacl` column of `pg_class` needs to be updated and the offending user removed, but it is not possible to drop a user by numerical value. Instead, you can use the internal function `repair_relation_acls` in the `_timescaledb_function` schema: ```sql tsdb=> CALL _timescaledb_functions.repair_relation_acls(); ``` This requires superuser privileges (since you're modifying the `pg_class` table) and that it removes any user not present in `pg_authid` from *all* tables, so use with caution. The permissions are usually corrupted for the hypertable as well, but not always, so it is better to look at the compressed hypertable to see if the problem is present. To find the compressed hypertable for an associated hypertable (`readings` in this case): ```sql tsdb=> select ht.table_name, tsdb-> (select format('%I.%I', schema_name, table_name)::regclass tsdb-> from _timescaledb_catalog.hypertable tsdb-> where ht.compressed_hypertable_id = id) as compressed_table tsdb-> from _timescaledb_catalog.hypertable ht tsdb-> where table_name = 'readings'; format | format ----------+------------------------------------------------ readings | _timescaledb_internal._compressed_hypertable_2 (1 row) ``` ## Getting more information ### EXPLAINing query performance Postgres's EXPLAIN feature allows users to understand the underlying query plan that Postgres uses to execute a query. There are multiple ways that Postgres can execute a query: for example, a query might be fulfilled using a slow sequence scan or a much more efficient index scan. The choice of plan depends on what indexes are created on the table, the statistics that Postgres has about your data, and various planner settings. The EXPLAIN output let's you know which plan Postgres is choosing for a particular query. Postgres has a [in-depth explanation][using explain] of this feature. To understand the query performance on a hypertable, we suggest first making sure that the planner statistics and table maintenance is up-to-date on the hypertable by running `VACUUM ANALYZE ;`. Then, we suggest running the following version of EXPLAIN: ```sql EXPLAIN (ANALYZE on, BUFFERS on) ; ``` If you suspect that your performance issues are due to slow IOs from disk, you can get even more information by enabling the [track\_io\_timing][track_io_timing] variable with `SET track_io_timing = 'on';` before running the above EXPLAIN. ## Dump TimescaleDB meta data To help when asking for support and reporting bugs, TimescaleDB includes a SQL script that outputs metadata from the internal TimescaleDB tables as well as version information. The script is available in the source distribution in `scripts/` but can also be [downloaded separately][]. To use it, run: ```bash psql [your connect flags] -d your_timescale_db < dump_meta_data.sql > dumpfile.txt ``` and then inspect `dump_file.txt` before sending it together with a bug report or support question. ## Debugging background jobs By default, background workers do not print a lot of information about execution. The reason for this is to avoid writing a lot of debug information to the Postgres log unless necessary. To aid in debugging the background jobs, it is possible to increase the log level of the background workers without having to restart the server by setting the `timescaledb.bgw_log_level` GUC and reloading the configuration. ```sql ALTER SYSTEM SET timescaledb.bgw_log_level TO 'DEBUG1'; SELECT pg_reload_conf(); ``` This variable is set to the value of [`log_min_messages`][log_min_messages] by default, which typically is `WARNING`. If the value of [`log_min_messages`][log_min_messages] is changed in the configuration file, it is used for `timescaledb.bgw_log_level` when starting the workers. Both `ALTER SYSTEM` and `pg_reload_conf()` require superuser privileges by default. Grant `EXECUTE` permissions to `pg_reload_conf()` and `ALTER SYSTEM` privileges to `timescaledb.bgw_log_level` if you want this to work for a non-superuser. Since `ALTER SYSTEM` privileges only exist on Postgres 15 and later, the necessary grants for executing these statements only exist on Tiger Cloud for Postgres 15 or later. ### Debug level 1 The amount of information printed at each level varies between jobs, but the information printed at `DEBUG1` is currently shown below. | Source | Event | |-------------------|------------------------------------------------------| | All jobs | Job exit with runtime information | | All jobs | Job scheduled for fast restart | | Custom job | Execution started | | Recompression job | Recompression job completed | | Reorder job | Chunk reorder completed | | Reorder job | Chunk reorder started | | Scheduler | New jobs discovered and added to scheduled jobs list | | Scheduler | Scheduling job for launch | ### Debug level 2 The amount of information printed at each level varies between jobs, but the information printed at `DEBUG2` is currently shown below. Note that all messages at level `DEBUG1` are also printed when you set the log level to `DEBUG2`, which is [normal Postgres behaviour][log_min_messages]. | Source | Event | |-----------|------------------------------------| | All jobs | Job found in jobs table | | All jobs | Job starting execution | | Scheduler | Scheduled jobs list update started | | Scheduler | Scheduler dispatching job | ### Debug level 5 | Source | Event | |-----------|--------------------------------------| | Scheduler | Scheduled wake up | | Scheduler | Scheduler delayed in dispatching job | ## hypertable chunks are not discoverable by the Postgres CDC service hypertables require special handling for CDC support. Newly created chunks are not not published, which means they are not discoverable by the CDC service. To fix this problem, use the following trigger to automatically publishe newly created chunks on the replication slot. Please be aware that TimescaleDB does not provide full CDC support. ```sql CREATE OR REPLACE FUNCTION ddl_end_trigger_func() RETURNS EVENT_TRIGGER AS $$ DECLARE r RECORD; pub NAME; BEGIN FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP SELECT pubname INTO pub FROM pg_inherits JOIN _timescaledb_catalog.hypertable ht ON inhparent = format('%I.%I', ht.schema_name, ht.table_name)::regclass JOIN pg_publication_tables ON schemaname = ht.schema_name AND tablename = ht.table_name WHERE inhrelid = r.objid; IF NOT pub IS NULL THEN EXECUTE format('ALTER PUBLICATION %s ADD TABLE %s', pub, r.objid::regclass); END IF; END LOOP; END; $$ LANGUAGE plpgsql; CREATE EVENT TRIGGER ddl_end_trigger ON ddl_command_end WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION ddl_end_trigger_func(); ``` ===== PAGE: https://docs.tigerdata.com/use-timescale/compression/ ===== # Compression Old API since [TimescaleDB v2.18.0](https://github.com/timescale/timescaledb/releases/tag/2.18.0) Replaced by hypercore. Time-series data can be compressed to reduce the amount of storage required, and increase the speed of some queries. This is a cornerstone feature of TimescaleDB. When new data is added to your database, it is in the form of uncompressed rows. TimescaleDB uses a built-in job scheduler to convert this data to the form of compressed columns. This occurs across chunks of TimescaleDB hypertables. ===== PAGE: https://docs.tigerdata.com/tutorials/real-time-analytics-transport/ ===== # Analytics on transport and geospatial data Real-time analytics refers to the process of collecting, analyzing, and interpreting data instantly as it is generated. This approach enables you track and monitor activity, and make decisions based on real-time insights on data stored in a Tiger Cloud service. ![Real-time analytics geolocation](https://assets.timescale.com/docs/images/use-case-rta-grafana-heatmap.png) This page shows you how to integrate [Grafana][grafana-docs] with a Tiger Cloud service and make insights based on visualization of data optimized for size and speed in the columnstore. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. * Install and run [self-managed Grafana][grafana-self-managed], or sign up for [Grafana Cloud][grafana-cloud]. ## Optimize time-series data in hypertables Hypertables are Postgres tables in TimescaleDB that automatically partition your time-series data by time. Time-series data represents the way a system, process, or behavior changes over time. Hypertables enable TimescaleDB to work efficiently with time-series data. Each hypertable is made up of child tables called chunks. Each chunk is assigned a range of time, and only contains data from that range. When you run a query, TimescaleDB identifies the correct chunk and runs the query on it, instead of going through the entire table. [Hypercore][hypercore] is the hybrid row-columnar storage engine in TimescaleDB used by hypertables. Traditional databases force a trade-off between fast inserts (row-based storage) and efficient analytics (columnar storage). Hypercore eliminates this trade-off, allowing real-time analytics without sacrificing transactional capabilities. Hypercore dynamically stores data in the most efficient format for its lifecycle: * **Row-based storage for recent data**: the most recent chunk (and possibly more) is always stored in the rowstore, ensuring fast inserts, updates, and low-latency single record queries. Additionally, row-based storage is used as a writethrough for inserts and updates to columnar storage. * **Columnar storage for analytical performance**: chunks are automatically compressed into the columnstore, optimizing storage efficiency and accelerating analytical queries. Unlike traditional columnar databases, hypercore allows data to be inserted or modified at any stage, making it a flexible solution for both high-ingest transactional workloads and real-time analytics—within a single database. Because TimescaleDB is 100% Postgres, you can use all the standard Postgres tables, indexes, stored procedures, and other objects alongside your hypertables. This makes creating and working with hypertables similar to standard Postgres. 1. **Import time-series data into a hypertable** 1. Unzip [nyc_data.tar.gz](https://assets.timescale.com/docs/downloads/nyc_data.tar.gz) to a ``. This test dataset contains historical data from New York's yellow taxi network. To import up to 100GB of data directly from your current Postgres-based database, [migrate with downtime][migrate-with-downtime] using native Postgres tooling. To seamlessly import 100GB-10TB+ of data, use the [live migration][migrate-live] tooling supplied by Tiger Data. To add data from non-Postgres data sources, see [Import and ingest data][data-ingest]. 1. In Terminal, navigate to `` and update the following string with [your connection details][connection-info] to connect to your service. ```bash psql -d "postgres://:@:/?sslmode=require" ``` 1. Create an optimized hypertable for your time-series data: 1. Create a [hypertable][hypertables-section] with [hypercore][hypercore] enabled by default for your time-series data using [CREATE TABLE][hypertable-create-table]. For [efficient queries][secondary-indexes] on data in the columnstore, remember to `segmentby` the column you will use most often to filter your data. In your sql client, run the following command: ```sql CREATE TABLE "rides"( vendor_id TEXT, pickup_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL, dropoff_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL, passenger_count NUMERIC, trip_distance NUMERIC, pickup_longitude NUMERIC, pickup_latitude NUMERIC, rate_code INTEGER, dropoff_longitude NUMERIC, dropoff_latitude NUMERIC, payment_type INTEGER, fare_amount NUMERIC, extra NUMERIC, mta_tax NUMERIC, tip_amount NUMERIC, tolls_amount NUMERIC, improvement_surcharge NUMERIC, total_amount NUMERIC ) WITH ( tsdb.hypertable, tsdb.partition_column='pickup_datetime', tsdb.create_default_indexes=false, tsdb.segmentby='vendor_id', tsdb.orderby='pickup_datetime DESC' ); ``` If you are self-hosting TimescaleDB v2.19.3 and below, create a [Postgres relational table][pg-create-table], then convert it using [create_hypertable][create_hypertable]. You then enable hypercore with a call to [ALTER TABLE][alter_table_hypercore]. 1. Add another dimension to partition your hypertable more efficiently: ```sql SELECT add_dimension('rides', by_hash('payment_type', 2)); ``` 1. Create an index to support efficient queries by vendor, rate code, and passenger count: ```sql CREATE INDEX ON rides (vendor_id, pickup_datetime DESC); CREATE INDEX ON rides (rate_code, pickup_datetime DESC); CREATE INDEX ON rides (passenger_count, pickup_datetime DESC); ``` 1. Create Postgres tables for relational data: 1. Add a table to store the payment types data: ```sql CREATE TABLE IF NOT EXISTS "payment_types"( payment_type INTEGER, description TEXT ); INSERT INTO payment_types(payment_type, description) VALUES (1, 'credit card'), (2, 'cash'), (3, 'no charge'), (4, 'dispute'), (5, 'unknown'), (6, 'voided trip'); ``` 1. Add a table to store the rates data: ```sql CREATE TABLE IF NOT EXISTS "rates"( rate_code INTEGER, description TEXT ); INSERT INTO rates(rate_code, description) VALUES (1, 'standard rate'), (2, 'JFK'), (3, 'Newark'), (4, 'Nassau or Westchester'), (5, 'negotiated fare'), (6, 'group ride'); ``` 1. Upload the dataset to your service ```sql \COPY rides FROM nyc_data_rides.csv CSV; ``` 1. **Have a quick look at your data** You query hypertables in exactly the same way as you would a relational Postgres table. Use one of the following SQL editors to run a query and see the data you uploaded: - **Data mode**: write queries, visualize data, and share your results in [Tiger Cloud Console][portal-data-mode] for all your Tiger Cloud services. - **SQL editor**: write, fix, and organize SQL faster and more accurately in [Tiger Cloud Console][portal-ops-mode] for a Tiger Cloud service. - **psql**: easily run queries on your Tiger Cloud services or self-hosted TimescaleDB deployment from Terminal. For example: - Display the number of rides for each fare type: ```sql SELECT rate_code, COUNT(vendor_id) AS num_trips FROM rides WHERE pickup_datetime < '2016-01-08' GROUP BY rate_code ORDER BY rate_code; ``` This simple query runs in 3 seconds. You see something like: | rate_code | num_trips | |-----------------|-----------| |1 | 2266401| |2 | 54832| |3 | 4126| |4 | 967| |5 | 7193| |6 | 17| |99 | 42| - To select all rides taken in the first week of January 2016, and return the total number of trips taken for each rate code: ```sql SELECT rates.description, COUNT(vendor_id) AS num_trips FROM rides JOIN rates ON rides.rate_code = rates.rate_code WHERE pickup_datetime < '2016-01-08' GROUP BY rates.description ORDER BY LOWER(rates.description); ``` On this large amount of data, this analytical query on data in the rowstore takes about 59 seconds. You see something like: | description | num_trips | |-----------------|-----------| | group ride | 17 | | JFK | 54832 | | Nassau or Westchester | 967 | | negotiated fare | 7193 | | Newark | 4126 | | standard rate | 2266401 | ## Optimize your data for real-time analytics When TimescaleDB converts a chunk to the columnstore, it automatically creates a different schema for your data. TimescaleDB creates and uses custom indexes to incorporate the `segmentby` and `orderby` parameters when you write to and read from the columstore. To increase the speed of your analytical queries by a factor of 10 and reduce storage costs by up to 90%, convert data to the columnstore: 1. **Connect to your Tiger Cloud service** In [Tiger Cloud Console][services-portal] open an [SQL editor][in-console-editors]. The in-Console editors display the query speed. You can also connect to your serviceusing [psql][connect-using-psql]. 1. **Add a policy to convert chunks to the columnstore at a specific time interval** For example, convert data older than 8 days old to the columstore: ``` sql CALL add_columnstore_policy('rides', INTERVAL '8 days'); ``` See [add_columnstore_policy][add_columnstore_policy]. The data you imported for this tutorial is from 2016, it was already added to the columnstore by default. However, you get the idea. To see the space savings in action, follow [Try the key Tiger Data features][try-timescale-features]. Just to hit this one home, by converting cooling data to the columnstore, you have increased the speed of your analytical queries by a factor of 10, and reduced storage by up to 90%. ## Connect Grafana to Tiger Cloud To visualize the results of your queries, enable Grafana to read the data in your service: 1. **Log in to Grafana** In your browser, log in to either: - Self-hosted Grafana: at `http://localhost:3000/`. The default credentials are `admin`, `admin`. - Grafana Cloud: use the URL and credentials you set when you created your account. 1. **Add your service as a data source** 1. Open `Connections` > `Data sources`, then click `Add new data source`. 1. Select `PostgreSQL` from the list. 1. Configure the connection: - `Host URL`, `Database name`, `Username`, and `Password` Configure using your [connection details][connection-info]. `Host URL` is in the format `:`. - `TLS/SSL Mode`: select `require`. - `PostgreSQL options`: enable `TimescaleDB`. - Leave the default setting for all other fields. 1. Click `Save & test`. Grafana checks that your details are set correctly. ## Monitor performance over time A Grafana dashboard represents a view into the performance of a system, and each dashboard consists of one or more panels, which represent information about a specific metric related to that system. To visually monitor the volume of taxi rides over time: 1. **Create the dashboard** 1. On the `Dashboards` page, click `New` and select `New dashboard`. 1. Click `Add visualization`. 1. Select the data source that connects to your Tiger Cloud service. The `Time series` visualization is chosen by default. ![Grafana create dashboard](https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-configure-dashboard.png) 1. In the `Queries` section, select `Code`, then select `Time series` in `Format`. 1. Select the data range for your visualization: the data set is from 2016. Click the date range above the panel and set: - From: ```2016-01-01 01:00:00``` - To: ```2016-01-30 01:00:00``` 1. **Combine TimescaleDB and Grafana functionality to analyze your data** Combine a TimescaleDB [time_bucket][use-time-buckets], with the Grafana `_timefilter()` function to set the `pickup_datetime` column as the filtering range for your visualizations. ```sql SELECT time_bucket('1 day', pickup_datetime) AS "time", COUNT(*) FROM rides WHERE _timeFilter(pickup_datetime) GROUP BY time ORDER BY time; ``` This query groups the results by day and orders them by time. ![Grafana real-time analytics](https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-final-dashboard.png) 1. **Click `Save dashboard`** ## Optimize revenue potential Having all this data is great but how do you use it? Monitoring data is useful to check what has happened, but how can you analyse this information to your advantage? This section explains how to create a visualization that shows how you can maximize potential revenue. ### Set up your data for geospatial queries To add geospatial analysis to your ride count visualization, you need geospatial data to work out which trips originated where. As TimescaleDB is compatible with all Postgres extensions, use [PostGIS][postgis] to slice data by time and location. 1. Connect to your [Tiger Cloud service][in-console-editors] and add the PostGIS extension: ```sql CREATE EXTENSION postgis; ``` 1. Add geometry columns for pick up and drop off locations: ```sql ALTER TABLE rides ADD COLUMN pickup_geom geometry(POINT,2163); ALTER TABLE rides ADD COLUMN dropoff_geom geometry(POINT,2163); ``` 1. Convert the latitude and longitude points into geometry coordinates that work with PostGIS: ```sql UPDATE rides SET pickup_geom = ST_Transform(ST_SetSRID(ST_MakePoint(pickup_longitude,pickup_latitude),4326),2163), dropoff_geom = ST_Transform(ST_SetSRID(ST_MakePoint(dropoff_longitude,dropoff_latitude),4326),2163); ``` This updates 10,906,860 rows of data on both columns, it takes a while. Coffee is your friend. ### Visualize the area where you can make the most money In this section you visualize a query that returns rides longer than 5 miles for trips taken within 2 km of Times Square. The data includes the distance travelled and is `GROUP BY` `trip_distance` and location so that Grafana can plot the data properly. This enables you to see where a taxi driver is most likely to pick up a passenger who wants a longer ride, and make more money. 1. **Create a geolocalization dashboard** 1. In Grafana, create a new dashboard that is connected to your Tiger Cloud service data source with a Geomap visualization. 1. In the `Queries` section, select `Code`, then select the Time series `Format`. ![Real-time analytics geolocation](https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-configure-dashboard.png) 1. To find rides longer than 5 miles in Manhattan, paste the following query: ```sql SELECT time_bucket('5m', rides.pickup_datetime) AS time, rides.trip_distance AS value, rides.pickup_latitude AS latitude, rides.pickup_longitude AS longitude FROM rides WHERE rides.pickup_datetime BETWEEN '2016-01-01T01:41:55.986Z' AND '2016-01-01T07:41:55.986Z' AND ST_Distance(pickup_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163) ) < 2000 GROUP BY time, rides.trip_distance, rides.pickup_latitude, rides.pickup_longitude ORDER BY time LIMIT 500; ``` You see a world map with a dot on New York. 1. Zoom into your map to see the visualization clearly. 1. **Customize the visualization** 1. In the Geomap options, under `Map Layers`, click `+ Add layer` and select `Heatmap`. You now see the areas where a taxi driver is most likely to pick up a passenger who wants a longer ride, and make more money. ![Real-time analytics geolocation](https://assets.timescale.com/docs/images/use-case-rta-grafana-heatmap.png) You have integrated Grafana with a Tiger Cloud service and made insights based on visualization of your data. ===== PAGE: https://docs.tigerdata.com/tutorials/real-time-analytics-energy-consumption/ ===== # Real-time analytics with Tiger Cloud and Grafana Energy providers understand that customers tend to lose patience when there is not enough power for them to complete day-to-day activities. Task one is keeping the lights on. If you are transitioning to renewable energy, it helps to know when you need to produce energy so you can choose a suitable energy source. Real-time analytics refers to the process of collecting, analyzing, and interpreting data instantly as it is generated. This approach enables you to track and monitor activity, make the decisions based on real-time insights on data stored in a Tiger Cloud service and keep those lights on. [Grafana][grafana-docs] is a popular data visualization tool that enables you to create customizable dashboards and effectively monitor your systems and applications. ![Grafana real-time analytics](https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-energy-cagg.png) This page shows you how to integrate Grafana with a Tiger Cloud service and make insights based on visualization of data optimized for size and speed in the columnstore. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. * Install and run [self-managed Grafana][grafana-self-managed], or sign up for [Grafana Cloud][grafana-cloud]. ## Optimize time-series data in hypertables Hypertables are Postgres tables in TimescaleDB that automatically partition your time-series data by time. Time-series data represents the way a system, process, or behavior changes over time. Hypertables enable TimescaleDB to work efficiently with time-series data. Each hypertable is made up of child tables called chunks. Each chunk is assigned a range of time, and only contains data from that range. When you run a query, TimescaleDB identifies the correct chunk and runs the query on it, instead of going through the entire table. [Hypercore][hypercore] is the hybrid row-columnar storage engine in TimescaleDB used by hypertables. Traditional databases force a trade-off between fast inserts (row-based storage) and efficient analytics (columnar storage). Hypercore eliminates this trade-off, allowing real-time analytics without sacrificing transactional capabilities. Hypercore dynamically stores data in the most efficient format for its lifecycle: * **Row-based storage for recent data**: the most recent chunk (and possibly more) is always stored in the rowstore, ensuring fast inserts, updates, and low-latency single record queries. Additionally, row-based storage is used as a writethrough for inserts and updates to columnar storage. * **Columnar storage for analytical performance**: chunks are automatically compressed into the columnstore, optimizing storage efficiency and accelerating analytical queries. Unlike traditional columnar databases, hypercore allows data to be inserted or modified at any stage, making it a flexible solution for both high-ingest transactional workloads and real-time analytics—within a single database. Because TimescaleDB is 100% Postgres, you can use all the standard Postgres tables, indexes, stored procedures, and other objects alongside your hypertables. This makes creating and working with hypertables similar to standard Postgres. 1. **Import time-series data into a hypertable** 1. Unzip [metrics.csv.gz](https://assets.timescale.com/docs/downloads/metrics.csv.gz) to a ``. This test dataset contains energy consumption data. To import up to 100GB of data directly from your current Postgres based database, [migrate with downtime][migrate-with-downtime] using native Postgres tooling. To seamlessly import 100GB-10TB+ of data, use the [live migration][migrate-live] tooling supplied by Tiger Data. To add data from non-Postgres data sources, see [Import and ingest data][data-ingest]. 1. In Terminal, navigate to `` and update the following string with [your connection details][connection-info] to connect to your service. ```bash psql -d "postgres://:@:/?sslmode=require" ``` 1. Create an optimized hypertable for your time-series data: 1. Create a [hypertable][hypertables-section] with [hypercore][hypercore] enabled by default for your time-series data using [CREATE TABLE][hypertable-create-table]. For [efficient queries][secondary-indexes] on data in the columnstore, remember to `segmentby` the column you will use most often to filter your data. In your sql client, run the following command: ```sql CREATE TABLE "metrics"( created timestamp with time zone default now() not null, type_id integer not null, value double precision not null ) WITH ( tsdb.hypertable, tsdb.partition_column='created', tsdb.segmentby = 'type_id', tsdb.orderby = 'created DESC' ); ``` If you are self-hosting TimescaleDB v2.19.3 and below, create a [Postgres relational table][pg-create-table], then convert it using [create_hypertable][create_hypertable]. You then enable hypercore with a call to [ALTER TABLE][alter_table_hypercore]. 1. Upload the dataset to your service ```sql \COPY metrics FROM metrics.csv CSV; ``` 1. **Have a quick look at your data** You query hypertables in exactly the same way as you would a relational Postgres table. Use one of the following SQL editors to run a query and see the data you uploaded: - **Data mode**: write queries, visualize data, and share your results in [Tiger Cloud Console][portal-data-mode] for all your Tiger Cloud services. - **SQL editor**: write, fix, and organize SQL faster and more accurately in [Tiger Cloud Console][portal-ops-mode] for a Tiger Cloud service. - **psql**: easily run queries on your Tiger Cloud services or self-hosted TimescaleDB deployment from Terminal. ```sql SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time", round((last(value, created) - first(value, created)) * 100.) / 100. AS value FROM metrics WHERE type_id = 5 GROUP BY 1; ``` On this amount of data, this query on data in the rowstore takes about 3.6 seconds. You see something like: | Time | value | |------------------------------|-------| | 2023-05-29 22:00:00+00 | 23.1 | | 2023-05-28 22:00:00+00 | 19.5 | | 2023-05-30 22:00:00+00 | 25 | | 2023-05-31 22:00:00+00 | 8.1 | ## Optimize your data for real-time analytics When TimescaleDB converts a chunk to the columnstore, it automatically creates a different schema for your data. TimescaleDB creates and uses custom indexes to incorporate the `segmentby` and `orderby` parameters when you write to and read from the columstore. To increase the speed of your analytical queries by a factor of 10 and reduce storage costs by up to 90%, convert data to the columnstore: 1. **Connect to your Tiger Cloud service** In [Tiger Cloud Console][services-portal] open an [SQL editor][in-console-editors]. The in-Console editors display the query speed. You can also connect to your service using [psql][connect-using-psql]. 1. **Add a policy to convert chunks to the columnstore at a specific time interval** For example, 60 days after the data was added to the table: ``` sql CALL add_columnstore_policy('metrics', INTERVAL '8 days'); ``` See [add_columnstore_policy][add_columnstore_policy]. 1. **Faster analytical queries on data in the columnstore** Now run the analytical query again: ```sql SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time", round((last(value, created) - first(value, created)) * 100.) / 100. AS value FROM metrics WHERE type_id = 5 GROUP BY 1; ``` On this amount of data, this analytical query on data in the columnstore takes about 250ms. Just to hit this one home, by converting cooling data to the columnstore, you have increased the speed of your analytical queries by a factor of 10, and reduced storage by up to 90%. ## Write fast analytical queries Aggregation is a way of combining data to get insights from it. Average, sum, and count are all examples of simple aggregates. However, with large amounts of data aggregation slows things down, quickly. Continuous aggregates are a kind of hypertable that is refreshed automatically in the background as new data is added, or old data is modified. Changes to your dataset are tracked, and the hypertable behind the continuous aggregate is automatically updated in the background. By default, querying continuous aggregates provides you with real-time data. Pre-aggregated data from the materialized view is combined with recent data that hasn't been aggregated yet. This gives you up-to-date results on every query. You create continuous aggregates on uncompressed data in high-performance storage. They continue to work on [data in the columnstore][test-drive-enable-compression] and [rarely accessed data in tiered storage][test-drive-tiered-storage]. You can even create [continuous aggregates on top of your continuous aggregates][hierarchical-caggs]. 1. **Monitor energy consumption on a day-to-day basis** 1. Create a continuous aggregate `kwh_day_by_day` for energy consumption: ```sql CREATE MATERIALIZED VIEW kwh_day_by_day(time, value) with (timescaledb.continuous) as SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time", round((last(value, created) - first(value, created)) * 100.) / 100. AS value FROM metrics WHERE type_id = 5 GROUP BY 1; ``` 1. Add a refresh policy to keep `kwh_day_by_day` up-to-date: ```sql SELECT add_continuous_aggregate_policy('kwh_day_by_day', start_offset => NULL, end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour'); ``` 1. **Monitor energy consumption on an hourly basis** 1. Create a continuous aggregate `kwh_hour_by_hour` for energy consumption: ```sql CREATE MATERIALIZED VIEW kwh_hour_by_hour(time, value) with (timescaledb.continuous) as SELECT time_bucket('01:00:00', metrics.created, 'Europe/Berlin') AS "time", round((last(value, created) - first(value, created)) * 100.) / 100. AS value FROM metrics WHERE type_id = 5 GROUP BY 1; ``` 1. Add a refresh policy to keep the continuous aggregate up-to-date: ```sql SELECT add_continuous_aggregate_policy('kwh_hour_by_hour', start_offset => NULL, end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour'); ``` 1. **Analyze your data** Now you have made continuous aggregates, it could be a good idea to use them to perform analytics on your data. For example, to see how average energy consumption changes during weekdays over the last year, run the following query: ```sql WITH per_day AS ( SELECT time, value FROM kwh_day_by_day WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year' ORDER BY 1 ), daily AS ( SELECT to_char(time, 'Dy') as day, value FROM per_day ), percentile AS ( SELECT day, approx_percentile(0.50, percentile_agg(value)) as value FROM daily GROUP BY 1 ORDER BY 1 ) SELECT d.day, d.ordinal, pd.value FROM unnest(array['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']) WITH ORDINALITY AS d(day, ordinal) LEFT JOIN percentile pd ON lower(pd.day) = lower(d.day); ``` You see something like: | day | ordinal | value | | --- | ------- | ----- | | Mon | 2 | 23.08078714975423 | | Sun | 1 | 19.511430831944395 | | Tue | 3 | 25.003118897837307 | | Wed | 4 | 8.09300571759772 | ## Connect Grafana to Tiger Cloud To visualize the results of your queries, enable Grafana to read the data in your service: 1. **Log in to Grafana** In your browser, log in to either: - Self-hosted Grafana: at `http://localhost:3000/`. The default credentials are `admin`, `admin`. - Grafana Cloud: use the URL and credentials you set when you created your account. 1. **Add your service as a data source** 1. Open `Connections` > `Data sources`, then click `Add new data source`. 1. Select `PostgreSQL` from the list. 1. Configure the connection: - `Host URL`, `Database name`, `Username`, and `Password` Configure using your [connection details][connection-info]. `Host URL` is in the format `:`. - `TLS/SSL Mode`: select `require`. - `PostgreSQL options`: enable `TimescaleDB`. - Leave the default setting for all other fields. 1. Click `Save & test`. Grafana checks that your details are set correctly. ## Visualize energy consumption A Grafana dashboard represents a view into the performance of a system, and each dashboard consists of one or more panels, which represent information about a specific metric related to that system. To visually monitor the volume of energy consumption over time: 1. **Create the dashboard** 1. On the `Dashboards` page, click `New` and select `New dashboard`. 1. Click `Add visualization`, then select the data source that connects to your Tiger Cloud service and the `Bar chart` visualization. ![Grafana create dashboard](https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-configure-dashboard.png) 1. In the `Queries` section, select `Code`, then run the following query based on your continuous aggregate: ```sql WITH per_hour AS ( SELECT time, value FROM kwh_hour_by_hour WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year' ORDER BY 1 ), hourly AS ( SELECT extract(HOUR FROM time) * interval '1 hour' as hour, value FROM per_hour ) SELECT hour, approx_percentile(0.50, percentile_agg(value)) as median, max(value) as maximum FROM hourly GROUP BY 1 ORDER BY 1; ``` This query averages the results for households in a specific time zone by hour and orders them by time. Because you use a continuous aggregate, this data is always correct in real time. ![Grafana real-time analytics](https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-energy-cagg.png) You see that energy consumption is highest in the evening and at breakfast time. You also know that the wind drops off in the evening. This data proves that you need to supply a supplementary power source for peak times, or plan to store energy during the day for peak times. 1. **Click `Save dashboard`** You have integrated Grafana with a Tiger Cloud service and made insights based on visualization of your data. ===== PAGE: https://docs.tigerdata.com/tutorials/simulate-iot-sensor-data/ ===== # Simulate an IoT sensor dataset The Internet of Things (IoT) describes a trend where computing capabilities are embedded into IoT devices. That is, physical objects, ranging from light bulbs to oil wells. Many IoT devices collect sensor data about their environment and generate time-series datasets with relational metadata. It is often necessary to simulate IoT datasets. For example, when you are testing a new system. This tutorial shows how to simulate a basic dataset in your Tiger Cloud service, and then run simple queries on it. To simulate a more advanced dataset, see [Time-series Benchmarking Suite (TSBS)][tsbs]. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. ## Simulate a dataset To simulate a dataset, run the following queries: 1. **Create the `sensors` table**: ```sql CREATE TABLE sensors( id SERIAL PRIMARY KEY, type VARCHAR(50), location VARCHAR(50) ); ``` 1. **Create the `sensor_data` hypertable** ```sql CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER, temperature DOUBLE PRECISION, cpu DOUBLE PRECISION, FOREIGN KEY (sensor_id) REFERENCES sensors (id) ) WITH ( tsdb.hypertable, tsdb.partition_column='time' ); ``` If you are self-hosting TimescaleDB v2.19.3 and below, create a [Postgres relational table][pg-create-table], then convert it using [create_hypertable][create_hypertable]. You then enable hypercore with a call to [ALTER TABLE][alter_table_hypercore]. 1. **Populate the `sensors` table**: ```sql INSERT INTO sensors (type, location) VALUES ('a','floor'), ('a', 'ceiling'), ('b','floor'), ('b', 'ceiling'); ``` 1. **Verify that the sensors have been added correctly**: ```sql SELECT * FROM sensors; ``` Sample output: ``` id | type | location ----+------+---------- 1 | a | floor 2 | a | ceiling 3 | b | floor 4 | b | ceiling (4 rows) ``` 1. **Generate and insert a dataset for all sensors:** ```sql INSERT INTO sensor_data (time, sensor_id, cpu, temperature) SELECT time, sensor_id, random() AS cpu, random()*100 AS temperature FROM generate_series(now() - interval '24 hour', now(), interval '5 minute') AS g1(time), generate_series(1,4,1) AS g2(sensor_id); ``` 1. **Verify the simulated dataset**: ```sql SELECT * FROM sensor_data ORDER BY time; ``` Sample output: ``` time | sensor_id | temperature | cpu -------------------------------+-----------+--------------------+--------------------- 2020-03-31 15:56:25.843575+00 | 1 | 6.86688972637057 | 0.682070567272604 2020-03-31 15:56:40.244287+00 | 2 | 26.589260622859 | 0.229583469685167 2030-03-31 15:56:45.653115+00 | 3 | 79.9925176426768 | 0.457779890391976 2020-03-31 15:56:53.560205+00 | 4 | 24.3201029952615 | 0.641885648947209 2020-03-31 16:01:25.843575+00 | 1 | 33.3203678019345 | 0.0159163917414844 2020-03-31 16:01:40.244287+00 | 2 | 31.2673618085682 | 0.701185956597328 2020-03-31 16:01:45.653115+00 | 3 | 85.2960689924657 | 0.693413889966905 2020-03-31 16:01:53.560205+00 | 4 | 79.4769988860935 | 0.360561791341752 ... ``` ## Run basic queries After you simulate a dataset, you can run some basic queries on it. For example: - Average temperature and CPU by 30-minute windows: ```sql SELECT time_bucket('30 minutes', time) AS period, AVG(temperature) AS avg_temp, AVG(cpu) AS avg_cpu FROM sensor_data GROUP BY period; ``` Sample output: ``` period | avg_temp | avg_cpu ------------------------+------------------+------------------- 2020-03-31 19:00:00+00 | 49.6615830013373 | 0.477344429974134 2020-03-31 22:00:00+00 | 58.8521540844037 | 0.503637770501276 2020-03-31 16:00:00+00 | 50.4250325243144 | 0.511075591299838 2020-03-31 17:30:00+00 | 49.0742547437549 | 0.527267253802468 2020-04-01 14:30:00+00 | 49.3416377226822 | 0.438027751864865 ... ``` - Average and last temperature, average CPU by 30-minute windows: ```sql SELECT time_bucket('30 minutes', time) AS period, AVG(temperature) AS avg_temp, last(temperature, time) AS last_temp, AVG(cpu) AS avg_cpu FROM sensor_data GROUP BY period; ``` Sample output: ``` period | avg_temp | last_temp | avg_cpu ------------------------+------------------+------------------+------------------- 2020-03-31 19:00:00+00 | 49.6615830013373 | 84.3963081017137 | 0.477344429974134 2020-03-31 22:00:00+00 | 58.8521540844037 | 76.5528806950897 | 0.503637770501276 2020-03-31 16:00:00+00 | 50.4250325243144 | 43.5192013625056 | 0.511075591299838 2020-03-31 17:30:00+00 | 49.0742547437549 | 22.740753274411 | 0.527267253802468 2020-04-01 14:30:00+00 | 49.3416377226822 | 59.1331578791142 | 0.438027751864865 ... ``` - Query the metadata: ```sql SELECT sensors.location, time_bucket('30 minutes', time) AS period, AVG(temperature) AS avg_temp, last(temperature, time) AS last_temp, AVG(cpu) AS avg_cpu FROM sensor_data JOIN sensors on sensor_data.sensor_id = sensors.id GROUP BY period, sensors.location; ``` Sample output: ``` location | period | avg_temp | last_temp | avg_cpu ----------+------------------------+------------------+-------------------+------------------- ceiling | 20120-03-31 15:30:00+00 | 25.4546818090603 | 24.3201029952615 | 0.435734559316188 floor | 2020-03-31 15:30:00+00 | 43.4297036845237 | 79.9925176426768 | 0.56992522883229 ceiling | 2020-03-31 16:00:00+00 | 53.8454438598516 | 43.5192013625056 | 0.490728285357666 floor | 2020-03-31 16:00:00+00 | 47.0046211887772 | 23.0230117216706 | 0.53142289724201 ceiling | 2020-03-31 16:30:00+00 | 58.7817596504465 | 63.6621567420661 | 0.488188337767497 floor | 2020-03-31 16:30:00+00 | 44.611586847653 | 2.21919436007738 | 0.434762630766879 ceiling | 2020-03-31 17:00:00+00 | 35.7026890735142 | 42.9420990403742 | 0.550129583687522 floor | 2020-03-31 17:00:00+00 | 62.2794370166957 | 52.6636955793947 | 0.454323202022351 ... ``` You have now successfully simulated and run queries on an IoT dataset. ===== PAGE: https://docs.tigerdata.com/tutorials/cookbook/ ===== # Tiger Data cookbook This page contains suggestions from the [Tiger Data Community](https://timescaledb.slack.com/) about how to resolve common issues. Use these code examples as guidance to work with your own data. ## Prerequisites To follow the steps on this page: * Create a target [Tiger Cloud service][create-service] with the Real-time analytics capability. You need [your connection details][connection-info]. This procedure also works for [self-hosted TimescaleDB][enable-timescaledb]. ## Hypertable recipes This section contains recipes about hypertables. ### Remove duplicates from an existing hypertable Looking to remove duplicates from an existing hypertable? One method is to run a `PARTITION BY` query to get `ROW_NUMBER()` and then the `ctid` of rows where `row_number>1`. You then delete these rows. However, you need to check `tableoid` and `ctid`. This is because `ctid` is not unique and might be duplicated in different chunks. The following code example took 17 hours to process a table with 40 million rows: ```sql CREATE OR REPLACE FUNCTION deduplicate_chunks(ht_name TEXT, partition_columns TEXT, bot_id INT DEFAULT NULL) RETURNS TABLE ( chunk_schema name, chunk_name name, deleted_count INT ) AS $$ DECLARE chunk RECORD; where_clause TEXT := ''; deleted_count INT; BEGIN IF bot_id IS NOT NULL THEN where_clause := FORMAT('WHERE bot_id = %s', bot_id); END IF; FOR chunk IN SELECT c.chunk_schema, c.chunk_name FROM timescaledb_information.chunks c WHERE c.hypertable_name = ht_name LOOP EXECUTE FORMAT(' WITH cte AS ( SELECT ctid, ROW_NUMBER() OVER (PARTITION BY %s ORDER BY %s ASC) AS row_num, * FROM %I.%I %s ) DELETE FROM %I.%I WHERE ctid IN ( SELECT ctid FROM cte WHERE row_num > 1 ) RETURNING 1; ', partition_columns, partition_columns, chunk.chunk_schema, chunk.chunk_name, where_clause, chunk.chunk_schema, chunk.chunk_name) INTO deleted_count; RETURN QUERY SELECT chunk.chunk_schema, chunk.chunk_name, COALESCE(deleted_count, 0); END LOOP; END $$ LANGUAGE plpgsql; SELECT * FROM deduplicate_chunks('nudge_events', 'bot_id, session_id, nudge_id, time', 2540); ``` Shoutout to **Mathias Ose** and **Christopher Piggott** for this recipe. ### Get faster JOIN queries with Common Table Expressions Imagine there is a query that joins a hypertable to another table on a shared key: ```sql SELECT timestamp, FROM hypertable as h JOIN related_table as rt ON rt.id = h.related_table_id WHERE h.timestamp BETWEEN '2024-10-10 00:00:00' AND '2024-10-17 00:00:00' ``` If you run `EXPLAIN` on this query, you see that the query planner performs a `NestedJoin` between these two tables, which means querying the hypertable multiple times. Even if the hypertable is well indexed, if it is also large, the query will be slow. How do you force a once-only lookup? Use materialized Common Table Expressions (CTEs). If you split the query into two parts using CTEs, you can `materialize` the hypertable lookup and force Postgres to perform it only once. ```sql WITH cached_query AS materialized ( SELECT * FROM hypertable WHERE BETWEEN '2024-10-10 00:00:00' AND '2024-10-17 00:00:00' ) SELECT * FROM cached_query as c JOIN related_table as rt ON rt.id = h.related_table_id ``` Now if you run `EXPLAIN` once again, you see that this query performs only one lookup. Depending on the size of your hypertable, this could result in a multi-hour query taking mere seconds. Shoutout to **Rowan Molony** for this recipe. ## IoT recipes This section contains recipes for IoT issues: ### Work with columnar IoT data Narrow and medium width tables are a great way to store IoT data. A lot of reasons are outlined in [Designing Your Database Schema: Wide vs. Narrow Postgres Tables][blog-wide-vs-narrow]. One of the key advantages of narrow tables is that the schema does not have to change when you add new sensors. Another big advantage is that each sensor can sample at different rates and times. This helps support things like hysteresis, where new values are written infrequently unless the value changes by a certain amount. #### Narrow table format example Working with narrow table data structures presents a few challenges. In the IoT world one concern is that many data analysis approaches - including machine learning as well as more traditional data analysis - require that your data is resampled and synchronized to a common time basis. Fortunately, TimescaleDB provides you with [hyperfunctions][hyperfunctions] and other tools to help you work with this data. An example of a narrow table format is: | ts | sensor_id | value | |-------------------------|-----------|-------| | 2024-10-31 11:17:30.000 | 1007 | 23.45 | Typically you would couple this with a sensor table: | sensor_id | sensor_name | units | |-----------|--------------|--------------------------| | 1007 | temperature | degreesC | | 1012 | heat_mode | on/off | | 1013 | cooling_mode | on/off | | 1041 | occupancy | number of people in room | A medium table retains the generic structure but adds columns of various types so that you can use the same table to store float, int, bool, or even JSON (jsonb) data: | ts | sensor_id | d | i | b | t | j | |-------------------------|-----------|-------|------|------|------|------| | 2024-10-31 11:17:30.000 | 1007 | 23.45 | null | null | null | null | | 2024-10-31 11:17:47.000 | 1012 | null | null | TRUE | null | null | | 2024-10-31 11:18:01.000 | 1041 | null | 4 | null | null | null | To remove all-null entries, use an optional constraint such as: ```sql CONSTRAINT at_least_one_not_null CHECK ((d IS NOT NULL) OR (i IS NOT NULL) OR (b IS NOT NULL) OR (j IS NOT NULL) OR (t IS NOT NULL)) ``` #### Get the last value of every sensor There are several ways to get the latest value of every sensor. The following examples use the structure defined in [Narrow table format example][setup-a-narrow-table-format] as a reference: - [SELECT DISTINCT ON][select-distinct-on] - [JOIN LATERAL][join-lateral] ##### SELECT DISTINCT ON If you have a list of sensors, the easy way to get the latest value of every sensor is to use `SELECT DISTINCT ON`: ```sql WITH latest_data AS ( SELECT DISTINCT ON (sensor_id) ts, sensor_id, d FROM iot_data WHERE d is not null AND ts > CURRENT_TIMESTAMP - INTERVAL '1 week' -- important ORDER BY sensor_id, ts DESC ) SELECT sensor_id, sensors.name, ts, d FROM latest_data LEFT OUTER JOIN sensors ON latest_data.sensor_id = sensors.id WHERE latest_data.d is not null ORDER BY sensor_id, ts; -- Optional, for displaying results ordered by sensor_id ``` The common table expression (CTE) used above is not strictly necessary. However, it is an elegant way to join to the sensor list to get a sensor name in the output. If this is not something you care about, you can leave it out: ```sql SELECT DISTINCT ON (sensor_id) ts, sensor_id, d FROM iot_data WHERE d is not null AND ts > CURRENT_TIMESTAMP - INTERVAL '1 week' -- important ORDER BY sensor_id, ts DESC ``` It is important to take care when down-selecting this data. In the previous examples, the time that the query would scan back was limited. However, if there any sensors that have either not reported in a long time or in the worst case, never reported, this query devolves to a full table scan. In a database with 1000+ sensors and 41 million rows, an unconstrained query takes over an hour. #### JOIN LATERAL An alternative to [SELECT DISTINCT ON][select-distinct-on] is to use a `JOIN LATERAL`. By selecting your entire sensor list from the sensors table rather than pulling the IDs out using `SELECT DISTINCT`, `JOIN LATERAL` can offer some improvements in performance: ```sql SELECT sensor_list.id, latest_data.ts, latest_data.d FROM sensors sensor_list -- Add a WHERE clause here to downselect the sensor list, if you wish LEFT JOIN LATERAL ( SELECT ts, d FROM iot_data raw_data WHERE sensor_id = sensor_list.id ORDER BY ts DESC LIMIT 1 ) latest_data ON true WHERE latest_data.d is not null -- only pulling out float values ("d" column) in this example AND latest_data.ts > CURRENT_TIMESTAMP - interval '1 week' -- important ORDER BY sensor_list.id, latest_data.ts; ``` Limiting the time range is important, especially if you have a lot of data. Best practice is to use these kinds of queries for dashboards and quick status checks. To query over a much larger time range, encapsulate the previous example into a materialized query that refreshes infrequently, perhaps once a day. Shoutout to **Christopher Piggott** for this recipe. ===== PAGE: https://docs.tigerdata.com/tutorials/blockchain-query/ ===== # Query the Bitcoin blockchain The financial industry is extremely data-heavy and relies on real-time and historical data for decision-making, risk assessment, fraud detection, and market analysis. Tiger Data simplifies management of these large volumes of data, while also providing you with meaningful analytical insights and optimizing storage costs. In this tutorial, you use Tiger Cloud to ingest, store, and analyze transactions on the Bitcoin blockchain. [Blockchains][blockchain-def] are, at their essence, a distributed database. The [transactions][transactions-def] in a blockchain are an example of time-series data. You can use TimescaleDB to query transactions on a blockchain, in exactly the same way as you might query time-series transactions in any other database. ## Steps in this tutorial This tutorial covers: 1. [Ingest data into a service][blockchain-dataset]: set up and connect to a Tiger Cloud service, create tables and hypertables, and ingest data. 1. [Query your data][blockchain-query]: obtain information, including finding the most recent transactions on the blockchain, and gathering information about the transactions using aggregation functions. 1. [Compress your data using hypercore][blockchain-compress]: compress data that is no longer needed for highest performance queries, but is still accessed regularly for real-time analytics. When you've completed this tutorial, you can use the same dataset to [Analyze the Bitcoin data][analyze-blockchain], using TimescaleDB hyperfunctions. ===== PAGE: https://docs.tigerdata.com/tutorials/blockchain-analyze/ ===== # Analyze the Bitcoin blockchain The financial industry is extremely data-heavy and relies on real-time and historical data for decision-making, risk assessment, fraud detection, and market analysis. Tiger Data simplifies management of these large volumes of data, while also providing you with meaningful analytical insights and optimizing storage costs. In this tutorial, you use Tiger Cloud to ingest, store, and analyze transactions on the Bitcoin blockchain. [Blockchains][blockchain-def] are, at their essence, a distributed database. The [transactions][transactions-def] in a blockchain are an example of time-series data. You can use TimescaleDB to query transactions on a blockchain, in exactly the same way as you might query time-series transactions in any other database. ## Prerequisites Before you begin, make sure you have: * Signed up for a [free Tiger Data account][cloud-install]. * [](#)Signed up for a [Grafana account][grafana-setup] to graph your queries. ## Steps in this tutorial This tutorial covers: 1. [Setting up your dataset][blockchain-dataset] 1. [Querying your dataset][blockchain-analyze] ## About analyzing the Bitcoin blockchain with Tiger Cloud This tutorial uses a sample Bitcoin dataset to show you how to aggregate blockchain transaction data, and construct queries to analyze information from the aggregations. The queries in this tutorial help you determine if a cryptocurrency has a high transaction fee, shows any correlation between transaction volumes and fees, or if it's expensive to mine. It starts by setting up and connecting to a Tiger Cloud service, create tables, and load data into the tables using `psql`. If you have already completed the [beginner blockchain tutorial][blockchain-query], then you already have the dataset loaded, and you can skip straight to the queries. You then learn how to conduct analysis on your dataset using Timescale hyperfunctions. It walks you through creating a series of continuous aggregates, and querying the aggregates to analyze the data. You can also use those queries to graph the output in Grafana. ===== PAGE: https://docs.tigerdata.com/tutorials/financial-tick-data/ ===== # Analyze financial tick data with TimescaleDB The financial industry is extremely data-heavy and relies on real-time and historical data for decision-making, risk assessment, fraud detection, and market analysis. Tiger Data simplifies management of these large volumes of data, while also providing you with meaningful analytical insights and optimizing storage costs. To analyze financial data, you can chart the open, high, low, close, and volume (OHLCV) information for a financial asset. Using this data, you can create candlestick charts that make it easier to analyze the price changes of financial assets over time. You can use candlestick charts to examine trends in stock, cryptocurrency, or NFT prices. In this tutorial, you use real raw financial data provided by [Twelve Data][twelve-data], create an aggregated candlestick view, query the aggregated data, and visualize the data in Grafana. ## OHLCV data and candlestick charts The financial sector regularly uses [candlestick charts][charts] to visualize the price change of an asset. Each candlestick represents a time period, such as one minute or one hour, and shows how the asset's price changed during that time. Candlestick charts are generated from the open, high, low, close, and volume data for each financial asset during the time period. This is often abbreviated as OHLCV: * Open: opening price * High: highest price * Low: lowest price * Close: closing price * Volume: volume of transactions ![candlestick](https://assets.timescale.com/docs/images/tutorials/intraday-stock-analysis/timescale_cloud_candlestick.png) TimescaleDB is well suited to storing and analyzing financial candlestick data, and many Tiger Data community members use it for exactly this purpose. Check out these stories from some Tiger Datacommunity members: * [How Trading Strategy built a data stack for crypto quant trading][trading-strategy] * [How Messari uses data to open the cryptoeconomy to everyone][messari] * [How I power a (successful) crypto trading bot with TimescaleDB][bot] ## Steps in this tutorial This tutorial shows you how to ingest real-time time-series data into a Tiger Cloud service: 1. [Ingest data into a service][financial-tick-dataset]: load data from [Twelve Data][twelve-data] into your TimescaleDB database. 1. [Query your dataset][financial-tick-query]: create candlestick views, query the aggregated data, and visualize the data in Grafana. 1. [Compress your data using hypercore][financial-tick-compress]: learn how to store and query your financial tick data more efficiently using compression feature of TimescaleDB. To create candlestick views, query the aggregated data, and visualize the data in Grafana, see the [ingest real-time websocket data section][advanced-websocket]. ===== PAGE: https://docs.tigerdata.com/tutorials/financial-ingest-real-time/ ===== # Ingest real-time financial data using WebSocket The financial industry is extremely data-heavy and relies on real-time and historical data for decision-making, risk assessment, fraud detection, and market analysis. Tiger Data simplifies management of these large volumes of data, while also providing you with meaningful analytical insights and optimizing storage costs. This tutorial shows you how to ingest real-time time-series data into TimescaleDB using a websocket connection. The tutorial sets up a data pipeline to ingest real-time data from our data partner, [Twelve Data][twelve-data]. Twelve Data provides a number of different financial APIs, including stock, cryptocurrencies, foreign exchanges, and ETFs. It also supports websocket connections in case you want to update your database frequently. With websockets, you need to connect to the server, subscribe to symbols, and you can start receiving data in real-time during market hours. When you complete this tutorial, you'll have a data pipeline set up that ingests real-time financial data into your Tiger Cloud. This tutorial uses Python and the API [wrapper library][twelve-wrapper] provided by Twelve Data. ## Prerequisites Before you begin, make sure you have: * Signed up for a [free Tiger Data account][cloud-install]. * Installed Python 3 * Signed up for [Twelve Data][twelve-signup]. The free tier is perfect for this tutorial. * Made a note of your Twelve Data [API key](https://twelvedata.com/account/api-keys). ## Steps in this tutorial This tutorial covers: 1. [Setting up your dataset][financial-ingest-dataset]: Load data from [Twelve Data][twelve-data] into your TimescaleDB database. 1. [Querying your dataset][financial-ingest-query]: Create candlestick views, query the aggregated data, and visualize the data in Grafana. This tutorial shows you how to ingest real-time time-series data into a Tiger Cloud service using a websocket connection. To create candlestick views, query the aggregated data, and visualize the data in Grafana. ## About OHLCV data and candlestick charts The financial sector regularly uses [candlestick charts][charts] to visualize the price change of an asset. Each candlestick represents a time period, such as one minute or one hour, and shows how the asset's price changed during that time. Candlestick charts are generated from the open, high, low, close, and volume data for each financial asset during the time period. This is often abbreviated as OHLCV: * Open: opening price * High: highest price * Low: lowest price * Close: closing price * Volume: volume of transactions ![candlestick](https://assets.timescale.com/docs/images/tutorials/intraday-stock-analysis/candlestick_fig.png) TimescaleDB is well suited to storing and analyzing financial candlestick data, and many Tiger Datacommunity members use it for exactly this purpose. ===== PAGE: https://docs.tigerdata.com/api/hypertable/ ===== # Hypertables and chunks Tiger Cloud supercharges your real-time analytics by letting you run complex queries continuously, with near-zero latency. Under the hood, this is achieved by using hypertables—Postgres tables that automatically partition your time-series data by time and optionally by other dimensions. When you run a query, Tiger Cloud identifies the correct partition, called chunk, and runs the query on it, instead of going through the entire table. ![Hypertable structure](https://assets.timescale.com/docs/images/hypertable.png) Hypertables offer the following benefits: - **Efficient data management with [automated partitioning by time][chunk-size]**: Tiger Cloud splits your data into chunks that hold data from a specific time range. For example, one day or one week. You can configure this range to better suit your needs. - **Better performance with [strategic indexing][hypertable-indexes]**: an index on time in the descending order is automatically created when you create a hypertable. More indexes are created on the chunk level, to optimize performance. You can create additional indexes, including unique indexes, on the columns you need. - **Faster queries with [chunk skipping][chunk-skipping]**: Tiger Cloud skips the chunks that are irrelevant in the context of your query, dramatically reducing the time and resources needed to fetch results. Even more—you can enable chunk skipping on non-partitioning columns. - **Advanced data analysis with [hyperfunctions][hyperfunctions]**: Tiger Cloud enables you to efficiently process, aggregate, and analyze significant volumes of data while maintaining high performance. To top it all, there is no added complexity—you interact with hypertables in the same way as you would with regular Postgres tables. All the optimization magic happens behind the scenes. Inheritance is not supported for hypertables and may lead to unexpected behavior. For more information about using hypertables, including chunk size partitioning, see the [hypertable section][hypertable-docs]. ## The hypertable workflow Best practice for using a hypertable is to: 1. **Create a hypertable** Create a [hypertable][hypertables-section] for your time-series data using [CREATE TABLE][hypertable-create-table]. For [efficient queries][secondary-indexes] on data in the columnstore, remember to `segmentby` the column you will use most often to filter your data. For example: ```sql CREATE TABLE conditions ( time TIMESTAMPTZ NOT NULL, location TEXT NOT NULL, device TEXT NOT NULL, temperature DOUBLE PRECISION NULL, humidity DOUBLE PRECISION NULL ) WITH ( tsdb.hypertable, tsdb.partition_column='time', tsdb.segmentby = 'device', tsdb.orderby = 'time DESC' ); ``` If you are self-hosting TimescaleDB v2.19.3 and below, create a [Postgres relational table][pg-create-table], then convert it using [create_hypertable][create_hypertable]. You then enable hypercore with a call to [ALTER TABLE][alter_table_hypercore]. 1. **Set the columnstore policy** ```sql CALL add_columnstore_policy('conditions', after => INTERVAL '1d'); ``` ===== PAGE: https://docs.tigerdata.com/api/hypercore/ ===== # Hypercore Hypercore is a hybrid row-columnar storage engine in TimescaleDB. It is designed specifically for real-time analytics and powered by time-series data. The advantage of hypercore is its ability to seamlessly switch between row-oriented and column-oriented storage, delivering the best of both worlds: ![Hypercore workflow](https://assets.timescale.com/docs/images/hypertable-with-hypercore-enabled.png) Hypercore solves the key challenges in real-time analytics: - High ingest throughput - Low-latency ingestion - Fast query performance - Efficient handling of data updates and late-arriving data - Streamlined data management Hypercore’s hybrid approach combines the benefits of row-oriented and column-oriented formats: - **Fast ingest with rowstore**: new data is initially written to the rowstore, which is optimized for high-speed inserts and updates. This process ensures that real-time applications easily handle rapid streams of incoming data. Mutability—upserts, updates, and deletes happen seamlessly. - **Efficient analytics with columnstore**: as the data **cools** and becomes more suited for analytics, it is automatically converted to the columnstore. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. - **Faster queries on compressed data in columnstore**: in the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries. Combined with [chunk skipping][chunk-skipping], this helps you save on storage costs and keeps your queries operating at lightning speed. - **Fast modification of compressed data in columnstore**: just use SQL to add or modify data in the columnstore. TimescaleDB is optimized for superfast INSERT and UPSERT performance. - **Full mutability with transactional semantics**: regardless of where data is stored, hypercore provides full ACID support. Like in a vanilla Postgres database, inserts and updates to the rowstore and columnstore are always consistent, and available to queries as soon as they are completed. For an in-depth explanation of how hypertables and hypercore work, see the [Data model][data-model]. Since [TimescaleDB v2.18.0](https://github.com/timescale/timescaledb/releases/tag/2.18.0) ## Hypercore workflow Best practice for using hypercore is to: 1. **Enable columnstore** Create a [hypertable][hypertables-section] for your time-series data using [CREATE TABLE][hypertable-create-table]. For [efficient queries][secondary-indexes] on data in the columnstore, remember to `segmentby` the column you will use most often to filter your data. For example: * [Use `CREATE TABLE` for a hypertable][hypertable-create-table] ```sql CREATE TABLE crypto_ticks ( "time" TIMESTAMPTZ, symbol TEXT, price DOUBLE PRECISION, day_volume NUMERIC ) WITH ( tsdb.hypertable, tsdb.partition_column='time', tsdb.segmentby='symbol', tsdb.orderby='time DESC' ); ``` If you are self-hosting TimescaleDB v2.19.3 and below, create a [Postgres relational table][pg-create-table], then convert it using [create_hypertable][create_hypertable]. You then enable hypercore with a call to [ALTER TABLE][alter_table_hypercore]. * [Use `ALTER MATERIALIZED VIEW` for a continuous aggregate][compression_continuous-aggregate] ```sql ALTER MATERIALIZED VIEW assets_candlestick_daily set ( timescaledb.enable_columnstore = true, timescaledb.segmentby = 'symbol' ); ``` 1. **Add a policy to move chunks to the columnstore at a specific time interval** For example, 7 days after the data was added to the table: ``` sql CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '7d'); ``` See [add_columnstore_policy][add_columnstore_policy]. 1. **View the policies that you set or the policies that already exist** ``` sql SELECT * FROM timescaledb_information.jobs WHERE proc_name='policy_compression'; ``` See [timescaledb_information.jobs][informational-views]. You can also [convert_to_columnstore][convert_to_columnstore] and [convert_to_rowstore][convert_to_rowstore] manually for more fine-grained control over your data. ## Limitations Chunks in the columnstore have the following limitations: * `ROW LEVEL SECURITY` is not supported on chunks in the columnstore. ===== PAGE: https://docs.tigerdata.com/api/continuous-aggregates/ ===== # Continuous aggregates In modern applications, data usually grows very quickly. This means that aggregating it into useful summaries can become very slow. If you are collecting data very frequently, you might want to aggregate your data into minutes or hours instead. For example, if an IoT device takes temperature readings every second, you might want to find the average temperature for each hour. Every time you run this query, the database needs to scan the entire table and recalculate the average. TimescaleDB makes aggregating data lightning fast, accurate, and easy with continuous aggregates. ![Reduced data calls with continuous aggregates](https://assets.timescale.com/docs/images/continuous-aggregate.png) Continuous aggregates in TimescaleDB are a kind of hypertable that is refreshed automatically in the background as new data is added, or old data is modified. Changes to your dataset are tracked, and the hypertable behind the continuous aggregate is automatically updated in the background. Continuous aggregates have a much lower maintenance burden than regular Postgres materialized views, because the whole view is not created from scratch on each refresh. This means that you can get on with working your data instead of maintaining your database. Because continuous aggregates are based on hypertables, you can query them in exactly the same way as your other tables. This includes continuous aggregates in the rowstore, compressed into the [columnstore][hypercore], or [tiered to object storage][data-tiering]. You can even create [continuous aggregates on top of your continuous aggregates][hierarchical-caggs], for an even more fine-tuned aggregation. [Real-time aggregation][real-time-aggregation] enables you to combine pre-aggregated data from the materialized view with the most recent raw data. This gives you up-to-date results on every query. In TimescaleDB v2.13 and later, real-time aggregates are **DISABLED** by default. In earlier versions, real-time aggregates are **ENABLED** by default; when you create a continuous aggregate, queries to that view include the results from the most recent raw data. For more information about using continuous aggregates, see the documentation in [Use Tiger Data products][cagg-docs]. ===== PAGE: https://docs.tigerdata.com/api/data-retention/ ===== # Data retention An intrinsic part of time-series data is that new data is accumulated and old data is rarely, if ever, updated. This means that the relevance of the data diminishes over time. It is therefore often desirable to delete old data to save disk space. With TimescaleDB, you can manually remove old chunks of data or implement policies using these APIs. For more information about creating a data retention policy, see the [data retention section][data-retention-howto]. ===== PAGE: https://docs.tigerdata.com/api/jobs-automation/ ===== # Jobs Jobs allow you to run functions and procedures implemented in a language of your choice on a schedule within Timescale. This allows automatic periodic tasks that are not covered by existing policies and even enhancing existing policies with additional functionality. The following APIs and views allow you to manage the jobs that you create and get details around automatic jobs used by other TimescaleDB functions like continuous aggregation refresh policies and data retention policies. To view the policies that you set or the policies that already exist, see [informational views][informational-views]. ===== PAGE: https://docs.tigerdata.com/api/uuid-functions/ ===== # UUIDv7 functions UUIDv7 is a time-ordered UUID that includes a Unix timestamp (with millisecond precision) in its first 48 bits. Like other UUIDs, it uses 6 bits for version and variant info, and the remaining 74 bits are random. ![UUIDv7 microseconds](https://assets.timescale.com/docs/images/uuidv7-structure-microseconds.svg) UUIDv7 is ideal anywhere you create lots of records over time, not only observability. Advantages are: - **No extra column required to partition by time with sortability**: you can sort UUIDv7 instances by their value. This is useful for ordering records by creation time without the need for a separate timestamp column. - **Indexing performance**: UUIDv7s increase with time, so new rows append near the end of a B-tree instead of This results in fewer page splits, less fragmentation, faster inserts, and efficient time-range scans. - **Easy keyset pagination**: `WHERE id > :cursor` and natural sharding. - **UUID**: safe across services, replicas, and unique across distributed systems. UUIDv7 also increases query speed by reducing the number of chunks scanned during queries. For example, in a database with 25 million rows, the following query runs in 25 seconds: ```sql WITH ref AS (SELECT now() AS t0) SELECT count(*) AS cnt_ts_filter FROM events e, ref WHERE uuid_timestamp(e.event_id) >= ref.t0 - INTERVAL '2 days'; ``` Using UUIDv7 excludes chunks at startup and reduces the query time to 550ms: ```sql WITH ref AS (SELECT now() AS t0) SELECT count(*) AS cnt_boundary_filter FROM events e, ref WHERE e.event_id >= to_uuidv7_boundary(ref.t0 - INTERVAL '2 days') ``` You use UUIDvs for events, orders, messages, uploads, runs, jobs, spans, and more. ## Examples - **High-rate event logs for observability and metrics**: UUIDv7 gives you globally unique IDs (for traceability) and time windows (“last hour”), without the need for a separate `created_at` column. UUIDv7 create less churn because inserts land at the end of the index, and you can filter by time using UUIDv7 objects. - Last hour: ```sql SELECT count(*) FROM logs WHERE id >= to_uuidv7_boundary(now() - interval '1 hour'); ``` - Keyset pagination ```sql SELECT * FROM logs WHERE id > to_uuidv7($last_seen'::timestamptz, true) ORDER BY id LIMIT 1000; ``` - **Workflow / durable execution runs**: Each run needs a stable ID for joins and retries, and you often ask “what started since X?”. UUIDs help by serving both as the primary key and a time cursor across services. For example: ```sql SELECT run_id, status FROM runs WHERE run_id >= to_uuidv7_boundary(now() - interval '5 minutes') ``` - **Orders / activity feeds / messages (SaaS apps)**: Human-readable timestamps are not mandatory in a table. However, you still need time-ordered pages and day/week ranges. UUIDv7 enables clean date windows and cursor pagination with just the ID. For example: ```sql SELECT * FROM orders WHERE id >= to_uuidv7('2025-08-01'::timestamptz, true) AND id < to_uuidv7('2025-08-02'::timestamptz, true) ORDER BY id; ``` ## Functions - [generate_uuidv7()][generate_uuidv7]: generate a version 7 UUID based on current time - [to_uuidv7()][to_uuidv7]: create a version 7 UUID from a PostgreSQL timestamp - [to_uuidv7_boundary()][to_uuidv7_boundary]: create a version 7 "boundary" UUID from a PostgreSQL timestamp - [uuid_timestamp()][uuid_timestamp]: extract a PostgreSQL timestamp from a version 7 UUID - [uuid_timestamp_micros()][uuid_timestamp_micros]: extract a PostgreSQL timestamp with microsecond precision from a version 7 UUID - [uuid_version()][uuid_version]: extract the version of a UUID ===== PAGE: https://docs.tigerdata.com/api/approximate_row_count/ ===== # approximate_row_count() Get approximate row count for hypertable, distributed hypertable, or regular Postgres table based on catalog estimates. This function supports tables with nested inheritance and declarative partitioning. The accuracy of `approximate_row_count` depends on the database having up-to-date statistics about the table or hypertable, which are updated by `VACUUM`, `ANALYZE`, and a few DDL commands. If you have auto-vacuum configured on your table or hypertable, or changes to the table are relatively infrequent, you might not need to explicitly `ANALYZE` your table as shown below. Otherwise, if your table statistics are too out-of-date, running this command updates your statistics and yields more accurate approximation results. ### Samples Get the approximate row count for a single hypertable. ```sql ANALYZE conditions; SELECT * FROM approximate_row_count('conditions'); ``` The expected output: ``` approximate_row_count ---------------------- 240000 ``` ### Required arguments |Name|Type|Description| |---|---|---| | `relation` | REGCLASS | Hypertable or regular Postgres table to get row count for. | ===== PAGE: https://docs.tigerdata.com/api/first/ ===== # first() The `first` aggregate allows you to get the value of one column as ordered by another. For example, `first(temperature, time)` returns the earliest temperature value based on time within an aggregate group. The `last` and `first` commands do not use indexes, they perform a sequential scan through the group. They are primarily used for ordered selection within a `GROUP BY` aggregate, and not as an alternative to an `ORDER BY time DESC LIMIT 1` clause to find the latest value, which uses indexes. ### Samples Get the earliest temperature by device_id: ```sql SELECT device_id, first(temp, time) FROM metrics GROUP BY device_id; ``` This example uses first and last with an aggregate filter, and avoids null values in the output: ```sql SELECT TIME_BUCKET('5 MIN', time_column) AS interv, AVG(temperature) as avg_temp, first(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS beg_temp, last(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS end_temp FROM sensors GROUP BY interv ``` ### Required arguments |Name|Type|Description| |---|---|---| |`value`|TEXT|The value to return| |`time`|TIMESTAMP or INTEGER|The timestamp to use for comparison| ===== PAGE: https://docs.tigerdata.com/api/last/ ===== # last() The `last` aggregate allows you to get the value of one column as ordered by another. For example, `last(temperature, time)` returns the latest temperature value based on time within an aggregate group. The `last` and `first` commands do not use indexes, they perform a sequential scan through the group. They are primarily used for ordered selection within a `GROUP BY` aggregate, and not as an alternative to an `ORDER BY time DESC LIMIT 1` clause to find the latest value, which uses indexes. ### 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; ``` This example uses first and last with an aggregate filter, and avoids null values in the output: ```sql SELECT TIME_BUCKET('5 MIN', time_column) AS interv, AVG(temperature) as avg_temp, first(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS beg_temp, last(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS end_temp FROM sensors GROUP BY interv ``` ### Required arguments |Name|Type|Description| |---|---|---| |`value`|ANY ELEMENT|The value to return| |`time`|TIMESTAMP or INTEGER|The timestamp to use for comparison| ===== PAGE: https://docs.tigerdata.com/api/histogram/ ===== # histogram() The `histogram()` function represents the distribution of a set of values as an array of equal-width buckets. It partitions the dataset into a specified number of buckets (`nbuckets`) ranging from the inputted `min` and `max` values. The return value is an array containing `nbuckets`+2 buckets, with the middle `nbuckets` bins for values in the stated range, the first bucket at the head of the array for values under the lower `min` bound, and the last bucket for values greater than or equal to the `max` bound. Each bucket is inclusive on its lower bound, and exclusive on its upper bound. Therefore, values equal to the `min` are included in the bucket starting with `min`, but values equal to the `max` are in the last bucket. ### Samples A simple bucketing of device's battery levels from the `readings` dataset: ```sql SELECT device_id, histogram(battery_level, 20, 60, 5) FROM readings GROUP BY device_id LIMIT 10; ``` The expected output: ```sql device_id | histogram ------------+------------------------------ demo000000 | {0,0,0,7,215,206,572} demo000001 | {0,12,173,112,99,145,459} demo000002 | {0,0,187,167,68,229,349} demo000003 | {197,209,127,221,106,112,28} demo000004 | {0,0,0,0,0,39,961} demo000005 | {12,225,171,122,233,80,157} demo000006 | {0,78,176,170,8,40,528} demo000007 | {0,0,0,126,239,245,390} demo000008 | {0,0,311,345,116,228,0} demo000009 | {295,92,105,50,8,8,442} ``` ### Required arguments |Name|Type|Description| |---|---|---| | `value` | ANY VALUE | A set of values to partition into a histogram | | `min` | NUMERIC | The histogram's lower bound used in bucketing (inclusive) | | `max` | NUMERIC | The histogram's upper bound used in bucketing (exclusive) | | `nbuckets` | INTEGER | The integer value for the number of histogram buckets (partitions) | ===== PAGE: https://docs.tigerdata.com/api/time_bucket/ ===== # time_bucket() The `time_bucket` function is similar to the standard Postgres `date_bin` function. Unlike `date_bin`, it allows for arbitrary time intervals of months or longer. The return value is the bucket's start time. Buckets are aligned to start at midnight in UTC+0. The time bucket size (`bucket_width`) can be set as INTERVAL or INTEGER. For INTERVAL-type `bucket_width`, you can change the time zone with the optional `timezone` parameter. In this case, the buckets are realigned to start at midnight in the time zone you specify. Note that during shifts to and from daylight savings, the amount of data aggregated into the corresponding buckets can be irregular. For example, if the `bucket_width` is 2 hours, the number of bucketed hours is either three hours or one hour. ## Samples Simple five-minute averaging: ```sql SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10; ``` To report the middle of the bucket, instead of the left edge: ```sql SELECT time_bucket('5 minutes', time) + '2.5 minutes' AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10; ``` For rounding, move the alignment so that the middle of the bucket is at the five-minute mark, and report the middle of the bucket: ```sql SELECT time_bucket('5 minutes', time, '-2.5 minutes'::INTERVAL) + '2.5 minutes' AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10; ``` In this example, add the explicit cast to ensure that Postgres chooses the correct function. To shift the alignment of the buckets, you can use the origin parameter passed as a timestamp, timestamptz, or date type. This example shifts the start of the week to a Sunday, instead of the default of Monday: ```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; ``` The value of the origin parameter in this example is `2017-12-31`, a Sunday within the period being analyzed. However, the origin provided to the function can be before, during, or after the data being analyzed. All buckets are calculated relative to this origin. So, in this example, any Sunday could have been used. Note that because `time < TIMESTAMPTZ '2018-01-03'` is used in this example, the last bucket would have only 4 days of data. This cast to TIMESTAMP converts the time to local time according to the server's time zone setting. ```sql SELECT time_bucket(INTERVAL '2 hours', timetz::TIMESTAMP) AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10; ``` Bucket temperature values to calculate the average monthly temperature. Set the time zone to 'Europe/Berlin' so bucket start and end times are aligned to midnight in Berlin. ```sql SELECT time_bucket('1 month', ts, 'Europe/Berlin') AS month_bucket, avg(temperature) AS avg_temp FROM weather GROUP BY month_bucket ORDER BY month_bucket DESC LIMIT 10; ``` ## Required arguments for interval time inputs |Name|Type|Description| |-|-|-| |`bucket_width`|INTERVAL|A Postgres time interval for how long each bucket is| |`ts`|DATE, TIMESTAMP, or TIMESTAMPTZ|The timestamp to bucket| If you use months as an interval for `bucket_width`, you cannot combine it with a non-month component. For example, `1 month` and `3 months` are both valid bucket widths, but `1 month 1 day` and `3 months 2 weeks` are not. ## Optional arguments for interval time inputs |Name|Type| Description | |-|-|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |`timezone`|TEXT| The time zone for calculating bucket start and end times. Can only be used with `TIMESTAMPTZ`. Defaults to UTC+0. | |`origin`|DATE, TIMESTAMP, or TIMESTAMPTZ| Buckets are aligned relative to this timestamp. Defaults to midnight on January 3, 2000, for buckets that don't include a month or year interval, and to midnight on January 1, 2000, for month, year, and century buckets. | |`offset`|INTERVAL| The time interval to offset all time buckets by. A positive value shifts bucket start and end times later. A negative value shifts bucket start and end times earlier. `offset` must be surrounded with double quotes when used as a named argument, because it is a reserved key word in Postgres. | ## Required arguments for integer time inputs |Name|Type|Description| |-|-|-| |`bucket_width`|INTEGER|The bucket width| |`ts`|INTEGER|The timestamp to bucket| ## Optional arguments for integer time inputs |Name|Type|Description| |-|-|-| |`offset`|INTEGER|The amount to offset all buckets by. A positive value shifts bucket start and end times later. A negative value shifts bucket start and end times earlier. `offset` must be surrounded with double quotes when used as a named argument, because it is a reserved key word in Postgres.| ===== PAGE: https://docs.tigerdata.com/api/time_bucket_ng/ ===== # timescaledb_experimental.time_bucket_ng() The `time_bucket_ng()` function is an experimental version of the [`time_bucket()`][time_bucket] function. It introduced some new capabilities, such as monthly buckets and timezone support. Those features are now part of the regular `time_bucket()` function. This section describes a feature that is deprecated. We strongly recommend that you do not use this feature in a production environment. If you need more information, [contact us](https://www.tigerdata.com/contact/). The `time_bucket()` and `time_bucket_ng()` functions are similar, but not completely compatible. There are two main differences. Firstly, `time_bucket_ng()` doesn't work with timestamps prior to `origin`, while `time_bucket()` does. Secondly, the default `origin` values differ. `time_bucket()` uses an origin date of January 3, 2000, for buckets shorter than a month. `time_bucket_ng()` uses an origin date of January 1, 2000, for all bucket sizes. ### Samples In this example, `time_bucket_ng()` is used to create bucket data in three month intervals: ```sql SELECT timescaledb_experimental.time_bucket_ng('3 month', date '2021-08-01'); time_bucket_ng ---------------- 2021-07-01 (1 row) ``` This example uses `time_bucket_ng()` to bucket data in one year intervals: ```sql SELECT timescaledb_experimental.time_bucket_ng('1 year', date '2021-08-01'); time_bucket_ng ---------------- 2021-01-01 (1 row) ``` To split time into buckets, `time_bucket_ng()` uses a starting point in time called `origin`. The default origin is `2000-01-01`. `time_bucket_ng` cannot use timestamps earlier than `origin`: ```sql SELECT timescaledb_experimental.time_bucket_ng('100 years', timestamp '1988-05-08'); ERROR: origin must be before the given date ``` Going back in time from `origin` isn't usually possible, especially when you consider timezones and daylight savings time (DST). Note also that there is no reasonable way to split time in variable-sized buckets (such as months) from an arbitrary `origin`, so `origin` defaults to the first day of the month. To bypass named limitations, you can override the default `origin`: ```sql -- working with timestamps before 2000-01-01 SELECT timescaledb_experimental.time_bucket_ng('100 years', timestamp '1988-05-08', origin => '1900-01-01'); time_bucket_ng --------------------- 1900-01-01 00:00:00 -- unlike the default origin, which is Saturday, 2000-01-03 is Monday SELECT timescaledb_experimental.time_bucket_ng('1 week', timestamp '2021-08-26', origin => '2000-01-03'); time_bucket_ng --------------------- 2021-08-23 00:00:00 ``` This example shows how `time_bucket_ng()` is used to bucket data by months in a specified timezone: ```sql -- note that timestamptz is displayed differently depending on the session parameters SET TIME ZONE 'Europe/Moscow'; SET SELECT timescaledb_experimental.time_bucket_ng('1 month', timestamptz '2001-02-03 12:34:56 MSK', timezone => 'Europe/Moscow'); time_bucket_ng ------------------------ 2001-02-01 00:00:00+03 ``` You can use `time_bucket_ng()` with continuous aggregates. This example tracks the temperature in Moscow over seven day intervals: ```sql CREATE TABLE conditions( day DATE NOT NULL, city text NOT NULL, temperature INT NOT NULL); SELECT create_hypertable( 'conditions', by_range('day', INTERVAL '1 day') ); INSERT INTO conditions (day, city, temperature) VALUES ('2021-06-14', 'Moscow', 26), ('2021-06-15', 'Moscow', 22), ('2021-06-16', 'Moscow', 24), ('2021-06-17', 'Moscow', 24), ('2021-06-18', 'Moscow', 27), ('2021-06-19', 'Moscow', 28), ('2021-06-20', 'Moscow', 30), ('2021-06-21', 'Moscow', 31), ('2021-06-22', 'Moscow', 34), ('2021-06-23', 'Moscow', 34), ('2021-06-24', 'Moscow', 34), ('2021-06-25', 'Moscow', 32), ('2021-06-26', 'Moscow', 32), ('2021-06-27', 'Moscow', 31); CREATE MATERIALIZED VIEW conditions_summary_weekly WITH (timescaledb.continuous) AS SELECT city, timescaledb_experimental.time_bucket_ng('7 days', day) AS bucket, MIN(temperature), MAX(temperature) FROM conditions GROUP BY city, bucket; SELECT to_char(bucket, 'YYYY-MM-DD'), city, min, max FROM conditions_summary_weekly ORDER BY bucket; to_char | city | min | max ------------+--------+-----+----- 2021-06-12 | Moscow | 22 | 27 2021-06-19 | Moscow | 28 | 34 2021-06-26 | Moscow | 31 | 32 (3 rows) ``` The `by_range` dimension builder is an addition to TimescaleDB 2.13. For simpler cases, like this one, you can also create the hypertable using the old syntax: ```sql SELECT create_hypertable('', '