Trying Out Direct Queries on S3 and S3 Tables with Aurora PostgreSQL's New Feature "aurora_analytics"

Introduction

On September 30, 2026, the aurora_analytics extension was added to Aurora PostgreSQL, making it possible to directly query Parquet files in S3 and Iceberg tables in S3 Tables as PostgreSQL foreign tables.

https://aws.amazon.com/about-aws/whats-new/2026/09/aurora-postgresql-query-apache-iceberg-and-parquet/

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/query-iceberg-and-parquet-data.html

In this article, I created foreign tables in two scenarios: one that directly references Parquet files in S3, and one that references Iceberg tables in S3 Tables. I then confirmed that each can be JOINed with business tables inside Aurora.

Test Environment

• Aurora PostgreSQL 17.11
• db.r8g.large (ap-northeast-1)
• Connection: RDS Data API

The aurora_analytics extension is supported on Aurora PostgreSQL 17.11 or later, or 18.6 or later.

Setup

Using aurora_analytics requires an IAM role that reads from S3 and a custom DB cluster parameter group. In this setup (default VPC, public access disabled), a VPC endpoint was also required.

I created the IAM role, parameter group, and Aurora cluster all at once with a CloudFormation template. There are three settings to get right. Only the VPC endpoint was created manually instead of being included in the template, which I describe later.

The first is the IAM role association. Specify AuroraAnalytics for FeatureName in AssociatedRoles. This is a different feature name from s3Import, which is used for S3 import. Grant the role permissions such as s3:GetObject on the bucket where the Parquet files are stored. If you read from S3 Tables, also add permissions such as s3tables:GetTableData.

The second is the parameter group. The default parameter group cannot be modified. Therefore, create a custom DB cluster parameter group with aurora_analytics.enabled set to "1" and assign it to the cluster.

AuroraAnalyticsRole:
  Type: AWS::IAM::Role
  Properties:
    RoleName: !Sub "${AWS::StackName}-AuroraAnalyticsRole"
    AssumeRolePolicyDocument:
      Version: "2012-10-17"
      Statement:
        - Effect: Allow
          Principal:
            Service: rds.amazonaws.com
          Action: sts:AssumeRole
    Policies:
      - PolicyName: AuroraAnalyticsS3Policy
        PolicyDocument:
          Version: "2012-10-17"
          Statement:
            - Effect: Allow
              Action:
                - s3:GetObject
                - s3:ListBucket
                - s3:GetBucketLocation
              Resource:
                - !Sub "arn:aws:s3:::${S3BucketName}"
                - !Sub "arn:aws:s3:::${S3BucketName}/*"
            - Effect: Allow
              Action:
                - s3tables:GetTableData
                - s3tables:GetTable
                - s3tables:ListTables
                - s3tables:GetTableMetadataLocation
              Resource: "*"

ClusterParameterGroup:
  Type: AWS::RDS::DBClusterParameterGroup
  Properties:
    Description: Aurora PostgreSQL 17 custom parameter group with aurora_analytics enabled
    Family: aurora-postgresql17
    Parameters:
      aurora_analytics.enabled: "1"

DBCluster:
  Type: AWS::RDS::DBCluster
  Properties:
    Engine: aurora-postgresql
    EngineVersion: "17.11"
    DBClusterParameterGroupName: !Ref ClusterParameterGroup
    EnableHttpEndpoint: true
    AssociatedRoles:
      - RoleArn: !GetAtt AuroraAnalyticsRole.Arn
        FeatureName: AuroraAnalytics

AWSTemplateFormatVersion: "2010-09-09"
Description: >
  Aurora PostgreSQL 17.11 / db.r8g.large cluster for aurora_analytics verification.
  Data API enabled, custom cluster parameter group (aurora_analytics.enabled=1),
  IAM role for S3 read access, auto-generated master password via Secrets Manager.

Parameters:
  DBMasterUsername:
    Type: String
    Default: postgres
    AllowedPattern: "[a-zA-Z][a-zA-Z0-9_]*"
    MinLength: 1
    MaxLength: 16

  S3BucketName:
    Type: String
    Description: S3 bucket name for Parquet files

