Troubleshooting PostgreSQL performance issue

Question

I have a data ware house running on PostgreSQL and I would like to check what all are the missing indices are in my database.

I tried to install the extension pg_qualstats but it is giving the below error.

root@Ubuntu-1604-xenial-64-minimal ~ # sudo pip install pg_qualstats
Collecting pg_qualstats
  Could not find a version that satisfies the requirement pg_qualstats (from versions: )
No matching distribution found for pg_qualstats
You are using pip version 18.1, however version 19.2.3 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
root@Ubuntu-1604-xenial-64-minimal ~ # 

So how can i install this extension? I tried the CREATE EXTENSION pg_qualstats but it was giving error.

Also is there any other database maintenance need to be done for the database regularly?

What all parameter I have to check?

Can we automate the maintenance activity?

I was a SQL server DB admin and it was much easier to find out the missing index, understand the execution plan, DB maintenance but I find it hard, when it comes to PostgreSQL.

So any guidance will be of great help.

Answer 1

I am only answering the immediate question regarding the installation of pg_qualstats - the rest of the questions are way too broad for a platform like stackoverflow (or dba.stackexchange).

---

Many interesting extensions are provided as source code in Postgres (that's one of the reasons why it's highly recommended to run Postgres on Linux, because compiling the extensions is way easier in Linux than it is on Windows, and may extensions are only developed for Linux).

pg_qualstats is no different.

It is provided together with PoWA and the installation of the extension is documented as part of their installation guide

In a nutshell:

Download the source:

wget https://github.com/powa-team/pg_qualstats/archive/1.0.7.tar.gz -O pg_qualstats-1.0.7.tar.gz
tar zxvf pg_qualstats-1.0.7.tar.gz
cd pg_qualstats-1.0.7

the compile it:

make
make install

Register the shared libraries by editing postgresql.conf and after restarting Postgres the extension can be created using CREATE EXTENSION pg_qualstats;