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
2 answers
28 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', ...
user avatar
1 vote
2 answers
49 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 ...
user avatar
2 votes
2 answers
45 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 ...
user avatar
2 votes
2 answers
40 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 ...
user avatar
2 votes
2 answers
122 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 --------+------------+-------------------------------+------------...
user avatar
  • 123
0 votes
0 answers
114 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 ...
user avatar
  • 11
3 votes
0 answers
81 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 ...
user avatar
1 vote
0 answers
32 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 ...
user avatar
  • 179
3 votes
1 answer
137 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 ...
user avatar
  • 179
1 vote
2 answers
50 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 ...
user avatar
  • 121
1 vote
1 answer
59 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 ...
user avatar
1 vote
0 answers
59 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 ...
user avatar
1 vote
1 answer
181 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 ...
user avatar
1 vote
0 answers
109 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 ...
user avatar
  • 11
1 vote
1 answer
110 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 ...
user avatar
  • 113
1 vote
1 answer
442 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 ...
user avatar
  • 11
0 votes
1 answer
42 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: ----------------------------------------------------...
user avatar
1 vote
1 answer
130 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 ...
user avatar
1 vote
0 answers
423 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://...
user avatar
2 votes
1 answer
84 views

TimescaleDB database design - should I use inheritance?

I have the following TimescaleDB hypertable: CREATE TABLE public.data ( event_time timestamp with time zone NOT NULL, pair_id integer NOT NULL, entry_id bigint NOT NULL, event_data1 ...
user avatar
  • 205
2 votes
2 answers
660 views

Converting PostgreSQL table to TimescaleDB hypertable

I have a PostgreSQL table which I am trying to convert to a TimescaleDB hypertable. The table looks as follows: CREATE TABLE public.data ( event_time timestamp with time zone NOT NULL, ...
user avatar
  • 205
0 votes
1 answer
397 views

postgres read query resample time series data for hourly averages

I am new to SQL trying to learn how to do read queries on time series data. Can someone give me a tip on how to resample interval time series data to hourly averages on the postgres read query? My ...
user avatar
  • 103
5 votes
1 answer
132 views

Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows

I'm trying to investigate why the performance of this query is so non-deterministic. It can take anywhere from 1 seconds, to 60 seconds and above. The nature of the query is to select a "time ...
user avatar
  • 53
2 votes
0 answers
468 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, ...
user avatar
  • 205
2 votes
0 answers
20 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?
user avatar
-1 votes
1 answer
112 views

TimescaleDB wildcard (%) slow

I have a timescaledb hypertable like this: create table logs ( time timestamp not null, partitionkey text not null, ip inet, raw text, transformed ...
user avatar
  • 5
3 votes
2 answers
453 views

On docker volume restore: could not access file "$libdir/timescaledb-1.6.0": No such file or directory

I am trying to restore TimescaleDB data from one ubuntu machine to another ubuntu machine. On the both machines we have deployed TimescaleDB through docker and using the same tag. Docker image used: ...
user avatar
1 vote
1 answer
79 views

TimescaleDB distributed setup strategy

What is the best setup strategy for TimescaleDB? My initial thoughts based on the TimescaleDB FAQ was that it is indeed recommended and possible to basically have one database that contains both time ...
user avatar
  • 179
1 vote
2 answers
183 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 ...
user avatar
0 votes
1 answer
38 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 ...
user avatar
  • 1
1 vote
1 answer
150 views

Postgres: Why is my delete query using up all available disk space?

I have a postgres table set up like so: Table "public.facts" Column | Type | Collation | Nullable | ...
user avatar
1 vote
2 answers
806 views

High autovacuum CPU/IO usage from database with more than 10k append-only tables

In my system I have more than 10k tables that are very similar, I only do insert operations in these tables and delete old data via a cron schedule one time per day. These insertions come each minute, ...
user avatar
  • 125
0 votes
0 answers
319 views

PostgreSQL query takes hours instead of seconds after adding one row in a referenced table

I'm new to the database world and I think I'm missing some basic understanding of my issue. It seems to me this is a conceptual issue. I have a table 'data' with the Timescaledb extension. The column '...
user avatar
  • 1
1 vote
1 answer
1k views

Postgres optimizing join of 2 large tables

I have 2 tables one with around 1M entries and other around 4M. I want to join these tables in a materialized view. But the materialized view creation is taking too long ~4 hours. I don't see the ...
user avatar
0 votes
0 answers
101 views

Space concerns for unique constraint on row for time-series db (PostgreSQL / TimescaleDB)

I was wondering if there is a more space-efficient way of doing unique row constraints on time-series DBs. Adding a normal unique key for the entire row will result in an index the same size or ...
user avatar
  • 123
0 votes
1 answer
18 views

Add aggregated value filtered by a condition to existing query

I have this table to collect data from IoT device: CREATE TABLE public.conferimenti ( id bigint NOT NULL DEFAULT nextval('conferimenti_id_seq'::regclass), codicestazione smallint NOT NULL, ...
user avatar
0 votes
1 answer
554 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 ...
user avatar
1 vote
1 answer
1k views

Unable to convert a table to hypertable due to unique index error

I am creating a table using Flask SQL Alchemy as follows: class Price(db.Model): __tablename__ = "prices" id = db.Column(db.Integer, primary_key=True) country_code = db.Column(db.String(2)...
user avatar
  • 113
0 votes
0 answers
276 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 ...
user avatar
  • 101
2 votes
1 answer
901 views

ZFS Tunings for PostgreSQL

What are the recommended ZFS tunings for PostgreSQL 11 (with TimescaleDB 1.6 extension) running on striped mirrored vdevs of SATA3 SSDs? I am considering: ashift=12 atime=off relatime=on (or off?) ...
user avatar
  • 1,001
3 votes
1 answer
994 views

Find Uncompressed/Compressed Sizes of TimescaleDB Database/Table

For a database/table in TimescaleDB and PostgreSQL 11 running on ZFS with both ZFS compression and native TimescaleDB compression enabled, how can you find the uncompressed and compressed sizes when ...
user avatar
4 votes
1 answer
364 views

Continuously Adding SSDs to an existing PostgreSQL Server

An Ubuntu 18.04 server dedicated to running a PostgreSQL 11.2 server (with TimescaleDB extension) will be running out of disk space soon so new SSD disks will need to be added to the machine to ...
user avatar
  • 1,001
0 votes
0 answers
83 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 ...
user avatar
  • 111
2 votes
0 answers
483 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 ...
user avatar
  • 1,001
0 votes
0 answers
256 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:/...
user avatar
  • 251
0 votes
1 answer
678 views

Best way to async data inserts into timescaledb

Having 3 files with one data set of daily data inside: 2019-09-11.txt 2019-09-12.txt 2019-09-13.txt Having lines like: 2019-09-11 15:59:37.459802,1 2019-09-11 15:59:38.959802,1 [...] The files are ...
user avatar
  • 135
1 vote
1 answer
831 views

Reordering a postgresql timescaledb hypertable

Is the standard postgresql way also possible on a timescaledb hypertable? CLUSTER [VERBOSE] tablename [ USING indexname ] Example: Creating table & hypertyble CREATE TABLE IF NOT EXISTS l1( ...
user avatar
  • 135
0 votes
2 answers
548 views

How to move old data to cheap hard drive

I have a quite big PostgreSQL database (with timescale plugin). Right now it is consuming about 500Gb on a SSD. Most of the data is in the form of time series. In most cases data older than a few ...
user avatar
1 vote
0 answers
74 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 ...
user avatar
8 votes
1 answer
352 views

Which data model/schema to apply to a timeseries datastore for data sources with varying fields

I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose. About the data The source data which is to ...
user avatar