Resources:
  DBSecret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: !Sub "${AWS::StackName}/db-master-password"
      GenerateSecretString:
        SecretStringTemplate: !Sub '{"username": "${DBMasterUsername}"}'
        GenerateStringKey: password
        PasswordLength: 32
        ExcludeCharacters: '"@/\'

  AuroraAnalyticsRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub "${AWS::StackName}-AuroraAnalyticsRole"
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: Allow
            Principal:
              Service: rds.amazonaws.com
            Action: sts:AssumeRole
      Policies:
        - PolicyName: AuroraAnalyticsS3Policy
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action:
                  - s3:GetObject
                  - s3:ListBucket
                  - s3:GetBucketLocation
                Resource:
                  - !Sub "arn:aws:s3:::${S3BucketName}"
                  - !Sub "arn:aws:s3:::${S3BucketName}/*"
              - Effect: Allow
                Action:
                  - s3tables:GetTableData
                  - s3tables:GetTable
                  - s3tables:ListTables
                  - s3tables:GetTableMetadataLocation
                Resource: "*"

  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Subnet group for Aurora analytics cluster
      SubnetIds:
        - "<subnet-id-1>"
        - "<subnet-id-2>"
        - "<subnet-id-3>"

  DBSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: SG for Aurora analytics cluster
      VpcId: "<vpc-id>"
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          CidrIp: "<vpc-cidr>"
          Description: PostgreSQL from within the VPC
        - IpProtocol: tcp
          FromPort: 443
          ToPort: 443
          CidrIp: "<vpc-cidr>"
          Description: HTTPS for S3 Tables Interface endpoint

  ClusterParameterGroup:
    Type: AWS::RDS::DBClusterParameterGroup
    Properties:
      Description: Aurora PostgreSQL 17 custom parameter group with aurora_analytics enabled
      Family: aurora-postgresql17
      Parameters:
        aurora_analytics.enabled: "1"

  DBCluster:
    Type: AWS::RDS::DBCluster
    DeletionPolicy: Delete
    UpdateReplacePolicy: Delete
    Properties:
      Engine: aurora-postgresql
      EngineVersion: "17.11"
      DBClusterIdentifier: !Sub "${AWS::StackName}-cluster"
      MasterUsername: !Sub "{{resolve:secretsmanager:${DBSecret}:SecretString:username}}"
      MasterUserPassword: !Sub "{{resolve:secretsmanager:${DBSecret}:SecretString:password}}"
      DBSubnetGroupName: !Ref DBSubnetGroup
      VpcSecurityGroupIds:
        - !Ref DBSecurityGroup
      DBClusterParameterGroupName: !Ref ClusterParameterGroup
      EnableHttpEndpoint: true
      StorageEncrypted: true
      DeletionProtection: false
      BackupRetentionPeriod: 1
      AssociatedRoles:
        - RoleArn: !GetAtt AuroraAnalyticsRole.Arn
          FeatureName: AuroraAnalytics

  DBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      DBInstanceClass: db.r8g.large
      DBInstanceIdentifier: !Sub "${AWS::StackName}-instance"
      Engine: aurora-postgresql
      DBClusterIdentifier: !Ref DBCluster
      PubliclyAccessible: false

  SecretAttachment:
    Type: AWS::SecretsManager::SecretTargetAttachment
    Properties:
      SecretId: !Ref DBSecret
      TargetId: !Ref DBCluster
      TargetType: AWS::RDS::DBCluster

Outputs:
  ClusterArn:
    Value: !Sub "arn:aws:rds:${AWS::Region}:${AWS::AccountId}:cluster:${AWS::StackName}-cluster"
  SecretArn:
    Value: !Ref DBSecret
  IAMRoleArn:
    Value: !GetAtt AuroraAnalyticsRole.Arn

The third is the VPC endpoint. The type differed depending on the source being referenced.

• Parquet (direct S3 reference): S3 Gateway VPC endpoint (com.amazonaws.ap-northeast-1.s3). Associate it with the route table used by the cluster's subnets. Instances placed in a private subnet have no public IP, so even if the default VPC has an IGW, they cannot reach S3. Before creating the endpoint, queries against the foreign table failed with curlCode: 28, Timeout. A NAT Gateway can also provide reachability, but I chose a Gateway endpoint this time.
• S3 Tables (Iceberg): S3 Tables Interface VPC endpoint (com.amazonaws.ap-northeast-1.s3tables). Enable private DNS. This time I reused the cluster's SG for the endpoint as well, and allowed inbound TCP 443 (source: the VPC's CIDR). This is why the SG in the template has a rule for 443.

Scenario 1: JOIN a Parquet file in S3 as a foreign table

First, create the extension with CREATE EXTENSION. At this point, the foreign server aurora_analytics_server, which the foreign tables reference, is also created automatically.

CREATE EXTENSION aurora_analytics;

Next, define the Parquet file as a foreign table. Leave the column definition as an empty (), and the schema is automatically inferred from the Parquet file. Specify the S3 URI in location, 'parquet' in format, and the region.

