Database Technology

Christophe Pettus discusses the plan_cache_mode GUC, which allows overriding PostgreSQL's default prepared statement planning behavior. This setting helps correct poor execution plans caused by inaccurate cost estimates when working with skewed data distributions. It provides a mechanism to choose between first-plan-caching and force-custom-plan strategies.

• Use plan_cache_mode to override automatic plan selection when cost estimates fail.
• Addresses execution plan degradation caused by data skew in prepared statements.
• Allows switching between caching the first plan or forcing a custom plan per execution.
• Valuable for consultants tuning applications with unpredictable query patterns.
• Avoids hardcoding plan hints by adjusting a session-level configuration parameter.

Radim Marek explains how TABLESAMPLE allows PostgreSQL to retrieve a random subset of rows without scanning the entire table, offering a performance alternative to full sequential scans for exploratory queries. While a standard COUNT(*) on a large table requires reading every page—resulting in significant I/O and latency even if cached—TABLESAMPLE bypasses this overhead by sampling directly from the heap or index. This technique is particularly useful when an exact count is unnecessary, enabling faster answers for large datasets where indexing the filtered column is not feasible.

• Full sequential scans on unindexed large tables cause high buffer read counts and latency, even for simple aggregations.
• TABLESAMPLE provides a low-cost alternative for exploratory data analysis when exact precision is not required.
• Caching benefits are limited for one-off full scans; sampling reduces I/O regardless of buffer cache hit rates.
• Use TABLESAMPLE to get rough estimates quickly on tables where creating an index for the filter column is prohibitive.

This guide covers migrating complex SSIS packages to Amazon Aurora PostgreSQL, specifically addressing control flow, foreach loops, and script tasks. It recommends substituting these with AWS Step Functions state machines, Lambda functions, and EventBridge for scheduling. The approach replaces SQL Server Agent with native AWS orchestration and event services.

• Replace SSIS control flow with AWS Step Functions state machines for robust orchestration
• Convert Foreach Loop Containers to Step Functions Map and Parallel states
• Swap Script Tasks for AWS Lambda functions to handle custom logic
• Use Amazon EventBridge to replace SQL Server Agent for job scheduling

PostgreSQL 17 can raise an 'infinite recursion detected in policy' error when specific row-level security policies trigger recursive evaluation loops. The article benchmarks the performance impact of various fixes on a million-row dataset, revealing a performance disparity ranging from 0.09 ms to 6.6 seconds. This highlights the critical need to audit RLS policy logic to prevent both runtime errors and significant query degradation.

• PostgreSQL 17 detects and halts recursive policy evaluation loops explicitly.
• Fix performance varies drastically, from negligible to 6.6 seconds per million rows.
• Audit RLS policies for self-referencing logic to prevent recursion errors.
• Benchmark policy changes against large datasets before production deployment.