Questions tagged [timescaledb]
TimescaleDB is an open source time-series extension of PostgreSQL. Use this tag for questions related to TimescaleDB. Also tag with the more general "time-series-databases".
26
questions with no upvoted or accepted answers
3
votes
0
answers
411
views
Improve performance of view based on TSDB continuous aggregation
I am currently trying to create a (materialized) view which is based on a TSDB (timescale DB) continuous aggregation. Some context:
I have a table which takes consumption data of meters through a ...
- 31
2
votes
1
answer
388
views
A complicated scenario with TimescaleDB and compression
We have a timescaleDB with a fairly large data set (> 1.5TB, >1B rows).
The project has been running into one delay after another because we just can't make the queries we need to perform fast ...
- 261
2
votes
2
answers
349
views
Slow query for multiple data at same timestamp
Hi everyone,
I'm currently facing a problem to query efficiently my database to retrieve all rows at same timestamp.
I will explain,
I have two table of data using TimescaleDb Hypertable with ...
- 31
2
votes
0
answers
188
views
How to optimize for performance when querying millions of rows in real-time analytics?
Suppose I have a simple table called events like this:
timestamp
event_id
action_count
apr 5 01:05:00
foo
10
apr 5 01:04:15
bar
20
apr 5 01:06:10
baz
200
apr 5 01:04:30
baz
5
apr 5 01:03:00
baz
...
- 121
2
votes
0
answers
869
views
Multiple tables vs rows for timescaledb / postgresql
I am designing a postgresql/timescale database where I will be recording trades on multiple stocks. For each stock I will be saving the following columns: Trade time, Attribute 1, Attribute 2, ...
- 247
2
votes
0
answers
34
views
How do I know which timescale db docker image was used?
I see timescaledb.last_tuned_version = '0.8.0' in postgresql.conf. How can I know which docker timescaledb image (exact version) was used?
- 153
2
votes
0
answers
643
views
Backing up Time Series Database (PostgreSQL/TimescaleDB) where old data will never be updated
If a PostgreSQL (using TimescaleDB extension) database that holds time series data is continuously growing in size, what is the recommended backup strategy if we do not want to continuously create ...
- 1,091
1
vote
0
answers
292
views
Improve TimescaleDB aggregate query execution time
Schema
The following database schema is realized in a PostgreSQL 14.2 database with TimescaleDB 2.5.1:
Indexes on tables exist for primary and foreign keys. As an example, this is the output of \d ...
- 171
1
vote
0
answers
105
views
How to speed up GroupAggregate
https://explain.depesz.com/s/I2IN
I have a query against a TimescaleDB 1.7 hypertable on Postgresql 12.
Lots of joins to other table. When I do the grouping it seems to take a lot of time, I switched ...
- 189
1
vote
0
answers
160
views
timescaleDB insertion speed issue
I’m trying to use pandas to interact with timescaleDB. I initiate my DB in DBeaver with the following
CREATE TABLE db_009a005a_df_downloaded_grand (
symbol VARCHAR(32),
timestamp TIMESTAMP ...
- 155
1
vote
2
answers
390
views
Optimizing table for timeseries Postgres data table
I have the below table which maintains a timeseries result. The row only becomes relevant when the signal is true, When signal is false, it just marks that for that particular timestamp we got a ...
- 123
1
vote
0
answers
96
views
Group by equidistant integer intervals
I am using TimescaleDB (an extension to PostgreSQL) to store altitude data from a GPS sensor. The table looks like:
CREATE TABLE IF NOT EXISTS altitude
(
time TIMESTAMPTZ NOT ...
1
vote
0
answers
53
views
How to aggregate transactions to form a stateful view which can be updated efficiently?
How to aggregate transactions to form a stateful view which can be updated efficiently?
For example how to turn the following transactions:
CREATE TABLE txns_combined(from_party,to_party,price,qty,...
- 371
0
votes
0
answers
28
views
postgresql with timescaledb backup/restore and upgrade issue
My customer is using TimescaleDB 2.3.1 at PostgreSQL 11.20. We are trying to upgrade PostgreSQL and Timescaledb currently
I installed PostgreSQL 13.11 and and TimescaleDB 2.11.0
I backedup the ...
- 15
0
votes
0
answers
5
views
js transform code applied to a postgres / timescale view
Let's assume this data type:
timestamp INT
ticker VARCHAR
price NUMERIC
with some example data:
0, A, 10
0, B, 100
1, A, 20
1, B, 1000
2, A, 30
2, B, 10000
...
And a JS function, implemented with ...
- 261
0
votes
0
answers
24
views
how to make joins on different tables based on the data retrieved, with TimeScale/Postgres
Here is a simplified version of the problem I'm trying to solve:
Table A with id and some data:
id, data
A01, xxxx
A02, xxxx
...
Table B with id and some other kind of data
id, data
B01, xxxx
B02, ...
- 261
0
votes
0
answers
84
views
Materialized view slow with Timescale
I have a table created like this:
-- enable timescale plugin
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
-- create schema
CREATE SCHEMA IF NOT EXISTS {schemaName};
-- create table
CREATE ...
- 261
0
votes
0
answers
160
views
Postgres and CopyManager, Database connection failed when starting copy at org.postgresql.core.PGStream.receiveChar
I'm trying to run copy mananger and load CSV file
val pgConnection = conn.unwrap(classOf[PgConnection])
val copyManager = new CopyManager(pgConnection)
val fis = new FileInputStream(tempTsvFilePath....
- 123
0
votes
0
answers
203
views
TimescaleDb : How to store long time events?
I’m trying to use TimescaleDb to store long time events (events started for example 2 days ago and ended 1 hour ago).
There are two types of events (current events without end date and historical ...
0
votes
0
answers
568
views
Installing older versions of timescaledb for postgresql on macos
I'm running postgresql 14.1 on MacOs Monterey, this was installed as a requirement of timescaledb which is at version 2.5.1.
I had issues while installing timescaledb with another version of ...
- 11
0
votes
1
answer
97
views
First metric in each time "bucket" for multiple IDs
I have the following table in PostgreSQL 12:
CREATE TABLE vehicle_fuel (
vehicle_id int NOT NULL
, submitted_at timestamp NOT NULL
, fuel float NOT NULL);
Currently there are ...
- 1
0
votes
1
answer
778
views
Ways to optimize the PostgreSQL/TimeScaleDB query
What approaches I could take to optimize the performance of the following joinless query on the following PostgreSQL/TimeScaleDB table? So far, I managed to create the right index which is obviously ...
- 101
0
votes
0
answers
567
views
Splitting columns and getting result as a JsonArray in Postgresql
I have a table like this:
Time | id/type | value
1 A/a 10
2 A/b 15
3 A/c 8
4 A/b 2
I need to get a Json Array in the format:
[[Time, value for ...
- 101
0
votes
0
answers
168
views
How does Timescale assign chunk ids?
Timescale allows us to query for chunk sizes in with something like
SELECT * FROM chunk_relation_size('....');
I am trying to monitor the chunk sizes so that if any of them reaches a size that is ...
- 123
0
votes
0
answers
348
views
How to use timescale-bitnami image on Windows?
Since there are issues with collations in the default alpine-based timescaledb images, I tried to use the bitnami-image. But I get an error on startup.
docker run ^
-p 5432:5432 ^
-v tsdb-bitnami:/...
- 233
0
votes
0
answers
85
views
How to partition a table without time data?
I am using postgresql to store information about event ticket sales.
I have two tables: event_sale and event_sale_seat:
event_sale (id integer, created_at timestamp with time zone)
event_sale_seat (...
- 1,105