search

powa-team / powa

PostgreSQL Workload Analyzer
http://powa.readthedocs.io/
PostgreSQL License
768 stars 57 forks source link

How to use PoWA at a backup database? #145

Closed milat closed 3 years ago

milat commented 3 years ago

My Postgres database is running on AWS RDS.

PoWA needs HypoPG in order to suggest indexes.

But RDS doesn't support HypoPG extension. So I had to install PoWA at my backup database (outside RDS).

The problem is: PoWA isn't analyzing the restored database. It can't recognizes any data. If I execute some SQL queries manually it works though.

Is there something that I can be missing?

And, my log files are dumped at the backup database in parallel. Is there a way to show PoWA their path?

Thanks.

rjuju commented 3 years ago

If the database is being restored using pg_dump/pg_restore, that can't work. Powa is using data from pg_stat_statements, which normalizes queries based on object identifiers. If you restore data this way, all tables will have a different identifier and the data won't match anymore. Using a physical backup (like pg_basebackup), a physical replication, this would work.

And, my log files are dumped at the backup database in parallel. Is there a way to show PoWA their path?

I'm sorry I don't understand that question, as PoWA doesn't use log files at all.

rjuju commented 3 years ago

By the way, if you want to suggest indexes you also need pg_qualstats extension. Is that extension available on RDS?

milat commented 3 years ago

I thought that PoWA used the log files. Please disregard the question about it.

We are using pg_dump/pg_restore. It seems that AWS RDS doesn't support pg_basebackup, only logical replications. I'm surprised how limited RDS is.

In this scenario, is there something else that I could try to make it work?

rjuju commented 3 years ago

As I mentioned PoWA can't suggest missing indexes unless you have pg_qualstats extension installed, as this is this extension which gives you metrics about WHERE/JOIN clauses. hypopg is optional, and only used to validate that the index would be useful. So even if you could do a physical backup or replicate the data, you'd still miss pg_qualstats information.

Unfortunately, RDS is a proprietary and limited platform, so you can't use anything RDS doesn't allow you too, which is 99% of the postgres ecosystem. The only alternative I can think of would be to dump/restore the database on another server as you already do, install PoWA there (including pg_qualstats) and then run some representative workload on this database.

milat commented 3 years ago

I've got pg_qualstats and PoWA installed on my backup server, this wouldn't be a problem if I could do a physical backup. But the alternative you gave me will work just fine for my purposes.

Thank you for your time and help.