pgsql_tweaks

Overview

Version

Build

You can build the DEB packages for pgsql_tweaks using pig build:

pig build pkg pgsql_tweaks         # build DEB packages

Install

You can install pgsql_tweaks directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pgsql_tweaks;          # Install for current active PG version

pig ext install -y pgsql_tweaks -v 18  # PG 18
pig ext install -y pgsql_tweaks -v 17  # PG 17
pig ext install -y pgsql_tweaks -v 16  # PG 16
pig ext install -y pgsql_tweaks -v 15  # PG 15
pig ext install -y pgsql_tweaks -v 14  # PG 14

dnf install -y pgsql_tweaks_18       # PG 18
dnf install -y pgsql_tweaks_17       # PG 17
dnf install -y pgsql_tweaks_16       # PG 16
dnf install -y pgsql_tweaks_15       # PG 15
dnf install -y pgsql_tweaks_14       # PG 14

apt install -y postgresql-18-pgsql-tweaks   # PG 18
apt install -y postgresql-17-pgsql-tweaks   # PG 17
apt install -y postgresql-16-pgsql-tweaks   # PG 16
apt install -y postgresql-15-pgsql-tweaks   # PG 15
apt install -y postgresql-14-pgsql-tweaks   # PG 14

Create Extension:

CREATE EXTENSION pgsql_tweaks;

Usage

pgsql_tweaks: PostgreSQL views and functions for DBA daily jobs

All objects are created in the pgsql_tweaks schema. Full documentation: rtfm.pgsql-tweaks.org

Data Type Check Functions

SELECT pgsql_tweaks.is_date('2024-01-15');       -- true
SELECT pgsql_tweaks.is_integer('42');             -- true
SELECT pgsql_tweaks.is_numeric('3.14');           -- true
SELECT pgsql_tweaks.is_json('{"a":1}');           -- true
SELECT pgsql_tweaks.is_jsonb('{"a":1}');          -- true
SELECT pgsql_tweaks.is_boolean('true');            -- true
SELECT pgsql_tweaks.is_timestamp('2024-01-15 10:30:00');  -- true
SELECT pgsql_tweaks.is_hex('FF');                 -- true

System Information Functions

SELECT pgsql_tweaks.pg_schema_size('public');     -- schema size in bytes

Aggregate Functions

• gap_fill – fill gaps in time series
• array_min, array_max, array_avg, array_sum – array aggregates

Conversion Functions

SELECT pgsql_tweaks.to_unix_timestamp(now());
SELECT pgsql_tweaks.hex2bigint('FF');

Utility Functions

SELECT pgsql_tweaks.is_empty('');                 -- true
SELECT pgsql_tweaks.array_trim(ARRAY['a','','b']);

System Information Views

• pg_db_views, pg_foreign_keys, pg_functions, pg_active_locks
• pg_table_matview_infos, pg_object_ownership, pg_unused_indexes
• pg_bloat_info, pg_missing_indexes, pg_role_permissions

Monitoring Views

• monitoring_wal, monitoring_active_locks, monitoring_replication
• monitoring_database_conflicts, monitoring_vacuum
• statistics_top_ten_query_times, statistics_query_activity