[Video] Office Hours from Iceland’s Glacier Lagoon

Looking Forward to Postgres 19: Logically Sequenced

Postgres 19 bổ sung hỗ trợ sao chép logic (logical replication) cho sequences, vốn bị loại trừ suốt gần một thập kỷ do tính phi giao dịch. Tính năng mới tự động đồng bộ sequences tại các thời điểm xác định như tạo/refresh subscription, cùng công cụ hỗ trợ như hàm pg_get_sequence_data() và cột sync_seq_error_count. Cách tiếp cận này tương tự pglogical nhưng được tích hợp sẵn vào Postgres.

Lập trình viên cần đọc bài này để hiểu cách PostgreSQL 19 tự động đồng bộ hóa các chuỗi (sequences) trong cơ sở dữ liệu replication, giúp tránh lỗi thủ công và bảo đảm tính nhất quán khi chuyển đổi từ máy chủ sang subscriber mà không cần script bổ sung.

How Five PostgreSQL Optimizations Sped Up Our Dashboard

Nhóm kỹ thuật GitGuardian đã giảm thời gian phản hồi p95 của dashboard từ 8 giây xuống 1 giây nhờ 5 tối ưu hóa PostgreSQL trên hệ thống Django, bao gồm: deferred JOINs bằng prefetch_related, đếm bất đồng bộ, replica đọc premium, cải tiến full-text search (pg_trgm), và denormalization để hỗ trợ composite indexes. Việc nâng cấp lên PostgreSQL 18 cũng mang lại lợi ích nhỏ. Họ sử dụng OpenTelemetry và EXPLAIN ANALYZE để theo dõi tiến trình.

Nếu bạn đang làm việc với ứng dụng backend sử dụng PostgreSQL và Django, bài viết này sẽ giúp bạn tìm hiểu cách tối ưu hóa hiệu suất dashboard hiệu quả bằng những kỹ thuật cụ thể, từ đó tiết kiệm thời gian và chi phí phát triển.

pgtt v4.5 has been released

pgtt v4.5 has been released, a PostgreSQL extension that implements Oracle/DB2-style Global Temporary Tables (GTT). This maintenance release adds support for PostgreSQL 19, introduces CREATE INDEX CONCURRENTLY support, and fixes bugs reported since the last release. The extension is designed to ease migrations from Oracle or DB2 to PostgreSQL by mimicking GTT behavior without requiring application code rewrites.

statements to Reduce Deallocations

Part 5 of a deep dive into pg_stat_statements focuses on configuration and deallocations. The pg_stat_statements_info view exposes a deallocation counter showing how often the extension drops 5% of its hash table to make room for new entries. A 10-minute sampling window is recommended: no change is healthy, ~10 deallocations per window means losing 5% of rows per minute, and 600+ means roughly every second with serious locking overhead. The five configuration settings are covered in detail: max (requires restart, reserves memory regardless of tracking state), track (top/all/none, reload only), track_utility (on by default, turning it off instantly reduces utility-statement entries), track_planning (off by default, adds overhead), and save (restart required, default on). Beyond settings, application-level fixes matter more: avoid variable-length WHERE IN lists (use arrays instead), reduce databases per server, and avoid executing the same SQL under multiple roles, as each combination creates a separate entry.

Where Your Query Text Actually Lives

pg_stat_statements stores query text not in its in-memory hash table but in a separate on-disk file (pgss_query_texts.stat) inside the Postgres data directory. Each hash table entry holds an offset and length pointer into this append-only file. The file grows to roughly 2× the mean query length multiplied by pg_stat_statements.max, and when it hits that ceiling, Postgres rewrites it entirely under a brief lock. Because evicted queryids are forgotten, the same query text can appear multiple times. ORM-heavy workloads are the most common cause of the file ballooning to hundreds of megabytes due to verbose SQL and high queryid cardinality. Using pg_stat_statements(false) skips the file read entirely, which is useful when only metrics are needed.

Part 1: What It Is and What It Isn't

A deep dive into pg_stat_statements, the built-in Postgres extension for tracking query performance. Covers how it stores data in an in-memory hash table, how to enable it, what its 45+ columns track, and critically what it does NOT provide: per-execution history, time-windowed data, or unlimited query tracking (default cap is 5,000 unique queries). Explains the cumulative metrics model and why misunderstanding these limitations can leave teams blind during performance incidents. Part 1 of a multi-episode series that will also cover query normalization, source code internals, configuration tuning, and high-cardinality workload strategies.

Live Class Enrollment Closes July 12. Here’s Why.

Brent Ozar explains why enrollment for his live SQL Server Mastering classes closes on July 12th. The change is driven by a structured rotation system designed to ensure students take classes in the correct order, preventing newer students from jumping into advanced material before completing prerequisites. The first rotation runs from July through October, covering Fundamentals, Index Tuning, Query Tuning, and Server Tuning. Season Pass holders get access to recordings and the next rotation if they miss sessions.