Pg_database
List PostgreSQL Collations with pg_collation and ICU
List Collations Available in PostgreSQL pg_collation is the system catalog that holds every collation a database can use — the sort-and-compare rules behind ORDER BY, DISTINCT, comparison operators and text indexes. One query against it shows each collation's name, the library that implements it, and the locale it maps …
Read MoreList PostgreSQL Roles and Their Privileges
Apr 17, 2026 / · 8 min read · postgresql administration security pg_roles pg_auth_members pg_database ·How to List PostgreSQL Roles and Privileges Which accounts can log in, which can create databases or other roles, and which are quietly superusers? PostgreSQL answers all three from pg_roles, a view over the role catalog that every user can read. Joined to pg_auth_members it also shows who belongs to which group, and …
Read MoreDetect PostgreSQL Transaction ID Wraparound Risk
Apr 3, 2026 / · 5 min read · postgresql administration vacuum monitoring database pg_class pg_database pg_settings pg_stat_activity pg_stat_user_tables ·Detect PostgreSQL Transaction ID Wraparound Before It Causes Downtime PostgreSQL assigns a transaction ID (XID) to every write transaction. These IDs are 32-bit integers, so the counter wraps around after about 4 billion transactions. Because IDs are compared modulo 2^32, each ID has two billion IDs that count as older …
Read MoreAnalyze PostgreSQL Query Times with pg_stat_statements
Aug 29, 2024 / · 8 min read · postgresql performance performance tuning pg_stat_statements pg_database pg_roles ·Analyze PostgreSQL Query Times with pg_stat_statements PostgreSQL 13 renamed the timing columns of pg_stat_statements: total_time became total_exec_time, mean_time became mean_exec_time, and the same suffix went onto the minimum, maximum and standard deviation columns. A query copied from an older tutorial fails on a …
Read MoreAssess PostgreSQL Database Sizes Quickly and Easily
Apr 2, 2024 / · 2 min read · PostgreSQL Database Administration Performance Optimization Storage Management pg_database_size pg_size_pretty pg_database ·Quickly Assess Your PostgreSQL Database Landscape by Size Managing multiple PostgreSQL databases requires an understanding of their storage footprint. This article introduces a simple yet effective PostgreSQL code snippet that helps you quickly determine the size of each database in your system, ordered by their …
Read MoreList Your PostgreSQL Databases by Size (With Access Check) Keeping your PostgreSQL database server organized is crucial for optimal performance and manageability. A critical aspect of this organization is understanding the storage footprint of each database. This SQL code snippet empowers you to efficiently retrieve a …
Read MoreHow Many Connections Can Your PostgreSQL Database Handle?
Mar 18, 2024 / · 7 min read · show postgresql database performance optimization pg_stat_activity pg_settings pg_stat_database pg_database pg_roles ·How Many Connections Can Your PostgreSQL Database Handle? PostgreSQL 16 split the connection limit into more than one pool. Besides the slots held back for superusers, a second block of slots can now be reserved for ordinary roles that have been granted pg_use_reserved_connections. The number of sessions an application …
Read More