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.