AI 原生开放教材

外观

Vibe Coding CN课时教程·中英混排原文 ↗原件 ↓仓库 ↗

Timescaledb - Compression ​

Pages: 19

Inserting or modifying data in the columnstore ​

URL: llms-txt#inserting-or-modifying-data-in-the-columnstore

Contents:

  • Earlier versions of TimescaleDB (before v2.11.0)

In TimescaleDB [v2.11.0][tsdb-release-2-11-0] and later, you can use the UPDATE and DELETE commands to modify existing rows in compressed chunks. This works in a similar way to INSERT operations. To reduce the amount of decompression, TimescaleDB only attempts to decompress data where it is necessary. However, if there are no qualifiers, or if the qualifiers cannot be used as filters, calls to UPDATE and DELETE may convert large amounts of data to the rowstore and back to the columnstore. To avoid large scale conversion, filter on the columns you use to segementby and orderby. This filters as much data as possible before any data is modified, and reduces the amount of data conversions.

DML operations on the columnstore work if the data you are inserting has unique constraints. Constraints are preserved during the insert operation. TimescaleDB uses a Postgres function that decompresses relevant data during the insert to check if the new data breaks unique checks. This means that any time you insert data into the columnstore, a small amount of data is decompressed to allow a speculative insertion, and block any inserts which could violate constraints.

For TimescaleDB [v2.17.0][tsdb-release-2-17-0] and later, delete performance is improved on compressed hypertables when a large amount of data is affected. When you delete whole segments of data, filter your deletes by segmentby column(s) instead of separate deletes. This considerably increases performance by skipping the decompression step. Since TimescaleDB [v2.21.0][tsdb-release-2-21-0] and later, DELETE operations on the columnstore are executed on the batch level, which allows more performant deletion of data of non-segmentby columns and reduces IO usage.

Earlier versions of TimescaleDB (before v2.11.0) ​

This feature requires Postgres 14 or later

From TimescaleDB v2.3.0, you can insert data into compressed chunks with some limitations. The primary limitation is that you can't insert data with unique constraints. Additionally, newly inserted data needs to be compressed at the same time as the data in the chunk, either by a running recompression policy, or by using recompress_chunk manually on the chunk.

In TimescaleDB v2.2.0 and earlier, you cannot insert data into compressed chunks.

===== PAGE: https://docs.tigerdata.com/use-timescale/jobs/create-and-manage-jobs/ =====

timescaledb_information.jobs ​

URL: llms-txt#timescaledb_information.jobs

Contents:

  • Samples
  • Arguments

Shows information about all jobs registered with the automation framework.

Shows a job associated with the refresh policy for continuous aggregates:

Find all jobs related to compression policies (before TimescaleDB v2.20):

Find all jobs related to columnstore policies (TimescaleDB v2.20 and later):

NameTypeDescription
job_idINTEGERThe ID of the background job
application_nameTEXTName of the policy or job
schedule_intervalINTERVALThe interval at which the job runs. Defaults to 24 hours
max_runtimeINTERVALThe maximum amount of time the job is allowed to run by the background worker scheduler before it is stopped
max_retriesINTEGERThe number of times the job is retried if it fails
retry_periodINTERVALThe amount of time the scheduler waits between retries of the job on failure
proc_schemaTEXTSchema name of the function or procedure executed by the job
proc_nameTEXTName of the function or procedure executed by the job
ownerTEXTOwner of the job
scheduledBOOLEANSet to true to run the job automatically
fixed_scheduleBOOLEANSet to true for jobs executing at fixed times according to a schedule interval and initial start
configJSONBConfiguration passed to the function specified by proc_name at execution time
next_startTIMESTAMP WITH TIME ZONENext start time for the job, if it is scheduled to run automatically
initial_startTIMESTAMP WITH TIME ZONETime the job is first run and also the time on which execution times are aligned for jobs with fixed schedules
hypertable_schemaTEXTSchema name of the hypertable. Set to NULL for a job
hypertable_nameTEXTTable name of the hypertable. Set to NULL for a job
check_schemaTEXTSchema name of the optional configuration validation function, set when the job is created or updated
check_nameTEXTName of the optional configuration validation function, set when the job is created or updated

