TimescaleDB Tune: A Program for Tuning TimescaleDB Databases

timescaledb-tune

timescaledb-tune is a program for tuning a TimescaleDB database to perform its best based on the host's resources such as memory and number of CPUs. It parses the existing postgresql.conf file to ensure that the TimescaleDB extension is appropriately installed and provides recommendations for memory, parallelism, WAL, and other settings.

Getting started

You need the Go runtime (1.12+) installed, then simply go install this repo:

$ go install github.com/timescale/timescaledb-tune/cmd/timescaledb-tune@main

It is also available as a binary package on a variety systems using Homebrew, yum, or apt. Search for timescaledb-tools.

Using timescaledb-tune

By default, timescaledb-tune attempts to locate your postgresql.conf file for parsing by using heuristics based on the operating system, so the simplest invocation would be:

$ timescaledb-tune

You'll then be given a series of prompts that require minimal user input to make sure your config file is up to date:

Using postgresql.conf at this path:
/usr/local/var/postgres/postgresql.conf

Is this correct? [(y)es/(n)o]: y
Writing backup to:
/var/folders/cr/zpgdkv194vz1g5smxl_5tggm0000gn/T/timescaledb_tune.backup201901071520

shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = 'timescaledb'
Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]: y
success: shared_preload_libraries will be updated

Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y
Recommendations based on 8.00 GB of available memory and 4 CPUs for PostgreSQL 11

Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 1GB
work_mem = 26214kB
Is this okay? [(y)es/(s)kip/(q)uit]:

If you have moved the configuration file to a different location, or auto-detection fails (file an issue please!), you can provide the location with the --conf-path flag:

$ timescaledb-tune --conf-path=/path/to/postgresql.conf

At the end, your postgresql.conf will be overwritten with the changes that you accepted from the prompts.

Other invocations

If you want recommendations for a specific amount of memory and/or CPUs:

$ timescaledb-tune --memory="4GB" --cpus=2

If you want to set a specific number of background workers (timescaledb.max_background_workers):

$ timescaledb-tune --max-bg-workers=16

If you have a dedicated disk for WAL, or want to specify how much of a shared disk should be used for WAL:

$ timescaledb-tune --wal-disk-size="10GB"

If you want to accept all recommendations, you can use --yes:

$ timescaledb-tune --yes

If you just want to see the recommendations without writing:

$ timescaledb-tune --dry-run

If there are too many prompts:

$ timescaledb-tune --quiet

And if you want to skip all prompts and get quiet output:

$ timescaledb-tune --quiet --yes

And if you want to append the recommendations to the end of your conf file instead of in-place replacement:

$ timescaledb-tune --quiet --yes --dry-run >> /path/to/postgresql.conf

Restoring backups

timescaledb-tune makes a backup of your postgresql.conf file each time it runs (without the --dry-run flag) in your temp directory. If you find that the configuration given is not working well, you can restore a backup by using the --restore flag:

$ timescaledb-tune --restore

Using postgresql.conf at this path:
/usr/local/var/postgres/postgresql.conf

Is this correct? [(y)es/(n)o]: y
Available backups (most recent first):
1) timescaledb_tune.backup201901222056 (14 hours ago)
2) timescaledb_tune.backup201901221640 (18 hours ago)
3) timescaledb_tune.backup201901221050 (24 hours ago)
4) timescaledb_tune.backup201901211817 (41 hours ago)

Use which backup? Number or (q)uit: 1
Restoring 'timescaledb_tune.backup201901222056'...
success: restored successfully

Contributing

We welcome contributions to this utility, which like TimescaleDB is released under the Apache2 Open Source License. The same Contributors Agreement applies; please sign the Contributor License Agreement (CLA) if you're a new contributor.

Author: timescale
Source Code: https://github.com/timescale/timescaledb-tune
License: Apache-2.0 License

#timescaledb 

TimescaleDB: an Open-source Time-series SQL Database Optimized

TimescaleDB

TimescaleDB is an open-source database designed to make SQL scalable for time-series data. It is engineered up from PostgreSQL and packaged as a PostgreSQL extension, providing automatic partitioning across time and space (partitioning key), as well as full SQL support.

If you prefer not to install or administer your instance of TimescaleDB, hosted versions of TimescaleDB are available in the cloud of your choice (pay-as-you-go, with a free trial to start).

To determine which option is best for you, see Timescale Products for more information about our Apache-2 version, TimescaleDB Community (self-hosted), and Timescale Cloud (hosted), including: feature comparisons, FAQ, documentation, and support.

