> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Integrate Supabase with Tiger Cloud

> Run real-time analytical queries across databases using foreign data wrappers

export const PG = 'Postgres';

export const HYPERCORE_CAP = 'Hypercore';

export const HYPERCORE = 'hypercore';

export const CLOUD_LONG = 'Tiger Cloud';

export const CAGG_CAP = 'Continuous aggregate';

export const CAGG = 'continuous aggregate';

export const SERVICE_LONG = 'Tiger Cloud service';

export const SELF_LONG = 'self-hosted TimescaleDB';

export const CHUNK = 'chunk';

export const HYPERTABLE = 'hypertable';

export const COLUMNSTORE = 'columnstore';

export const TIMESCALE_DB = 'TimescaleDB';

[Supabase][supabase] is an open source Firebase alternative. This page shows how to run real-time analytical queries
against a {SERVICE_LONG} through Supabase using a foreign data wrapper (fdw) to bring aggregated data from your
{SERVICE_LONG}.

## Prerequisites

To follow the steps on this page:

* Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.<p />

  You need [your connection details][connection-info]. This procedure also
  works for [{SELF_LONG}][enable-timescaledb].

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted

[connection-info]: /integrations/find-connection-details

* Create a [Supabase project][supabase-new-project]

## Set up your service

To set up a {SERVICE_LONG} optimized for analytics to receive data from Supabase:

