I am using a Postgres instance in an HPC cluster, where they have generously given me an entire node. This means I have 28 cores and 252GB RAM. I have to assume that the very conservative default settings for things like buffers and max working memory are too small here.
We have about 20 billion rows in a single large table. The database is not intended to run an application but rather to allow a few individuals to do data analysis, so we can guarantee the number of concurrent queries will be small, and that nothing else will need to use the server. Creating multiple different indices on a few subsets of the columns will be needed to support the kinds of queries we want.
What settings should be changed to maximize performance?
