Using the shared plan cache for Amazon Aurora PostgreSQL

This article explains how Aurora PostgreSQL's Shared Plan Cache feature reduces memory consumption from duplicated query plans in high-concurrency environments.

• PostgreSQL prepared statements create generic plans reused across executions after six runs
• Each session maintains its own copy of identical generic plans, causing massive memory duplication
• Example: 100 prepared statements × 100 connections = 40GB memory overhead
• Shared Plan Cache keeps one copy of each generic plan shared across all sessions
• Enable with parameter: apg_shared_plan_cache.enable = ON (requires Aurora PostgreSQL 17.6+ or 16.10+)
• Same workload reduced from 40GB to 400MB memory consumption in testing
• Benefits: run smaller instances, support more connections, avoid out-of-memory errors
• Best for applications with hundreds/thousands of connections using prepared statements extensively

Shared Plan Cache significantly reduces memory overhead for high-concurrency PostgreSQL workloads with prepared statements, enabling cost savings and improved scalability.