I am running Postgres 10 in the following configuration:

  • Centos 7
  • VM on VirtualBox hypervisor
  • 32 GB RAM
  • 4 CPU
  • 800GB disk space on a compressed zfs volume.

I am trying to load and then query 5.5 billion records on a virtual machine. I was able to load all the data that has 20 columns (varchar(200)) without an index. select * from table queries took 45 minutes or more.

So I attempted to create an index on 1 column in the where clause. The index never completed before the server became unresponsive. I then added in an index and started loading 1 days files at a time. After loading two days with an index a query takes at least 6 minutes (around 25 million rows). I assume this is going to grow as the table grows.

Is the VM properties sufficient or can this be tuned or loaded differently. I considered partitioning tables but all the disks are 7200 RPM and shared across all VMs. I am NOT able to add any more resources as I am at the limit already.

// these are some of the changes I have played with in many variations 
shared_buffers = 4GB
huge_pages = try
maintenance_work_mem = 16MB
full_page_writes = off
  • If you want to tune the Postgresql refer here pgtune.leopard.in.ua , and right now what your major concern Disk space is growing or Import takes time or select operation is slow? – Bhuvanesh Jul 25 '18 at 1:11
  • I think I am ok with disk space and import time. Since 1 time import with no updates. The select operation is too slow. – npa Jul 25 '18 at 9:08
  • Select * from is not a good practice, obviously if your database grows then this operation will take more time. But yes this configuration is sufficient. Please refer the above like and tune your postgresql. Also there are many blog available to tune your select. – Bhuvanesh Jul 25 '18 at 9:20
  • Can you please provide more information, like datatypes ( especially the column that should be indexed)? – Thomas Berger Aug 1 '18 at 15:28

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.