Docs›Guides›Table›Foreign Table›Foreign Table Dynamodb

DynamoDB Foreign Table

Learn how to integrate DynamoDB with Tacnode using foreign tables, enabling standard sql queries with your DynamoDB data.

Amazon DynamoDB — Overview

Amazon DynamoDB is a fully managed, serverless NoSQL key-value and document database on AWS, built for low-latency, high-scale applications.

Key Characteristics

multi-millisecond latency
Automatic scaling and replication
Schema-less (beyond primary key)
Tight integration with AWS services (Lambda, Kinesis, IAM)

Pros

High performance at small scale
Serverless (no infrastructure management)
Highly available (multi-AZ replication)
Strong AWS ecosystem integration

Cons

Limited querying (no joins, ad-hoc queries)
Access patterns must be known upfront
Data modeling can be complex
Cost can grow quickly with inefficient access
400 KB item size limit

Good Fit When

Access patterns are well-defined
AWS-native architectures

Poor Fit When

Complex queries or analytics are required
Data is highly relational
Query needs change frequently

Feature	Benefit	Use Case
Primary Key Design	Session data, user profiles
Limited Global Secondary Indexes (GSI)	Multiple access patterns	Querying by alternate keys
On-Demand / Provisioned Capacity	Flexible throughput management	Spiky or steady workloads
Streams	Change data capture	Event-driven processing, audits
TTL (Time to Live)	Automatic data expiration	Caching, transient event data

DynamoDB Integration

Install DynamoDB FDW Extension

The following instructions are written to be run from the psql command line

-- Install DynamoDB foreign data wrapper (only once per database)
CREATE EXTENSION IF NOT EXISTS dynamodb_fdw;

-- Verify installation
SELECT extname, extversion 
FROM pg_extension 
WHERE extname = 'dynamodb_fdw';

-- Check available FDW options
\des+ dynamodb_fdw

Create DynamoDB Foreign Server

-- Create the DynamoDB server

CREATE SERVER  
FOREIGN DATA WRAPPER dynamodb_fdw 
OPTIONS ( 
   AWS_REGION 'region-id'
);

-- AWS_REGION: The Region ID of your AWS DynamoDB service. Refer to the AWS DynamoDB Region documentation for specific IDs.
-- For example:
CREATE SERVER dynamodb_server 
FOREIGN DATA WRAPPER dynamodb_fdw
OPTIONS ( 
   AWS_REGION 'us-east-1'
);

Configure DynamoDB User Mapping

User Mapping

-- Grant the local database user account access to the DynamoDB service using AWS credentials (AWS_ACCESS_ID and AWS_ACCESS_KEY).

CREATE USER MAPPING FOR  SERVER dynamodb_server
OPTIONS ( 
   AWS_ACCESS_ID 'access-id', 
   AWS_ACCESS_KEY 'access-key'
);

Create a Foreign Table

Foreign Table

-- Define a foreign table to map to a specific DynamoDB table
-- server_name: The name of the foreign server created previously.
-- table: Specifies the name of the DynamoDB table.

CREATE FOREIGN TABLE dynamodb_test(
 id text,       
 attr1 text
)
SERVER dynamodb_server
OPTIONS( table 'dynamodb_table_name');

Import Schema and Tables

Import Entire Schema

-- The dynamodb_fdw extension supports the IMPORT FOREIGN SCHEMA command to bulk-create foreign tables, simplifying table management.

IMPORT FOREIGN SCHEMA remote_schema    
[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]    
FROM SERVER server_name    
INTO local_schema    
[ OPTIONS ( option 'value' [, ... ] ) ]

Supported Parameters

remote_schema: Currently, dynamodb_fdw only supports the value: public.
LIMIT TO: Restricts the import to only the specified tables (DynamoDB table names).
EXCEPT: Excludes the specified tables from the import.
server_name: The name of the foreign server created previously.
local_schema: The local PostgreSQL schema where the foreign table metadata will be stored.
OPTIONS: Optional parameters for specifying additional configurations.

Schema Inference during Import — The system automatically samples DynamoDB items (default is 100 items) to infer PostgreSQL data types based on the attributes in each item. — DynamoDB attribute names are used as foreign table column names. — DynamoDB Type PostgreSQL Type

S – String ‘text’
N – Number ‘numeric’
B – Binary ‘bytea’
BOOL – Boolean ‘bool’
M – Map ‘jsonb’
L – List ‘jsonb’
SS – String Set ‘text[]’
NS – Number Set ‘numeric[]’
BS – Binary Set ‘bytea[]’

-- Bulk create tables from DynamoDB

IMPORT FOREIGN SCHEMA public 
LIMIT TO (dynamodb_table1, dynamodb_table2)
FROM SERVER dynamodb_server
INTO public;

Important Notes

Currently, only public is supported for remote_schema.
The foreign table name defaults to the same name as the DynamoDB table.

Advanced Feature: Mapping the Raw Item

Users can provide the raw_item_column option during IMPORT FOREIGN SCHEMA or CREATE FOREIGN TABLE to designate a column that fully maps the entire DynamoDB item content.
Importing a foreign schema

IMPORT FOREIGN SCHEMA "public" 
LIMIT TO (dynamodb_table) 
FROM SERVER dynamodb_server 
INTO public
OPTIONS (
   raw_item_column 'all_item'
);

Creating a foreign table

CREATE FOREIGN TABLE t
(
  id text, 
  all_item jsonb -- This column will store the raw item
)
SERVER dynamodb_server 
OPTIONS (
   table 'dynamodb_table', 
   raw_item_column 'all_item'
);

Best Practices

Design for Read Access Patterns

Project only required attributes
Avoid full table scans whenever possible
Ensure queries align with partition keys or GSIs Control Scan Costs
Use filters at the Tandode level, not downstream
Prefer key-based lookups over wide scans
Limit result sets with LIMIT Use GSIs Strategically
Create GSIs specifically for foreign-table queries
Avoid overloading the base table’s primary key
Monitor GSI read capacity independently Choose the Right Consistency Model
Use eventual consistency unless strong consistency is required
Strong consistency doubles read cost and latency Throttle and Protect the Source Table
Avoid analytical queries
Consider direct import of the data into Tacnode for analytics-heavy access Handle Schema Drift Explicitly
DynamoDB is schema-less; foreign tables are not
Version attributes or use nullable fields
Validate missing or unexpected attributes in queries with operators like COALESCE Know When Not to Use It
- Large analytical scans
- Joins across many rows
- Historical reporting

Rule of Thumb:
DynamoDB foreign tables are best for light, selective reads — not analytics.

This comprehensive approach to DynamoDB integration enables you to leverage DynamoDBs capabilities while maintaining the flexibility and performance of Tacnode’s query engine.