PostgreSQL Database Module

This module contains the PostgreSQL database handler for general database checks.

Functions:

get_db_size(cursor)	Get the size of the database in a human readable format
get_table_sizes(cursor[, only_public_schema])	Get the size of each table in the database and return it as a pandas DataFrame with sizes in KB.
get_wal_gb_size(cursor)	Get the size of the Write Ahead Log (WAL) in the database and return it in GB.
get_active_connections(cursor[, ...])	Get the number of active connections in the database.
get_autovacuum_level(cursor)	Get the Autovacuum status for tables in the database.
get_ungranted_locks(cursor)	Get the ungranted locks in the database.
get_cache_statistics(cursor)	Get cache statistics for tables in the database.
get_hypertable_info(cursor)	Get information about hypertables in the TimescaleDB.

get_db_size(cursor) → str

Get the size of the database in a human readable format

Parameters:

cursor – A psycopg2 cursor

Returns:

A string with the size of the database

get_table_sizes(cursor, only_public_schema=False) → DataFrame

Get the size of each table in the database and return it as a pandas DataFrame with sizes in KB.

Parameters:

• cursor – A psycopg2 cursor

• only_public_schema – Optional, set to True to only get sizes of tables in the ‘public’ schema.

Returns:

A pandas DataFrame with the size of each table in the database

get_wal_gb_size(cursor) → float

Get the size of the Write Ahead Log (WAL) in the database and return it in GB.

Parameters:

cursor – a psycopg2 cursor

Returns:

Size in GB of the WAL

get_active_connections(cursor, database_name='crypto', include_superuser=True) → int

Get the number of active connections in the database.

Parameters:

• cursor – a psycopg2 cursor

• database_name – Optional, name of the specific database to filter.

• include_superuser – Optional, whether to include superuser connections or not.

Returns:

Number of active connections

get_autovacuum_level(cursor) → DataFrame

Get the Autovacuum status for tables in the database.

This function returns a DataFrame with the following columns:

• schemaname:

  The schema where the table resides.

• relname:

  The name of the table.

• last_autovacuum:

  Timestamp of the last autovacuum operation performed on the table.

• last_autoanalyze:

  Timestamp of the last autoanalyze operation performed on the table.

• n_dead_tup:

  Number of dead tuples in the table. Dead tuples are rows that have been updated or deleted and are awaiting removal by autovacuum.

• n_live_tup:

  Number of live tuples in the table. Live tuples are rows that are currently valid and not marked for deletion.

• n_mod_since_analyze:

  Number of tuples modified since the last analyze operation. Analyze operations collect statistics about the data in the table to help the query planner optimize queries.

• seq_scan:

  Number of sequential scans performed on the table. A high number can indicate that indexes are not being effectively utilized.

• idx_scan:

  Number of index scans performed on the table. Indicates how often indexes are used for querying this table.

Parameters:

cursor – a psycopg2 cursor

Returns:

DataFrame with Autovacuum status

get_ungranted_locks(cursor) → DataFrame

Get the ungranted locks in the database.

Parameters:

cursor – a psycopg2 cursor

Returns:

DataFrame with ungranted lock details

get_cache_statistics(cursor) → DataFrame

Get cache statistics for tables in the database.

This function returns a DataFrame with the following columns:

• table_name:

  The name of the table.

• heap_blks_read:

  Number of disk blocks read for the main table (heap).

• heap_blks_hit:

  Number of buffer hits in the cache for the main table (heap).

• idx_blks_read:

  Number of disk blocks read for all indexes on the table.

• idx_blks_hit:

  Number of buffer hits in the cache for all indexes on the table.

• toast_blks_read:

  Number of disk blocks read for the TOAST table (used for storing large values out of main table rows).

• toast_blks_hit:

  Number of buffer hits in the cache for the TOAST table.

• tidx_blks_read:

  Number of disk blocks read for indexes on the TOAST table.

• tidx_blks_hit:

  Number of buffer hits in the cache for indexes on the TOAST table.

• heap_cache_hit_rate:

  Cache hit rate for the main table (heap).

• idx_cache_hit_rate:

  Cache hit rate for all indexes on the table.

Cache hit rates are useful metrics for determining the efficiency of the cache. A high hit rate indicates that the cache is effectively reducing the need for disk reads.

Parameters:

cursor – a psycopg2 cursor

Returns:

DataFrame with cache statistics

get_hypertable_info(cursor) → DataFrame

Get information about hypertables in the TimescaleDB.

This function returns a DataFrame with the following columns:

• hypertable_schema:

  The schema in which the hypertable resides.

• hypertable_name:

  The name of the hypertable.

• owner:

  The owner of the hypertable.

• num_dimensions:

  The number of dimensions of the hypertable.

• num_chunks:

  The total number of chunks associated with the hypertable.

• compression_enabled:

  Whether compression is enabled for the hypertable.

• tablespaces:

  Tablespaces associated with the hypertable.

• primary_dimension:

  The primary partitioning dimension (typically a time column).

• primary_dimension_type:

  Data type of the primary dimension.

Parameters:

cursor – a psycopg2 cursor

Returns:

DataFrame with hypertable details