Swiftpack.co - Package - timescale/timescaledb

|Linux/macOS|Windows|Coverity|Code Coverage| |:---:|:---:|:---:|:---:| |Build Status|Windows build status|Coverity Scan Build Status|Code Coverage

TimescaleDB

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

TimescaleDB is packaged as a PostgreSQL extension and released under the Apache 2 open-source license. Contributors welcome.

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

(Before building from source, see instructions below.)

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.

Creating a hypertable

-- 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');

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.)

Installation

TimescaleDB can be installed via a variety of ways:

We recommend following our detailed installation instructions.

Building from source (Unix-based systems)

If you are building from source for non-development purposes (i.e., you want to run TimescaleDB, not submit a patch), you should always use a release-tagged commit and not build from master. See the Releases tab for the latest release.

Prerequisites:

  • A standard PostgreSQL 9.6 or 10 installation with development environment (header files) (e.g., postgresql-server-dev-9.6package for Linux, Postgres.app for MacOS)
  • C compiler (e.g., gcc or clang)
  • CMake version 3.4 or greater
git clone git@github.com:timescale/timescaledb.git
cd timescaledb

# Find the latest release and checkout, e.g. for 0.8.0:
git checkout 0.8.0

# Bootstrap the build system
./bootstrap

# To build the extension
cd build && make

# To install
make install

Note, if you have multiple versions of PostgreSQL installed you can specify the path to pg_config that should be used by using ./bootstrap -DPG_CONFIG=/path/to/pg_config.

Please see our additional configuration instructions.

Building from source (Windows)

If you are building from source for non-development purposes (i.e., you want to run TimescaleDB, not submit a patch), you should always use a release-tagged commit and not build from master. See the Releases tab for the latest release.

Prerequisites:

  • A standard PostgreSQL 9.6 or 10 64-bit installation
  • OpenSSL for Windows
  • Microsoft Visual Studio 2017 with CMake and Git components
  • OR Visual Studio 2015/2016 with CMake version 3.4 or greater and Git
  • Make sure all relevant binaries are in your PATH: pg_config, cmake, MSBuild

If using Visual Studio 2017 with the CMake and Git components, you should be able to simply clone the repo and open the folder in Visual Studio which will take care of the rest.

If you are using an earlier version of Visual Studio, then it can be built in the following way:

git clone git@github.com:timescale/timescaledb.git
cd timescaledb

# Find the latest release and checkout, e.g. for 0.8.0:
git checkout 0.8.0

# Bootstrap the build system
bootstrap.bat

# To build the extension from command line
cmake --build ./build --config Release

# To install
cmake --build ./build --config Release --target install


# Alternatively, build in Visual Studio via its built-in support for CMake or by
# opening the generated build/timescaledb.sln solution file.

Additional documentation

Support

Contributing

Github

link
Stars: 5820
Help us keep the lights on

Dependencies

Used By

Total: 0

Releases

1.0.0-rc2 - Sep 28, 2018

This release is our second 1.0 release candidate. We expect to only merge bug fixes between now and our final 1.0 release. This is a big milestone for us and signifies our maturity and enterprise readiness.

PLEASE NOTE that release candidate (rc) builds will only be made available via GitHub and Docker, and not on other release channels. Please help us test these release candidates out if you can!

Potential breaking change: We updated our error codes to be prefixed with TS instead of the old IO prefix. If you were checking for these error codes by name, please update your checks.

