Administration

How to Monitor PostgreSQL HOT Updates and Fillfactor When PostgreSQL updates a row, it normally writes a new row version and creates a new index entry pointing to it. This is safe, but it adds overhead — especially on tables with many indexes. HOT updates (Heap Only Tuple) are a smarter path. When a HOT update happens, …

How to Find Idle in Transaction Sessions in PostgreSQL An idle-in-transaction session is a database connection that has started a transaction but is not currently running a query. It is just sitting there, holding the transaction open. This is different from a regular idle connection, which has no open transaction. …

pg_settings — Query PostgreSQL Configuration Parameters The live configuration PostgreSQL is running with and the values written in postgresql.conf are not always the same. A parameter that requires a server restart has already been updated in the file but the old value is still in effect. A reload that produced a …

Your PostgreSQL Configuration PostgreSQL 12 finished a set of SQL functions that list the contents of the server's own directories: pg_ls_waldir() and pg_ls_logdir() arrived in version 10, and pg_ls_archive_statusdir() and pg_ls_tmpdir() followed in 12. Combined with current_setting() for the paths themselves, a single …

Unveiling Your PostgreSQL Server: A Treasure Trove of Information This comprehensive PostgreSQL script delves into the inner workings of your database server, revealing valuable details about its version, configuration, connections, users, and more. It empowers database administrators and developers with insights to …