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!

  • I expect this question is going to be harder to answer, really you're more worried about the actual performance of the server, assuming that the database is properly indexed. – jcolebrand Mar 19 '13 at 2:49
  • Yes, in a way. I'm more interested in the overall performance / throughput of the server as a whole, when executing its function of serving data using PostgreSQL. For example, the checkpoints for WAL might be too frequent - or something like that, which can affect the performance of the DB server in its function as a data repository. – Juan Carlos Coto Mar 20 '13 at 16:45

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.