Notable commits

  • [b43574f] Switch 'IO' error prefix to 'TS'
  • [9747885] Prefix public C functions with ts_
  • [39510c3] Block unknown alter table commands on hypertables
  • [2408a83] Add support for ALTER TABLE SET TABLESPACE on hypertables
  • [41d9846] Enclose macro replacement list and arguments in parentheses
  • [cc59d51] Replace macro LEAST_TIMESTAMP by a static function
  • [281f363] Modify telemetry BGW to run every hour the first 12 hours
  • [a09b3ec] Add pg_isolation_regress support to the timescale build system
  • [2c267ba] Handle SIGTERM/SIGINT asynchronously
  • [5377e2d] Fix use-after-free bug for connections in the telemetry BGW
  • [248f662] Fix pg_dump for unprivileged users
  • [193fa4a] Stop background workers when extension is DROP OWNED
  • [625e3fa] Fix negative value handling in int time_bucket
  • [a33224b] Add loader API version function
  • [18b8068] Remove unnecessary index on dimension metadata table
  • [d09405d] Fix adaptive chunking when hypertables have multiple dimensions
  • [a81dc18] Block signals when writing to the log table in tests
  • [d5a6392] Fix adaptive chunking so it chooses correct index
  • [3489cca] Fix sigterm handling in background jobs
  • [2369ae9] Remove !WIN32 for sys/time.h and sys/socket.h, pg provides fills
  • [ebbb4ae] Also add sys/time.h for NetBSD. Fixes #700
  • [1a9ae17] Fix build on FreeBSD wrt sockets
  • [8225cd2] Remove (redefined) macro PG_VERSION and replace with PACKAGE_VERSION
  • [2a07cf9] Release SpinLock even when we're about to Error due to over-decrementing
  • [b2a15b8] Make sure DB schedulers are not decremented if they were never incremented
  • [6731c86] Add support for pre-release version checks

Thanks

  • @did-g for an improvement to our macros to make compiliers happy
  • @mx781 and @HeikoOnnebrink for reporting issues with working with pg_dump fully
  • @znbang and @timolson for reporting a bug that was causing telemetry to fail
  • @alanhamlett for reporting an issue with spinlocks when handling SIGTERMs
  • @oldgreen for reporting an issue with building on NetBSD
  • @kev009 for fixing build issues on FreeBSD and NetBSD
  • All the others who have helped us test and used these RCs!

0.12.1 - Sep 19, 2018

High-level changes

  • Fixes for a few issues related to the new scheduler and background worker framework.
  • Fixed bug in adaptive chunking where the incorrect index could be used for determining the current interval.
  • Improved testing, code cleanup, and other housekeeping.

Notable commits

  • [0f6f7fc] Fix adaptive chunking so it chooses correct index
  • [3ed79ed] Fix sigterm handling in background jobs
  • [bea098f] Remove !WIN32 for sys/time.h and sys/socket.h, pg provides fills
  • [9f62a1a] Also add sys/time.h for NetBSD. Fixes #700
  • [95a982f] Fix build on FreeBSD wrt sockets
  • [fcb4a79] Remove (redefined) macro PG_VERSION and replace with PACKAGE_VERSION
  • [2634897] Release SpinLock even when we're about to Error due to over-decrementing
  • [1f30dbb] Make sure DB schedulers are not decremented if they were never incremented
  • [f518cd0] Add support for pre-release version checks
  • [acebaea] Don't start schedulers for template databases.
  • [f221a12] Fix use-after-free bug in telemetry test
  • [0dc5bbb] Use pg_config bindir directory for pg executables

Thanks

  • @did-g for reporting a use-after-free bug in a test and for improving the robustness of another test
  • @kev009 for fixing build issues on FreeBSD and NetBSD

1.0.0-rc1 - Sep 12, 2018

This release is our 1.0 release candidate! We expect to only merge bug fixes between now and our final 1.0 release. This is a big milestone for us and signifies our maturity and enterprise readiness.

PLEASE NOTE that release candidate (rc) builds will only be made available via GitHub and Docker, and not on other release channels. Please help us test these release candidates out if you can!

Notable commits

  • [acebaea] Don't start schedulers for template databases.
  • [f221a12] Fix use-after-free bug in telemetry test
  • [2092b2a] Fix unused variable warning in Release build
  • [0dc5bbb] Use pg_config bindir directory for pg executables

Thanks

  • @did-g for reporting a use-after-free bug in a test and for improving the robustness of another test

0.12.0 - Sep 10, 2018

High-level changes

  • Scheduler framework: This release introduces a background job framework and scheduler. Each database running within a PostgreSQL instance has a scheduler that schedules recurring jobs from a new jobs table while maintaining statistics that inform the scheduler's policy. Future releases will leverage this scheduler framework for more automated management of data retention, archiving, analytics, and the like.

  • Telemetry: Using this new scheduler framework, TimescaleDB databases now send anonymized usage information to a telemetry server via HTTPS, as well as perform version checking to notify users if a newer version is available. For transparency, a new get_telemetry_report function details the exact JSON that is sent, and users may also opt out of this telemetry and version check.

  • Continued hardening: This release addresses several issues around more robust backup and recovery, handling large numbers of chunks, and additional test coverage.

