2

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.

5
  • 1
    Disc layout? Why use something as cost and performance inefficient as SAS discs? – TomTom Apr 11 '16 at 6:30
  • Because I don't have a choice. What would you suggest as a cost-performance efficient alternative? – Edmon Apr 11 '16 at 12:29
  • SSD. Any decent mid range (SATA) SSD will provide 100 times the IOPS budget of a SAS disc. If you do not have enough RAM, you need IOPS- – TomTom Apr 11 '16 at 12:45
  • What OS are you using for this server setup? – Kassandry Apr 12 '16 at 21:27
  • See above. Linux. RHEL/CentOS 7.2, but might as well be Ubuntu 14.04 LTS. – Edmon Apr 12 '16 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.