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)