Use Dynamic Tables

Dynamic tables are database objects similar to materialized views that refresh data automatically and speed up queries. SynxDB introduces dynamic tables to make query processing faster and data updates automatic.

Dynamic tables can be created from base tables, external tables, or materialized views. They update data automatically based on a schedule, keeping the data current.

Use cases

Dynamic tables are suitable for these scenarios:

  • Faster queries in lakehouse setups: Replace external table queries with dynamic table queries to improve performance. This substitution reuses the AQUMV query rewrite path, so both enable_answer_query_using_materialized_views and aqumv_allow_foreign_table must be set to ON. Both default to off.

  • Automatic data updates: Schedule refresh tasks to keep data updated without manual effort.

  • Real-time analysis: Do well for scenarios that need frequent and up-to-date queries, such as financial analysis or operations monitoring.

Comparison with materialized views

Dynamic tables and Answer Query Using Materialized Views have these differences:

Feature

Dynamic table

AQUMV

Purpose

A special table that automatically refreshes, processes data pipelines, and simplifies ETL.

Uses materialized views to improve query efficiency and automatically rewrite queries.

Base and structure

Can be based on ordinary tables, external tables, materialized views.

Based on materialized views, usually targeting a single table.

Query rewrite

Supports replacing external table queries, requires enable_answer_query_using_materialized_views and aqumv_allow_foreign_table

Supports single-table rewrite and multi-table JOIN exact-match rewrite

Data refresh mechanism

Users can define automatic refresh interval through SQL; without SCHEDULE, refresh defaults to every 5 minutes

Incremental materialized views maintain themselves; regular materialized views need a manual REFRESH

Usage

Create a dynamic table

To create a dynamic table, use the CREATE DYNAMIC TABLE statement:

CREATE DYNAMIC TABLE table_name [SCHEDULE '<cron_string>'] AS <select_query> [WITH NO DATA] [DISTRIBUTED BY <distribution_key>];

Parameter details:

  • <select_query>: The SQL query with which the dynamic table is defined. Supports joins with base tables, materialized views, or other dynamic tables.

  • WITH NO DATA: Creates the table without initial data.

  • SCHEDULE '<cron_string>': Sets the refresh schedule using Linux cron expressions. See Cron Expressions for more details. This clause must come before AS; placing it after AS <select_query> is a syntax error. If you omit the clause, the table still refreshes automatically: a refresh task is created with the default schedule */5 * * * * (every 5 minutes).

  • DISTRIBUTED BY: Defines the distribution key to improve query performance.

Full syntax:

CREATE DYNAMIC TABLE table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ SCHEDULE schedule_string ]
    AS query
    [ WITH [ NO ] DATA ]
    [ DISTRIBUTED BY (keys [, ...]) ];

Example:

CREATE DYNAMIC TABLE dt0 SCHEDULE '5 * * * *' AS SELECT a, b, sum(c) FROM t1 GROUP BY a, b WITH NO DATA DISTRIBUTED BY(b);

This example creates a dynamic table dt0 and schedules it to refresh at minute 5 of every hour. To refresh every 5 minutes, write the cron expression as */5 * * * *.

Use \dm to confirm the object type is dynamic table:

\dm dt0

            List of relations
 Schema | Name |     Type      |  Owner
--------+------+---------------+---------
 public | dt0  | dynamic table | gpadmin
(1 row)

Refresh a dynamic table

Dynamic tables refresh automatically based on their SCHEDULE setting. To manually refresh a table, use the REFRESH DYNAMIC TABLE statement. Replace table_name with the actual table name:

REFRESH DYNAMIC TABLE table_name;

To clear the table and make it unreadable, use the WITH NO DATA option. Replace table_name with the actual table name:

REFRESH DYNAMIC TABLE table_name WITH NO DATA;

View schedule information

To view the refresh schedule of a dynamic table, use the pg_get_dynamic_table_schedule function. Replace table_name with the actual table name:

SELECT pg_get_dynamic_table_schedule('table_name'::regclass::oid);

This function covers one table at a time. To see the refresh tasks of every dynamic table in the current database at once, query pg_task:

SELECT jobid, active, schedule, command, jobname FROM pg_task ORDER BY jobid;

 jobid | active |  schedule   |              command              |            jobname
-------+--------+-------------+-----------------------------------+--------------------------------
 25892 | t      | 5 * * * *   | REFRESH DYNAMIC TABLE public.dt0  | gp_dynamic_table_refresh_25888
 25897 | t      | */5 * * * * | REFRESH DYNAMIC TABLE public.dt_b | gp_dynamic_table_refresh_25893

Drop a dynamic table

To delete a dynamic table and its associated refresh tasks, use the DROP DYNAMIC TABLE statement. Replace table_name with the actual table name:

DROP DYNAMIC TABLE table_name;

View distribution key

You can specify a distribution key when creating a dynamic table to optimize query performance. Use the \d+ command to view distribution keys and query definitions.

Example:

\d+ dt0

Output:

                                       Dynamic table "public.dt0"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 a      | integer |           |          |         | plain   |             |              |
 b      | integer |           |          |         | plain   |             |              |
 sum    | bigint  |           |          |         | plain   |             |              |
View definition:
 SELECT t1.a,
    t1.b,
    sum(t1.c) AS sum
   FROM t1
  GROUP BY t1.a, t1.b;
Distributed by: (b)
Access method: heap

In this example, the dynamic table dt0 uses column b as its distribution key. This ensures efficient query and aggregation operations, because data is distributed across nodes based on b.

Examples

