We do have a dedicated server where only PostgreSQL 9.4.4 server is running, 2 physical cores, 16 logical cores, 128GB RAM.
On this server we have quite atypical data - there are several tables with approx. 500 columns each, ranging from .5 - 2 milion rows. All columns are either of
integer data type. The typical operations we do:
- perform some aggregation function on one column, grouped by another column (this may well be also some window function)
- read subset of variables (e.g. 20) and all rows
- transform entire table into new table (typically there are no joins to other tables, these are mostly simple
case when then endstatements
There are two concurrent users at maximum. There is a primary key (single column), but this is almost never used in queries. There are no additional table constraints or indexes.
What would be the recommend configuration of PostgreSQL server? Increasing
shared_buffers to 60GB,
work_mem to 10GB? Anything else?
Note: Using such strange tables is not bad design in this case. We really have that many attributes for one case. We use this data as an input to in-database machine learning (MADlib) and as data pre-processing storage before using other statistical tools.