I have the following query to display the performance of a machine in 8-hour segments (although it can change to 1 hour, 1 week, 1 month, etc.). My table has 2 million records, and the query takes 10 seconds to…
I have a docker image that is generated using https://github.com/timescale/timescaledb The base OS of my docker image is Alpine. Could not find any document that uses apk to install the toolkit
I am using pg_dump (15.3) to dump information on a database. This command works: pg_dump --schema-only --schema foo -d foodb -U postgres -Fp and dumps among other things a CREATE SCHEMA foo; statement This command doesn't: pg_dump --schema-only --schema cron…
My system consists of Database (Timescale Db) and table telemetry_aggregations .Net Worker nodes (1...n) Every couple minutes, telemetry_aggregations table gets updated with couple hundreds of new rows. How system should work Every node periodically requests new batch of data (SQL…
I'm seeking advice on how to optimize my timeseries database setup, which should handle a large volume of time-series data. I have around 20,000 time-series profiles with a one-year duration, using a quarterly time resolution (4 timestamps per hour). This…
I have created a table like this CREATE TABLE IF NOT EXISTS public.table_name( id SERIAL PRIMARY KEY, user_id int4 NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW(), ); SELECT create_hypertable('table_name', 'created_at', if_not_exists => TRUE); CREATE INDEX IF NOT EXISTS ix_table_name_user_id…
I have this materialized view: Materialized view "public.kwh_tag" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+------------------+-----------+----------+---------+---------+--------------+------------- timest | date | | | | plain | | Zählerstand | double precision |…
I was looking to update fields on a row in PostgreSQL only if the fields were null. Otherwise I would overwrite what's already in the row. My update looks as simple as this: UPDATE public.people SET flag=$flag, name=$name, surname=$surname WHERE…
I'm trying to calculate the time weighted average using LOCF in a 24 hour interval using the following data: Time Value 2020-01-01 00:00:00 1 2020-01-01 23:00:00 1000 The query looks as follows: SELECT time_bucket('1 days', time) AS timebucket, average(time_weight( 'locf',…
The postgresql.conf file found in /var/lib/postgresql/data is all commented (I checked) like this: but when I try to get some configurations using: SELECT current_setting('autovacuum') why is that? Can I edit the file or there is another file on the TimescaleDB…