Usage

Your dsn value will be specific to the Postgres database you want to connect to. See the Postgres documentation for more information on configuring connection strings:

>>> from pgextras import PgExtras
>>> with PgExtras(dsn='dbname=testing') as pg:
...     results = pg.bloat()
...     for row in results:
...         print(row)
...
Record(type='index', schemaname='public', object_name='addresses_to_geocode::addresses_to_geocode_address_trgm_idx', bloat=Decimal('1.6'), waste='233 MB')
Record(type='table', schemaname='public', object_name='addresses_to_geocode', bloat=Decimal('1.2'), waste='84 MB')
Record(type='table', schemaname='pg_catalog', object_name='pg_attribute', bloat=Decimal('2.5'), waste='1056 kB')

Or from the CLI:

$ pgextras -dsn "dbname=testing" -methods bloat version

Class Methods

.bloat()

    def bloat(self):
        """
        Table and index bloat in your database ordered by most wasteful.

        Record(
            type='index',
            schemaname='public',
            object_name='pgbench_accounts::pgbench_accounts_pkey',
            bloat=Decimal('0.2'),
            waste='0 bytes'
        )

        :returns: list of Records
        """

        return self.execute(sql.BLOAT)

.blocking()

    def blocking(self):
        """
        Display queries holding locks other queries are waiting to be
        released.

        Record(
            pid=40821,
            source='',
            running_for=datetime.timedelta(0, 0, 2857),
            waiting=False,
            query='SELECT pg_sleep(10);'
        )

        :returns: list of Records
        """

        return self.execute(
            sql.BLOCKING.format(
                query_column=self.query_column,
                pid_column=self.pid_column
            )
        )

.cache_hit()

    def cache_hit(self):
        """
        Calculates your cache hit rate (effective databases are at 99% and up).

        Record(
            name='index hit rate',
            ratio=Decimal('0.99994503346970922117')
        )

        :returns: list of Records
        """

        return self.execute(sql.CACHE_HIT)

.calls(truncate=False)

    def calls(self, truncate=False):
        """
        Show 10 most frequently called queries. Requires the pg_stat_statements
        Postgres module to be installed.

        Record(
            query='BEGIN;',
            exec_time=datetime.timedelta(0, 0, 288174),
            prop_exec_time='0.0%',
            ncalls='845590',
            sync_io_time=datetime.timedelta(0)
        )

        :param truncate: trim the Record.query output if greater than 40 chars
        :returns: list of Records
        """

        if self.pg_stat_statement():
            if truncate:
                select = """
                    SELECT CASE
                        WHEN length(query) < 40
                        THEN query
                        ELSE substr(query, 0, 38) || '..'
                    END AS qry,
                """
            else:
                select = 'SELECT query,'

            return self.execute(sql.CALLS.format(select=select))
        else:
            return [self.get_missing_pg_stat_statement_error()]

.index_usage()

    def index_usage(self):
        """
        Calculates your index hit rate (effective databases are at 99% and up).

        Record(
            relname='pgbench_history',
            percent_of_times_index_used=None,
            rows_in_table=249976
        )

        :returns: list of Records
        """

        return self.execute(sql.INDEX_USAGE)

.locks()

    def locks(self):
        """
        Display queries with active locks.

        Record(
            procpid=31776,
            relname=None,
            transactionid=None,
            granted=True,
            query_snippet='select * from hello;',
            age=datetime.timedelta(0, 0, 288174),
        )

        :returns: list of Records
        """

        return self.execute(
            sql.LOCKS.format(
                pid_column=self.pid_column,
                query_column=self.query_column
            )
        )

.long_running_queries()

    def long_running_queries(self):
        """
        Show all queries longer than five minutes by descending duration.

        Record(
            pid=19578,
            duration=datetime.timedelta(0, 19944, 993099),
            query='SELECT * FROM students'
        )

        :returns: list of Records
        """

        if self.is_pg_at_least_nine_two():
            idle = "AND state <> 'idle'"
        else:
            idle = "AND current_query <> '<IDLE>'"

        return self.execute(
            sql.LONG_RUNNING_QUERIES.format(
                pid_column=self.pid_column,
                query_column=self.query_column,
                idle=idle
            )
        )

.outliers()

    def outliers(self, truncate=False):
        """
        Show 10 queries that have longest execution time in aggregate. Requires
        the pg_stat_statments Postgres module to be installed.

        Record(
            qry='UPDATE pgbench_tellers SET tbalance = tbalance + ?;',
            exec_time=datetime.timedelta(0, 19944, 993099),
            prop_exec_time='67.1%',
            ncalls='845589',
            sync_io_time=datetime.timedelta(0)
        )

        :param truncate: trim the Record.qry output if greater than 40 chars
        :returns: list of Records
        """

        if self.pg_stat_statement():
            if truncate:
                query = """
                    CASE WHEN length(query) < 40
                        THEN query
                        ELSE substr(query, 0, 38) || '..'
                    END
                """
            else:
                query = 'query'

            return self.execute(sql.OUTLIERS.format(query=query))
        else:
            return [self.get_missing_pg_stat_statement_error()]

.ps()

    def ps(self):
        """
        View active queries with execution time.

        Record(
            pid=28023,
            source='pgbench',
            running_for=datetime.timedelta(0, 0, 288174),
            waiting=0,
            query='UPDATE pgbench_accounts SET abalance = abalance + 423;'
        )

        :returns: list of Records
        """

        if self.is_pg_at_least_nine_two():
            idle = "AND state <> 'idle'"
        else:
            idle = "AND current_query <> '<IDLE>'"

        return self.execute(
            sql.PS.format(
                pid_column=self.pid_column,
                query_column=self.query_column,
                idle=idle
            )
        )

.seq_scans()

    def seq_scans(self):
        """
        Show the count of sequential scans by table descending by order.

        Record(
            name='pgbench_branches',
            count=237
        )

        :returns: list of Records
        """

        return self.execute(sql.SEQ_SCANS)

.total_table_size()

    def total_table_size(self):
        """
        Show the size of the tables (including indexes), descending by size.

        Record(
            name='pgbench_accounts',
            size='15 MB'
        )

        :returns: list of Records
        """

        return self.execute(sql.TOTAL_TABLE_SIZE)

.unused_indexes()

    def unused_indexes(self):
        """
        Show unused and almost unused indexes, ordered by their size relative
        to the number of index scans. Exclude indexes of very small tables
        (less than 5 pages), where the planner will almost invariably select
        a sequential scan, but may not in the future as the table grows.

        Record(
            table='public.grade_levels',
            index='index_placement_attempts_on_grade_level_id',
            index_size='97 MB',
            index_scans=0
        )

        :returns: list of Records
        """

        return self.execute(sql.UNUSED_INDEXES)

.vacuum_stats()

    def vacuum_stats(self):
        """
        Show dead rows and whether an automatic vacuum is expected to be
        triggered.

        Record(
            schema='public',
            table='pgbench_tellers',
            last_vacuum='2014-04-29 14:45',
            last_autovacuum='2014-04-29 14:45',
            rowcount='10',
            dead_rowcount='0',
            autovacuum_threshold='52',
            expect_autovacuum=None
        )

        :returns: list of Records
        """

        return self.execute(sql.VACUUM_STATS)

.version()

    def version(self):
        """
        Get the Postgres server version.

        Record(
            version='PostgreSQL 9.3.3 on x86_64-apple-darwin13.0.0'
        )

        :returns: list of Records
        """

        return self.execute(sql.VERSION)