Posted inDatabase

PostgreSQL Data Tiering: Moving Cold Data to S3 to Slash Costs

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The 2 AM Storage Emergency

It’s 2 AM, and my pager is screaming. The primary PostgreSQL instance just hit 98% disk capacity. Again. We’ve already upsized our EBS volumes twice this month. Now, the finance team is asking why our monthly RDS bill has spiked to $12,000. The culprit is a telemetry_logs table that has ballooned to 4TB, even though our application only needs the last 30 days of data for daily operations.

Storing five years of historical logs on high-performance NVMe storage is a waste of resources. It’s like keeping old high school yearbooks in a high-security bank vault. It’s expensive, unnecessary, and it drags down your backup speeds and maintenance windows. This is where Data Tiering comes in. You keep “Hot” data on fast disks and offload “Cold” data to inexpensive storage like Amazon S3.

Evaluating Your Archiving Options

When your database starts hitting storage limits, you generally have three paths forward. I’ve implemented all of them, and here is how they compare in production environments:

1. The “Delete and Forget” Strategy

You run a cron job to purge data older than 90 days. It’s the simplest move, but it’s risky. The moment a customer requests a year-over-year growth report, you’re stuck. In modern SaaS, data is an asset you rarely want to destroy completely.

2. The Secondary Archive Database

You migrate old records to a separate, lower-spec PostgreSQL instance. This clears space on the primary, but it creates a massive headache for developers. Your application now needs complex logic to decide which database to query, doubling your connection management overhead.

3. Data Tiering with Foreign Data Wrappers (FDW)

This is the most balanced approach. You move data to S3 but keep it accessible via standard SQL. To your application, the data looks like just another partition. To your budget, it costs pennies. This is the strategy we will implement below.

The Trade-offs of S3 Tiering

Before moving production workloads, you must understand the performance profile. S3 is not a direct replacement for local SSDs.

  • The Benefits:
    • Huge Cost Savings: S3 Standard costs roughly $0.023/GB. Compare that to $0.125/GB for AWS gp3 volumes or $0.20+/GB for Provisioned IOPS.
    • Infinite Headroom: S3 doesn’t have a “disk full” state. You can scale from 1TB to 1PB without touching a single slider.
    • Lighter Primary DB: Keeping your active indexes small allows them to stay in RAM. This keeps your “Hot” queries running in the sub-10ms range.
  • The Downsides:
    • Increased Latency: A local query might take 20ms, while the same query against S3 could take 2 to 5 seconds. Save this for internal reports, not user-facing features.
    • Management Overhead: You have to handle IAM roles and ensure your foreign table schemas stay in sync with your data files.

The Architecture: Partitioning + S3

For a production-grade setup, use PostgreSQL Declarative Partitioning combined with s3_fdw. This setup allows you to have one parent table (e.g., orders) with child partitions like orders_2024_q3 (on disk) and orders_2022_archive (on S3).

During these migrations, you often need to transform legacy data. If you need to convert CSV exports to JSON for testing or validation, toolcraft.app/en/tools/data/csv-to-json is a great utility. It processes everything in your browser, keeping sensitive data off external servers—a must for SOC2 compliance.

Step-by-Step Implementation

Let’s configure s3_fdw. This guide assumes a Linux-based PostgreSQL instance or a service that allows custom extensions.

Step 1: Install the Extension

Enable the extension in your database. On AWS RDS or Aurora, you would typically use the aws_s3 extension instead, but s3_fdw is the standard for self-managed Postgres.

CREATE EXTENSION s3_fdw;

Step 2: Define the Foreign Server

PostgreSQL needs to know how to reach your S3 bucket. Replace the placeholders with your specific AWS region and credentials.

CREATE SERVER s3_server 
FOREIGN DATA WRAPPER s3_fdw 
OPTIONS (host 's3.us-east-1.amazonaws.com');

CREATE USER MAPPING FOR current_user 
SERVER s3_server 
OPTIONS (access_key 'YOUR_ACCESS_KEY', secret_key 'YOUR_SECRET_KEY');

Step 3: Map the Foreign Table

Now, define a table that points to a specific CSV file in your bucket. This acts as the bridge between your SQL queries and the S3 object.

CREATE FOREIGN TABLE logs_2023_archive (
    id bigint,
    event_name text,
    created_at timestamp
)
SERVER s3_server
OPTIONS (bucket 'my-company-archives', filename 'logs/2023_backup.csv', format 'csv');

Step 4: Automate the Data Migration

Manual moves are prone to error. You should script a PL/pgSQL function to handle the rotation. The logic follows a simple pattern: export the data, drop the local storage, and link the S3 table.

Here is how the partition swap looks in practice:

-- 1. Move local data to S3 (Example using AWS RDS tools)
SELECT * from aws_s3.query_export_to_s3(
  'SELECT * FROM logs_old_partition',
  aws_commons.create_s3_uri('my-company-archives', 'logs_old.csv', 'us-east-1')
);

-- 2. Remove the heavy local partition
ALTER TABLE logs DETACH PARTITION logs_old_partition;
DROP TABLE logs_old_partition;

-- 3. Plug in the S3 partition
ALTER TABLE logs ATTACH PARTITION logs_2023_archive 
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Practical Lessons Learned

Tiering data is about more than just saving money; it makes your database manageable again. When a full pg_dump takes 15 minutes instead of 12 hours, your recovery time objective (RTO) improves drastically. Just keep one thing in mind: S3 does not support traditional database indexes. If you query the archived data, always include a date range to prevent the engine from scanning every file in your bucket.

Share:
Tags: