Crunchy Bridge: Announcing Postgres Insights in Your CLI
Today we're excited to release a big update to our Crunchy Bridge CLI: a new
interactive menu for psql! Now when connecting to your Crunchy Bridge database
with cb psql you'll have a :menu option. The cb menu is an easy to
navigate collection of insights about your database. All of these insights are
powered by data already contained in Postgres system catalogs. We have these
same database insights in the dashboard, so this feature extends that to those
working directly from the command line.
Before we added this new Bridge CLI, you had to find your own system catalog queries. You had to know which catalogs existed, where to find some community queries, and had to construct things basically from scratch. Now with a simple command you can get the insights you need and stay in the flow of developing or debugging:
Cache
1 – Cache and index hit rates
Size Information
2 – Database sizes
3 – Table sizes
Query Performance
4 – Queries consuming the most system time
5 – Queries running over 1 minute
6 – Slowest average queries
Connection Management
7 – Connection count by state
8 – Connection count by user and application
Indexes
9 – Duplicate indexes
10 – List of indexes
11 – Unused indexes
Locks
12 – Blocking queries
Extensions
13 – Available extensions
14 – Installed extensions
A quick sample result for you:
Type choice and press <Enter> (q to quit): 1
schemaname | Table Name | Cache Hit Ratio | Index Hit Ratio | Read Percentage | Row Count | Size
------------+-----------------------------------+-----------------+-----------------+-----------------+-----------+---------
public | event | 97 | 100 | 83.19 | 52939 | 199 MB
public | notification | 100 | 100 | 4.33 | 40863 | 135 MB
public | account_notification | 100 | 100 | 2.09 | 10192 | 8272 kB
public | account | 100 | 100 | 0.47 | 1621 | 3768 kB
Give it a try today by upgrading your cb install to get these new insights. If
you’re new to Crunchy Bridge, learn about
getting started with cb. Read
on to learn more about how we built our interactive menu for Postgres.
Named queries in Postgres
Psql is a great CLI editor. Most of us that spend a lot of time in our shell or
CLI have tweaked and tuned our setups. You may have a nice bashrc. In my case
I opted for the fish shell – which generally works and I have to tweak and tune
less. But in my .psqlrc I have spent some time tweaking and tuning it over the
years. A few defaults I always leverage:
\x auto- Autoformat the output of queries based screen\timing- Show timing of query\pset null 👻- Display value for null
The other thing I do is create a collection of named queries. Named queries in
your psql will then execute when you run a colon and the named query. In our
case this new named query is :menu but we could also define all of the queries
that are useful to us as individually named queries. For years I have had a
named query as :cache_hit.
Incredibly useful, but scary looking, SQL
Now many of these useful queries give you great insights, but when you look at
the SQL itself it's not quite so friendly. There a lot of complex SQL and system
catalog data. If you don't regularly work with the catalog tables, getting this
data out of Postgres can be a little bit intimidating. But your database
shouldn't intimidate you. We're curating those so you don't need to know about
so it is one less thing for you to worry about. Let's just take an example of
one, the :cache_hit query I had on my personal .psqlrc for years:
WITH data AS ( SELECT
d.oid,
(SELECT
spcname
FROM
pg_tablespace
WHERE
oid = dattablespace) AS tblspace,
d.datname AS database_name,
pg_catalog.pg_get_userbyid(d.datdba) AS owner,
has_database_privilege(d.datname,
'connect') AS has_access,
pg_database_size(d.datname) AS size,
blks_hit,
blks_read,
temp_files,
temp_bytes
FROM
pg_catalog.pg_database d
JOIN
pg_stat_database s
on s.datid = d.oid
WHERE
d.datname NOT IN ('template1', 'crunchy_monitoring', 'template0') ), data2 AS ( SELECT
null::oid AS oid,
null AS tblspace,
'*** TOTAL ***' AS database_name,
null AS owner,
true AS has_access,
sum(size) AS size,
sum(blks_hit) AS blks_hit,
sum(blks_read) AS blks_read,
sum(temp_files) AS temp_files,
sum(temp_bytes) AS temp_bytes
FROM
data
UNION ALL
SELECT
null::oid,
null,
null,
null,
true,
null,
null,
null,
null,
null
UNION ALL
SELECT
oid,
tblspace,
database_name,
owner,
has_access,
size,
blks_hit,
blks_read,
temp_files,
temp_bytes
FROM
data ) select
database_name || coalesce(' [' || nullif(tblspace,
'pg_default') || ']',
'') AS "Database",
CASE
WHEN has_access then pg_size_pretty(size) || ' (' || round( 100 * size::numeric / nullif(sum(size) over (partition
by
(oid is null)),
0),
2 )::text || '%)'
else 'no access'
END as "Size",
CASE
when blks_hit blks_read > 0 then (round(blks_hit * 100::numeric / (blks_hit blks_read),
2))::text || '%'
else null
END as "Cache eff.",
temp_files::text || coalesce(' (' || pg_size_pretty(temp_bytes) || ')',
'') as "Temp. Files"
FROM
data2
ORDER BY
oid is null DESC,
size DESC nulls last;
Putting it all together
Now when you connect to a database with cb psql we check if you've already got
a .psqlrc. We leave your file in place and prepend our configuration for :menu
along with some good default psql settings. This is a good balance of getting
some useful tools for folks unfamiliar with this, but let you add more settings
as needed.
You deserve a great database, you deserve it being user friendly, we're excited to continue to deliver that with this change to ours Crunchy Bridge CLI.
Shoutout to
Nikolay Samokhvalov for some of the
inspiration behind an interactive menu in Postgres.
