Postgres comes, out of the box, configured as a general purpose database for both OLTP and OLAP types of loads. My need is to do use Postgres, in this case, purely as an OLAP running aggregate queries over few relatively large tables.
I am looking for instructions how to adjust default configuration parameters relating to buffers and cacheing, including OS settings (Linux) for page sizes, so that the database can handle large sorts, joins, projections and filters more efficiently than out of the box, given the resources on the system.
I have seen several tuning and tweaking best practices but these are still general purposes (addressing WAL, transaction synchronisation, etc.), where I am mostly interested in maximizing read, sort and join performance by adjusting the parameters that related to these.
I have a 4-core server with 64 GB of memory and about 500 GB of SAS-2 drives.
My dataset consists of about 8-10 tables of which some are 100 MB, and some are about 20 GB.
This dataset will be used for read-only analysis with direct querying of the individual tables, or some complex queries joining two or three tables.
Tables should be partitionable by month or year, at least.
Given the hardware I have, and the fact that we are accessing few relatively flat but large tables, could someone please recommend some basic but specific Postgres 9.4 or 9.5 optimizations that would maximize the performance of the read-only queries and the database.
