Created readings, diagnostics and tag table. Modified readings table to create data partitions as follows.
CREATE TABLE public.readings (
time timestamp with time zone
,tags_id integer
,name text
,latitude double precision
,longitude double precision
,elevation double precision
,velocity double precision
,heading double precision
,grade double precision
,fuel_consumption double precision
,additional_tags jsonb
) PARTITION BY RANGE (time);
ALTER TABLE public.readings
ALTER COLUMN elevation TYPE real,
ALTER COLUMN velocity TYPE real,
ALTER COLUMN heading TYPE real,
ALTER COLUMN grade TYPE real,
ALTER COLUMN fuel_consumption TYPE real;
CREATE INDEX readings_tags_id_time_idx ON public.readings USING btree (tags_id, time DESC);
CREATE INDEX readings_time_idx ON public.readings USING btree (time DESC);
CREATE INDEX readings_latitude_time_idx ON public.readings USING btree (latitude, time DESC);
CREATE INDEX readings_time_brin_idx ON public.readings USING BRIN (time) WITH (pages_per_range = 32);
SELECT partman.create_parent(
p_parent_table => 'public.readings',
p_control => 'time',
p_type => 'native',
p_interval=> 'hourly',
p_start_partition=> '2021-06-08 00:00:00',
p_premake => 168); -- 24*7
UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'public.readings';
Without creating data partitions on readings table, benchmark works fine.
Trying to use tsbs_load_timescaledb to benchmark native partitions in Postgres 13.3.
cat timescaledbdata_iot_Jun21_3days.gz | gunzip | /root/go/bin/tsbs_load_timescaledb --postgres=sslmode=disable --db-name=test --host="$PGHOST" --port="$PGPORT" --pass="$PGPASSWORD" --user="$PGUSER" --workers=4 --batch-size=10000 --reporting-period=10s --use-hypertable=false --use-jsonb-tags=false --in-table-partition-tag=true --hash-workers=false --time-partition-index=false --partitions=0 --write-profile= --field-index-count=1 --create-metrics-table=false --do-create-db=false --force-text-format=false 2>&1
It fails with the following error -
Created readings, diagnostics and tag table. Modified readings table to create data partitions as follows.
Without creating data partitions on readings table, benchmark works fine.