Trajectory data in PolarDB for PostgreSQL (GanosBase) grows continuously and can degrade query and write performance without appropriate configuration. This guide covers five practices that address the most common performance and storage issues: index selection, partitioning, attribute field types, write batching, and compression.
Choose the right index type
Ganos Trajectory supports three index types. The right choice depends on which dimensions your queries filter on.
| Index type | When to use | Index method |
|---|---|---|
| Spatial index | Queries that filter only on geographic area | GiST |
| Temporal index | Queries that filter only on time range | GiST or B-tree |
| Spatio-temporal composite index | Queries that filter on both area and time | GiST (with btree_gist) |
Creating the wrong index type—or no index—results in full table scans even when your query predicates match a single dimension.
Spatial index
Use a function-based GiST index on st_trajectoryspatial() to accelerate spatial filtering:
-- Accelerate filtering on geographic area
CREATE INDEX tr_spatial_geometry_index ON trajtab USING gist (st_trajectoryspatial(traj));Temporal index
For time-range queries, choose based on the filter type:
-- GiST index on time spans
CREATE INDEX tr_timespan_time_index ON trajtab USING gist (st_timespan(traj));
-- B-tree indexes on start and end times
CREATE INDEX tr_starttime_index ON trajtab USING btree (st_starttime(traj));
CREATE INDEX tr_endtime_index ON trajtab USING btree (st_endtime(traj));Spatio-temporal composite index
When queries filter on both spatial data and time range, a composite index avoids scanning each dimension separately. The btree_gist extension enables mixing B-tree and GiST columns in a single GiST index.
-- Enable the btree_gist extension (run once per database)
CREATE EXTENSION btree_gist;
-- Create a composite index on start time, end time, and spatial data
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 for growing datasets
As trajectory data accumulates, a single large table increases index size and slows queries. When table size reaches a point where query latency is unacceptable, partition the table by time to keep each partition's data volume manageable.
For setup instructions, see Overview.
Minimize string-type attribute fields
A large number of string-type attribute fields increases storage consumption and degrades query performance. Apply these strategies:
Convert fixed-value strings to integers. If an attribute field has a fixed set of values (such as status codes or category labels), convert it to an integer type in your application code. Integer storage is significantly more compact.
Set a default maximum length for required string fields. If string fields are necessary, cap their length to avoid unbounded storage growth.
To set the default maximum length for string-type attribute fields:
-- Set the default length of string-type attribute fields to 32 characters
SET ganos.trajectory.attr_string_length = 32;Build trajectory objects from batches of points
Appending trajectory points one by one generates many small write operations, increasing I/O overhead. Accumulate multiple points in your application and write them as a single trajectory object in one operation.
Enable LZ4 compression
LZ4 offers a higher compression ratio and execution speed than the default PostgreSQL compression algorithm. Enable it per session or for an entire database.
Per session:
-- Enable LZ4 compression for the current session
SET toast_compression_use_lz4 = true;
-- Revert to the default PostgreSQL compression algorithm
SET toast_compression_use_lz4 = false;Per database (applies to all subsequent connections):
-- Enable LZ4 compression as the database default
ALTER DATABASE dbname SET toast_compression_use_lz4 = true;
-- Revert to the default compression algorithm for the database
ALTER DATABASE dbname SET toast_compression_use_lz4 = false;