CREATE FOREIGN TABLE IF NOT EXISTS transaction_history_parquet ()
  SERVER aurora_analytics_server
  OPTIONS (
    location 's3://<bucket>/data/transaction_history.parquet',
    format 'parquet',
    region 'ap-northeast-1'
  );

After defining it, I confirmed with SELECT COUNT(*) that 1019 rows could be read.

I JOINed this foreign table by customer ID with a regular Aurora table, recent_transactions (a table containing the 12 most recent transactions, standing in for a business table). No special syntax is needed to account for the foreign table; it is just the normal PostgreSQL JOIN syntax.

SELECT
    r.customer_id,
    COUNT(h.transaction_id)              AS history_count,
    ROUND(SUM(h.amount)::numeric, 0)     AS history_total
FROM recent_transactions r
JOIN transaction_history_parquet h
    ON r.customer_id = h.customer_id
GROUP BY r.customer_id
ORDER BY history_total DESC;

Here are the statistics for this query (values are from a single execution; how to obtain them is explained in "How to check S3 reads and cache").

query: SELECT r.customer_id, COUNT(h.transaction_id) AS history_count, ROUND(SU
calls      : 1
s3_read    : 0 bytes      (analytics_remote_read_bytes)
cache_hit  : 32992 bytes  (analytics_cache_hit_bytes)
mean_ms    : 71.2 ms      (mean_exec_time)
result_rows: 6

calls is "the number of times this SQL statement was executed" and does not mean the first access to the foreign table. Because the Parquet file was already in the cache from the SELECT COUNT(*) run before the JOIN, this JOIN had zero reads from S3.

Scenario 2: JOIN an Iceberg table in S3 Tables as a foreign table

For S3 Tables, specify the table's ARN in location. The format and region are detected automatically from the ARN, so format and region can be omitted.

CREATE FOREIGN TABLE IF NOT EXISTS transaction_history_s3tables ()
  SERVER aurora_analytics_server
  OPTIONS (
    location 'arn:aws:s3tables:ap-northeast-1:<account>:bucket/<bucket>/table/<table-id>'
  );

Here too, I confirmed 1019 rows with SELECT COUNT(*) before the JOIN. The JOIN is written the same way as in Scenario 1; only the referenced foreign table name changes to transaction_history_s3tables, and the SQL structure is identical.

Here are the statistics for this query (values are from a single execution).

query: SELECT r.customer_id, COUNT(h.transaction_id) AS history_count, ROUND(SU
calls      : 1
s3_read    : 13195 bytes  (analytics_remote_read_bytes)
cache_hit  : 24919 bytes  (analytics_cache_hit_bytes)
mean_ms    : 54.4 ms      (mean_exec_time)
result_rows: 6

Here, even though I ran COUNT(*) first, part of the data was still read from S3. Since Iceberg is split into metadata files and data files, I believe there were parts that COUNT(*) alone did not load into the cache. Note that the execution times in Scenarios 1 and 2 were measured with different cache states, so they cannot be used to compare the performance of Parquet and S3 Tables.

How to check S3 reads and cache

How much a query against a foreign table read from S3, and how much was served from the cache, can be checked with pg_catalog.aurora_analytics_stat_statements().

SELECT
    LEFT(query, 80)                        AS query,
    calls,
    analytics_remote_read_bytes            AS s3_read,
    analytics_cache_hit_bytes              AS cache_hit,
    ROUND(mean_exec_time::numeric, 1)      AS mean_ms,
    rows                                   AS result_rows
FROM pg_catalog.aurora_analytics_stat_statements()
ORDER BY stats_since DESC;

Limitations

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-analytics-limitations.html

Summary

With the aurora_analytics extension on Aurora PostgreSQL 17.11, I was able to define Parquet files in S3 and Iceberg tables in S3 Tables as foreign tables and combine them with Aurora business tables using ordinary JOIN syntax.

Until now, to combine Parquet or Iceberg data on S3 with Aurora data, you had to load it into Aurora using something like Glue, or call Lambda from Aurora to fetch it.

https://dev.classmethod.jp/articles/invoke-lambda-from-amazon-aurora-postgresql/

With this extension, once a foreign table is defined, you only need to change the table name referenced in the query. No ETL or intermediate tables were needed.

Foreign tables are read-only, and the cache is cleared by restarts and failovers. A good first use case to try is read-oriented analysis that matches the latest data in the business DB against historical data in S3. If you analyze data by combining an S3 data lake with Aurora data, please give it a try.