Request a Demo
29.4. Marking Snapshots as Expired and Deleting Parquet Files
Prev UpChapter 29. OLAP DataHome Next

29.4. Marking Snapshots as Expired and Deleting Parquet Files #

29.4.1. Marking Snapshots as Expired
29.4.1.1. Marking Snapshots as Expired Based on the Creation Date and Time (metastore.expire_snapshot_on_time)
29.4.1.2. Marking Snapshots as Expired Based on Their IDs (metastore.expire_snapshot_on_ids)
29.4.2. Deleting Parquet Files (metastore.delete_expired_files)

In Postgres Pro AXE, the deletion of Parquet files is a two-stage process. First, you mark snapshots as expired, which excludes the associated Parquet files from analytical queries. Then, you delete Parquet files from the storage.

29.4.1. Marking Snapshots as Expired #

29.4.1.1. Marking Snapshots as Expired Based on the Creation Date and Time (metastore.expire_snapshot_on_time) #

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 22.1.

Execute the following command: 

  SELECT metastore.expire_snapshot_on_time('start_date_and_time', 'end_date_and_time');

Snapshots created between start_date_and_time and end_date_and_time are marked as expired.

Example 29.4. Possible value formats

  • '2025-11-13 12:22:46.123456+03'

  • '2025-11-13 12:22:46+03'

  • '2025-11-13 12:22:46'

  • '2025-11-13'


The fractional part of seconds must have no more than 6 digits.

The snapshot creation date and time are contained in the snapshot_time column of the pga_snapshot metadata table.

Postgres Pro AXE performs the following actions:

  1. Marks snapshots as expired if their snapshot_time values in the pga_snapshot metadata table are between start_date_and_time and end_date_and_time.

  2. Finds entries in the pga_data_file metadata table whose begin_snapshot values reference expired snapshots and creates associated entries in the pga_files_scheduled_for_deletion metadata table if they were not already created.

    Note

    Other metadata tables whose begin_snapshot values reference expired snapshots are not impacted.

Example 29.5. Executing the metastore.expire_snapshot_on_time stored procedure

To mark snapshots created within the last 24 hours as expired, use to_char() and now() functions: 

  SELECT metastore.expire_snapshot_on_time(to_char(now() - INTERVAL '1 day', 'YYYY-MM-DD HH24:MI:SS.US TZH:TZM'), to_char(now(), 'YYYY-MM-DD HH24:MI:SS.US TZH:TZM'));

To mark all snapshots created before a certain date and time as expired, use an empty string as the first parameter: 

  SELECT metastore.expire_snapshot_on_time('', '2025-11-28');

To mark all snapshots created after a certain date and time as expired, use an empty string as the second parameter: 

  SELECT metastore.expire_snapshot_on_time('2025-11-28', '');

Executing this stored procedure with an empty string as both parameters or with the first parameter greater than the second parameter returns an error message, and no snapshots are marked as expired.


You must recreate Postgres Pro views for analytical tables to exclude expired Parquet files from analytical queries.

Important

Postgres Pro views can be recreated only during periods of zero user activity to prevent potential data loss.

29.4.1.2. Marking Snapshots as Expired Based on Their IDs (metastore.expire_snapshot_on_ids) #

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 22.1.

Execute the following command: 

  SELECT metastore.expire_snapshot_on_ids('list_of_snapshot_IDs');

Where list_of_snapshot_IDs is a comma-separated list of snapshot IDs.

Specify the list as a string literal.

Example 29.6. 

'[1,2,3,4]'


Snapshot IDs are contained in the snapshot_id column of the pga_snapshot metadata table.

Postgres Pro AXE performs the following actions:

  1. Marks snapshots as expired if their snapshot_id values in the pga_snapshot metadata table equal one of the specified snapshot IDs.

  2. Finds entries in the pga_data_file metadata table whose begin_snapshot values reference expired snapshots and creates associated entries in the pga_files_scheduled_for_deletion metadata table if they were not already created.

    Note

    Other metadata tables whose begin_snapshot values reference expired snapshots are not impacted.

Example 29.7. Executing the metastore.expire_snapshot_on_ids stored procedure

  SELECT metastore.expire_snapshot_on_ids('[1,2,3,4]');

You must recreate Postgres Pro views for analytical tables to exclude expired Parquet files from analytical queries.

Important

Postgres Pro views can be recreated only during periods of zero user activity to prevent potential data loss.

29.4.2. Deleting Parquet Files (metastore.delete_expired_files) #

Before deleting Parquet files, you must mark the associated snapshots as expired.

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 22.1.

Execute the following command: 

SELECT metastore.delete_expired_files();

Postgres Pro AXE performs the following actions:

  1. Deletes Parquet files from the storage and their entries from pga_files_scheduled_for_deletion, pga_data_file, and pga_file_column_statistics metadata tables.

  2. Updates statistics for analytical tables and columns associated with deleted Parquet files in pga_table_stats and pga_table_column_stats metadata tables.

  3. Updates column_order values for columns associated with deleted Parquet files in the pga_column metadata table.

Prev Up Next
29.3. Description of Parquet File Storage Parameters Home Chapter 30. Hive Partitioning
epub pdf