===== PAGE: https://docs.tigerdata.com/api/informational-views/hypertables/ =====

Examples:

Example 1 (sql):

sql
SELECT * FROM timescaledb_information.jobs;
job_id            | 1001
application_name  | Refresh Continuous Aggregate Policy [1001]
schedule_interval | 01:00:00
max_runtime       | 00:00:00
max_retries       | -1
retry_period      | 01:00:00
proc_schema       | _timescaledb_internal
proc_name         | policy_refresh_continuous_aggregate
owner             | postgres
scheduled         | t
config            | {"start_offset": "20 days", "end_offset": "10
days", "mat_hypertable_id": 2}
next_start        | 2020-10-02 12:38:07.014042-04
hypertable_schema | _timescaledb_internal
hypertable_name   | _materialized_hypertable_2
check_schema      | _timescaledb_internal
check_name       | policy_refresh_continuous_aggregate_check

Example 2 (sql):

sql
SELECT * FROM timescaledb_information.jobs where application_name like 'Compression%';
-[ RECORD 1 ]-----+--------------------------------------------------
job_id            | 1002
application_name  | Compression Policy [1002]
schedule_interval | 15 days 12:00:00
max_runtime       | 00:00:00
max_retries       | -1
retry_period      | 01:00:00
proc_schema       | _timescaledb_internal
proc_name         | policy_compression
owner             | postgres
scheduled         | t
config            | {"hypertable_id": 3, "compress_after": "60 days"}
next_start        | 2020-10-18 01:31:40.493764-04
hypertable_schema | public
hypertable_name   | conditions
check_schema      | _timescaledb_internal
check_name        | policy_compression_check

Example 3 (sql):

sql
SELECT * FROM timescaledb_information.jobs where application_name like 'Columnstore%';
-[ RECORD 1 ]-----+--------------------------------------------------
job_id            | 1002
application_name  | Columnstore Policy [1002]
schedule_interval | 15 days 12:00:00
max_runtime       | 00:00:00
max_retries       | -1
retry_period      | 01:00:00
proc_schema       | _timescaledb_internal
proc_name         | policy_compression
owner             | postgres
scheduled         | t
config            | {"hypertable_id": 3, "compress_after": "60 days"}
next_start        | 2025-10-18 01:31:40.493764-04
hypertable_schema | public
hypertable_name   | conditions
check_schema      | _timescaledb_internal
check_name        | policy_compression_check

Example 4 (sql):

sql
SELECT * FROM timescaledb_information.jobs where application_name like 'User-Define%';
-[ RECORD 1 ]-----+------------------------------
job_id            | 1003
application_name  | User-Defined Action [1003]
schedule_interval | 01:00:00
max_runtime       | 00:00:00
max_retries       | -1
retry_period      | 00:05:00
proc_schema       | public
proc_name         | custom_aggregation_func
owner             | postgres
scheduled         | t
config            | {"type": "function"}
next_start        | 2020-10-02 14:45:33.339885-04
hypertable_schema |
hypertable_name   |
check_schema      | NULL
check_name        | NULL
-[ RECORD 2 ]-----+------------------------------
job_id            | 1004
application_name  | User-Defined Action [1004]
schedule_interval | 01:00:00
max_runtime       | 00:00:00
max_retries       | -1
retry_period      | 00:05:00
proc_schema       | public
proc_name         | custom_retention_func
owner             | postgres
scheduled         | t
config            | {"type": "function"}
next_start        | 2020-10-02 14:45:33.353733-04
hypertable_schema |
hypertable_name   |
check_schema      | NULL
check_name        | NULL

Low compression rate ​

URL: llms-txt#low-compression-rate

Low compression rates are often caused by [high cardinality][cardinality-blog] of the segment key. This means that the column you selected for grouping the rows during compression has too many unique values. This makes it impossible to group a lot of rows in a batch. To achieve better compression results, choose a segment key with lower cardinality.

===== PAGE: https://docs.tigerdata.com/_troubleshooting/dropping-chunks-times-out/ =====

Query time-series data tutorial - set up compression ​

URL: llms-txt#query-time-series-data-tutorial---set-up-compression

Contents:

  • Compression setup
  • Add a compression policy
  • Taking advantage of query speedups

You have now seen how to create a hypertable for your NYC taxi trip data and query it. When ingesting a dataset like this is seldom necessary to update old data and over time the amount of data in the tables grows. Over time you end up with a lot of data and since this is mostly immutable you can compress it to save space and avoid incurring additional cost.

It is possible to use disk-oriented compression like the support offered by ZFS and Btrfs but since TimescaleDB is build for handling event-oriented data (such as time-series) it comes with support for compressing data in hypertables.

TimescaleDB compression allows you to store the data in a vastly more efficient format allowing up to 20x compression ratio compared to a normal Postgres table, but this is of course highly dependent on the data and configuration.

TimescaleDB compression is implemented natively in Postgres and does not require special storage formats. Instead it relies on features of Postgres to transform the data into columnar format before compression. The use of a columnar format allows better compression ratio since similar data is stored adjacently. For more details on how the compression format looks, you can look at the [compression design][compression-design] section.

A beneficial side-effect of compressing data is that certain queries are significantly faster since less data has to be read into memory.

  1. Connect to the Tiger Cloud service that contains the dataset using, for example psql.
  2. Enable compression on the table and pick suitable segment-by and order-by column using the ALTER TABLE command:

Depending on the choice if segment-by and order-by column you can get very different performance and compression ratio. To learn more about how to pick the correct columns, see [here][segment-by-columns].

  1. You can manually compress all the chunks of the hypertable using compress_chunk in this manner:

    You can also [automate compression][automatic-compression] by adding a [compression policy][add_compression_policy] which will be covered below.

  2. Now that you have compressed the table you can compare the size of the dataset before and after compression:

    This shows a significant improvement in data usage:

Add a compression policy ​

To avoid running the compression step each time you have some data to compress you can set up a compression policy. The compression policy allows you to compress data that is older than a particular age, for example, to compress all chunks that are older than 8 days:

Compression policies run on a regular schedule, by default once every day, which means that you might have up to 9 days of uncompressed data with the setting above.

You can find more information on compression policies in the [add_compression_policy][add_compression_policy] section.

Taking advantage of query speedups ​

Previously, compression was set up to be segmented by vendor_id column value. This means fetching data by filtering or grouping on that column will be more efficient. Ordering is also set to time descending so if you run queries which try to order data with that ordering, you should see performance benefits.

For instance, if you run the query example from previous section:

You should see a decent performance difference when the dataset is compressed and when is decompressed. Try it yourself by running the previous query, decompressing the dataset and running it again while timing the execution time. You can enable timing query times in psql by running:

To decompress the whole dataset, run:

On an example setup, speedup performance observed was pretty significant, 700 ms when compressed vs 1,2 sec when decompressed.

Try it yourself and see what you get!

===== PAGE: https://docs.tigerdata.com/tutorials/blockchain-query/blockchain-compress/ =====

Examples:

Example 1 (sql):

sql
ALTER TABLE rides
    SET (
        timescaledb.compress,
        timescaledb.compress_segmentby='vendor_id',
        timescaledb.compress_orderby='pickup_datetime DESC'
    );

Example 2 (sql):

sql
SELECT compress_chunk(c) from show_chunks('rides') c;

Example 3 (sql):

sql
SELECT
        pg_size_pretty(before_compression_total_bytes) as before,
        pg_size_pretty(after_compression_total_bytes) as after
     FROM hypertable_compression_stats('rides');

Example 4 (sql):

sql
before  | after
    ---------+--------
    1741 MB | 603 MB

add_policies() ​

URL: llms-txt#add_policies()

Contents:

  • Samples
  • Required arguments
  • Optional arguments
  • Returns

Add refresh, compression, and data retention policies to a continuous aggregate in one step. The added compression and retention policies apply to the continuous aggregate, not to the original hypertable.

Experimental features could have bugs. They might not be backwards compatible, and could be removed in future releases. Use these features at your own risk, and do not use any experimental features in production.

add_policies() does not allow the schedule_interval for the continuous aggregate to be set, instead using a default value of 1 hour.

If you would like to set this add your policies manually (see [add_continuous_aggregate_policy][add_continuous_aggregate_policy]).

Given a continuous aggregate named example_continuous_aggregate, add three policies to it:

  1. Regularly refresh the continuous aggregate to materialize data between 1 day and 2 days old.
  2. Compress data in the continuous aggregate after 20 days.
  3. Drop data in the continuous aggregate after 1 year.

Required arguments ​

NameTypeDescription
relationREGCLASSThe continuous aggregate that the policies should be applied to

Optional arguments ​

NameTypeDescription
if_not_existsBOOLWhen true, prints a warning instead of erroring if the continuous aggregate doesn't exist. Defaults to false.
refresh_start_offsetINTERVAL or INTEGERThe start of the continuous aggregate refresh window, expressed as an offset from the policy run time.
refresh_end_offsetINTERVAL or INTEGERThe end of the continuous aggregate refresh window, expressed as an offset from the policy run time. Must be greater than refresh_start_offset.
compress_afterINTERVAL or INTEGERContinuous aggregate chunks are compressed if they exclusively contain data older than this interval.
drop_afterINTERVAL or INTEGERContinuous aggregate chunks are dropped if they exclusively contain data older than this interval.

For arguments that could be either an INTERVAL or an INTEGER, use an INTERVAL if your time bucket is based on timestamps. Use an INTEGER if your time bucket is based on integers.

Returns true if successful.

===== PAGE: https://docs.tigerdata.com/api/continuous-aggregates/create_materialized_view/ =====

Examples:

Example 1 (sql):

sql
timescaledb_experimental.add_policies(
     relation REGCLASS,
     if_not_exists BOOL = false,
     refresh_start_offset "any" = NULL,
     refresh_end_offset "any" = NULL,
     compress_after "any" = NULL,
     drop_after "any" = NULL)
) RETURNS BOOL