Example 1: Accelerate external table queries in lake-house architecture

SynxDB automatically replaces queries to external tables with queries on dynamic tables when processing data from data lakes or external storage, thereby accelerating the query process.

  1. Register the sample protocol demoprot. It is provided by gpextprotocol.so, which ships with SynxDB, but it is not registered by default. In real use, substitute a protocol such as gpfdist or file.

    CREATE OR REPLACE FUNCTION demoprot_export() RETURNS integer AS '$libdir/gpextprotocol', 'demoprot_export' LANGUAGE C STABLE;
CREATE OR REPLACE FUNCTION demoprot_import() RETURNS integer AS '$libdir/gpextprotocol', 'demoprot_import' LANGUAGE C STABLE;
CREATE TRUSTED PROTOCOL demoprot (writefunc = demoprot_export, readfunc = demoprot_import);

    Prepare the data file through a writable external table. A demoprot path is relative to each segment’s data directory, and every segment writes its own copy of the file.

    CREATE WRITABLE EXTERNAL TABLE ext_w(id int)
    LOCATION('demoprot://dynamic_table_text_file.txt')
FORMAT 'text';

CREATE TABLE dyn_src(id int) DISTRIBUTED BY (id);
INSERT INTO dyn_src SELECT i FROM generate_series(1, 1000) i;
INSERT INTO ext_w SELECT id FROM dyn_src;

  2. Create a readable external table ext_r with data sourced from the specified file dynamic_table_text_file.txt.

    CREATE READABLE EXTERNAL TABLE ext_r(id int)
    LOCATION('demoprot://dynamic_table_text_file.txt')
FORMAT 'text';

  3. Use EXPLAIN to view the query execution plan for the table with the query condition id > 5.

    EXPLAIN(COSTS OFF, VERBOSE)
SELECT sum(id) FROM ext_r WHERE id > 5;

    Query plan output. In the following plan, Foreign Scan on public.ext_r appears, which means that the planner directly scanned the external table ext_r.

                       QUERY PLAN
------------------------------------------------
 Finalize Aggregate
   Output: sum(id)
   ->  Gather Motion 4:1  (slice1; segments: 4)
         Output: (PARTIAL sum(id))
         ->  Partial Aggregate
               Output: PARTIAL sum(id)
               ->  Foreign Scan on public.ext_r
                     Output: id
                     Filter: (ext_r.id > 5)
 Settings: optimizer = 'off'
 Optimizer: Postgres query optimizer
(11 rows)

  4. Create a dynamic table dt_external to store the filtered data from the external table ext_r, and execute ANALYZE to update the table statistics.

    CREATE DYNAMIC TABLE dt_external AS
SELECT * FROM ext_r WHERE id > 5;
ANALYZE dt_external;

  5. Set the parameters in a transaction to enable dynamic table query acceleration.

    BEGIN;
SET optimizer = OFF;
SET LOCAL enable_answer_query_using_materialized_views = ON;
SET LOCAL aqumv_allow_foreign_table = ON;

  6. Again, use EXPLAIN to view the query execution plan for the table with the query condition id > 5.

    Use dynamic table to automatically replace external table queries, replacing the query to the external table ext_r with a query to the dynamic table dt_external, thereby accelerating the query.

    EXPLAIN(COSTS OFF, VERBOSE)
SELECT sum(id) FROM ext_r WHERE id > 5;

COMMIT;

    Query plan output. In the following plan, Seq Scan on public.dt_external indicates that the planner automatically replaced the query on the external table with the query on the dynamic table dt_external.

                                        QUERY PLAN
----------------------------------------------------------------------------------
 Finalize Aggregate
   Output: sum(id)
   ->  Gather Motion 4:1  (slice1; segments: 4)
         Output: (PARTIAL sum(id))
         ->  Partial Aggregate
               Output: PARTIAL sum(id)
               ->  Seq Scan on public.dt_external
                     Output: id
 Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off'
 Optimizer: Postgres query optimizer
(10 rows)

    Note

    The SET LOCAL statements above must run inside a transaction block. Outside one they produce WARNING: SET LOCAL can only be used in transaction blocks and take no effect, and the plan still shows Foreign Scan.

Through this process, query efficiency is improved because the operation that originally required scanning the external table is replaced by scanning the dynamic table, which reduces query response time.

Example 2: Create an empty dynamic table

  1. Create a base table existing_table and insert data:

    CREATE TABLE existing_table (
    id INT,
    name VARCHAR(100),
    value INT
);

INSERT INTO existing_table (id, name, value) VALUES
(1, 'Alice', 100),
(2, 'Bob', 150),
(3, 'Charlie', 200);

  2. Create an empty dynamic table:

    CREATE DYNAMIC TABLE empty_table AS
SELECT * FROM existing_table
WITH NO DATA;

    This statement creates an empty dynamic table, which can be populated with data through manual refresh later.

  3. Check that this table is empty:

    SELECT * FROM empty_table;

-- ERROR:  materialized view "empty_table" has not been populated
-- HINT:  Use the REFRESH MATERIALIZED VIEW command.

Notes

  • Refresh strategy: Ensure to set an appropriate refresh interval. If the refresh interval is too frequent, it might increase system load; if the refresh interval is too sparse, the query data might not be timely.

  • Query replacement: The performance advantage of dynamic tables lies in automatically replacing external table queries, making them suitable for query scenarios that frequently require external table data.

  • Data consistency: Because dynamic tables are a type of materialized view, pay attention to the balance between refresh strategy and data consistency to ensure the accuracy of application logic.