Hologres:Compact data files

Prerequisites and limitations

• Full compaction requires Hologres V2.1 or later. To upgrade, see Upgrade an instance or get online support to request an upgrade.

• Only column-oriented tables and hybrid row-column tables support full compaction.

• For hybrid row-column tables, full compaction applies only to the column store portion.

Syntax

SELECT hologres.hg_full_compact_table(
  '<schema_name.table_name>'
  [,'max_file_size_mb=<value>']
);

Parameters

Examples

Run full compaction on public.lineitem:

SELECT hologres.hg_full_compact_table('public.lineitem');

Run full compaction on public.lineitem with a maximum output file size of 256 MB:

SELECT hologres.hg_full_compact_table(
  'public.lineitem',
  'max_file_size_mb=256'
);

Prerequisites and limitations

• These table properties require Hologres V4.0 or later. On earlier versions, setting them has no effect. Upgrade the instance first.

• These properties apply only to column-oriented tables and hybrid row-column tables. For a hybrid row-column table, they affect only the column store portion. Setting them on a row-oriented table has no effect.

• Auto compaction needs no manual intervention in most cases. Apart from online_config_compaction_semaphore, the advanced properties interact with concurrency settings in ways that are hard to predict. Change them only to address a specific problem, preferably with guidance from technical support, and restore the defaults afterward.

Set, query, and reset table properties

Set table-level compaction properties with SET_TABLE_PROPERTY. Every property name carries the online_config_ prefix, and every value is passed as a string. A new value applies to compaction tasks scheduled after the change; tasks already running are unaffected. You do not need to restart the instance or rebuild the table.

-- Syntax
CALL SET_TABLE_PROPERTY('<schema_name>.<table_name>', 'online_config_<property_name>', '<value>');

-- Pause compaction scheduling on public.orders
CALL SET_TABLE_PROPERTY('public.orders', 'online_config_compaction_semaphore', '0');

CALL SET_TABLE_PROPERTY('public.orders', 'online_config_compaction_semaphore', 'reset');

SELECT property_key, property_value
FROM hologres.hg_table_properties
WHERE table_namespace = '<schema_name>'
  AND table_name = '<table_name>'
  AND property_key LIKE 'online_config_%';

Control compaction concurrency

online_config_compaction_semaphore is the property to reach for first. It caps concurrent compaction tasks per tablet, not per table—a tablet is the unit that compaction is scheduled against. A table has one or more tablets on each shard; in a hybrid row-column table, the row store and column store portions are separate tablets. The theoretical ceiling for the whole table is therefore roughly tablet_count × property_value, though the worker-level concurrency limit and available resources usually cap it lower.

What setting the value to 0 actually does

• New compaction tasks for the table are no longer scheduled.

• Tasks already running are not cancelled. They finish normally.

• Tasks that were already selected for this table and are waiting to run still hold worker-level concurrency slots. Setting many tables to 0 at once therefore starves compaction on other tables.

• After you raise the value above 0, queued tasks resume, but a backlog that was never scheduled is not guaranteed to be picked up on its own—especially if the table receives no new writes. Run VACUUM once to force the backlog through. For what VACUUM does and does not do, see Examples.

To restore concurrency and merge the backlog:

CALL SET_TABLE_PROPERTY('public.orders', 'online_config_compaction_semaphore', '2');
VACUUM public.orders;

Logical partitioned tables

For a logical partitioned table, Hologres raises concurrency in proportion to the number of active partitions. The ceiling for a single scheduling unit is min(online_config_partition_table_compaction_semaphore_max, active_partition_count × online_config_compaction_semaphore), capped at 8 by default. active_partition_count is measured at runtime and is not a property you can set.

Risks

• Do not leave the value at 0 for long. File counts and deleted rows keep accumulating, query performance and storage usage both degrade, and the longer the backlog grows, the heavier the eventual merge.

• Do not set 0 or raise the value across many tables at once. All tables share the worker-level concurrency budget: a mass pause lets queued tasks occupy that budget, and a mass increase causes global queuing and a resource spike.

• Change one table and one property at a time, in the smallest useful increment, and observe a full business cycle before the next change.

Advanced properties

The following properties change how compaction selects files and how often it triggers. They interact with the concurrency settings, so change them only for a specific diagnosed problem—ideally with guidance from technical support—and restore the defaults afterward.

Examples

Example 1: Pause compaction for a bulk load

-- 1. Record the current state. An empty result means no override is set.
SELECT property_key, property_value FROM hologres.hg_table_properties
WHERE table_namespace = 'public' AND table_name = 'orders'
  AND property_key = 'online_config_compaction_semaphore';

-- 2. Stop scheduling new compaction tasks.
CALL SET_TABLE_PROPERTY('public.orders', 'online_config_compaction_semaphore', '0');

-- 3. Run the bulk load. Watch CPU, I/O, and load throughput while it runs.

-- 4. Restore the property. Choose one of the following based on step 1.
-- 4a. Step 1 returned no rows:
CALL SET_TABLE_PROPERTY('public.orders', 'online_config_compaction_semaphore', 'reset');
-- 4b. Step 1 returned a value. Write that value back:
-- CALL SET_TABLE_PROPERTY('public.orders', 'online_config_compaction_semaphore', '<original_value>');

-- 5. Merge the backlog. Run this during a low-write period.
VACUUM public.orders;

Example 2: Clear long-tail small files

Use this when every level holds fewer files than the trigger threshold, yet the table has accumulated dozens of small files that never merge.

CALL SET_TABLE_PROPERTY('public.orders', 'online_config_ignore_level_compaction', 'true');
VACUUM public.orders;
-- Restore the default once the file count drops. Leaving it on inflates compaction
-- frequency and CPU and I/O usage on continuously written tables.
CALL SET_TABLE_PROPERTY('public.orders', 'online_config_ignore_level_compaction', 'reset');

Example 3: Produce larger merged files

Use this when a tablet holds a large volume of data spread across many files.

CALL SET_TABLE_PROPERTY('public.orders', 'online_config_orc_max_total_size_to_merge_mb', '512');
VACUUM public.orders;
-- Restore the default once the file count drops.
CALL SET_TABLE_PROPERTY('public.orders', 'online_config_orc_max_total_size_to_merge_mb', 'reset');

Verify that a change took effect

1. Property names are not validated. Any name after the online_config_ prefix is accepted and the statement succeeds. A misspelled name matches no configuration, so the change silently does nothing. To protect against this:

   • Copy property names from the tables in this topic instead of typing them.

   • Confirm the write by querying hologres.hg_table_properties.

   • Confirm the behavior actually changed by watching file counts and file sizes. A statement that succeeds proves nothing on its own.

2. Partitioned tables inherit properties only at creation. Running SET_TABLE_PROPERTY on a parent table does not affect existing child partitions. The property is written to the parent alone, and the parent holds no data, so compaction behavior on existing partitions is unchanged. Child partitions copy the parent's properties once, when they are created—including partitions created automatically by dynamic partitioning—and later changes to the parent are not propagated. To change compaction behavior on a partitioned table, run SET_TABLE_PROPERTY on each target child partition and verify each one. Set the property on the parent as well if you want future partitions to inherit it.

3. Close the loop with observation. Query hologres.hg_table_file_status for the current file count and file sizes, hologres.hg_partition_file_status for a per-partition view of a logical partitioned table, and hologres.hg_table_info (produced once a day) to review trends. A successful adjustment shows a falling total file count and a rising average file size. Storage usage can rise briefly while merged-away files await cleanup; this is expected.

   SELECT * FROM hologres.hg_table_file_status('<schema_name>.<table_name>'::regclass);