Example 2 (sql):

sql
SELECT timescaledb_experimental.add_policies(
    'example_continuous_aggregate',
    refresh_start_offset => '1 day'::interval,
    refresh_end_offset => '2 day'::interval,
    compress_after => '20 days'::interval,
    drop_after => '1 year'::interval
);

About writing data ​

URL: llms-txt#about-writing-data

TimescaleDB supports writing data in the same way as Postgres, using INSERT, UPDATE, INSERT ... ON CONFLICT, and DELETE.

TimescaleDB is optimized for running real-time analytics workloads on time-series data. For this reason, hypertables are optimized for inserts to the most recent time intervals. Inserting data with recent time values gives excellent performance. However, if you need to make frequent updates to older time intervals, you might see lower write throughput.

===== PAGE: https://docs.tigerdata.com/use-timescale/write-data/upsert/ =====

Decompression ​

URL: llms-txt#decompression

Contents:

  • Decompress chunks manually
    • Decompress individual chunks
    • Decompress chunks by time
    • Decompress chunks on more precise constraints

Old API since TimescaleDB v2.18.0 Replaced by <a href="https://docs.tigerdata.com/api/latest/hypercore/convert_to_rowstore/">convert_to_rowstore</a>.

When compressing your data, you can reduce the amount of storage space used. But you should always leave some additional storage capacity. This gives you the flexibility to decompress chunks when necessary, for actions such as bulk inserts.

This section describes commands to use for decompressing chunks. You can filter by time to select the chunks you want to decompress.

Decompress chunks manually ​

Before decompressing chunks, stop any compression policy on the hypertable you are decompressing. The database automatically recompresses your chunks in the next scheduled job. If you accumulate a large amount of chunks that need to be compressed, the [troubleshooting guide][troubleshooting-oom-chunks] shows how to compress a backlog of chunks. For more information on how to stop and run compression policies using alter_job(), see the [API reference][api-reference-alter-job].

There are several methods for selecting chunks and decompressing them.

本篇目录 ​