michelp-adminpack

0.0.1Created 3 months agoBy michelp

michelp-adminpack

A Trusted Language Extension containing a variety of useful databse admin queries.

Postgres database admins are often faced with a variety of issues that require them introspecting the database state. This extension contains a mixed-bag of views that reflect useful information for admins.

Installation

Using dbdev:

postgres=> select dbdev.install('michelp-adminpack');
 install
---------
 t
(1 row)

postgres=> create schema adminpack;
CREATE SCHEMA
postgres=> create extension "michelp-adminpack" with schema adminpack;
CREATE EXTENSION

This will the extension into the adminpack schema, substitute with some other schema if you want it to go somewhere else.

Index Bloat

Index updates and querying can end up getting slower and slower over time due to what's called "index bloat". This is where frequent updates and deletes can cause space in index data structures to go unused. Understanding when this is happening is not obvious, so there is a rather complex query you can run to detect it.

index_bloat

ColumnType
current_databasename
schemanamename
tblnamename
idxnamename
real_sizenumeric
extra_sizenumeric
extra_pctdouble precision
fillfactorinteger
bloat_sizedouble precision
bloat_pctdouble precision
is_naboolean

Table Bloat

Like index bloat, tables with high update and delete rates can also end up containing lots of allocated but unused space. This query shows which tables have the most bloat.

table_bloat

ColumnType
current_databasename
schemanamename
tblnamename
real_sizenumeric
extra_sizedouble precision
extra_pctdouble precision
fillfactorinteger
bloat_sizedouble precision
bloat_pctdouble precision
is_naboolean

Blocking PID Tree

Postgres queries can block each other, and this blocking relationship can form a tree, where A blocks B, which blocks C, and so on. This query formats blocking queries into a tree structure so it's easy to see what query is causing the blockage.

blocking_pid_tree

ColumnType
PIDtext
Lock Infotext
Statetext

Duplicate Indexes

If a table contains duplicate indexes, then unecessary work is done updating and storing them, this query will show up to 4 duplicate indexes per table if they exist.

duplicate_indexes

ColumnType
sizetext
idx1regclass
idx2regclass
idx3regclass
idx4regclass

Table Sizes

This view shows tables and their sizes.

table_sizes

ColumnType
table_schemaname
table_namename
row_estimatereal
totaltext
indextext
toasttext
tabletext
total_size_sharedouble precision

Schema Sizes

This view shows schemas and their sizes, which is the sum of the sizes of all the tables and indexes in the schema.

schema_sizes

ColumnType
schemanamename
tabletext
indextext
table_indextext
sumnumeric

Index Usage

This view shows index size and usage. An unused index still needs to be updated and that takes time an storage, so it's a good candidate to drop.

index_usage

ColumnType
schemanamename
tablenamename
num_rowsbigint
table_sizetext
index_namename
index_sizetext
uniquetext
number_of_scansbigint
tuples_readbigint
tuples_fetchedbigint

Last Vacuum Analyze

This views shows the last time a table was vacuumed an analyzed.

last_vacuum_analyze

ColumnType
relnamename
last_vacuumtimestamp with time zone
n_mod_since_analyzetimestamp with time zone
last_analyzetimestamp with time zone
last_autoanalyzetimestamp with time zone
analyze_countbigint
autoanalyze_countbigint

Table Row Estimates

This view shows estimates for the number of rows in a table. This is just an estimate and depends on up to date table statistics.

table_row_estimates

ColumnType
schemanamename
relnamename
n_live_tupbigint

PGMeta Columns

This view shows infromation for the columns of tables in the system.

pgmeta_columns

ColumnType
table_idbigint
schemaname
tablename
idtext
ordinal_positionsmallint
namename
default_valuetext
data_typetext
formatname
is_identityboolean
identity_generationtext
is_generatedboolean
is_nullableboolean
is_updatableboolean
is_uniqueboolean
enumsjson
commenttext

PGMeta Config

This views shows the configuration of the database.

pgmeta_config

