Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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".

31 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3 votes
0 answers
491 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 ...
Streamline's user avatar
2 votes
0 answers
302 views

Postgres: cache lookup failed for type 5308416

When inserting data into a Postgres (timescaledb hyper)table, the following error occurs approximately every 1-5 seconds. ERROR: cache lookup failed for type 5308416 I'm attempting to find what this ...
Dan's user avatar
  • 71
2 votes
1 answer
812 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 ...
Thomas's user avatar
  • 303
2 votes
2 answers
662 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 ...
Eventeos's user avatar
2 votes
0 answers
226 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 ...
xddz9's user avatar
  • 121
2 votes
0 answers
1k 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, ...
sev's user avatar
  • 247
2 votes
0 answers
42 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?
Onkar Janwa's user avatar
2 votes
0 answers
711 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 ...
Nyxynyx's user avatar
  • 1,121
1 vote
0 answers
18 views

Optimizing subqueries and common table expression queries

I have a database table where users send alert messages, the alert messages have their own categories. I use PostgreSQL with Timescale. I have two queries: Given a user, get the latest alert per ...
Razgriz's user avatar
  • 113
1 vote
0 answers
39 views

Does creating partitions using pg_partman locks the table

We are exploring and comparing pg_partman and timescale for auto creation and maintenance of partitions. The post here says pg_partman locks the table and may block writes on table. https://www....
dev Joshi's user avatar
  • 111
1 vote
0 answers
392 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 ...
Technaton's user avatar
  • 171
1 vote
0 answers
135 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 ...
Sylvain's user avatar
  • 189
1 vote
0 answers
205 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 ...
hg628193hg's user avatar
1 vote
2 answers
569 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 ...
user4772933's user avatar
1 vote
0 answers
114 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 ...
Philipp Staudt's user avatar
1 vote
0 answers
55 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,...
Greg's user avatar
  • 381
0 votes
0 answers
19 views

timescaledb/postgresql size reduction - _timescaledb_internal tables take up tons of space after deleting rows

I have a timescaledb set up, from which I just deleted a large amount of data. I vacuumed the tables that I deleted the data from, and the output of pg_size_pretty(pg_database_size(current_database()))...
Datguy's user avatar
  • 11
0 votes
0 answers
62 views

Optimize queries to track trading algorithm performance

I have a panel where a user can upload Python trading algorithms acting on the Binance API and track the performance of each algorithm in e.g a chart like this: For each algorithm the amount of BTC, ...
O'Niel's user avatar
  • 61
0 votes
1 answer
205 views

finding cause for sometimes slow query

I am trying to find the cause for a query that is slow, but only sometimes. I have slow query logging active, and I see the query being logged, for example: timescaledb-2 timescaledb 2023-08-04 13:43:...
simao's user avatar
  • 107
0 votes
1 answer
188 views

Optimizing TimescaleDB Setup for High-Volume Time-Series Data

I'm seeking advice on how to optimize my TimescaleDB setup, which handles a large volume of time-series data. I have around 20,000 time-series profiles with a one-year duration, using a quarterly time ...
Hannes 's user avatar
0 votes
0 answers
150 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 ...
Murat Suluhan's user avatar
0 votes
0 answers
32 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, ...
Thomas's user avatar
  • 303
0 votes
0 answers
201 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 ...
Thomas's user avatar
  • 303
0 votes
0 answers
333 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 ...
Anthony Le Gal's user avatar
0 votes
0 answers
926 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 ...
Ricoh's user avatar
  • 11
0 votes
1 answer
142 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 ...
Vic's user avatar
  • 1
0 votes
1 answer
888 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 ...
Min-Soo Pipefeet's user avatar
0 votes
0 answers
639 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 ...
newera's user avatar
  • 101
0 votes
0 answers
267 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 ...
sinanspd's user avatar
  • 123
0 votes
0 answers
402 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:/...
TmTron's user avatar
  • 253
0 votes
0 answers
87 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 (...
Gajus's user avatar
  • 1,195