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:
|
simulate |
Specifies whether to simulate the storage settings and produce a report describing the impact of those settings.
|
Privileges
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:
- 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.
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)