Database Technology

Christophe Pettus clarifies that max_standby_streaming_delay and max_standby_archive_delay control replication lag, not query execution time. Once the allocated delay budget is exhausted, standby queries are canceled immediately, regardless of their duration. This distinction is critical for tuning read replicas to handle heavy analytical workloads without unnecessary aborts.

• These GUCs manage replication lag, not query runtime on the standby.
• Queries are canceled immediately once the delay budget is exceeded.
• Long-running analytical queries on standbys are vulnerable to cancellation.
• Tune these parameters based on acceptable replication lag, not query length.

Dmitry Narizhnykh introduces a free PostgreSQL Dump Viewer that allows users to inspect backup files without restoring them to a live server. The tool replays the dump into a virtual PostgreSQL instance within the browser tab, enabling read-only SQL queries and schema inspection. This approach keeps the dump file local and avoids the overhead of provisioning a temporary database server for verification.

• Avoids full restore overhead by replaying dumps in a browser-based virtual instance.
• Enables read-only SQL execution for quick schema and row verification.
• Keeps sensitive dump files local; no data is uploaded to external servers.
• Useful for validating backup contents before committing to a full restore process.

AWS demonstrates a fully managed, event-driven CDC pipeline that streams row-level changes from RDS for PostgreSQL or Aurora PostgreSQL to Amazon S3 in near real time. The solution is deployed using a single CloudFormation template and operates within private subnets, ensuring no internet gateway exposure. This architecture enables secure, low-latency data ingestion for analytics or archival purposes without public internet access.

• Deploy full CDC pipeline via single CloudFormation template for rapid provisioning
• Supports both RDS for PostgreSQL and Amazon Aurora PostgreSQL sources
• Operates in private subnets with no internet gateway for enhanced security
• Streams row-level changes to S3 in near real time for downstream consumption

Amazon FireTV redesigned its watch-progress data model in DynamoDB by applying vertical partitioning and hash-prefixed sort keys to enable parallel reads. This architecture removes item-size limits and allows the system to serve millions of customers with read latencies under 50 milliseconds regardless of profile size. The approach resulted in a 97% reduction in write costs while maintaining high performance.

• Vertical partitioning eliminates DynamoDB's 400KB item size limit for large datasets
• Hash-prefixed sort keys enable efficient parallel read execution across partitions
• Write costs dropped 97% by optimizing data structure and query patterns
• Read latency remains under 50ms at scale without sacrificing throughput

Setting the GUC max_slot_wal_keep_size allows PostgreSQL to automatically terminate replication slots that exceed a specified WAL size threshold. This prevents stalled replicas from silently consuming all available disk space and crashing the primary server. It serves as a safety mechanism to enforce WAL retention limits without manual intervention.

• Prevents primary disk exhaustion caused by stalled replication slots
• Automatically kills replicas exceeding the configured WAL retention limit
• Configurable via the max_slot_wal_keep_size GUC parameter
• Mitigates risk of server crashes due to full disk scenarios