Below is an introduction to TimescaleDB. For more information, please check out these other resources:

• Developer Documentation
• Slack Channel
• Timescale Release Notes & Future Plans

For reference and clarity, all code files in this repository reference licensing in their header (either the Apache-2-open-source license or Timescale License (TSL) ). Apache-2 licensed binaries can be built by passing -DAPACHE_ONLY=1 to bootstrap.

Contributors welcome.

(To build TimescaleDB from source, see instructions in Building from source.)

Using TimescaleDB

TimescaleDB scales PostgreSQL for time-series data via automatic partitioning across time and space (partitioning key), yet retains the standard PostgreSQL interface.

In other words, TimescaleDB exposes what look like regular tables, but are actually only an abstraction (or a virtual view) of many individual tables comprising the actual data. This single-table view, which we call a hypertable, is comprised of many chunks, which are created by partitioning the hypertable's data in either one or two dimensions: by a time interval, and by an (optional) "partition key" such as device id, location, user id, etc. (Architecture discussion)

Virtually all user interactions with TimescaleDB are with hypertables. Creating tables and indexes, altering tables, inserting data, selecting data, etc., can (and should) all be executed on the hypertable.

From the perspective of both use and management, TimescaleDB just looks and feels like PostgreSQL, and can be managed and queried as such.

Before you start

PostgreSQL's out-of-the-box settings are typically too conservative for modern servers and TimescaleDB. You should make sure your postgresql.conf settings are tuned, either by using timescaledb-tune or doing it manually.

Creating a hypertable

-- Do not forget to create timescaledb extension
CREATE EXTENSION timescaledb;

-- We start by creating a regular SQL table
CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);

-- Then we convert it into a hypertable that is partitioned by time
SELECT create_hypertable('conditions', 'time');

• Quick start: Creating hypertables
• Reference examples

Inserting and querying data

Inserting data into the hypertable is done via normal SQL commands:

INSERT INTO conditions(time, location, temperature, humidity)
  VALUES (NOW(), 'office', 70.0, 50.0);

SELECT * FROM conditions ORDER BY time DESC LIMIT 100;

SELECT time_bucket('15 minutes', time) AS fifteen_min,
    location, COUNT(*),
    MAX(temperature) AS max_temp,
    MAX(humidity) AS max_hum
  FROM conditions
  WHERE time > NOW() - interval '3 hours'
  GROUP BY fifteen_min, location
  ORDER BY fifteen_min DESC, max_temp DESC;

In addition, TimescaleDB includes additional functions for time-series analysis that are not present in vanilla PostgreSQL. (For example, the time_bucket function above.)

• Quick start: Basic operations
• Reference examples
• TimescaleDB API

Installation

TimescaleDB is available pre-packaged for several platforms:

• Linux:
  • RedHat / CentOS
  • Ubuntu
  • Debian
• Docker
• MacOS (Homebrew)
• Windows

Timescale Cloud (cloud-hosted and managed TimescaleDB) is available via free trial. You create database instances in the cloud of your choice and use TimescaleDB to power your queries, automating common operational tasks and reducing management overhead.

We recommend following our detailed installation instructions.

To build from source, see instructions here.

Resources

Useful tools

• timescaledb-tune: Helps set your PostgreSQL configuration settings based on your system's resources.
• timescaledb-parallel-copy: Parallelize your initial bulk loading by using PostgreSQL's COPY across multiple workers.

Additional documentation

• Why use TimescaleDB?
• Migrating from PostgreSQL
• Writing data
• Querying and data analytics
• Tutorials and sample data

Community & help

• Slack Channel
• Github Issues
• Timescale Support: see support options (community & subscription)

Releases & updates

• Timescale Release Notes & Future Plans: see planned and in-progress updates and detailed information about current and past releases.
• Subscribe to Timescale Release Notes to get notified about new releases, fixes, and early access/beta programs.

Contributing

• Contributor instructions
• Code style guide

Download Details:
Author: timescale
Source Code: https://github.com/timescale/timescaledb
License: View license

#database #postgresql #timescaledb 

How To Clean Data in Python using PostgreSQL and TimescaleDB

Cleaning is a very important part of the analysis process and generally can be the most grueling from my experience! By cleaning data directly within my database, I am able to perform a lot of my cleaning tasks one time rather than repetitively within a script, saving me considerable time in the long run.

⭐️You can see more at the link at the end of the article. Thank you for your interest in the blog, if you find it interesting, please give me a like, comment and share to show your support for the author.

#timescaledb #postgresql #python 
 

