Re: postgres.conf settings

Thanks Martijn and to the others who replied. Here's some more detail...

Quoting Martijn van Oosterhout <kleptog@svana.org>:

> Not a bad start, but to be sure you really need to provide a bit more
> info, like:
>
> - How many simultaneous clients you're expecting?
> - Lots of updates, or is it read only?
> - Lots of simple queries, or fewer but more complex queries?
>
> Basically, what's the workload?
>

About the workload, I'll try to be a bit more specific:

- Number of clients: I am the only person using my database. Usually I
run one or two queries at the same time but I can have more sessions
open at the same time (but typically <10). Hence I set max_connections
= 20.

- INSERT, UPDATE, DELETE queries are rare.

- However, I use a lot of CREATE [TEMP] TABLE, CREATE INDEX

- Also frequent is the use of COPY TO/FROM to import/export datafiles
that will be handled by other applications (R, Python etc.)

- SELECT queries: usually simple in design but handling large datasets
which require JOIN and GROUP BY (e.g. "SELECT a, b FROM t1 INNER JOIN
t2 ON t1.c = t2.c ORDER BY a;" where t1 and t2 could have millions of
rows).

- Background information: I'm a geneticist/bioinformatician using
postgresql to manage the data I or others produce. I'm not a proper
database programmer who run a web server.

Many thanks for all and any feedback!

Dario

--

Dr. Dario Beraldi
Institute of Evolutionary Biology
University of Edinburgh
West Mains Road
Edinburgh EH9 3JT
Scotland, UK


--
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.