The PostgreSQL team at Compose have been busy working on ways for you to get more control over your PostgreSQL deployments and that work is now available for you to use. The new Performance view lets you see how your tables and indexes are doing while the Extensions view lets you see what extensions are available to install and what's already installed.

Both these views are available from the database browser in the Compose console. Select your deployment and then Browser, then pick the database you want to work with. In the left-hand tabs, along with Tables, Roles and Admin are Extensions and Performance.

Note that all the performance data is per database, but the extension settings are per-deployment and changes the settings for all databases.

Extensions

The Extensions view is designed to make it easy, and command-line free, to activate and de-activate PostgreSQL extensions. It alphabetically lists the name, version and a description for each available extension. On each line these are preceded by an indicator which is clear for uninstalled and green for installed extensions and followed by a button which displays install or remove as appropriate:

The extensions are mostly sourced from the PostgreSQL Additional supplied modules along with explicit entries for the available PG language extensions (plpgsql, plperl, plv8, plls, plcoffee) and the PostGis geo-extensions (postgis). Some of the additional modules, like spi actually contain a number of extensions and it's those extensions that the Extensions view lists.

Performance

The Performance view is a composite of different views in one page designed to give insight into your database, connections, tables and indexes. They cover cache performance, space usage, maintenance, table bloat and index bloat. Let's step through these views and discuss the information they offer.

Overview

Cache Performance

This is one of the easiest to read panels. It gives two simple percentages for cache hits for the tables and for the indexes. You ideally want high percentages here on a busy database as this reflects how often the system needs to go to disk; the higher the percentage, the less disk access is occurring.

Disk Usage

This tab looks at statistics about how your database is consuming disk. There's a number of tables and it starts with information about your database housekeeping.

Maintenance Info

PostgreSQL tables are at their best when regularly maintained with the VACUUM command and AUTOVACUUM features. They can help with that as long as you know when they are being applied and analysis is being run. This time and date of when each of those happens is available in the pg_stat_all_tables view, but to save you time, we make it available in the Maintenance Info view too. If you scroll down, you'll find the next view...

Table Bloat

Your tables can waste space quietly through process called bloat. A Compose Article discusses the phenomenon and mentions ways of measuring bloat. We've integrated those queries into this view. For each table you can see the actual size of the data, how much larger it is, the ratio of those values, the fill factor, bloat size and a percentage of how much of that table is bloat.

Index Bloat

It's not just tables that can get bloated. Indexes are subject to the same kinds of pressures and also find themselves getting wasteful. This carries the same kind of information as the Table Bloat view.

You may wonder what you can do about bloat in tables and indexes: We'll be following this subject up in a future article.

Connections

Moving onto the next tab, Connections. This is concerned with where the activity on your database is coming from. First of all, there's the Connection sources.

Connection sources

Here we can see a view of all the connections coming into the database. Currently it shows the IP address on the private LAN which contains the PostgreSQL deployment. The process name contains more useful data, repeating however the connected process has identified itself. In the screenshot above there's psql connected in from my own remote desktop, coming in through the Haproxy for the deployment with one connection and a number of "unicorns" which are part of the high availability management. Finally, there's an option to kill all current connections so you can clear down a server's workload.

Active queries

Below the Connection sources is the current active queries. We can see a query's duration and, in the screenshot above, one query that is taking quite a bit of time. That's because we've slipped in a pg_sleep() call to make it stall. The active queries view is a snapshot of the current queries, enough to give you a feel for the query load on your database. If you find a query that you don't like, the Kill button on the right hand side will let you remove it (and its connection).

Slow Queries

The final tab gives visibility to PostgreSQL's slow query logging via the pg_stat_statements extension. If you haven't enabled it, you'll be offered the chance install and enable it:

Once enabled, the slow query log is collected automatically and you can return to this tab to see what has been taking more that 20ms to run:

Here we've had to overrun the server with pgbench to get some slow queries to appear. You get to see the query, the total time it has been taking, the average time each call to it has taken and the number of times it has been called. The Clear slow queries button doesn't make your queries faster, but it does clear the log of slow queries so you can locate new slow queries more easily.

Community Counts

It's only right to thank the people who's work we're using to get this information to you. The Performance views take advantage of code in PGHero inspired by Craig Kerstiens work and of queries by @ioguix and all the PostgreSQL community. In the future, as we take on our customers problems, we look forward to giving back more to the community.