Questions tagged [timescaledb]

TimescaleDB is an open source time-series database built on PostgreSQL. Use this tag for questions related to TimescaleDB. Also tag with the more general "time-series-databases".

Filter by
Sorted by
Tagged with
2
votes
1answer
21 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: ...
1
vote
1answer
21 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 ...
1
vote
2answers
56 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 ...
0
votes
1answer
22 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 ...
0
votes
1answer
43 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 | ...
1
vote
2answers
184 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, ...
0
votes
0answers
79 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 '...
0
votes
1answer
306 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 ...
0
votes
0answers
48 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 ...
0
votes
1answer
17 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, ...
0
votes
1answer
116 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 ...
1
vote
1answer
326 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)...
0
votes
0answers
41 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 ...
2
votes
1answer
350 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?) ...
2
votes
1answer
440 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 ...
4
votes
1answer
169 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 ...
0
votes
0answers
39 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 ...
2
votes
0answers
309 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 ...
0
votes
0answers
171 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:/...
0
votes
1answer
437 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 ...
1
vote
1answer
442 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( ...
0
votes
2answers
261 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 ...
1
vote
0answers
63 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 ...
7
votes
1answer
250 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 ...
0
votes
0answers
76 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 (...
8
votes
1answer
2k views

Can I use hypertables in timescaledb merely to get better insert rates?

I have a PostgreSQL db that I have a large bulk load running into. I wish this load to be as fast as possible. I'm already using the copy command etc. I have been reading about timescaledb and how ...
1
vote
1answer
186 views

Speeding up group by and max query

In my iot use case, I have a table which stores sensor readings. I am using the timescaledb extension. I have the following table definition: CREATE TABLE readings ( id BIGSERIAL ...
0
votes
4answers
177 views

Dynamically generate batched data for import

I need to execute a test in which I have to simulate 20 years' historical data in PostgreSQL (and TimescaleDB) DB. My problem is that I have to generate .sql files and ingest them (using psql-client) ...
1
vote
0answers
36 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,...