Database Technology

Amazon RDS for PostgreSQL and Aurora PostgreSQL frequently encounter out-of-memory (OOM) errors and excessive disk spilling in production. This guide explains how PostgreSQL allocates and consumes memory, enabling engineers to identify memory-intensive queries. It provides actionable steps to diagnose, prevent, and recover from these critical incidents.

• OOM events and disk spilling are top production incidents on RDS and Aurora PostgreSQL.
• Understanding memory allocation mechanics helps pinpoint resource bottlenecks early.
• Identify memory-intensive queries to optimize execution plans and reduce pressure.
• Follow specific diagnostic and recovery workflows to minimize downtime during OOMs.

Fleet impact: Monitor memory utilization metrics closely on RDS PostgreSQL and Aurora PostgreSQL instances; tune work_mem and shared_buffers to mitigate OOM risks and reduce disk spilling, ensuring stability for your database fleet.

Christophe Pettus explains that PostgreSQL acquires locks on every index associated with a table during query execution, regardless of whether the query actually utilizes those indexes. This behavior applies to all index types and can lead to unexpected contention. The post highlights that this is a fundamental aspect of the engine's locking protocol rather than a bug.

• All table indexes are locked during any query execution, not just those in the plan.
• Index usage in the query plan does not exempt the index from lock acquisition.
• This behavior is inherent to PostgreSQL's locking protocol and applies broadly.
• Monitor for contention on unused indexes in high-concurrency workloads.

Amazon Aurora DSQL introduces a time-based observability model accessible via Amazon CloudWatch Database Insights. The system leverages the DASH framework, PromQL, and a system diagnostics AI skill to help engineers identify performance bottlenecks. A key capability is connecting session time directly to cost, providing granular visibility into resource consumption.

• Use CloudWatch Database Insights for time-based observability of Aurora DSQL workloads.
• Leverage the DASH model and PromQL for detailed metric querying and analysis.
• Deploy the system diagnostics AI skill to automate bottleneck identification.
• Trace session duration directly to cost for precise resource attribution.

PostgreSQL 17 introduces failover slot synchronization to address disconnection issues in logical replication during high-availability failovers. This feature keeps replication slots active on standby servers, preventing the need for expensive full resynchronization once a standby is promoted. The update significantly improves recovery continuity for logical replication subscribers.

• Logical replication slots remain accessible on standbys during failover events
• Eliminates costly full resynchronization after standby promotion
• Critical for maintaining HA integrity in logical replication topologies
• Requires upgrade to PostgreSQL 17 to leverage this synchronization

A recent Hacker News discussion highlights a blog post advocating for PostgreSQL as a universal database solution. The conversation, which garnered significant engagement, explores the versatility and broad applicability of the open-source relational database system across various engineering use cases.

• PostgreSQL is increasingly viewed as a single database strategy for diverse workloads.
• High HN engagement indicates strong community interest in unifying database stacks.
• The discussion likely covers extensions and capabilities enabling broad adoption.

Michael Stonebraker attributes PostgreSQL's rise to Oracle's acquisition of MySQL, which pushed users toward the open-source alternative. The move effectively accelerated the market shift away from Oracle's proprietary ecosystem. This historical pivot highlights how major vendor actions can reshape the database landscape.

• Oracle's MySQL acquisition acted as a catalyst for PostgreSQL adoption
• Stonebraker notes a direct correlation between the deal and user migration
• Open-source alternatives benefit from proprietary vendor consolidation events
• Market dynamics favor open-source when trusted vendors shift focus

Christophe Pettus clarifies the behavior of two critical PostgreSQL GUCs for GSSAPI authentication. The krb_server_keyfile setting must point to a dedicated keytab file rather than the system default to ensure security. The article also details the implications of the krb_caseins_users parameter on authentication flows.

• Never use the system keytab file; always specify a dedicated path via krb_server_keyfile.
• Understand how krb_caseins_users affects principal matching in GSSAPI auth.
• Review current GUCs to ensure no unintended case-insensitive matching is active.
• Align keytab permissions and ownership with PostgreSQL service user requirements.