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 vm.swappiness=0