Configure PGFS storage for Tiered Tables

Configure PGFS storage for Tiered Tables#

To enable Tiered Tables, you must configure a PGFS storage location on your PGD cluster. This defines where PGD will offload “cold” data (as Iceberg tables) to object storage.

You can use an S3-compatible object store (AWS S3, Google Cloud Storage, or compatible private storage).

Why configure PGFS for Tiered Tables?#

By configuring this:

  • You give PGD a destination for analytics offload of partitions managed by Tiered Tables.

  • You unlock the ability to transparently query cold data using Lakehouse clusters or PGD parent table.

  • This is the first required step before configuring the PGD node group for offload and enabling AutoPartition.

For background, see: Tiered Tables in Hybrid Manager

Goals#

After completing this How-To, you will be able to:

  • Create a valid PGFS storage location pointing to your object storage bucket.

  • Verify that your PGD cluster can use this location for offloading.

  • Proceed to configuring PGD node group offload settings.

Prerequisites#

Before you begin:

  • You have a PGD cluster provisioned in Hybrid Manager.

  • You have an object storage bucket created.

  • You have access credentials for the bucket (if private).

  • You have connected to your PGD node using psql or a SQL client.

Steps#

Step 1: Connect to your PGD node#

Connect using:

  • psql

  • DBeaver

  • pgAdmin

  • Any SQL client that supports your PGD node.

Step 2: Run pgfs.create_storage_location()#

Public S3 bucket example:

SELECT pgfs.create_storage_location(
name => hm_tiered_analytics_store,
url => s3://your-public-bucket/path/to/tiered-data,
options => {"aws_skip_signature": "true"},
credentials => {}
);

Private S3 bucket example:

SELECT pgfs.create_storage_location(
name => hm_tiered_analytics_store,
url => s3://your-private-bucket/path/to/tiered-data,
options => {"region": "us-west-2"},
credentials => {"access_key_id": "YOUR_AWS_ACCESS_KEY_ID", "secret_access_key": "YOUR_AWS_SECRET_ACCESS_KEY"}
);

Guidance:

  • Replace url with your target bucket path.

  • Adjust options and credentials for your cloud provider as needed.

Step 3: Validate that the PGFS storage location was created#

Run:

SELECT * FROM pgfs.storage_location;

You should see your newly created PGFS location listed.

Notes#

  • You can create multiple PGFS storage locations and assign them to different PGD node groups.

  • If multiple node groups offload to the same bucket, use distinct path prefixes per node group to avoid data overlap.

Next steps#

Now that you have configured your PGFS storage location:

Contents