Re: Feature Request --- was: PostgreSQL Performance Tuning

Greg Smith wrote:
> If you're going to the trouble of building a tool for offering
> configuration advice, it can be widly more effective if you look inside
> the database after it's got data in it, and preferably after it's been
> running under load for a while, and make your recommendations based on
> all that information.

There are two completely different problems that are getting mixed together in this discussion. Several people have tried to distinguish them, but let's be explicit:

1. Generating a resonable starting configuration for neophyte users who have installed Postgres for the first time.

2. Generating an optimal configuration for a complex, running system that's loaded with data.

The first problem is easy: Any improvement would be welcome and would give most users a better initial experience. The second problem is nearly impossible. Forget the second problem (or put it on the "let's find someone doing a PhD project" list), and focus on the first.

From my limited experience, a simple questionaire could be used to create a pretty good starting configuration file. Furthermore, many of the answers can be discovered automatically:

1. How much memory do you have?
2. How many disks do you have?
a. Which disk contains the OS?
b. Which disk(s) have swap space?
c. Which disks are "off limits" (not to be used by Postgres)
3. What is the general nature of your database?
a. Mostly static (few updates, lots of access)
b. Mostly archival (lots of writes, few reads)
c. Very dynamic (data are added, updated, and deleted a lot)
4. Do you have a lot of small, fast transactions or a few big, long transactions?
5. How big do you expect your database to be?
6. How many simultaneous users do you expect?
7. What are the users you want configured initially?
8. Do you want local access only, or network access?

With these few questions (and perhaps a couple more), a decent set of startup files could be created that would give good, 'tho not optimal, performance for most people just getting started.

I agree with an opinion posted a couple days ago: The startup configuration is one of the weakest features of Postgres. It's not rocket science, but there are several files, and it's not obvious to the newcomer that the files even exist.

Here's just one example: A coworker installed Postgres and couldn't get it to work at all. He struggled for hours. When he contacted me, I tried his installation and it worked fine. He tried it, and he couldn't connect. I asked him, "Are you using localhost?" He said yes, but what he meant was he was using the local *network*, 192.168.0.5, whereas I was using "localhost". He didn't have network access enabled. So, four hours wasted.

This is the sort of thing that makes experienced users say, "Well, duh!" But there are many number of these little traps and obscure configuration parameters that make the initial Postgres experience a poor one. It wouldn't take much to make a big difference to new users.

Craig