Skip to main content

Enable PostgreSQL for Analytics on Aiven for PostgreSQL®

Request access to PostgreSQL for Analytics, connect your service to your Amazon S3 bucket, and create your first Iceberg table.

note

PostgreSQL for Analytics is in limited availability (LA). There's no self-service toggle: Aiven enables it for you on a per-service basis after a request.

Prerequisites​

  • An Aiven for PostgreSQL 17 service with PostgreSQL for Analytics enabled, created by Aiven during onboarding.
  • An organization or project admin role in the project where that service runs.
  • An Amazon S3 bucket that you own, in the same region as your service, and an AWS access key with permission to read from and write to that bucket.
  • Optionally, to connect the bucket programmatically:

Request access​

  1. Contact Aiven or your account team, and describe your analytical use case and the data volume you plan to work with.
  2. Aiven reviews your request against the eligibility criteria for this LA stage.
  3. After approval, Aiven creates a PostgreSQL service with PostgreSQL for Analytics enabled and schedules an onboarding call to walk through the feature and set expectations for this LA stage.
important

PostgreSQL for Analytics is set when the service is created. You can't add it to an existing Aiven for PostgreSQL service.

Connect your Amazon S3 bucket​

Create an Amazon S3 integration endpoint in your project, then connect that endpoint to your service with a PostgreSQL for Analytics Credentials integration. The endpoint holds the bucket URL and the AWS credentials, and the integration passes them to the analytical query engine. The integration's source is your PostgreSQL for Analytics service, and its destination is the Amazon S3 endpoint.

Requires Aiven Provider for Terraform 4.64.0 or later.

resource "aiven_service_integration_endpoint" "analytics_storage" {
  project       = var.project_name
  endpoint_name = "analytics-storage"
  endpoint_type = "external_aws_s3"

  external_aws_s3_user_config {
    url               = "https://BUCKET_NAME.s3.REGION.amazonaws.com"
    access_key_id     = var.aws_access_key_id
    secret_access_key = var.aws_secret_access_key
  }
}

resource "aiven_service_integration" "analytics_credentials" {
  project                 = var.project_name
  integration_type        = "pg_for_analytics_credentials"
  source_service_name     = aiven_pg.analytics.service_name
  destination_endpoint_id = aiven_service_integration_endpoint.analytics_storage.id
}

If you later rotate the access key on the endpoint, your service picks up the new credentials without any further action.

important

Use a bucket dedicated to this one service. Aiven doesn't prevent you from pointing two services at the same bucket, or from using a bucket that already holds other data, and neither is supported.

Don't remove or replace this integration once you have Iceberg tables. Queries expect those tables in the bucket the integration points to, so they fail if it changes.

Enable the extension​

Connect to the database where you plan to keep Iceberg tables as your primary service user, avnadmin, and run:

CREATE EXTENSION pg_lake CASCADE;

Your service comes with three group roles that control access to analytics data. Grant membership in them to the database users who need it, using your avnadmin user:

RoleMembers can
lake_readRead data in Iceberg tables
lake_writeWrite data in Iceberg tables
lake_read_writeBoth read and write data in Iceberg tables
GRANT lake_read_write TO analytics_user;

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(18,4),
    created_at timestamptz
) USING iceberg WITH (location = 's3://BUCKET_NAME/orders_analytics');
Always declare precision and scale on numeric columns

Iceberg can't represent a numeric column with no declared precision and scale as an exact decimal. If you create an Iceberg table from a numeric column without one, PostgreSQL for Analytics converts it to double precision, an inexact binary floating-point type, and only reports this with a low-severity NOTICE, which many clients don't show by default:

NOTICE:  column "order_total" has type that cannot be stored as an Iceberg
decimal, converting to double precision
HINT:  Use numeric(P,S) with precision <= 38 to preserve exact decimal
semantics.

A single converted value usually looks correct, but summing many of them, as most analytical queries do, accumulates rounding error. Totals computed through PostgreSQL for Analytics can silently stop matching the source data. This is particularly risky for financial, billing, or audit workloads.

PostgreSQL for Analytics makes this conversion by default. To keep exact decimal semantics, declare an explicit numeric(P,S) with a precision of 38 or less on every numeric column you migrate. If you can't change the source table, cast the column in your SELECT, for example total::numeric(18,4).

To catch these columns instead of converting them, turn the conversion off for your session. Creating a table with an unbounded numeric column then fails with an error instead of a notice:

SET pg_lake_iceberg.unsupported_numeric_as_double = off;

You can write into an Iceberg table directly, which keeps a single copy of the data in your bucket:

INSERT INTO orders_analytics VALUES (1001, 42, 99.9500, now());

You can also copy rows from a regular PostgreSQL table in the same service. This leaves you with two copies, and the Iceberg copy is only as current as your last load:

INSERT INTO orders_analytics SELECT * FROM orders;

Load in batches rather than row by row. Each write creates files in your bucket, so many small writes leave many small files and slow your queries down.

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