Best way to run performance tests in a production PostgreSQL 13 database?

Question

First, I'm asking this question because I'm studying to be a DBA. Recently I learned a lot about database locking, and then some questions arouse in my mind.

Suppose I'm a DBA, I'm in charge of a huge database from a online sales website. Some users reported that the site is slow, and my boss asks me to test the most used queries to see if they are running smooth or not. If I get locking right, if I just run an EXPLAIN ANALYZE on our tb_products I would probably lock it and it can have a negative impact on sales being processed at that time (i.e. some users could not buy their products because I'm "testing" queries).

My question is: how can I properly tests queries in a production database? In my (probably naive) way of thinking, what I need to do is to dump the database and its configurations, create a "sample" database with that and do the tests there. But them, if I'm testing locally on my machine, even with the same configs, numbers will differ, as we're testing on another hardware that's not the server hardware, right? I can't depict how can this tests be done when I think of it. It is probably a silly question, but any help to clarify that would be appreciated.

• André Carvalho: PostgreSQL 13, but my question is about general concepts, not about a specifc database system.

Answer 1

My expectation is that you should mimic the real life usage of the production online sales website on HTTP protocol level using a suitable load testing tool which supports HTTP (and maybe other protocols which might be used)

Given you run a stress test like starting with 1 user and gradually increase the load the store website will be generating more and more concurrent queries so you would be able to inspect the slow query log or deadlocks

With regards to where to run the test, it's not a good idea to run the test against a scaled-down environment so if you cannot use the production server for tests (i.e. during night or weekend) you should restore the database backup against the server having the same hardware resources and the database engine configuration as the production one so it would be an exact replica.