PostgreSQL DDL Pitfalls and Clever Solutions

(For memory basics, refer to Linux Memory Analysis; this article covers memory knowledge above that foundation) Memory Basic Concepts # buddy # The process of buddy system allocating and merging pages is omitted. Easily overlooked knowledge points: The prerequisite for buddy merging two blocks of the same size is that their physical addresses are contiguous The merge algorithm is iterative: after merging at the current level, it will automatically attempt to merge larger blocks. This means compactd is not strictly required for merging page table & PTE # page table and PTE are actually two different concepts, and they are easily confused because both generally refer to page tables. Below is relevant knowledge about page table and PTE[^ 《深入理解Linux内核》 (Understanding the Linux Kernel)]

Among all relational databases, PostgreSQL’s CLOG is a very special type of log. CLOG’s existence is inseparable from PostgreSQL’s MVCC mechanism. Some basic knowledge about transaction IDs and CLOG won’t be covered in this article. If interested, please refer to CLOG and Hint Bits. This article focuses on the structure of CLOG files, manually locating transaction states, and the CLOG WAL log synchronization mechanism, to further understand PostgreSQL’s CLOG. CLOG Segment # CLOG Directory # To distinguish from regular logs, PostgreSQL 10 renamed the CLOG and WAL directories 1:

What is Logical Replication # PostgreSQL logical replication is based on logical decoding, which parses WAL log streams into a specified format for output. The subscriber node receives the parsed data and applies it. Logical replication differs from streaming replication (physical replication) which is based on instance-level primary-standby where the physical structures are identical. Logical replication can selectively replicate at the table level. Logical Replication in official documentation specifically refers to the “publish-subscribe” model. In fact, many tools can use logical decoding for heterogeneous database data synchronization.