COMPACT_STORAGE

Bundles existing data (.fdb) and index (.pidx) files into the .gt file format. The .gt format is enabled by default for data files created version 7.2 or later. If you upgrade a database from an earlier version, use COMPACT_STORAGE to bundle storage files into the .gt format. Your database can continue to operate with a mix of file storage formats.

If the settings you specify for COMPACT_STORAGE vary from the limit specified in configuration parameter MaxBundleableROSSizeKB, Vertica does not change the size of the automatically created bundles.

Run this function during periods of low demand.

Syntax

SELECT COMPACT_STORAGE ('[[[database.]schema.]object‑name]', min‑ros‑filesize‑kb, 'small‑or‑all‑files', 'simulate');

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.
object‑name

Specifies the table or projection to bundle. If set to an empty string, COMPACT_STORAGE evaluates the data of all projections in the database for bundling.
min‑ros‑filesize‑kb

Integer ≥ 1, specifies in kilobytes the minimum size of an independent ROS file. COMPACT_STORAGE bundles storage container ROS files below this size into a single file.

small‑or‑all‑files

One of the following:

  • small: Bundles only files smaller than the limit specified in min‑ros‑filesize‑kb
  • all: Bundles files smaller than the limit specified in min‑ros‑filesize‑kb and bundles the .fdb and .pidx files for larger storage containers.
simulate

Specifies whether to simulate the storage settings and produce a report describing the impact of those settings.

  • true: Produces a report on the impact of the specified bundle settings without actually bundling storage files.
  • false: Performs the bundling as specified.

Privileges

Superuser

Storage and Performance Impact

Bundling reduces the number of files in your file system by at least fifty percent and improves the performance of file-intensive operations. Improved operations include backups, restores, and mergeout.

Vertica creates small files for the following reasons:

  • Tables contain hundreds of columns.
  • Partition ranges are small (partition by minute).
  • Local segmentation is enabled and your factor is set to a high value.

Example

The following example describes the impact of bundling the table EMPLOYEES:

=> SELECT COMPACT_STORAGE('employees', 1024,'small','true');
Task: compact_storage

On node v_vmart_node0001:
Projection Name :public.employees_b0 | selected_storage_containers :0 | 
selected_files_to_compact :0 | files_after_compact : 0 | modified_storage_KB :0

On node v_vmart_node0002:
Projection Name :public.employees_b0 | selected_storage_containers :1 | 
selected_files_to_compact :6 | files_after_compact : 1 | modified_storage_KB :0

On node v_vmart_node0003:
Projection Name :public.employees_b0 | selected_storage_containers :2 | 
selected_files_to_compact :12 | files_after_compact : 2 | modified_storage_KB :0

On node v_vmart_node0001:
Projection Name :public.employees_b1 | selected_storage_containers :2 | 
selected_files_to_compact :12 | files_after_compact : 2 | modified_storage_KB :0

On node v_vmart_node0002:
Projection Name :public.employees_b1 | selected_storage_containers :0 | 
selected_files_to_compact :0 | files_after_compact : 0 | modified_storage_KB :0

On node v_vmart_node0003:
Projection Name :public.employees_b1 | selected_storage_containers :1 | 
selected_files_to_compact :6 | files_after_compact : 1 | modified_storage_KB :0

Success

(1 row)