hacking.postgres.tv
./menu

postgres@hacking:~$ grep -R '#SQL' sessions/

#SQL

6 related sessions.

· 88:38 · youtube ↗

WHERE support for REFRESH MATERIALIZED VIEW

Partial refresh restricted by a predicate, including concurrency semantics.

The session explored adding a WHERE clause to REFRESH MATERIALIZED VIEW so selected rows can be refreshed without rebuilding the whole result. Reading the concurrent-refresh implementation revealed that Postgres creates a temporary difference table, then applies ordinary SQL deletes and inserts through SPI. After experimentally bypassing the normal DML guard, updates to a materialized view and its indexes worked. The group concluded that partial refresh looked technically approachable, while delete semantics, grammar, safety, and consistency still needed design work.

[timecodes · 7]
  1. The case for partial materialized-view refresh
  2. Building a concurrent-refresh test case
  3. Tracing REFRESH through parser and executor code
  4. How concurrent refresh computes and applies a diff
  5. Designing a filtered merge without global deletes
  6. Testing direct DML on a materialized view
  7. Conclusions and remaining partial-refresh semantics
#SQL@Nikolay Samokhvalov@Andrey Borodin@Kirk Wolak
-hackersCF #6305
active

direct mapping
· 48:43 · youtube ↗

Include ANALYZE steps in all dumps, part 2

Continue design and patch work on dump-time analysis.

Part two implemented the pg_dump side of per-object ANALYZE. The group modeled ANALYZE as a new dumpable archive object, dependent on its table and emitted in the post-data section after data and indexes. They limited the first prototype to ordinary tables and materialized views, excluding sequences, views, and foreign tables. The code compiled, but no ANALYZE appeared in output because the new object was not reached or enabled correctly; print debugging exposed control-flow problems, and they deferred completion.

[timecodes · 8]
  1. Resuming from pg_dump archive entries
  2. Creating a dumpable ANALYZE object
  3. Tables, partitions, foreign tables, and sequences
  4. Object IDs and table dependencies
  5. Choosing an ArchiveEntry implementation
  6. Building the ANALYZE command in post-data
  7. Enabling ANALYZE and considering opt-out
  8. Compilation succeeds, output debugging begins
#SQL@Nikolay Samokhvalov@Andrey Borodin@Kirk Wolak
-hackers
exploration

direct mapping
· 44:37 · youtube ↗

Include ANALYZE steps in all dumps, part 1

Preserve or trigger statistics collection in dump/restore output.

The group investigated why pg_restore leaves restored relations without planner statistics and agreed that a database-wide ANALYZE in pg_restore would be too coarse. Instead, pg_dump should add an ANALYZE entry for every dumped table so archive filtering and partial restores retain correct behavior. They explored directory-format archives, TOC dependencies, table-data files, materialized-view refresh ordering, and index ArchiveEntry code. They concluded ANALYZE belongs after data, indexes, and materialized-view population, then began defining a new DumpableObject type before the call ended.

[timecodes · 8]
  1. Why restored databases need ANALYZE
  2. Choosing pg_dump over a global pg_restore step
  3. Materialized views as the ordering model
  4. Exploring directory archives and the TOC
  5. How table definitions and data entries connect
  6. ANALYZE must follow indexes and refreshes
  7. Adding a new DumpableObject priority
  8. Learning from index ArchiveEntry generation
#SQL@Nikolay Samokhvalov@Andrey Borodin@Kirk Wolak
-hackers
exploration

direct mapping
· 59:00 · youtube ↗

Hacking Postgres 101 — ULID function

The ULID exercise evolved into PostgreSQL's UUIDv7 implementation.

They implemented a C prototype of a time-ordered 128-bit identifier, initially called ULID, combining microsecond time with random bytes in UUID storage. Generation speed matched gen_random_uuid(), while inserting millions of values into indexed tables remained constant instead of degrading as random UUIDs scattered writes across the B-tree. Buffer measurements showed a dramatic reduction in pages read, dirtied, and written. Rather than submit a bespoke format, they decided to align the implementation with the emerging UUIDv7 standard, which later reached core.

[timecodes · 8]
  1. Why random UUIDs hurt B-tree locality
  2. Security, performance, and ordering trade-offs
  3. Preparing the in-core identifier prototype
  4. Adding the catalog function and C implementation
  5. Encoding microsecond time in network byte order
  6. First generated values and generation benchmark
  7. Comparing indexed inserts with random UUIDs
  8. Buffer evidence and decision to target UUIDv7
#SQL@Nikolay Samokhvalov
-hackersUUIDv7 commit
committed

direct mapping
· 51:28 · youtube ↗

Postgres Hacking 101: prevent unqualified DML

Prototype GUCs rejecting unqualified UPDATE and DELETE.

The group prototyped opt-in GUCs to reject UPDATE and DELETE plans that would affect an entire table. They added checks after planning, which also reject constant-true predicates optimized away but can be bypassed by volatile expressions, exposing ambiguity between syntactic WHERE protection and actual full-table impact. They implemented separate prevent_unqualified_deletes and prevent_unqualified_updates settings, tested role-level defaults and permissions, noted users could disable their own safeguards without stronger controls, and prepared a rough patch for pgsql-hackers discussion.

[timecodes · 8]
  1. One-click CommitFest testing with PG Cloud Hacker
  2. Checking the transaction_timeout workspace
  3. Idea: reject destructive unqualified DML
  4. Finding command type and WHERE quals in Query
  5. First planner check for DELETE
  6. Constant predicates reveal semantic ambiguity
  7. Naming two opt-in safety GUCs
  8. Role settings, bypasses, and patch submission
#SQL@Nikolay Samokhvalov
-hackers
stalled

direct mapping
· 44:59 · youtube ↗

Implementing transaction_timeout

Add a timeout for any transaction lasting too long.

Nik demonstrated a browser-based workflow for reviewing the proposed transaction_timeout patch: mirror the mailing-list attachment, open a preconfigured Gitpod workspace, compile Postgres, and test the feature. The timeout correctly canceled a long transaction. Andrey then moved timer startup from transaction begin to assignment of a real XID, exploring a narrower write-transaction/Xmin-horizon timeout. They concluded this changed the patch's intended semantics and needed separate user discussion; isolation tests also exposed timing complexity and Windows instability.

[timecodes · 8]
  1. Why Postgres needs transaction_timeout
  2. Turning a mailing-list patch into a browser review
  3. Building patched Postgres in Gitpod
  4. Testing transaction_timeout from psql
  5. Virtual, real, and prepared transactions
  6. Starting the timer only after XID assignment
  7. Transaction duration versus Xmin-horizon protection
  8. Isolation-test failures and unresolved semantics
#SQL@Nikolay Samokhvalov
-hackersCF #4040commit
committed

direct mapping