Useful Postgres Extensions to explore right away!

Ever thought, about what makes Postgres more than just a relational database system?

Postgres Extensions!

Simply put, extensions are modules, functions or low-level APIs that extend postgres capabilities beyond its core functionalities. And there are many of them, each with unique use cases, working wonders for its users.

Today, I am going to shed light on two postgres extensions that you can enable for your database to assess its performance, gather insights and take necessary steps to enhance productivity.

Pg_stat_statements

The most popular extension, pg_stat_statements works under the hood to record the queries run against your database and extracts vital information about the time it took to run the query, the total number of query hits, and data about underlying read/writes.

A thing to note here is that pg_stat_statements doesn’t store individual queries but parameterizes them. For example,

SELECT * FROM mytable where email = 'abc@xmail.com'
SELECT * FROM mytable where email = 'xyz@zmail.com'

The above queries will be parameterized on column email and aggregated results will be stored for the following query,

SELECT * FROM mytable where email = ?

To gather insights for queries run against your database, you can query pg_stat_statements in the following way,

SELECT
(total_time / 1000 / 60) as total,
(total_time/calls) as avg,
query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 100;

This will output the total time in minutes that have been consumed for a particular type of parameter query and the average time in milliseconds to run a query. The costliest queries are the ones that you would be interested in. With this information, you can understand where indexes are required in your tables.

HypoPG

Talking of indexes brings me to this extension called hypothetical indexes. We all know how indexes can optimize query plans if used carefully. Since index creation requires resources, it would have been nice to have a way to know beforehand whether postgres will use those indexes or not.

HypoPg, a postgres extension lets you do just that. It can be used to create a hypothetical index without costing you resources and enables you to analyse the index performance.

To enable hypothetical index extension, run

CREATE EXTENSION hypopg;

To create one, you can use the hypopg_create_index function.

SELECT * FROM hypopg_create_index('CREATE INDEX ON mytable (id)');

To check if this index is being used you can use EXPLAIN, like this

EXPLAIN SELECT * FROM mytable WHERE id = 1;

It will show if the Query plan uses the Sequence Scan or Index Scan, based on which you can decide on the usefulness of the specific index creation.

There are other useful functions related to this extension listed below,

1. hypopg_reset() — to clear the previous effects of other indexes
2. hypopg_drop_index(indexrelid) — to remove your backend’s hypothetical indexes
3. hypopg_hide_index(oid) — to hide an index
4. hypopg_unhide_index(oid) — to restore the index

Cloud providers like AWS also support these extensions.

So go on exploring these useful extensions if you haven’t already.

Share if you found this useful.