After importing large volumes of offline data or running many DELETE or UPDATE operations, data file fragmentation can degrade read and write performance. Compaction merges small data files into larger ones, restoring storage efficiency and improving query speed.
How it works
Hologres uses a data structure similar to an LSM-Tree (log-structured merge-tree) for data writes. All writes are append-only, converting random writes into sequential writes to maximize write throughput. Over time, this produces many small files that must be merged through compaction.
Hologres supports two compaction types:
Auto Compaction: Hierarchical compaction with up to five levels. When a level accumulates more than five files, compaction triggers automatically and moves the result to the next level. For example, five files at Level 0 merge into a single file (up to 64 MB by default) that moves to Level 1. Auto Compaction only merges files within the same level—never across levels.
Full Compaction: Merges all files across all levels into new files (64 MB each by default) placed at the final level. Full Compaction must be triggered manually.
When to run Full Compaction
Auto Compaction handles routine file merging continuously in the background. Run Full Compaction only when file fragmentation has accumulated beyond what Auto Compaction can address incrementally—specifically, after either of these situations:
Importing large volumes of offline data
Running many
DELETEorUPDATEoperations
Full Compaction is a resource-intensive administrative operation. It consumes significant I/O and CPU resources. Execution typically takes more than 10 minutes. Run it during low-write periods to minimize impact on your workload.
Limitations
Full Compaction requires Hologres V2.1 or later. To upgrade, see Instance upgrades 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.
Run Full Compaction
Prerequisites
Before you begin, ensure that you have:
A Hologres instance running V2.1 or later
A target table that is column-oriented or hybrid row-column
Syntax
SELECT hologres.hg_full_compact_table(
'<schema_name.table_name>'
[,'max_file_size_mb=<value>']
);Parameters
| Parameter | Description | Required | Default |
|---|---|---|---|
schema_name.table_name | Name of the table to compact | Yes | — |
max_file_size_mb | Maximum size in MB for each output file. Must be a positive integer. Setting this value too low creates too many small files, which slows down queries. Do not change this value unless necessary. | No | 64 |
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'
);