pgdd

Overview

manual updated pgrx by Vonng

Version

Build

You can build the RPM / DEB packages for pgdd using pig build:

pig build pkg pgdd         # build RPM / DEB packages

Install

You can install pgdd 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 pgdd;          # Install for current active PG version

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

dnf install -y pgdd_18       # PG 18
dnf install -y pgdd_17       # PG 17
dnf install -y pgdd_16       # PG 16
dnf install -y pgdd_15       # PG 15
dnf install -y pgdd_14       # PG 14

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

Create Extension:

CREATE EXTENSION pgdd;

Usage

pgdd: Introspect pg data dictionary via standard SQL

PgDD provides data dictionary views in the dd schema for introspecting database objects via standard SQL.

Database Overview

SELECT * FROM dd.database;

Returns: db_name, db_size, schema_count, table_count, size_in_tables, view_count, size_in_views, extension_count.

Schemas

SELECT s_name, table_count, view_count, function_count, size_plus_indexes, description
  FROM dd.schemas;

Tables

SELECT t_name, size_pretty, rows, bytes_per_row
  FROM dd.tables
  WHERE s_name = 'public';

Views

SELECT s_name, v_name, description FROM dd.views;

Columns

SELECT source_type, s_name, t_name, c_name, data_type
  FROM dd.columns
  WHERE data_type LIKE 'int%';

Functions

SELECT s_name, f_name, argument_data_types, result_data_types FROM dd.functions;

Partitioned Tables

SELECT * FROM dd.partition_parents WHERE s_name = 'public';
SELECT * FROM dd.partition_children WHERE s_name = 'public';

The partition_parents view shows aggregate partition stats (count, total size, total rows). The partition_children view shows per-partition details with percentage calculations against the parent.

System objects are excluded by default. To include them, query the underlying functions directly: SELECT * FROM dd.tables() WHERE system_object;