dataegret/pg-utils: a DBA's shelf of PostgreSQL SQL scripts, including the ones that are half finished

A shelf of files rather than an extension

pg-utils is a set of useful DBA tools from Data Egret, and the shape of the repository says what it is. There is a `sql/` directory holding the scripts, and alongside it `bin/` for shell tooling, a `loganalyze/` directory for log analysis, a `pg_index_watch` directory, a `slonik_template/` directory for Slonik, and a `.gitmodules` entry, which means at least one component is a submodule rather than a file in this tree.

Nothing here is packaged. There is no extension, no control file, no install step and no release history, since the repository has no GitHub releases. What you get is SQL that you read, adjust and run against a database you already have, which is a different contract from a dependency and a different one from a runnable tool.

The scripts are named after what they check. There are `check_missing_grants.sql`, `check_strange_fk.sql`, `check_uniq_indexes.sql` and `check_are_all_subscribed.sql`, where the unique index check lists all tables that do not have UNIQUE constraints. There is `generate_drop_items.sql` for generating drop statements, and `83compat.sql`, a file whose name suggests PostgreSQL 8.3 compatibility handling. There are also `db_activity.sql` and `db_activity9.2.sql`, and the same version split reappears elsewhere in the collection, which is the clearest signal of its age and of how far back its compatibility reaches.

The primary language is listed as Shell, which fits a repository where the SQL is the payload and the surrounding tooling is the part that is scripted.

check_config.sql separates the file from the session

The configuration report is the most immediately useful script in the collection, and its idea is that a parameter can be changed in three different places, and you need to know which.

The query retrieves non default configuration parameters from PostgreSQL. The markers in the output are the whole design. A `(*)` before a parameter name means the parameter has the default value. A `(c)` after the name means the parameter was changed for the client session or by another reason such as a database or user level setting. A `!!!` after the name means the parameter was changed in the configuration file but is still not applied.

That last marker is the one that catches people. A setting sitting in the configuration file that has not taken effect is a class of problem that is otherwise invisible, because the file looks right and the session behaves as if it were not. The `reset_val` column holds the value from the configuration file, and the README notes with appropriate caution that for user or client sessions you can see modified parameters, but `reset_val` will contain the value from the configuration file.

The report also aligns all fields by width so that two reports can be compared side by side, which is the kind of detail that only appears in a script somebody uses to diff a server's configuration over time.

Two versions of the activity view

`create_db_activity_view.sql` creates a more convenient view around the `pg_stat_activity` catalog view, showing all non idle queries running for more than 100ms, with a note in the view definition that the threshold can be changed to 500ms.

It requires `track_activities=enabled` in `postgresql.conf` to display process states correctly. Without it, the state column is described as disabled, and that column carries the values you would actually filter on, such as active, idle, idle in transaction, idle in transaction (aborted) and fastpath function call.

The columns are a tidy set of ages rather than raw timestamps. `ts_age` is the time elapsed since the transaction started, `query_age` the time elapsed since the current query started, and `change_age` the time elapsed since the last change of the process state. Alongside them sit the identifying fields, `datname`, `pid`, `usename`, `waiting`, `client_addr`, `client_port` and the `query` text itself. The `waiting` column is honest about its own reliability, being described as true if the current process is waiting for another one, with the caveat that it may not be correct.

The important structural note is the version split. This view works with PostgreSQL versions below 9.2, and for 9.2 and higher the repository points you at `create_db_activity_view9.2.sql`, which does the same thing against the newer catalog layout. The `db_activity.sql` and `db_activity9.2.sql` pair follows the same pattern. That kind of split is a good indicator of when the scripts were written, and a reminder that the collection predates the current catalog and needs checking against your server version.

Three views over pg_stat_statements, each with a 0.02 second floor

The query statistics scripts come in three flavours, and the difference between them is the metric they rank on. `create_query_stat_time_view.sql` shows queries with total runtime of 0.02 seconds or more, including time spent on block IO. `create_query_stat_cpu_time_view.sql` shows queries running 0.02 seconds or more with IO time not taken into account, so the ranking is by CPU. `create_query_stat_io_time_view.sql` shows queries with IO time of 0.02 seconds or more.

All three require the `pg_stat_statements` extension, and the documentation also names `track_io_timings` in `postgresql.conf`.