Learn More About The Best Use Cases for TimescaleDB

TimescaleDB is a time series database. Quite simply what that means is that it’s optimized for and includes additional functions for data that has a time component. When you’re dealing with data across the time dimension, TimescaleDB is faster and easier to use than a standard SQL or NoSQL database.

⭐️You can see more at the link at the end of the article. Thank you for your interest in the blog, if you find it interesting, please give me a like, comment and share to show your support for the author.

#timescaledb 
 

How to Speed Up Data analysis with PostgreSQL and TimescaleDB

In this introductory post, I’ll explore a few of the common frustrations that I experienced with popular data analysis tools, and from there, dive into how I’ve used TimescaleDB and PostgreSQL to help alleviate each of those pain points.

#postgresql #timescaledb 

Learn About influxDB, TimescaleDB, and QuestDB Time Series Databases

We're living in the golden age of databases, as money flows into the industry at historical rates (e.g., Snowflake, MongoDB, Cockroach Labs, Neo4j). If the debate between relational vs. non-relational or online analytical processing (OLAP) vs. online transaction processing (OLTP) ruled the past decade, a new type of database has been steadily growing in popularity. According to DB-Engines, an initiative to collect and present information on database management systems, time series databases are the fastest growing sector since 2020:

#timescaledb #influxdb #questdb 

Using Postgres and TimescaleDB with Node.js series: Part 2

In this post we will look at creating tables and querying data from them in Node.js.

Originally published at https://fek.io.

This is part two of a three part series on using Postgres DB with Node.js.

Read part one here

In the first part of this series I wrote about how to get an instance of Postgres or TimescaleDB running and communicating with Node.js. In this post we will describe how tables and indexes are used to store and query data. We will also cover DDL, DML and DQL SQL statements.

Tables and Indexes

Relational database software is composed of specific database objects such as tables and indexes. Some systems also have objects like sequences. This will depend on the vendor.

Tables are used to store your raw data. Tables are composed of columns and rows. You will use columns to store individual fields in your table, while rows are used to store entire records.

Defining Tables

Creating tables is fairly simple. This can be done with a CREATE TABLE statement. This kind of query is considered part of the Data Definition Language or DDL for short.

Lets’ say we wanted to create a table for storing persons. We would probably need to have columns for the person’s name, the date the record was created and some sort of record identifier or id. We could create a table like this with the following statement in Postgres;

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    firstname TEXT NOT NULL,
    lastname TEXT NOT NULL,
    createdate TIMESTAMP DEFAULT NOW() NOT NULL,
    active BOOLEAN DEFAULT TRUE NOT NULL
);

#timescaledb #nodejs #javascript #postgres

Using Postgres and TimescaleDB with Node.js Series

In this post we take a look at getting Postgres set up for developing with Node.js.In this post we take a look at getting Postgres set up for developing with Node.js.

This is part one of a three part series on using  Postgres DB with  Node.js.

Prerequisites

To use the examples in this post you will need to have the Docker client and Node.js installed on your computer.

Postgres

I am going to be giving a presentation next week and using Postgres SQL and TimescaleDB with Node.js for the JaxNode user group. If you are not familiar with Postgres, it is a relation database server that is very popular in the open source world, but also used heavily by large organizations. Whether you are large or small, you can use Postgres. There are Postgres services that are available on most of the large cloud providers.

To understand what a relation database is and how we can use it in our applications we need to understand what a relational database does. At the end of the day all a database is a piece of software that lets’ us persist data onto non-volatile memory, and query the data back quickly in our applications. Non-volatile memory can be anything from flash memory to a large drive array.

NoSQL vs SQL Databases

Postgres is a SQL database, or Structured Query Language database. SQL is an industry standard for a query language. There are many of other types of databases including graph databases, document databases and column stores. There are many reasons why you may want to use a NoSQL database, but the main reason might be scalability. While NoSQL database in many cases might be more scalable, they tend not to be as consistent as a SQL database because of the nature of how they store data. The nice thing about about Postgres is that it offers features that can be found in both types of database systems.

#nodejs #javascript #timescaledb #node

Time Series Databases Are the Future for Your Data

Data has been the gold mine of the last decade, and big data is growing faster every year than the previous one thanks to all other related fields like machine learning and IoT.

Time Series Era

You will be able to analyze the past, the present, and the future! What has changed from the old ways to keep the latest states of your data only?

You are generating more data every minute!

We are getting better hardware, storage, and smarter algorithms.

Data is the standard to do everything.

#database #timescaledb #timeseries #data