Synchronize data between a vertex table and a trajectory table-PolarDB(PolarDB)-阿里云帮助中心

In most business scenarios, data is written as individual trajectory points. To run spatial analysis, those points must be aggregated into trajectory objects and stored in a trajectory table. PolarDB for PostgreSQL supports two approaches: manual synchronization on demand and automatic synchronization via a trigger.

Choose a synchronization method

Method	Real-time support	Write performance impact	When to use
Manual synchronization	No — runs on demand	Not applicable (runs outside of insert path)	Batch workloads or low-frequency syncs where write throughput matters
Automatic synchronization	Yes — fires on every insert	Reduced write performance on the vertex table	Workloads that require up-to-date trajectory data immediately after each insert

Choose manual synchronization when you run aggregation jobs on a schedule and cannot afford the per-row trigger overhead. Choose automatic synchronization when queries must reflect the latest trajectory data immediately after each insert.

Manual synchronization

Manual synchronization uses a user-defined function (UDF) that you call on demand. It collects all unsynced trajectory points from the vertex table, aggregates them into trajectory objects, and upserts the results into the trajectory table.

Step 1: Add a sync flag column and index

Add a boolean column to the vertex table to track which rows have been synced. Create a B-tree index on that column to speed up filtering of unsynced rows.

The sync column defaults to false, which marks every new row as not yet synced.

-- Add a column to track sync status (false = not yet synced)
ALTER TABLE sample_points ADD COLUMN sync bool DEFAULT false;

-- Create a B-tree index for efficient filtering of unsynced rows
CREATE INDEX ON sample_points USING btree(sync);

Step 2: Create the sync function

The function trajectory_cast_append() selects all unsynced rows, builds a sorted trajectory for each user, and upserts it into the trajectory table. If a trajectory for that user already exists, it appends the new points using ST_Append. After the upsert, it marks all previously unsynced rows as synced so they are excluded from the next run.

trajectory_cast_append() → void

CREATE OR REPLACE FUNCTION trajectory_cast_append()
RETURNS void
AS $$
BEGIN
  INSERT INTO trajectory_table
  SELECT userid, ST_Sort(ST_MakeTrajectory(pnts.tjraw, true, '{"intensity"}'::cstring[]))
  FROM
    (SELECT sample_points.userid,
            array_agg(ROW(sample_points.sample_time, sample_points.x, sample_points.y,
                          sample_points.z, sample_points.intensity)) AS tjraw
     FROM sample_points
     WHERE sync = false
     GROUP BY userid) pnts
  ON CONFLICT(userid) DO UPDATE
    SET traj = ST_Append(trajectory_table.traj, excluded.traj);

  -- Mark synced rows so they are excluded from the next run
  UPDATE sample_points
  SET sync = true WHERE sync = false;
END;
$$ LANGUAGE plpgsql STRICT PARALLEL SAFE;

Step 3: Run the sync

Call the function whenever you want to sync unsynced trajectory points to the trajectory table.

SELECT trajectory_cast_append();

Automatic synchronization

Automatic synchronization uses a PostgreSQL trigger that fires after every row inserted into the vertex table. The trigger function builds a trajectory from the new row and upserts it into the trajectory table immediately.

The trigger fires once per inserted row. On high-volume insert workloads, this reduces write performance on the vertex table.

Step 1: Create the trigger function

The function trajectory_sync_point() reads the newly inserted row from NEW, builds a single-point trajectory, and upserts it into the trajectory table. If a trajectory for that user already exists, it appends the new point using ST_Append.

trajectory_sync_point() → TRIGGER

CREATE OR REPLACE FUNCTION trajectory_sync_point() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO trajectory_table
  SELECT NEW.userid,
         ST_MakeTrajectory(array_agg(ROW(NEW.sample_time, NEW.x, NEW.y, NEW.z, NEW.intensity)),
                           true,
                           '{"intensity"}'::cstring[])
  ON CONFLICT(userid) DO UPDATE
    SET traj = ST_Append(trajectory_table.traj, excluded.traj);
  RETURN NULL;
END;
$$ LANGUAGE plpgsql STRICT PARALLEL SAFE;

Step 2: Register the trigger

Attach the trigger to the vertex table so it fires after each row is inserted.

CREATE TRIGGER point_trigger AFTER INSERT ON sample_points
    FOR EACH ROW EXECUTE PROCEDURE trajectory_sync_point();

After this statement runs, every subsequent INSERT on sample_points automatically updates the corresponding trajectory in trajectory_table.