Notable commits

  • [efab2aa] Fix net lib functionality on Windows and improve error handling
  • [71589c4] Fix issues when OpenSSL is not available
  • [a43cd04] Call the main telemetry function inside BGW executor
  • [faf481b] Add telemetry functionality
  • [45a2b76] Add new Connection and HTTP libraries
  • [b6fe657] Fix max_background_workers guc, errors on EXEC_BACKEND and formatting
  • [5d8c7cc] Add a scheduler for background jobs
  • [55a7141] Implement a cluster-wide launcher for background workers
  • [5bc705f] Update bootstrap to check for cmake and exit if not found
  • [98e56dd] Improve show_indexes test func to be more platform agnostic
  • [b928caa] Note how to recreate templated files
  • [8571e41] Use AttrNumberGetAttrOffset instead of Anum_name - 1 for array indexing
  • [d1710ef] Improve regression test script to cleanup more thoroughly
  • [fc3677f] Reduce number of open chunks per insert
  • [027b7b2] Hide extension symbols by default on Unix platforms
  • [6a3abe5] Fix SubspaceStore to ensure max_open_chunks_per_insert is obeyed

Thanks

@EvanCarroll for updates to the bootstrap script to check for cmake

0.11.0 - Aug 8, 2018

Major Features

  • Adaptive chunking: This feature, currently in beta, allows the database to automatically adapt a chunk's time interval, so that users do not need to manually set (and possibly manually change) this interval size. In this release, users can specify either a target chunk data size (in terms of MB or GB), and the chunk's time intervals will be automatically adapted. Alternatively, users can ask the database to just estimate a target size itself based on the platform's available memory and other parameters, and the system will adapt accordingly. This type of automation can simplify initial database testing and operations. This feature is default off. Note: The default time interval for non-adaptive chunking has also been changed from 1 month to 1 week.

  • Continued hardening: This release addresses a number of less frequently used schema modifications, functions, or constraints. Unsupported functions are safely blocked, while we have added support for a number of new types of table alterations. This release also adds additional test coverage.

Notable commits

  • [9ba2e81] Fix segfault with custom partition types
  • [7e9bf25] Change default chunk size to one week
  • [506fa18] Add tests for custom types
  • [1d9ade7] add support for other types as timescale column
  • [570f2f8] Validate parameters when creating partition info
  • [148f2da] Use shared_buffers as the available cache memory
  • [e0a15c1] Add additional comments to explain algorithm
  • [d81dccb] Set the default chunk_time_interval to 1 day with adaptive chunking enabled
  • [2e7b32c] Add WARNING when doing min-max heap scan for adaptive chunking
  • [6b452a8] Update adaptive chunk algorithm to handle very small chunks.
  • [9c9cdca] Add support for adaptive chunk sizing
  • [7f8d17d] Handle DEFERRED and VALID options for constraints
  • [0c5c21b] Block using rules with hypertables
  • [37142e9] Block INSERTs on a hypertable's root table
  • [4daf087] Fix some ALTER TABLE corner case bugs on hypertables
  • [122f5f1] Block replica identity usage with hypertables
  • [8bf552e] Block unlogged tables from being used as hypertables
  • [a8c637e] Create aggregate functions only once to avoid dependency issues
  • [a97f2af] Add support for custom hypertable dimension types
  • [dfe026c] Refactor create_hypertable rel access.
  • [ed379c3] Validate existing indexes before adding a new dimension
  • [1f2d276] Fix and improve show_indexes test support function
  • [77b0035] Enforce IMMUTABLE partitioning functions
  • [cbc5e60] Block NO INHERIT constraints on hypertables
  • [e362e9c] Block mixing hypertables with postgres inheritance
  • [011f12b] Add support for CLUSTER ON and SET WITHOUT CLUSTER
  • [e947c6b] Improve handling of column settings
  • [fc4957b] Update statistics on parent table when doing ANALYZE
  • [82942bf] Enable backwards compatibility for loader for 0.9.0 and 0.9.1

Thanks

  • @Ngalstyan4 and @hjsuh18, our interns, for all of the PRs this summer
  • @fvannee for a PR adding support for binary compatible custom types as a time column
  • @fmacelw for reporting a bug where first() and last() hold reference across extension update
  • @soccerdroid for reporting a corner case bug in ALTER TABLE