Postgres Dba

PostgreSQL Replication Settings Query Guide This PostgreSQL query retrieves all replication-related configuration settings from the pg_settings system view. These parameters control streaming replication, logical replication, standby server behaviour, and WAL sender configuration. Purpose and Overview Replication is …

PostgreSQL Resource Settings Query Guide This PostgreSQL query retrieves all resource usage configuration settings from the pg_settings system view. Resource settings control how PostgreSQL allocates memory, disk, and CPU resources across queries, background processes, and maintenance operations. Purpose and Overview …

PostgreSQL Statistics Settings Query Guide This PostgreSQL query retrieves all statistics collection configuration settings from the pg_settings system view. These parameters control what runtime data PostgreSQL collects about query execution, table activity, and performance, feeding into the pg_stat_* monitoring …

PostgreSQL SSL Settings Query Guide This PostgreSQL query retrieves all SSL and TLS configuration settings from the pg_settings system view. It surfaces whether SSL is enabled, which certificates and keys are in use, what cipher suites are allowed, and all other SSL-related parameters. Purpose and Overview Encrypting …

PostgreSQL WAL Settings Query Guide This PostgreSQL query retrieves all Write-Ahead Log (WAL) configuration settings from the pg_settings system view. WAL settings control durability, crash recovery, checkpointing, archiving, and replication log generation. Purpose and Overview The Write-Ahead Log is the foundation of …

List PostgreSQL Tables by Size with SQL This PostgreSQL query lists all tables in the current database sorted by their total size, largest first. It uses pg_total_relation_size() to include the table data, indexes, TOAST storage, and free space map in the size calculation. Purpose and Overview Disk space pressure is a …

PostgreSQL Table Row Count Estimates with SQL This PostgreSQL query returns estimated row counts for all user tables, sorted by the largest tables first. It reads from PostgreSQL's cumulative statistics rather than scanning the tables, making it near-instant even on tables with billions of rows. Purpose and Overview …

PostgreSQL File Location Settings Query Guide This PostgreSQL query helps database administrators examine file location configuration settings by querying the pg_settings system view. It retrieves essential information about where PostgreSQL stores its files and directories. Purpose and Overview The query targets …

Monitor PostgreSQL Active Sessions with pg_stat_activity pg_stat_activity is the system view that shows every backend process running against a PostgreSQL server right now — one row per connection, with the client address, the query it is running, how long it has been running, and what kind of backend it is. A single …

Identifying Blocking PostgreSQL Queries: A Guide for Database Administrators This article explores a PostgreSQL query designed to identify currently blocked database queries and the processes (identified by PIDs) responsible for blocking them. This information is crucial for database administrators troubleshooting …