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.