COMPACT_STORAGE

Bundles existing data (.fdb) and index (.pidx) files into the .gt file format. The .gt format was introduced in Vertica 7.2 and is enabled by default for any data files created version 7.2 or later. If you have a database from an earlier version, you can use COMPACT_STORAGE to upgrade existing storage files to the new 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. You can use this function even if storage bundling is not enabled on your database.

Note: Run this function during periods of low demand.

Syntax

SELECT COMPACT_STORAGE (object‑name, min‑ros‑filesize‑kb, small‑or‑all‑files, simulate);

Parameters

object‑name

Specifies the table or projection to bundle.

min‑ros‑filesize‑kb

Specifies the minimum size, in kilobytes, of an independent ROS file. Vertica bundles storage container ROS files below this size into a single file.

If set to 0, Vertica bundles the data and index files of an individual column, but not with other columns in that storage container.

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: Vertica performs the bundling according to the settings you 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, mergeouts and moveouts.

Vertica creates small files for the following reasons:

Evaluating the Benefits of Bundled Storage

You can determine whether bundling existing storage can provide a significant benefit, as follows:

View the median file size of a projection on a node

Run the following query:

SELECT MEDIAN(size) OVER() AS median_fsize 
FROM vs_ros AS ros, storage_containers AS cont WHERE ros.delid=cont.storage_oid AND cont.node_name='node-name' AND cont.projection_name='proj-name' LIMIT 1;

If many files are smaller than 1mb, bundling storage is likely to provide significant performance benefits.

Run COMPACT_STORAGE in simulation mode

The simulate parameter produces a report that shows the number of affected files and the change in file storage.

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)