Integrate Supabase with Timescale Cloud

Optimize time-series data in hypertables

Time-series data represents how a system, process, or behavior changes over time. Hypertables are PostgreSQL tables that help you improve insert and query performance by automatically partitioning your data by time.

1. Connect to your Timescale Cloud service and create a table that will point to a Supabase database:

   CREATE TABLE signs (
       time timestamptz NOT NULL DEFAULT now(), 
       origin_time timestamptz NOT NULL, 
       name TEXT
   );

   Copy

2. Turn the table to a hypertable:

   SELECT create_hypertable('signs', by_range('time'));

   Copy

Optimize cooling data for analytics

Hypercore is the Timescale 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 Timescale Cloud to deliver the best of both worlds, solving the key challenges in real-time analytics.

ALTER TABLE signs SET (
  timescaledb.enable_columnstore = true, 
  timescaledb.segmentby = 'name');

Copy

Create optimized analytical queries

Continuous aggregates are designed to make queries on very large datasets run faster. Continuous aggregates in Timescale Cloud use PostgreSQL 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 continuous aggregate pointing to the Supabase database.

   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;

   Copy

2. Setup a delay stats comparing origin_time to time.

   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;

   Copy

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

   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

   Copy

Add refresh policies for your analytical queries

You use start_offset and end_offset to define the time range that the continuous aggregate 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 continuous aggregate is refreshed every minute, and the refresh covers the last 5 minutes. You set schedule_interval to INTERVAL '1 minute' so the continuous aggregate refreshes on your Timescale Cloud service every minute. The data is accessed from Supabase, and the continuous aggregate is refreshed every minute in the other side.

SELECT add_continuous_aggregate_policy('signs_per_minute',
 start_offset => INTERVAL '5 minutes',
 end_offset => INTERVAL '1 minute', 
 schedule_interval => INTERVAL '1 minute');

Copy

Do the same thing for data inserted with a delay:

SELECT add_continuous_aggregate_policy('_signs_per_minute_delay',
 start_offset => INTERVAL '5 minutes', 
 end_offset => INTERVAL '1 minute', 
 schedule_interval => INTERVAL '1 minute');

Copy

Connect a foreign server in Supabase to your Timescale Cloud service

1. Connect to your Supabase project using Supabase dashboard or psql.

2. Enable the postgres_fdw extension.

   CREATE EXTENSION postgres_fdw;

   Copy

3. Create a foreign server that points to your Timescale Cloud service.

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

   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'
   );

   Copy

Create the user mapping for the foreign server

Update the following command with your connection details, the run it in the Supabase database:

CREATE USER MAPPING FOR CURRENT_USER 
SERVER timescale 
OPTIONS (
   user '<value of user>', 
   password '<value of password>'
);

Copy

Create a foreign table that points to a table in your Timescale Cloud service.

This query introduced the following columns:

• time: with a default value of now(). This is because the time column is used by Timescale Cloud 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 Timescale Cloud service (time).

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'
);

Copy

Create a foreign table in Supabase

1. Create a foreign table that matches the signs_per_minute view in your Timescale Cloud service. It represents a top level view of the data.

   CREATE FOREIGN TABLE signs_per_minute (
    ts timestamptz, 
    name text, 
    total int
   ) 
   SERVER timescale OPTIONS (schema_name 'public', table_name 'signs_per_minute');

   Copy

2. Create a foreign table that matches the signs_per_minute_delay view in your Timescale Cloud service.

   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');

   Copy

Insert data into your Supabase database

Connect to Supabase and run the following query:

INSERT INTO signs (origin_time, name) VALUES (now(), 'test')

Copy

Check the data in your Timescale Cloud service

Connect to your Timescale Cloud service and run the following query:

SELECT * from signs;

Copy

You see something like: