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

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
  • 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
cd build
MSBuild.exe timescaledb.sln

# To install
MSBuild.exe /p:Configuration=Release INSTALL.vcxproj

# Alternatively, open build/timescaledb.sln in Visual Studio and build

Additional documentation

Support

Contributing

Github

link
Stars: 5161
Help us keep the lights on

Dependencies

Used By

Total: 0

Releases

0.10.1 - Jul 12, 2018

High-level changes

  • Improved memory management for long-lived connections.
  • Fixed handling of dropping triggers that would lead to orphaned references in pg_depend.
  • Fixed pruning in CustomScan when the subplan is not a Scan type that caused a crash with LATERALs.
  • Corrected size reporting that was not accurately counting TOAST size
  • Updated error messages that more closely conform to PG style.
  • Corrected handling of table and schema name changes to chunks; TimescaleDB metadata catalogs are now properly updated

Notable commits

  • [8b58500] Fix bug where dropping triggers caused dangling references in pg_depend, disallow disabling triggers on hypertables
  • [745b8ab] Fixing CustomScan pruning whenever the subplan is NOT of a Scan type.
  • [67a8a41] Make chunk identifiers formatting safe using format
  • [41af6ff] Fix misreported toast_size in chunk_relation_size funcs
  • [4f2f1a6] Update the error messages to conform with the style guide; Fix tests
  • [3c28f65] Release cache pin memory
  • [abe76fc] Add support for changing chunk schema and name

Thanks

  • @mfuterko for updating our error messages to conform with PG error message style
  • @fvannee for reporting a crash when using certain LATERAL joins with aggregates
  • @linba708 for reporting a memory leak with long lived connections
  • @phlsmk for reporting an issue where dropping triggers prevented drop_chunks from working due to orphaned dependencies

0.10.0 - Jun 27, 2018

High-level changes

  • Planning time improvement (up to 15x) when a hypertable has many chunks by only expanding (and taking locks on) chunks that will actually be used in a query, rather than on all chunks (as was the default PostgreSQL behavior).
  • Smarter use of HashAggregate by teaching the planner to better estimate the number of output rows when using time-based grouping.
  • New convenience function for getting the approximate number of rows in a hypertable (hypertable_approximate_row_count).
  • Fixed support for installing extension into non-public schemas
  • Other bug fixes and refactorings.

Notable commits

  • [12bc117] Fix static analyzer warning when checking for index attributes
  • [7d9f49b] Fix missing NULL check when creating default indexes
  • [2e1f3b9] Improve memory allocation during cache lookups
  • [ca6e5ef] Fix upserts on altered tables.
  • [2de6b02] Add optimization to use HashAggregate more often
  • [4b4211f] Fix some external functions when setting a custom schema
  • [b7257fc] Optimize planning times when hypertables have many chunks
  • [c660fcd] Add hypertable_approximate_row_count convenience function
  • [9ce1576] Fix a compilation issue on pre 9.6.3 versions

Thanks

  • @viragkothari for suggesting the addition of hypertable_approximate_row_count and @fvannee for providing the initial SQL used to build that function
  • 'mintekhab' from Slack for reporting a segfault when using upserts on an altered table
  • @mmouterde for reporting an issue where the extension implicitly expected to be installed in the public schema
  • @mfuterko for bringing some potential bugs to our attention via static analysis

0.9.2 - May 4, 2018

High-level changes

  • Fixed handling of DISCARD ALL command when parallel workers are involved, which sometimes caused the extension to complain it was not preloaded
  • User permission bug fix where users locating TRIGGER permissions in a database could not insert data into a hypertable
  • Fixes for some issues with 32-bit architectures

Notable commits

  • [256b394] Fix parsing of GrantRoleStmt
  • [b78953b] Fix datum conversion typo
  • [c7283ef] Fix bug with extension loader when DISCARD ALL is executed
  • [fe20e48] Fix chunk creation with user that lacks TRIGGER permission

Thanks

  • @gumshoes, @manigandham, @wallies, & @cjrh for reporting a problem where sometimes the extension would appear to not be preloaded when it actually was
  • @thaxy for reporting a permissions issue when user creating a hypertable lacks TRIGGER permission
  • @bertmelis for reporting some bugs with 32-bit architectures

0.9.1 - Mar 26, 2018

High-level changes

  • For this release only, you will need to restart the database before running ALTER EXTENSION
  • Several edge cases regarding CTEs addressed
  • Updated preloader with better error messaging and fixed edge case
  • ABI compatibility with latest PostgreSQL to help catch any breaking changes

Notable commits

  • [40ce037] Fix crash on explain analyze with insert cte
  • [8378beb] Enable hypertable inserts within CTEs
  • [bdfda75] Fix double-loading of extension
  • [01ea77e] Fix EXPLAIN output for ConstraintAwareAppend inside CTE
  • [fc05637] Add no preload error to versioned library.
  • [38f8e0c] Add ABI compatibility tests
  • [744ca09] Fix Cache Pinning for Subtxns
  • [39010db] Move more drops into event trigger system
  • [fc36699] Do not fail add_dimension() on non-empty table with 'if_not_exists'

Thanks

  • @The-Alchemist for pointing out broken links in the README
  • @chaintng for pointing out a broken link in the docs
  • @jgranstrom for reporting a edge case crash with UPSERTs in CTEs
  • @saosebastiao for reporting the lack of an error message when the library is not preloaded and trying to delete/modify a hypertable
  • @jbylund for reporting a cache invalidation issue with the preloader

0.9.0 - Mar 5, 2018

Please note: When updating your database, you should connect using psql with the -X flag to prevent any .psqlrc commands from accidentally triggering the load of a previous DB version.

High-level changes

  • Support for multiple extension versions on different databases in the same PostgreSQL instance. This allows different databases to be updated independently and provides for smoother updates between versions. No more spurious errors in the log as the extension is being updated, and new versions no longer require a restart of the database.
  • Streamlined update process for smaller binary/package sizes
  • Significant refactoring to simplify and improve codebase, including improvements to error handling, security/permissions, and more
  • Corrections to edge-case scenarios involving dropping schemas, hypertables, dimensions, and more
  • Correctness improvements through propagating reloptions from main table to chunk tables and blocking ONLY commands that try to alter hypertables (i.e., changes should be applied to chunks as well)
  • Addition of a migrate_data option to create_hypertable to allow non-empty tables to be turned into hypertables without separate creation & insertion steps. Note, this option may take a while if the original table has lots of data
  • Support for ALTER TABLE RENAME CONSTRAINT
  • Support for adjusting the number of partitions for a space dimension
  • Improvements to tablespace handling

Notable commits

  • [4672719] Fix error in handling of RESET ALL
  • [9399308] Refactor/simplify update scripts and build process
  • [0e79df4] Fix handling of custom SQL-based partitioning functions
  • [f13969e] Fix possible memory safety issue and squash valgrind error.
  • [ef74491] Migrate table data when creating a hypertable
  • [2696582] Move index and constraints drop handling to event trigger
  • [d6baccb] Improve tablespace handling, including blocks for DROP and REVOKE
  • [b9a6f89] Handle DROP SCHEMA for hypertable and chunk schemas
  • [b534a5a] Add test case for adding metadata entries automatically
  • [6adce4c] Handle TRUNCATE without upcall and handle ONLY modifier
  • [71b1124] Delete orphaned dimension slices
  • [fa19a54] Handle deletes on metadata objects via native catalog API
  • [6e011d1] Refactor hypertable-related API functions
  • [5afd39a] Fix locking for serializing chunk creation
  • [6dd2c46] Add check for null in ca_append_rescan to prevent segfault
  • [71962b8] Refactor dimension-related API functions
  • [cc254a9] Fix CREATE EXTENSION IF NOT EXISTS and error messages
  • [d135256] Spread chunk indexes across tablespaces like chunks
  • [e85721a] Block ONLY hypertable on all ALTER TABLE commands.
  • [78d36b5] Handle subtxn for cache pinning
  • [26ef77f] Add subtxn abort logic to process_utility.c
  • [25f3284] Handle cache invalidation during subtxn rollback
  • [264956f] Block DROP NOT NULL on time-partitioned columns.
  • [ad7d361] Better accounting for number of items stored in a subspace
  • [12f92ea] Improve speed of out-of-order inserts
  • [87f055d] Add support for ALTER TABLE RENAME CONSTRAINT.
  • [da8cc79] Add support for multiple extension version in one pg instance
  • [68faddc] Make chunks inherit reloptions set on the hypertable
  • [4df8f28] Add proper permissions handling for associated (chunk) schemas
  • [21efcce] Refactor chunk table creation and unify constraint handling

Thanks

  • @Anthares for a request to pass reloptions like fill factor to child chunks
  • @oldgreen for reporting an issue with subtransaction handling
  • @fvannee for a PR that fixed a bug with ca_append_rescan
  • @maksm90 for reporting an superfluous index being created in an internal catalog table
  • @Rashid987 for reporting an issue where deleting a chunk, then changing the time interval would not apply the change when a replacement chunk is created
  • RaedA from Slack for reporting compilation issues on Windows between 0.8.0 and this release
  • @haohello for a request to adjust the number of partitions for a given dimension
  • @LonghronShen and @devereaux for reporting an issue (and submitting a PR) for handling version identification when there is more to the version than just major and minor numbers
  • @carlospeon for reporting an issue with dropping hypertables
  • @gumshoes, @simpod, @jbylund, and @ryan-shaw for testing a pre-release version to verify our new update path works as expected
  • @gumshoes for reporting an issue with RESET ALL