· Charlotte Will · Amazon API · 8 min read
How to Use Amazon RDS for Relational Database Management with API Data
Discover how to efficiently manage relational databases using Amazon RDS and API data. This comprehensive guide offers practical advice, keyword integration, and internal linking to related articles for a well-rounded SEO strategy.

Introduction
Most teams that pull data from an Amazon API end up in the same place: a JSON file on disk and nowhere to put it. The Selling Partner API returns order records and inventory levels a page at a time, on a schedule you set yourself. Storing that properly means settling on an engine and a schema, plus finding somewhere to run it.
Amazon RDS takes care of the running part: you pick the engine and the instance size, and Amazon handles the storage and the engine patching. What you keep is the schema and the code that fills it.
Provisioning an RDS instance
The RDS API is scriptable, which matters the moment you have more than one environment. boto3 exposes it through the rds client, and the create call takes the engine and the instance class as plain arguments.
import os
import boto3
rds = boto3.client('rds', region_name='us-east-1')
rds.create_db_instance(
DBInstanceIdentifier='seller-metrics',
Engine='postgres',
EngineVersion='15.4',
DBInstanceClass='db.t4g.medium',
AllocatedStorage=50,
StorageType='gp3',
StorageEncrypted=True,
MasterUsername='dbadmin',
MasterUserPassword=os.environ['RDS_MASTER_PASSWORD'],
BackupRetentionPeriod=14,
PubliclyAccessible=False,
)
rds.get_waiter('db_instance_available').wait(
DBInstanceIdentifier='seller-metrics'
)
db.t4g.medium is burstable, and it throttles if a backfill pushes it past its CPU credit balance. db.m6g.large costs more per hour but holds a steady rate. Storage is billed separately from compute, so you can grow the volume later with modify_db_instance without changing the endpoint.
Leave PubliclyAccessible off. A loader outside the VPC then needs a bastion host or a VPC endpoint to reach it. Getting the API responses is a separate problem, covered in What is Amazon SP-API for Automated Data Collection?.
Designing the schema for API data
Amazon API responses are nested and mostly optional, so a schema mirroring the raw JSON fights you later. A flat set of typed columns, plus one JSONB column for everything else, behaves better.
import os
import psycopg2
conn = psycopg2.connect(
host=os.environ['RDS_HOST'],
port=5432,
dbname='seller_metrics',
user='dbadmin',
password=os.environ['RDS_MASTER_PASSWORD'],
connect_timeout=10,
)
with conn:
with conn.cursor() as cur:
cur.execute("""
CREATE TABLE IF NOT EXISTS order_records (
order_id TEXT PRIMARY KEY,
marketplace_id TEXT NOT NULL,
purchase_date TIMESTAMPTZ,
total_amount NUMERIC(12, 2),
item_count INTEGER,
raw_payload JSONB NOT NULL,
ingested_at TIMESTAMPTZ NOT NULL DEFAULT now()
)
""")
cur.execute("""
CREATE INDEX IF NOT EXISTS idx_order_records_purchase
ON order_records (purchase_date)
""")
The order_id is the natural key, and every SP-API order record carries one. That choice decides a lot here. Without a stable identifier the upsert has no target, so duplicate rows accumulate and reporting queries get slower.
raw_payload exists so a field you did not think of on day one is still recoverable. It has a price: storage grows faster than a typed schema, and queries that ignore the typed columns get slower. Treat it as a holding area.
Upserting records without duplicating rows
Backfills get re-run. A script that half failed on Tuesday needs to run again on Wednesday and leave the same table behind, not a doubled one. ON CONFLICT gives you that.
import json
UPSERT_SQL = """
INSERT INTO order_records (
order_id, marketplace_id, purchase_date,
total_amount, item_count, raw_payload
)
VALUES (%s, %s, %s, %s, %s, %s)
ON CONFLICT (order_id) DO UPDATE SET
purchase_date = EXCLUDED.purchase_date,
total_amount = EXCLUDED.total_amount,
item_count = EXCLUDED.item_count,
raw_payload = EXCLUDED.raw_payload,
ingested_at = now()
RETURNING order_id
"""
def upsert_order(cur, record):
cur.execute(
UPSERT_SQL,
(
record['order_id'],
record['marketplaceId'],
record.get('purchaseDate'),
record.get('totalAmount'),
len(record.get('orderItems', [])),
json.dumps(record),
),
)
return cur.fetchone()
Parameterised queries matter for a practical reason. Values from a JSON payload are untrusted input, and the %s placeholders let psycopg2 handle escaping instead of you. Building the statement with an f-string puts anyone who can influence a field value in a position to change the query.
ON CONFLICT ... DO UPDATE is idempotent by construction. Send the same order twice and you get one row holding the newer values. For the live side, where you chase changes rather than history, Maximizing Efficiency with Real-Time Data Sync via Amazon PA-API covers the pricing feed and where it fits in the pipeline.
Pooling connections with SQLAlchemy
Opening a fresh TCP connection and a fresh Postgres backend for every page of API results is slow, and Postgres caps concurrent connections per instance. A pool fixes both problems at once.
from sqlalchemy import create_engine, text
engine = create_engine(
'postgresql+psycopg2://[email protected]:5432/seller_metrics',
pool_size=5,
max_overflow=10,
pool_timeout=30,
pool_recycle=1800,
pool_pre_ping=True,
)
with engine.connect() as conn:
rows = conn.execute(
text("SELECT marketplace_id, count(*) FROM order_records GROUP BY 1"),
).all()
pool_recycle=1800 is there because RDS closes idle connections after a while, and the error you get back is a dropped connection partway through a query, which is confusing when it lands on the first request of a scheduled job. pool_pre_ping costs one round trip and clears out dead ones first.
Set pool_size from the connection limit of the instance, not from the size of your application. db.t4g.medium sits far lower than a large instance, and going over the limit produces too many clients already errors rather than a slow query.
Backfilling historical data with pagination
SP-API endpoints hand back a NextToken whenever there is more to fetch. A backfill is a loop that follows that token, and a good one can be resumed after a failure.
from datetime import datetime, timezone
import requests
API_ROOT = 'https://sellingpartnerapi-na.amazon.com'
def fetch_orders(access_token, marketplace_id, next_token=None, page_size=50):
url = f'{API_ROOT}/orders/v0/orders'
params = {
'marketplaceIds': [marketplace_id],
'limit': page_size,
}
if next_token:
params['NextToken'] = next_token
response = requests.get(
url,
params=params,
headers={
'x-amz-access-token': access_token,
'x-amz-date': datetime.now(timezone.utc).strftime('%Y-%m-%dT%H:%M:%SZ'),
},
timeout=30,
)
response.raise_for_status()
return response.json()
def backfill(conn, access_token, marketplace_id):
next_token = None
seen = 0
while True:
page = fetch_orders(access_token, marketplace_id, next_token)
records = page.get('payload', {}).get('Orders', [])
with conn:
with conn.cursor() as cur:
for record in records:
upsert_order(cur, record)
seen += len(records)
next_token = page.get('payload', {}).get('NextToken')
if not next_token:
break
return seen
Real SP-API requests also need SigV4 signing, which adds an Authorization header the call above does not build. The SP-API article linked earlier shows the full shape.
SP-API does not expose every field a seller wants to track, and a scraping API is a common second source feeding the same tables. How to Use Amazon Scraping API for E-Commerce Data Extraction covers that route.
Two details decide whether a multi-million row backfill survives. Rate limits apply across the whole SP-API operation family, so back off on a 429 instead of retrying at once. And a run that dies halfway should be resumable, which the upsert makes possible: re-running from the start of a date range is safe because the writes are idempotent.
Monitoring connections and storage usage
A database that fills its disk or runs out of connection slots fails in ways that are awkward to diagnose from an application log. Two queries cover most of it.
def monitor(conn):
with conn.cursor() as cur:
cur.execute("""
SELECT count(*) AS connections,
count(*) FILTER (WHERE state = 'active') AS active
FROM pg_stat_activity
WHERE datname = current_database()
""")
connections, active = cur.fetchone()
cur.execute("""
SELECT pg_size_pretty(pg_database_size(current_database())) AS size
""")
size = cur.fetchone()[0]
return {
'connections': connections,
'active': active,
'database_size': size,
}
pg_stat_activity counts every session pointed at the instance, including the one asking the question. If that number sits near the limit for the class, the fix is a smaller pool or a bigger instance, not a faster query.
For storage, watch the FreeStorageSpace metric in the RDS console and alarm before it reaches zero. gp3 volumes grow on their own up to a ceiling, though autoscaling never shrinks them again. A database that has run a year of daily backfills also holds many rows you may not need.
Conclusion
RDS gives you a managed Postgres at a predictable per-hour cost, and the parts you still own decide data quality: the schema and the primary key, plus the logic that writes the rows. Provision the instance with boto3 and model the schema around a natural key, keeping unmodelled fields in a JSONB column. Pool the connections, then page through the API in a loop that can be re-run safely. Two bills stay with you either way: the capacity you provision, and every row you keep. Decide what you need to retain rather than storing everything you can fetch again.
FAQs
Does Amazon RDS include backups and database maintenance? Yes. RDS takes automated snapshots on a schedule you set and keeps them for a retention window you choose. It also patches the engine and the underlying operating system. You do not run the database server or the backup tooling yourself.
What is the difference between RDS and a database running on EC2? With RDS, Amazon owns the host and the disk and handles failover. With EC2 you manage the operating system and the Postgres install yourself. RDS costs more at the same instance size, and you give up some low-level control in exchange.
How do I avoid duplicate rows when a backfill runs twice? Give the table a natural primary key, such as
order_id, and write inserts withON CONFLICT (order_id) DO UPDATE. The second run updates the existing rows instead of inserting new ones.Can I store the raw JSON response alongside the parsed columns? Yes. PostgreSQL has a JSONB type, and you can read a field out of it with the
->and->>operators. Keep it as a fallback for fields you have not modelled, since it grows the table faster than typed columns do.Is the Amazon MWS API still usable for order management? No. Amazon ended support for the Marketplace Web Service on April 1, 2024, so new integrations need the Selling Partner API. Older workflows written against MWS are described in How to Integrate Amazon MWS API for Order Management, which is worth reading as a migration reference rather than a starting point.


