tmo Labs

#004 Features that I enjoy in PostgreSQL 16

August 9, 2024

Allow Logical Replication from Standby Servers

This feature allows you to set up a new PostgreSQL cluster without disrupting traffic on the primary servers, enabling smoother migrations and scaling.

Monitor I/O Statistics with the New pg_stat_io View

Many managed cloud databases have I/O limitations. The pg_stat_io view provides granular details on I/O usage, helping identify clients and queries that consume the most I/O resources, along with relevant wait events.

Improve Performance of VACUUM Freezing

In environments where I/O is costly, vacuum freezing of large tables can quickly deplete I/O credits and negatively impact query latencies. This improvement helps mitigate such issues.

Add Wait Event SpinDelay to Report Spinlock Sleep Delays

This new wait event allows you to monitor delays caused by spinlocks, offering insights into potential performance bottlenecks.

Create New Wait Event DSMAllocate to Indicate Waiting for Dynamic Shared Memory Allocation This wait event helps track delays in allocating dynamic shared memory, aiding in performance tuning.

Introduce Additional Wait Events

  • LogicalParallelApplyMain
  • LogicalParallelApplyStateChange
  • LogicalRepLauncherDSA
  • LogicalApplySendData

These wait events provide deeper insights into the logical replication process, making it easier to monitor and debug performance issues.Wait events have changed the way we monitor and debug PostgreSQL performance since their introduction in version 9.6. They help identify performance bottlenecks and guide system tuning.