776 questions
0
votes
0
answers
50
views
TimescaleDB Hypertable Performance Slower Than Normal Table for Time-Series Queries – Why?
I created two tables—one normal table and one TimescaleDB hypertable—and inserted 50 million records into each. However, I’m not seeing a significant performance difference between them. In fact, the ...
- 163
0
votes
0
answers
36
views
PostgreSQL / TimeScaleDB issue after reimporting a huge set of data [migrated]
I have a PostgresQL 17 DB with a quite simple table (no outer relations) but with a lot of data (around > 446 000 000 records) storing some events on a day basis representing more than 2 years of ...
- 3
1
vote
1
answer
94
views
Slow TimescaleDB lookup on indexed column (event_key) without a time filter
I'm using a self-hosted TimescaleDB instance to store logs. I have a hypertable logs that is partitioned by the timestamp column.
I need to perform a fast lookup query based on the event_key column, ...
- 11
1
vote
0
answers
44
views
Unique ID in timescaledb's hypertable
I've encountered a big roadblock on migrating my data to hypertables: a unique constraint can only exist if it includes the column used by the hypertable to partition. However, we often reference data ...
- 20
0
votes
1
answer
69
views
PgCat sometimes routes write queries to replicas
I’m testing a TimescaleDB cluster (1 primary + 2 replicas) behind PgCat, running in Docker Swarm.
Sometimes write queries (CREATE TABLE, INSERT) fail with:
psycopg.errors.ReadOnlySqlTransaction: ...
- 144
0
votes
1
answer
124
views
Why does the same query breaks when upgrading from postgre 14.17 to 14.19 when using timestamp?
After our provider upgraded our pg-14 database from 14.17 to 14.19, with timescaledb extension 2.5.2, our app completely broke because of awful database performances.
Our analysis showed that the ...
- 2,479
0
votes
0
answers
41
views
TimescaleDB hierarchical continuous aggregate with same time_bucket intermittently not refreshing
I’m working with hierarchical continuous aggregates in TimescaleDB and noticed inconsistent behavior. According to the documentation:
"The time bucket of a continuous aggregate should be greater ...
- 1
0
votes
0
answers
60
views
Hypercore Timescaledb ORDER BY not working as expected
We have a hypertable :
transact_time (BIGINT) : time column for partition,
id (BIGINT) : unique key
All the queries that hit this table are of the pattern :
SELECT [columns]
FROM hypertable
WHERE ...
0
votes
0
answers
105
views
Prepared statements not working with Psycopg3 + PgBouncer (transaction pooling)
I'm running into unexpected behavior when trying to use prepared statements with TimescaleDB through PgBouncer in transaction pooling mode.
According to the Psycopg3 docs on prepared statements
and ...
- 1
2
votes
1
answer
224
views
How can I make Telegraf create TimescaleDB columns as double precision[] when ingesting array data from MQTT?
I am trying to configure Telegraf to ingest array data from an MQTT topic into TimescaleDB.
My goal is for array-like values to end up as double precision[] instead of text.
For example, if I prepare ...
- 197
3
votes
1
answer
99
views
Why does my query get slower and not faster when I constrain the data set?
I have a TimescaleDB table that records activities of various devices. This is a huge table and any interactions with it are normally expensive. Devices can be enabled as a group, and activation has a ...
- 16.1k
0
votes
0
answers
230
views
TimescaleDB installation on postgres 14
I'm struggling to properly install TimescaleDB on my existing postgres 14 database.
On my other machine I have postgres 16 and I installed it flawlessly, everything worked from the start but on psql14 ...
0
votes
0
answers
92
views
Upgrade PostgreSQL TimescaleDB on Windows
I have PostgreSQL v17.0 and TimescaleDB v2.18.0 installed on a windows 11 64bit machine. Now I want to upgrade TimescaleDB to v2.20.3, but the upgrade procedure doesn't work, PostgreSQL still reports ...
- 156
-1
votes
1
answer
493
views
How can you use the TimescaleDB extension with Azure Postgres Flexible server for Postgres v17.*
I am attempting to use the TimescaleDB extension with Azure Postgres but I keep getting this error
Because timescaledb isn't a trusted extension, only members of "azure_pg_admin" are ...
- 4,014
1
vote
1
answer
75
views
TimeScaleDb/Postgres: Materialized Views(COGG): GROUP BY: group by certain field values
What I'm currently doing is this:
SELECT
time_bucket('60 min', raw_data.timestamp) AS time_60min,
COUNT(raw_data.vehicle_class) AS "count",
raw_data.vehicle_class AS "...
- 23
1
vote
0
answers
62
views
Issue refreshing continuous aggregate in timescale: out of order points
I'm running into an issue trying to create a continuous aggregate in timescale where the refresh command throws an error ERROR: out of order points: points must be submitted in time-order. I'm new to ...
- 535
0
votes
0
answers
118
views
TIMESCALEDB SLOW PERFORMANCE WITH IGNITION HISTORY
I’m currently using TimescaleDB 2.19.0 integrated with Ignition, and I’m encountering a significant performance discrepancy between executing the same SQL query via different tools:
When the query is ...
-1
votes
1
answer
60
views
SQL Query for time-dependent condition [closed]
I need some help with selecting a self-hosted time-series database
Let's say
I have a device that forecasts some value, e.g. tomorrow's temperature in my room.
The device always forecasts exactly 24 ...
- 908
0
votes
0
answers
276
views
Very slow query on TimescaleDB when retrieving latest values
I am testing a Timescale DB for a migration, and there is a query which is very slow depending on the time range, when using m.ts >= '2019-07-03 11:50:00+00' the query takes 1m30s (!), but with m....
- 53
0
votes
1
answer
114
views
Telegraf read password from environment/file for PostgreSQL output
I have a Telegraf configuration with which I connect my PostgreSQL.
If I store the password there in plain text, the connection works:
connection="host=HOST port=PORT user=USER password=PASSWORD ...
- 23
0
votes
1
answer
72
views
Add compression policy to timescaledb on continuous aggregate with integer time
I have a continuous aggregate on a hypertable that has an integer based time. The integer value is not an epoch() but it is a monotonically increasing integer, producing a new value approximately ...
- 987
4
votes
3
answers
111
views
LIMIT clause in subquery with identical row count finishes faster than when omitted
I have two identical queries to a PostGIS database, but one takes much more time to finish.
'table1' is a TimescaleDB hypertable and contains ~7 million rows, while table2 contains only 10 rows. These ...
- 41
0
votes
1
answer
110
views
Alembic keeps deleting and recreating Foreign Keys during autogenration
After the autogenrated initial migration, when running another autogeneration all foreign keys are beeing dropped and recreated.
Example Code from the migration script:
op.drop_constraint('...
- 364
2
votes
0
answers
175
views
installing postgresql with extension timescaledb with Replication and HA
I have installed postgres using helm charts
helm install patroni bitnami/postgresql-ha --values default-values-repmgr.yaml
Image: bitnami/postgresql-repmgr:16.4.0-debian-12-r27
postgresql-repmgr this ...
- 121
0
votes
0
answers
126
views
Timescaledb and replication along with HA using Helm Charts
Im using kubernates with 3 Nodes.
I need one Image which has postgres extention timescaledb & patroni (or repmgr) which will do the replication and HA using Helm Charts
Im using this image ...
- 121
0
votes
1
answer
48
views
TimescaleDB carry forward then aggregate in buckets
I have a table that contains IoT updates from many devices. I'd like to calculate an average for each hour based on the device readings. I would like those readings to be carried forward before ...
- 1,289
0
votes
1
answer
199
views
How to determine the adequate number of partitions on Postgres and Timescale?
Edit note: This question was updated a couple of times for clarification.
The problem
We have a hypertable that holds the latest device readings. At this point we have around 1000 devices (each device ...
- 36
0
votes
1
answer
161
views
Postgres read replica tuning for parallel workloads. Queries canceled
I am currently setting up a read replica to offload a heavy parallel workload from master to a replica, so that the heavy parallel reads we are doing are not affecting other important task that the ...
- 33
1
vote
1
answer
58
views
Creating a merged dataset from a subset of variables from multiple hypertables with TimescaleDB - continuous aggregate
I wanted to see if anyone can suggest ways around this before I give up on it entirely as an option.
We have a Postgres (14) TimescaleDB database. It has several hypertables, each of which is ...
- 199
1
vote
0
answers
159
views
Rolling Window Averages
I'll try to keep this brief. I have a hyper table for stock data called tick_data. I then create a continuous aggregation of 5 seconds of this data. With this I'm trying to create 1 minute ...
- 15
0
votes
1
answer
515
views
Inserting new data into the HyperTable fails with Exception
I am trying to insert data into a hyper table using c# .net application and I am getting the error/exception:
MessageText: invalid INSERT on the root table of hypertable "_hyper_31_1083_chunk&...
- 25
1
vote
1
answer
51
views
Registered job in TimescalDB calls incorrect user function
I have user function:
CREATE FUNCTION public.some_func(arg1 timestamp without time zone DEFAULT NULL, arg2
BOOLEAN DEFAULT FALSE)
RETURNS text
...
In timescaledb(2.15.3) I registered job as:
...
- 43.3k
0
votes
0
answers
97
views
Issues while interpolating using time_bucket_gapfill with data within that bucket
I'm having the same issue here as already asked before in this post:
time_bucket_gapfill and interpolate
When using time bucket with readings the interpolation only calculate the correct value on &...
1
vote
2
answers
81
views
Retaining and inserting state into a postgres aggregation function
I have a case where I have a table "tab" that stores data (keyed by symbol and time) as well as a second table "summ" that stores the exponential weighted running average of the ...
- 1,502
0
votes
2
answers
176
views
TimescaleDB not accepting intervales less than one day
I have a timescaleDB but I cannot seem to be able to use the time_bucket method with less than one day intervals.
The code attempting to access it is running inside of a fast API and is as follows :
...
0
votes
0
answers
197
views
Improving TimescaleDB Compression Ratios; Using Brotli Parquet to Compare
I've got about 435 million rows of the form:
time, sensor_id, sensor_value
where time is a unix timestamp rounded to one decimal point, sensor_id is an integer, and the sensor_value is a float. The ...
- 1,310
0
votes
1
answer
68
views
Extracting a date selector from a string in postgreSQL and timescaleDB
How do I schedule performant string extraction in postgreSQL and timescaledb?
I want to extract unique dates from a string to use as a selector for a realtime time series database. I use the DISTINCT ...
- 63
1
vote
0
answers
82
views
Convert Timescaledb query to Clickhouse query
Following are the queries in timescaledb which i need to convert into clickhouse as clickhouse supports timeseries data as well
Following are the timescaledb queries:
CREATE MATERIALIZED VIEW
...
1
vote
1
answer
110
views
Create time series in TimeScaleDB from filtered raw data
This is my first time using TimeScaleDB so I don't have a lot of experience with its usage patterns.
Here's the context: I'm building an analytics application that uses TimeScaleDB to generate time ...
- 668
0
votes
1
answer
366
views
Issue with Storing and Loading Index Timescale Vector Llama Index
I'm currently working with the llama_index Python package and using the llama-index-vector-stores-timescalevector extension to manage my vectors with Timescale. However, I’ve encountered an issue ...
-1
votes
1
answer
203
views
Timescaledb high resource consume
I’m working with a TimescaleDB instance that has 10 CPU cores and 10 GiB of RAM, currently hosting approximately 9 million rows, which is continually growing. I'm encountering resource limitations and ...
- 97
0
votes
1
answer
101
views
Foreign key constraint violation for referencing timescale postgres table
I have postgres timescale table that stores sensor measures. The table has composite primary key with the id int incremental column and the dateTime column(it was required to be composite in oreder to ...
- 1
0
votes
0
answers
157
views
upgrade of Postgres 12.3 /Timescaledb 2.5.1 , very large database of 30TB to PG 16.2 /timescaledb 2.14
We have a very large Postgres database on version 12.3 with timescaledb version 2.5.1.
Size of db ~30 TB
**# of entries in _timescaledb_internal = ~1.7 million. (chunks for historical data).
**# of ...
- 1
0
votes
2
answers
270
views
Performance Issues with High Insert Rate and Lock Accumulation in PostgreSQL Cluster with TimescaleDB
I have a PostgreSQL cluster composed of four machines, configured as follows:
Three PostgreSQL nodes managed by Patroni and ETCD, using PgBouncer. One of these nodes is the primary, and the other two ...
1
vote
1
answer
163
views
How should I automate refreshing of my continuous aggregates every time I insert historical data
I have created multiple continous aggregates on my hypertable using timescaledb 2.15.3 extension in postgreSQL 15.
When inserting historical data to this hypertable, I have to manually refresh all ...
- 91
1
vote
1
answer
67
views
TimescaleDB: Populate a Continuous Aggregate Gradually?
I have a massive live table that I would like to continuously aggregate. The live table is massive, so it would take hours to fully populate the materialized view for the continuous aggregate.
Is it ...
- 1,234
0
votes
1
answer
199
views
Data not getting deleted automatically or manually in TimescaleDB
I observe that data is not getting deleted from Timescale DB tables either automatically or manually. Here are my DDLs:
CREATE TABLE traffic
(
src_ip_addr INET,
ts TIMESTAMPTZ NOT ...
- 7,397
0
votes
1
answer
322
views
Kafka Connect JDBC sink date/time conversion to PostgreSQL timestamp with timezone
We have a Kafka topic with Cloud Events in JSON Schema format that we connected to Timescaledb/PostgreSQL with JDBC sink.
To convert a table to a hypertable we need a time column, but our date time ...
- 1,041
0
votes
0
answers
55
views
Is it possible to change compression settings of non-empty hypertable?
I'm curious what would happen in case I'll change the compression settings of certain hypertable which already have some data.
I'm not talking about compression policy (which is, as I understand, ...
- 1,819
0
votes
1
answer
160
views
Add dimension to existing timescaled hypertable
I currently have a hypertable that is partitioned only by time and I want to add an extra dimension e.g. account_id.
My timescaledb version is 2.8.1 and reading through the docs, I found this:
You ...
- 1,190