ColumnType
nametext
settingtext
categorytext
grouptext
subgrouptext
unittext
short_desctext
extra_desctext
contexttext
vartypetext
sourcetext
min_valtext
max_valtext
enumvalstext[]
boot_valtext
reset_valtext
sourcefiletext
sourcelineinteger
pending_restartboolean

PGMeta Extensions

This view shows installed extensions in the database.

pgmeta_extensions

ColumnType
namename
schemaname
default_versiontext
installed_versiontext
commenttext

PGMeta Foreign Tables

This view shows foreign tables in the database.

pgmeta_foreign_tables

ColumnType
idbigint
schemaname
namename
commenttext

PGMeta Functions

This view shows functions in the database.

pgmeta_functions

ColumnType
idbigint
schemaname
namename
languagename
definitiontext
complete_statementtext
argsjsonb
argument_typestext
identity_argument_typestext
return_type_idbigint
return_typetext
return_type_relation_idbigint
is_set_returning_functionboolean
behaviortext
security_definerboolean
config_paramsjsonb

PGMeta Materialized Views

This view shows materialized views in the database.

pgmeta_materialized_views

ColumnType
idbigint
schemaname
namename
is_populatedboolean
commenttext

PGMeta Policies

This view shows Row Level Security Policies in the database.

pgmeta_policies

ColumnType
idbigint
schemaname
tablename
table_idbigint
namename
actiontext
rolesjson
commandtext
definitiontext
checktext

PGMeta Primary Keys

This view shows primary keys in the database.

pgmeta_primary_keys

ColumnType
schemaname
table_namename
namename
table_idbigint

PGMeta Publications

This view shows logical replication publishers in the database.

pgmeta_publications

ColumnType
idbigint
namename
ownertext
publish_insertboolean
publish_updateboolean
publish_deleteboolean
publish_truncateboolean
tablesjson[]

PGMeta Relationships

This view shows foreign key relationships in the database.

pgmeta_relationships

ColumnType
idbigint
constraint_namename
source_schemaname
source_table_namename
source_column_namename
target_table_schemaname
target_table_namename
target_column_namename

PGMeta Roles

This view shows roles in the database system. Note that roles are global objects and apply to all databases.

pgmeta_roles

ColumnType
idbigint
namename
is_superuserboolean
can_create_dbboolean
can_create_roleboolean
inherit_roleboolean
can_loginboolean
is_replication_roleboolean
can_bypass_rlsboolean
active_connectionsbigint
connection_limitbigint
passwordtext
valid_untiltimestamp with time zone
configtext[]

PGMeta Schemas

This view shows all schemas in the database.

pgmeta_schemas

ColumnType
idbigint
namename
ownername

PGMeta Tables

This view shows all tables in the database.

pgmeta_tables

ColumnType
idbigint
schemaname
namename
rls_enabledboolean
rls_forcedboolean
replica_identitytext
bytesbigint
sizetext
live_rows_estimatebigint
dead_rows_estimatebigint
commenttext

PGMeta Triggers

This view shows all triggers in the database.

pgmeta_triggers

ColumnType
idoid
table_idoid
enabled_modetext
function_argstext[]
nameinformation_schema.sql_identifier
tableinformation_schema.sql_identifier
schemainformation_schema.sql_identifier
conditioninformation_schema.character_data
orientationinformation_schema.character_data
activationinformation_schema.character_data
eventstext[]
function_namename
function_schemaname

PGMeta Types

This view shows all types in the database.

pgmeta_types

ColumnType
idbigint
namename
schemaname
formattext
enumsjsonb
attributesjsonb
commenttext

PGMeta Version

This view shows the current database version.

pgmeta_version

ColumnType
versiontext
version_numberbigint
active_connectionsbigint
max_connectionsbigint

PGMeta Views

This view shows all views in the database.

pgmeta_views

ColumnType
idbigint
schemaname
namename
is_updatableboolean
commenttext

Install

  1. Install the dbdev package manager
  2. Install the package:
select dbdev.install('michelp-adminpack');
create extension "michelp-adminpack"
    version '0.0.1';

Downloads

  • 0 all time downloads
  • 0 downloads in the last 30 days
  • 0 downloads in the last 90 days
  • 0 downloads in the last 180 days