Skip to main content

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
17 views

INSTEAD OF INSERT ON never gets triggered from continous aggregate (timescale)

I'm trying to make a trigger based on when a continuous aggregate is updated. I had the trigger working on the source table, but when trying to add it on the aggregate: CREATE TRIGGER ...
Cyberwiz's user avatar
  • 101
0 votes
0 answers
34 views

Best database solution for high volume tables / data indexing (snapshots) [PostgreSQL]

My problem at the moment, is that I need to index/save snapshots of some data every x minutes, so every x minutes I am inserting around 500k new rows, into a table, which each one represents a ...
Ala's user avatar
  • 1
0 votes
0 answers
52 views

What kind of DB is best for frequent writes / rare reads?

It needs to: Handle around 80,000 writes / second Store billions of rows Store them for many years Consistency is a must Reads are very rare Data is currently stored in an RDBMS (I need to collect ...
Puffin's user avatar
  • 1
0 votes
0 answers
24 views

How well TimescaleDB supports custom types based on standard interger types like timestamp9?

There is a handy PostgreSQL extension timestamp9 that defined a type with the same name which is internally just bigint. I'm curious how well TimescaleDB support timestamp9 time in particular whether ...
oliora's user avatar
  • 101
0 votes
0 answers
20 views

What are downsides of using composite types and array type columns in TimescaleDB hypertable?

I'm considering to use a composite type, array of standard type and array of composite type columns in TimescaleDB hypertable. I'm curious whether using such columns has any downsides like less ...
oliora's user avatar
  • 101
1 vote
1 answer
91 views

Should a time index be in ascending or descending order?

Planning some new tables, I am trying to decide whether an index should be "ascending" or "descending". The table will quite large (I imagine approx. 2000 inserts per minute, ...
birgersp's user avatar
  • 175
1 vote
0 answers
26 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
0 votes
1 answer
57 views

A different filter value results in a different (slower) query plan

I am executing the following query in Postgres 15 with the Timescale extension on an alerts table to get the latest alert for a username. EXPLAIN ANALYZE SELECT * FROM alerts_alerts WHERE username IN (...
Razgriz's user avatar
  • 113
0 votes
0 answers
27 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
2 answers
75 views

Database size is far larger than sum of tables, even after VACUUM FULL?

Here is my query and its output in psql, run as an admin on that database. There is a massive discrepancy between the 'total size of the database' at 50GB, and the sum of the tables, ~7MB. This is ...
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
2 votes
0 answers
430 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
1 vote
0 answers
44 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
0 votes
1 answer
220 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
218 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
176 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
4 answers
231 views

Window function with subquery results to unexpectedly slow query plan

This is my victim query SELECT *, lag(quantity) over (partition by product_id, size_id, warehouse_id order by size_id, warehouse_id, created_at ASC) - quantity as orders FROM ...
glmn's user avatar
  • 1
2 votes
1 answer
380 views

Error while creating continuous aggregate (materialized view) when source and new time columns have the same name (PSQL/TSDB)

I'm facing a problem when creating a continuous aggregate on timescaledb (based on materialized views) when the source time column and the new time column have the same name like below: CREATE ...
Héctor's user avatar
  • 297
1 vote
1 answer
244 views

add_continuous_aggregate_policy does not exist, with timescale

One picture sums it all: The function is part of the public schema, but is it not found; it happens whether I put 'public.' or not in front of the function name. I'm using DataGrip and the ...
Thomas's user avatar
  • 303
0 votes
0 answers
33 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
1 vote
1 answer
718 views

Unnest array of rows with a tagged column

I'm trying to create a query that will return data in this format: id time value 1 1 2.5 1 2 3.5 1 3 6.4 2 1 8.3 2 2 8.5 I'm using Timescaledb and wish to use one of their downsampling ...
dotconnor's user avatar
  • 135
0 votes
1 answer
579 views

Move timescaledb hypertable from a postgresql server to another one

I want to move a timescaledb hypertable that's on the terabytes order. I wanted to do something like the following: pg_dump -t "schema1"."table1" -h host1_ip -U username1 dbname1 | ...
Héctor's user avatar
  • 297
0 votes
0 answers
220 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
2 votes
1 answer
2k views

How to determine which foreign keys are circular? As highlighted by warning from pg_dump

When carrying out pg_dump on a (timescale) database running within a local container I have the following: pg_dump: warning: there are circular foreign-key constraints on this table: pg_dump: ...
baxx's user avatar
  • 306
2 votes
2 answers
2k views

How to interpret "transactions per second"?

Short version: When I execute one transaction in IntelliJ, the "transactions per second" graph increases by 10 tx/sec - I only expect it to increase by 1. So, it seems that my understanding ...
TmTron's user avatar
  • 253
0 votes
1 answer
123 views

Database to save measurements

I am creating an infrastructure to save measurements coming from a fleet of around 2000 cars. Each car contains about 60 sensors (depending from car) with a sum of about 800 values par second coming ...
Jam. G.'s user avatar
5 votes
3 answers
214 views

Select telemetry data based on relational data in PostgreSQL/TimescaleDB

I'm trying to solve a particularly difficult problem. I am storing some telemetry data from some sensors in an SQL table (PostgreSQL) and I want to know how I can I write a query that will group the ...
kk_p's user avatar
  • 81
0 votes
0 answers
344 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
3 votes
1 answer
644 views

I can't understand a syntax error issue in SQL / TimeScaleDB

This does NOT compile: SELECT add_compression_policy( hypertable => 'exchange.candles', compress_after => INTERVAL ((SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange....
Thomas's user avatar
  • 303
2 votes
1 answer
871 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
1 vote
0 answers
402 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
0 votes
2 answers
351 views

How to check whether TimescaleDB view is defined `WITH DATA` or `WITH NO DATA`?

TimescaleDB materialized view can be created with or without DATA. Normal Postgres way to check view definition does not have trailing pragmas including DATA. # \sv wmd # select pg_get_viewdef('wmd', ...
Daniil Iaitskov's user avatar
2 votes
2 answers
680 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
2 answers
221 views

PostgreSQL (TimescaleDB) asynchronous replication (WAL) doesn't work

I'm trying to setup asynchronous replication of a PostgreSQL (TimescaleDB) database. I've been following numerous of guides, and even after trying things from documentation it doesn't seem to work. I ...
imnotarobot's user avatar
2 votes
2 answers
543 views

Index not picked up on nullable boolean column

I have 2 TimescaleDB Databases (PROD and DEV), both have a hypertable with the same schema and almost the same data (44M rows for the first one, 40M for the 2nd one). I have to query some data on a ...
Louis AW's user avatar
2 votes
2 answers
723 views

Wall clock hourly buckets out of time intervals with postgres or timescale

I have a record of session durations with start and end timestamps: user_id | session_id | session_start | session_end --------+------------+-------------------------------+------------...
amq's user avatar
  • 123
0 votes
0 answers
993 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
3 votes
0 answers
493 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
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
3 votes
1 answer
511 views

Is it ok or how to backfilling an uncompressed hypertable in TimescaleDB?

For PostgreSQL (v13.4) with TimescaleDB (v2.4.1) Created a hyper table, with each day as a chunk, and I found a day in last month is missing, so I've inserted those data of the missing day into the ...
Eric's user avatar
  • 253
1 vote
2 answers
3k views

parallelize select/insert for postgresql

I have a relatively big postgresql table (timescaleDB), 200GB ~ 1B rows, index by timestamp and id. I realize a lot of times, my very task is only handled by 1 process. I believe my query can be split ...
thinker's user avatar
  • 121
2 votes
1 answer
386 views

Are out of order inserts an issue?

I am running a PostgreSQL database that is logging state-of-health information for a number of systems. At the moment, it is running on a standard PostgreSQL server, but it seems like TimescaleDB has ...
MortenSickel's user avatar
1 vote
0 answers
207 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
2 votes
1 answer
872 views

TimescaleDB on Azure Postgres error when creating continuous aggregation

I am getting the following error SQL Error [0A000]: ERROR: functionality not supported under the current license "ApacheOnly", license¶ Hint: Upgrade to a Timescale-licensed binary to ...
Carl Kristensen's user avatar
2 votes
0 answers
227 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
1 answer
1k views

timescaledb: Bulk insert exhausts all memory

Summary I am attempting to insert data into a timescaledb hypertable in bulk. Regardless of what I try, the memory usage grows gradually until the server process is killed due to a lack of memory. I ...
josteinb's user avatar
  • 123
2 votes
1 answer
2k views

Can't do simple query or drop table due to out of memory error, but pg_locks return no lock

I received this out of memory error when trying to drop a large table (70GB including index) but couldn't identify the root cause from pg_locks view DROP TABLE big_table; ERROR: out of shared memory ...
Hannah's user avatar
  • 21
0 votes
1 answer
246 views

How to quickly return the number of rows in a distributed hypertable with more than 100 million rows in TimescaleDB?

In a 'vanilla' PostgreSQL 12.7 database, I generally run the following query to learn the estimated number of rows in tables with 100+ million rows: ----------------------------------------------------...
Emre Sevinç's user avatar
2 votes
1 answer
625 views

Database architecture of high frequency sensor values

I have a lot of sensors (around 1000) and want to save their values. The sensors will mostly work around a sampling rate of 10Hz. Regular queries will be used for a live-view application. It can be ...
BloodWorkXGaming's user avatar
1 vote
1 answer
2k views

PostgreSQL does not start: FATAL: xlog flush request is not satisfied --- flushed only to 0

I have a Docker PostgreSQL (TimescaleDB) developer instance with a local Linux volume mapping. version: '3' services: dex-timeseriesdb: image: timescale/timescaledb:latest-pg12 # https://...
Mikko Ohtamaa's user avatar