Skip to main content

Enable PostgreSQL for Analytics on Aiven for PostgreSQL®

Enable PostgreSQL for Analytics on a new Aiven for PostgreSQL® service, connect it to your Amazon S3 bucket, and create your first Iceberg table.

note

PostgreSQL for Analytics is in limited availability (LA) and requires access from Aiven before you can use it.

Prerequisites​

  • Access to PostgreSQL for Analytics granted by Aiven for your organization.
  • An organization or project admin role in the project where you create the service.
  • An Amazon S3 bucket that you own, and an AWS access key with permission to read from and write to that bucket.

Create a PostgreSQL for Analytics service​

  1. Log in to the Aiven Console and go to your organization and project.
  2. Click Create service and select PostgreSQL®.
  3. Select a PostgreSQL 17 service plan.
  4. In the service creation options, select PostgreSQL for Analytics.
  5. Enter a service name, select a cloud region, and click Create service.
important

You can enable PostgreSQL for Analytics only when you create a service. You can't add it to an existing service.

Connect your Amazon S3 bucket​

  1. In the Aiven Console, go to your project.
  2. Click Integration endpoints.
  3. Click Amazon S3, then click Add new endpoint.
  4. Enter an endpoint name.
  5. In Url, enter your bucket's virtual-hosted-style URL: https://BUCKET_NAME.s3.REGION.amazonaws.com.
  6. Enter the Access Key Id and Secret Access Key for an AWS user with read and write access to the bucket, and click Create.
  7. Open your PostgreSQL for Analytics service page and click Integrations.
  8. Click Lakehouse credentials, and select the Amazon S3 endpoint you created.

Enable the extension​

Connect to your service and run:

CREATE EXTENSION pg_lake CASCADE;

Create an Iceberg table​

Create a table that stores its data as Iceberg files in your S3 bucket. Set location to a path in your bucket:

CREATE TABLE orders_analytics (
    order_id bigint,
    customer_id bigint,
    order_total numeric,
    created_at timestamptz
) USING iceberg WITH (location = 's3://BUCKET_NAME/orders_analytics');

Load data into the table from an existing PostgreSQL table:

INSERT INTO orders_analytics SELECT * FROM orders;

Query the Iceberg table with standard SQL:

SELECT customer_id, sum(order_total)
FROM orders_analytics
GROUP BY customer_id
ORDER BY sum(order_total) DESC
LIMIT 10;

For more on managing extensions, see Manage Aiven for PostgreSQL® extensions.

Maintain Iceberg tables​

Iceberg tables accumulate data files, snapshots, and metadata over time, particularly if you insert data frequently in small batches. Run vacuum maintenance regularly to compact data files, expire old snapshots, and remove orphan files:

VACUUM orders_analytics;

Without regular maintenance, frequent small writes can leave many small files in your S3 bucket, which slows down queries. Schedule maintenance with pg_cron or run it manually after loading data in batches.

Related pages