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.
Related Articles
- Postgres Partitioning with a Default Partition
16 min read
- Iceberg ahead! Analyzing Shipping Data in Postgres
8 min read
- PostGIS Day 2024 Summary
8 min read
- Crunchy Data Warehouse: Postgres with Iceberg for High Performance Analytics
8 min read
- Loading the World! OpenStreetMap Import In Under 4 Hours
6 min read