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".
71
questions
9
votes
1
answer
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 ...
- 488
8
votes
1
answer
491
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 ...
- 89
5
votes
3
answers
134
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 ...
- 81
5
votes
1
answer
236
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 ...
- 53
4
votes
1
answer
522
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 ...
- 1,091
3
votes
1
answer
405
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 ...
- 223
3
votes
2
answers
903
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: ...
- 153
3
votes
1
answer
409
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....
- 261
3
votes
1
answer
1k
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
...
- 133
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
2
answers
2k
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,
...
- 247
2
votes
2
answers
148
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 ...
- 21
2
votes
1
answer
245
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 ...
- 251
2
votes
1
answer
479
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 ...
- 123
2
votes
1
answer
778
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 ...
- 123
2
votes
2
answers
621
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 ...
- 233
2
votes
2
answers
413
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
--------+------------+-------------------------------+------------...
- 123
2
votes
1
answer
1k
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
...
- 21
2
votes
1
answer
153
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 ...
- 247
2
votes
1
answer
1k
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?)
...
- 1,091
2
votes
2
answers
298
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 ...
- 23
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
2
answers
835
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 ...
- 121
1
vote
2
answers
2k
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, ...
- 125
1
vote
1
answer
509
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: ...
- 256
1
vote
2
answers
935
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 ...
- 115
1
vote
1
answer
102
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 ...
- 257
1
vote
1
answer
372
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 ...
1
vote
1
answer
2k
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)...
- 113
1
vote
1
answer
546
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 ...
- 113
1
vote
1
answer
64
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 ...
- 261
1
vote
1
answer
215
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 ...
- 135
1
vote
1
answer
124
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 ...
- 189
1
vote
1
answer
360
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
1
answer
3k
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 ...
- 123
1
vote
1
answer
1k
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(
...
- 135
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
1
answer
1k
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://...
- 289
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
4
answers
119
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 ...
- 1
0
votes
1
answer
144
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:
----------------------------------------------------...
- 189