Trajectory best practices-AnalyticDB(AnalyticDB)-阿里云帮助中心

Trajectory datasets grow continuously. Without deliberate optimization, query latency and storage costs increase with your data volume. Apply the following practices to keep queries fast and storage lean as your data scales.

Choose the right index type

Trajectory data supports three index types. Match the index to your query pattern—using the wrong index wastes storage and provides no speed benefit.

Query pattern	Index type	Use when
Spatial filter only	Spatial index	Filtering by geographic area, not by time
Time filter only	Temporal index	Filtering by time range, not by location
Both spatial and time filters	Spatio-temporal composite index	Combining a geographic area with a time window

Create a spatial index

A spatial index on st_trajectoryspatial(traj) lets the query planner prune rows by bounding box before running exact geometric comparisons.

-- Create a function-based spatial index to accelerate the filtering of spatial data.
create index tr_spatial_geometry_index on trajtab using gist (st_trajectoryspatial(traj));

Create a temporal index

For time-only queries, index st_timespan(traj) to filter by the full time span of each trajectory, or use B-tree indexes on st_starttime and st_endtime for point-in-time comparisons.

-- Create a function-based temporal index to accelerate the filtering of time.
create index tr_timespan_time_index on trajtab using gist (st_timespan(traj));

-- Create function-based indexes on the start time and end time of a trajectory object.
create index tr_starttime_index on trajtab using btree (st_starttime(traj));
create index tr_endtime_index on trajtab using btree (st_endtime(traj));

Create a spatio-temporal composite index

A composite index on start time, end time, and spatial extent handles queries that filter on both location and time window in a single index scan. This index requires the btree_gist extension.

-- Create a btree_gist extension.
create extension btree_gist;

-- Use btree_gist to create a spatio-temporal composite index on the start time, end time, and spatial data of a trajectory object.
create index tr_traj_test_stm_etm_sp_index on traj_test using gist (st_starttime(traj),st_endtime(traj),st_trajectoryspatial(traj));

Use partitioned tables

As trajectory data accumulates, index size grows and queries slow down. Partition your table to keep each partition's index small and scans targeted.

For partitioning syntax, see Table partitioning in the PostgreSQL documentation.

Minimize string-type attribute fields

String fields in trajectory data consume storage space and slow down queries. Apply the following strategies to reduce their impact:

Convert fixed-value strings to integers. If a string field takes a bounded set of values (for example, a vehicle type or status code), map those values to integers in your application code before writing to the database.
Set a maximum length for required string fields. Use ganos.trajectory.attr_string_length to cap the default string field length and avoid allocating excess space.

-- Set the default length of string-type attribute fields to 32.
Set ganos.trajectory.attr_string_length = 32;

Build trajectory objects from multiple points

Appending trajectory points one at a time causes repeated write amplification and fragmentation. Collect multiple points in your application and generate the trajectory object in a single write.

Enable LZ4 compression

LZ4 is a fast compression algorithm with a high compression ratio. Enable it at the session level or set it as the database default.

Session level:

-- Enable LZ4 compression.
Set toast_compression_use_lz4 = true;

-- Disable LZ4 compression and use the default PostgreSQL compression algorithm.
Set toast_compression_use_lz4 = false;

Database level (applies to all new sessions by default):

-- Enable LZ4 compression for the database.
Alter database <dbname> Set toast_compression_use_lz4 = true;

-- Disable LZ4 compression and use the default compression algorithm for the database.
Alter database <dbname> Set toast_compression_use_lz4 = false;

Replace <dbname> with the name of your database.