What is a good way to determine the performance I should expect from a PostgreSQL server?

Question

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server scaling better.

This question aims more towards ways that I can calculate or estimate (really, any idea of this would be good) expected performance. This should ideally help me come up with a formula I can run based on several factors (like record size, number of rows, etc), instead of a subjective assumption based on a particular server / DB.

So, what is a good way to determine expected performance on a PostgreSQL server?

Thanks very much!

Answer 1

I think determining the expected performance is a lot harder than planning for capacity. My guess is that is what you are really trying to do so I will go over what I consider to be the most important rules for OLTP workloads here.

1. You want the working data set to fit in memory if possible. Calculating performance where this is not the case depends on how often the disks are hit and what sort of disk I/O. If you can't fit all of it in memory, at least you should try to fit the most commonly used data, and all commonly used indexes in memory.

2. You want to have available at least one core per two concurrent queries. Better is one core per concurrent query.

Both of these are a little tricky to calculate and they depend on row size, number of pages in the table, number of dead tuples in the table, which in turn depends partly on the number of concurrent updates.

Beyond this, performance will slowly degrade, hopefully gracefully. Estimating performance when these are or aren't met is very difficult because it is very fact specific.