The column list is the same across the three, and it is where the value is. You get `time_percent` for the query's share of total runtime, `iotime_percent` and `cputime_percent` for the split between block IO and CPU as percentages of runtime, `total_time` and `avg_time`, `avg_io_time`, `calls` with `calls_percent`, and `rows` with `row_percent` for the share of rows returned. The last column is the query text.

One design decision is worth noting. Queries whose runtime is below the 0.02 second threshold are not dropped, they are accounted into a dedicated `other` query. That keeps the percentages honest, since a short query storm still shows up as a share of total runtime rather than silently vanishing from the denominator.

The `create_query_stat_log.sql` file has no description in the documentation, and it is one of several files listed without explanation.

WAL arithmetic, buffer cache dirt and index placement

Three scripts cover areas that monitoring tools tend to summarise rather than expose.

`create_xlog_math_procedures.sql` creates three functions. `xlog_location_numeric` shows the current WAL position in a decimal expression, which is what makes the number readable rather than a hexadecimal offset. `replay_lag_mb` shows the estimated lag between master and standby server in megabytes. `all_replayed` returns true if all WAL is replayed, that is, zero lag. These are the building blocks for replication monitoring, and the documentation for this file ends with the line `Usage: ???`, so the intended call pattern is left unstated.

`dirty_to_read_stat.sql` reports statistics for dirty buffers and requires the `pg_buffercache` extension. It breaks down by relation, with the object name and schema, the `relkind` object type, the tablespace, the object size in megabytes, an estimate of the percentage of pages cached, reads that went to disk, dirty page size in megabytes, the percentage of dirty pages, and a `read_to_dirty_ratio` that expresses the average amount read from disk per dirty page. That last column is the interesting one, since a high ratio says the server is reading more than it is writing back. This file also ends with `Usage: ???`.

The index placement scripts, `index_candidates_from_ssd.sql` and `index_candidates_to_ssd.sql`, display indexes which should be moved from or to SSD. The columns include the table the index belongs to, the index name, the tablespace it is stored in, the size and the amount of disk block reads from the index, with a ratio column for the same comparison. This is the one script in the group that gives a recommendation rather than a measurement, and a recommendation is exactly the kind of thing that needs a second opinion, because moving an index between tablespaces on a busy server is not a free action.

Half finished notes, an unclassifiable licence and no releases

Two things distinguish this collection from a polished tool, and both are visible in the documentation rather than hidden in the code.

The first is the unfinished business. Two files end their documentation with `Usage: ???`, and several files are listed in the README with a heading and no explanation at all, such as `create_slonik_set_full.sql`, `create slonik_set_incremental.sql`, `generate_drop_items.sql`, `check_missing_grants.sql` and `check_strange_fk.sql`. The Slonik templates sit in a directory of their own, and the file name with a space in it, `create slonik_set_incremental.sql`, suggests that these came out of a working environment rather than a curated set.

The second is the licence. The repository carries a `LICENSE.md` and GitHub cannot classify it, which means it is a custom licence and the file is the only thing that tells you what you may do with the SQL. That matters more than usual here, because the scripts are things you run inside your own database, and several of them create views and functions in the database they are pointed at.

On maintenance, the repository is not archived and the last push was on 2026-09-15, so somebody is still touching it. There are no releases, no tags in the release list and no versioning scheme, so a copy of the SQL is a copy of whatever was on a given day, and the practical way to use this collection is to read each file, understand what it creates, and keep your own version in your own repository rather than depending on this one.

Editorial conclusion

Adopt pg-utils when you want a set of ready made SQL views for a PostgreSQL server you already administer, especially the activity and query statistics views, since the column lists are documented and the 0.02 second floor on the pg_stat_statements views is a sane default for hunting slow queries. Do not treat it as a maintained extension, because it is a directory of files with no release history, no versioning and several entries whose own usage notes read ???, and do not run the scripts against a production database before reading them, since the views create objects in the database. Verify first that the extensions your chosen scripts need are enabled, `pg_stat_statements` and `track_io_timings` for the query statistics views and `pg_buffercache` for the dirty buffer report, that `track_activities` is on for the activity view to show process states, and that the licence in the repository covers your use.

Frequently asked questions

Official sources

1. dataegret/pg-utils on GitHub
2. Issues
3. README