Improve Integer Query Performance with Lightweight Compression-AnalyticDB(AnalyticDB)-阿里云帮助中心

The lightweight compression (LightInt) feature provided by the Beam storage engine of AnalyticDB for PostgreSQL compresses integer data (int16/int32/int64) with lightweight encoding. It maintains a high compression ratio while supporting filter pushdown without decompression, significantly improving the performance of highly selective filter queries.

Notes

Lightweight compression is supported only in elastic storage mode of v7.5.0.1 or later.
Lightweight compression supports only integer data types (int2/smallint, int4/integer, and int8/bigint).
Specify compresstype='auto' at the table level. If beam_default_enable_lightint is enabled at the instance level, lightweight compression is automatically configured and no manual configuration is required. This parameter is in the grayscale stage. To enable it at the instance level, submit a ticket to contact technical support. Otherwise, enable lightweight compression by using the following table-level configuration.
If you use other compression types such as lz4 and zstd, manually add table-level and column-level options to enable lightweight compression.

Usage

Method 1: Use the auto compression type (recommended)

Specify compresstype='auto' at the table level. If beam_default_enable_lightint is enabled at the instance level, lightweight compression is automatically configured. If beam_default_enable_lightint is not enabled, add the enable_lightint=1 table-level option.

CREATE TABLE lineitem (
    l_orderkey      integer NOT NULL,
    l_partkey       integer NOT NULL,
    l_suppkey       integer NOT NULL,
    l_linenumber    integer NOT NULL,
    l_quantity      double precision NOT NULL,
    l_extendedprice double precision NOT NULL,
    l_discount      double precision NOT NULL,
    l_shipdate      date NOT NULL,
    l_comment       character varying(44) NOT NULL
)
USING beam WITH (compresstype = 'auto', enable_lightint=1)
DISTRIBUTED BY (l_orderkey);

After the table is created, you can query pg_class.reloptions to verify that lightweight compression is enabled at the table level:

SELECT relname, reloptions FROM pg_class WHERE relname = 'lineitem';
-- reloptions: {compresstype=auto,compresslevel=1,ctidversion=2,enable_lightint=1}

Method 2: Manually enable lightweight compression (non-auto compression types)

If you use other compression types such as lz4 and zstd, set both table-level and column-level options:

CREATE TABLE store_sales (
    ss_sold_date_sk    integer NOT NULL,
    ss_sold_time_sk    integer NOT NULL,
    ss_item_sk         integer NOT NULL,
    ss_customer_sk     integer NOT NULL,
    ss_cdemo_sk        integer NOT NULL,
    ss_hdemo_sk        integer NOT NULL,
    ss_addr_sk         integer NOT NULL,
    ss_store_sk        integer NOT NULL,
    ss_promo_sk        integer NOT NULL,
    ss_ticket_number   bigint NOT NULL,
    ss_quantity        integer NOT NULL,
    ss_list_price      numeric(7,2),
    ss_coupon_amt      numeric(7,2)
)
USING beam WITH (
    compresstype = 'lz4',
    compresslevel = 1,
    enable_lightint = 1      -- Enable lightweight compression at the table level
)
DISTRIBUTED BY (ss_item_sk);

To enable lightweight compression only for specific columns, specify column-level encoding:

CREATE TABLE sales_detail (
    id          integer NOT NULL,
    product_id  integer NOT NULL ENCODING (compresstype='lightint'),
    quantity    integer NOT NULL ENCODING (compresstype='lightint'),
    price       numeric(10,2),
    order_date  date
)
USING beam WITH (
    compresstype = 'lz4',
    enable_lightint = 1       -- Enable lightweight compression at the table level
)
DISTRIBUTED BY (id);

Performance comparison

The following table compares the performance of LightInt and LZ4 compression on the TPC-H 20 GB dataset.

Compressed size (lineitem table)

Table	Compression method	Storage space (MB)
lineitem	LZ4	6123
lineitem	LightInt	4796

Query latency

Query	LZ4 (seconds)	LightInt (seconds)
Q12	2838.35	1943.16
Q21	4244.14	2681.55

The overall TPC-H 20 GB query latency is improved by more than 15%. Highly selective filter queries benefit the most because the zone skip mechanism can skip a large number of chunks that do not meet the filter conditions and avoid the overhead of full decompression.

Related parameters

Parameter	Default value	Description
`beam_enable_lightint_pushdown`	on	Enables filter pushdown without decompression. This parameter takes effect on sequential scans and index scans.
`beam_default_enable_lightint`	off	Enables the lightint option by default.

-- Enable filter pushdown without decompression
SET beam_enable_lightint_pushdown = on;
set beam_default_enable_lightint = on;

Recommendations

We recommend that you use compresstype=auto together with the instance-level beam_default_enable_lightint parameter. The system automatically enables lightweight compression for integer columns without additional configuration.

Enable pushdown for highly selective queries. For filter-centric workloads, we recommend that you set SET beam_enable_lightint_pushdown = on to achieve optimal performance.