1. **Optimize time-series data in {HYPERTABLE}s**

   Time-series data represents how a system, process, or behavior changes over time. [Hypertables][hypertables-section]
   are {PG} tables that help you improve insert and query performance by automatically partitioning your data by
   time.

   [Connect to your {SERVICE_LONG}][in-console-editors] and create a table that will point to a Supabase database:

   ```sql theme={"dark"}
   CREATE TABLE signs (
       time timestamptz NOT NULL DEFAULT now(),
       origin_time timestamptz NOT NULL,
       name TEXT
   ) WITH (
     tsdb.hypertable
   );
   ```

   When you create a {HYPERTABLE} using [CREATE TABLE ... WITH ...][hypertable-create-table], the default partitioning
   column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a
   [columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an
   interval equal to the value of the [chunk\_interval][create_table_arguments], defined through `compress_after` in the
   policy. This columnar format enables fast scanning and
   aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the
   {COLUMNSTORE} conversion, {HYPERTABLE} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries.

   You can customize this policy later using [alter\_job][alter_job_samples]. However, to change `after` or
   `created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must
   [remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy].

   You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}.

   [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

   [alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples

   [convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore

   [create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments

   [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

   [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy

2. **Optimize cooling data for analytics**

   {HYPERCORE_CAP} is the hybrid row-columnar storage engine in {TIMESCALE_DB}, 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 {TIMESCALE_DB} to deliver the best of both worlds, solving the
   key challenges in real-time analytics.

   ```sql theme={"dark"}
   ALTER TABLE signs SET (
     timescaledb.enable_columnstore = true,
     timescaledb.segmentby = 'name');
   ```

3. **Create optimized analytical queries**

   {CAGG_CAP}s are designed to make queries on very large datasets run
   faster. {CAGG_CAP}s in {CLOUD_LONG} use {PG} [materialized views][postgres-materialized-views] to
   continuously, and incrementally refresh a query in the background, so that when you run the query,
   only the data that has changed needs to be computed, not the entire dataset.

   1. Create a {CAGG} pointing to the Supabase database.

      ```sql theme={"dark"}
      CREATE MATERIALIZED VIEW IF NOT EXISTS signs_per_minute
      WITH (timescaledb.continuous)
      AS
      SELECT time_bucket('1 minute', time) as ts,
       name,
       count(*) as total
      FROM signs
      GROUP BY 1, 2
      WITH NO DATA;
      ```

   2. Setup a delay stats comparing `origin_time` to `time`.

      ```sql theme={"dark"}
      CREATE MATERIALIZED VIEW IF NOT EXISTS _signs_per_minute_delay
      WITH (timescaledb.continuous)
      AS
      SELECT time_bucket('1 minute', time) as ts,
        stats_agg(extract(epoch from origin_time - time)::float8) as delay_agg,
        candlestick_agg(time, extract(epoch from origin_time - time)::float8, 1) as delay_candlestick
      FROM signs GROUP BY 1
      WITH NO DATA;
      ```

   3. Setup a view to recieve the data from Supabase.

      ```sql theme={"dark"}
      CREATE VIEW signs_per_minute_delay
      AS
        SELECT ts,
        average(delay_agg) as avg_delay,
        stddev(delay_agg) as stddev_delay,
        open(delay_candlestick) as open,
        high(delay_candlestick) as high,
        low(delay_candlestick) as low,
        close(delay_candlestick) as close
      FROM _signs_per_minute_delay
      ```

4. **Add refresh policies for your analytical queries**

   You use `start_offset` and `end_offset` to define the time range that the {CAGG} will cover. Assuming
   that the data is being inserted without any delay, set the `start_offset` to `5 minutes` and the `end_offset` to
   `1 minute`. This means that the {CAGG} is refreshed every minute, and the refresh covers the last 5
   minutes.
   You set `schedule_interval` to `INTERVAL '1 minute'` so the {CAGG} refreshes on your {SERVICE_LONG}
   every minute. The data is accessed from Supabase, and the {CAGG} is refreshed every minute in
   the other side.

   ```sql theme={"dark"}
   SELECT add_continuous_aggregate_policy('signs_per_minute',
    start_offset => INTERVAL '5 minutes',
    end_offset => INTERVAL '1 minute',
    schedule_interval => INTERVAL '1 minute');
   ```

   Do the same thing for data inserted with a delay:

   ```sql theme={"dark"}
   SELECT add_continuous_aggregate_policy('_signs_per_minute_delay',
    start_offset => INTERVAL '5 minutes',
    end_offset => INTERVAL '1 minute',
    schedule_interval => INTERVAL '1 minute');
   ```

## Set up a Supabase database

To set up a Supabase database that injects data into your {SERVICE_LONG}:

1. **Connect a foreign server in Supabase to your {SERVICE_LONG}**

   1. Connect to your Supabase project using Supabase dashboard or psql.
   2. Enable the `postgres_fdw` extension.

      ```sql theme={"dark"}
      CREATE EXTENSION postgres_fdw;
      ```
   3. Create a foreign server that points to your {SERVICE_LONG}.

      Update the following command with your [connection details][connection-info], then run it
      in the Supabase database:

      ```sql theme={"dark"}
      CREATE SERVER timescale
      FOREIGN DATA WRAPPER postgres_fdw
      OPTIONS (
          host '<value of host>',
          port '<value of port>',
          dbname '<value of dbname>',
          sslmode 'require',
          extensions 'timescaledb'
      );
      ```

2. **Create the user mapping for the foreign server**

   Update the following command with your [connection details][connection-info], the run it
   in the Supabase database:

   ```sql theme={"dark"}
   CREATE USER MAPPING FOR CURRENT_USER
   SERVER timescale
   OPTIONS (
      user '<value of user>',
      password '<value of password>'
   );
   ```

3. **Create a foreign table that points to a table in your {SERVICE_LONG}.**

   This query introduced the following columns:

   * `time`: with a default value of `now()`. This is because the `time` column is used by {CLOUD_LONG} to optimize data
     in the {COLUMNSTORE}.
   * `origin_time`: store the original timestamp of the data.

   Using both columns, you understand the delay between Supabase (`origin_time`) and the time the data is
   inserted into your {SERVICE_LONG} (`time`).

   ```sql theme={"dark"}
   CREATE FOREIGN TABLE signs (
     TIME timestamptz NOT NULL DEFAULT now(),
     origin_time timestamptz NOT NULL,
     NAME TEXT)
   SERVER timescale OPTIONS (
     schema_name 'public',
     table_name 'signs'
   );
   ```

4. **Create a foreign table in Supabase**

   1. Create a foreign table that matches the  `signs_per_minute` view in your {SERVICE_LONG}. It represents a top level
      view of the data.

      ```sql theme={"dark"}
      CREATE FOREIGN TABLE signs_per_minute (
       ts timestamptz,
       name text,
       total int
      )
      SERVER timescale OPTIONS (schema_name 'public', table_name 'signs_per_minute');
      ```

   2. Create a foreign table that matches the  `signs_per_minute_delay` view in your {SERVICE_LONG}.

      ```sql theme={"dark"}
      CREATE FOREIGN TABLE signs_per_minute_delay (
         ts timestamptz,
         avg_delay float8,
         stddev_delay float8,
         open float8,
         high float8,
         low float8,
         close float8
      ) SERVER timescale OPTIONS (schema_name 'public', table_name 'signs_per_minute_delay');
      ```

## Test the integration

To inject data into your {SERVICE_LONG} from a Supabase database using a foreign table:

1. **Insert data into your Supabase database**

   Connect to Supabase and run the following query:

   ```sql theme={"dark"}
   INSERT INTO signs (origin_time, name) VALUES (now(), 'test')
   ```

2. **Check the data in your {SERVICE_LONG}**

   [Connect to your {SERVICE_LONG}][in-console-editors] and run the following query:

   ```sql theme={"dark"}
   SELECT * from signs;
   ```

   You see something like:

   | origin\_time                  | time                          | name |
   | ----------------------------- | ----------------------------- | ---- |
   | 2025-02-27 16:30:04.682391+00 | 2025-02-27 16:30:04.682391+00 | test |

You have successfully integrated Supabase with your {SERVICE_LONG}.

[connection-info]: /integrations/find-connection-details

[hypertables-section]: /manage-data/capabilities/hypertables/understand-hypertables

[in-console-editors]: /deploy-and-operate/tiger-cloud/get-started/run-queries-from-console

[postgres-materialized-views]: https://www.postgresql.org/docs/current/rules-materializedviews.html

[supabase]: https://supabase.com/

[supabase-new-project]: https://supabase.com/dashboard/new