Postgres configuration suitable for a single 300 GB dataset (5 billion rows)

Question

Our purpose is to use up every bit of resources on 1 machine dedicated to just serve one dataset to 2 data scientists and make it available for slicing, grouping, etc.

Here is our current setup and configuration

• Data: 5 billion rows (~300GB) of AWS cloudwatch 5-minute data
• Hardware: AWS EC2 t2.2xlarge (8 cores, 32GB RAM, 500GB gp2 disk)
• Postgresql version 10
• Modified sections from the defaults of /etc/postgresql/10/main/postgresql.conf

work_mem = 25GB
maintenance_work_mem = 25GB

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

With the current configuration, select count(1) from resource_usage8 takes forever to execute.

enel_cloudwatch=# explain select count(1) from resource_usage8;
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=19774628.42..19774628.43 rows=1 width=8)
   ->  Gather  (cost=19774628.00..19774628.41 rows=4 width=8)
         Workers Planned: 4
         ->  Partial Aggregate  (cost=19773628.00..19773628.01 rows=1 width=8)
               ->  Parallel Seq Scan on resource_usage8  (cost=0.00..18904390.40 rows=347695040 width=0)
(5 rows)

Here's the table's schema

enel_cloudwatch=# \d resource_usage8
                        Table "public.resource_usage8"
     Column     |            Type             | Collation | Nullable | Default 
----------------+-----------------------------+-----------+----------+---------
 mtr_resourceid | text                        |           | not null | 
 mtr_project    | character varying(30)       |           |          | 
 mtr_timestamp  | timestamp without time zone |           |          | 
 mtr_metrictype | character varying(30)       |           |          | 
 mtr_max        | real                        |           |          | 
 mtr_sum        | real                        |           |          | 
 mtr_average    | real                        |           |          | 

and here is the index I'm creating (which takes several hours to complete)

enel_cloudwatch=# CREATE INDEX ON resource_usage8 USING btree (mtr_resourceid, mtr_metrictype, mtr_timestamp);

Are there any other suggestions that I'm overlooking? Should I just give up on postgresql and resort to another database?

PS: This question was prompted from this SO.

Answer 1

work_mem = 25GB

With only 32GB of RAM, this is much too high. With two people working independently, and some queries potentially having multiple sort/hash nodes apiece, you can easily oversubscribed memory here. I wouldn't set this this higher than 4 GB. And even that is probably aggressive if you expect much parallelism, as each parallel worker takes work_mem independently. So unless you are going to keep a window open running "top" or similar and constantly monitor it, probably 1 GB is a better upper limit.

max_worker_processes = 8 
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

I'd maybe double all of these. Since you have chosen a burstable instance, I'm assuming there are long periods of idleness. When it is active, you want to throw all the CPUs you have at "max_parallel_workers_per_gather". There is no point saving half the CPUs for the benefit of another backend who probably doesn't exist at the moment. On the other hand, what AWS calls a vCPU is only about half a CPU, so maybe the current settings are about correct.

I didn't know what to make of your collation of "C.UTF-8", so I ran a test. A CPU-bound index build under "C.UTF-8" took about the same amount of time as it did under "en_US", which was about two to three times as long as under "C". (twice as long in version 12dev, three times as long in version 10.) So I would recreate your database using "C", not "C.UTF-8", unless you have a librarian or a lawyer telling you that you can't do that.

The first where-less 'select count(*)...' after the data is loaded is going to have do much of the same work as VACUUM does, if the table hasn't already been vacuumed. This will slow it down a lot, and so will not give you an accurate idea of what the steady state will look like. So you should either make sure to VACUUM first, or discard the timing of the first 'select count(*)'. Also, "forever" is not very precise, something like "I killed it after 5 hours" would at least give us a lower bound.

Should I just give up on postgresql and resort to another database?

I don't know enough about them to recommend them by name, but a database designed specifically for analytics should certainly be better at that tailored task then a general purpose database like PostgreSQL. There are many tradeoffs to be made, and when it really must choose between them PostgreSQL usually chooses in favor of ACID-compliant transaction processing, rather than batch processing. But squeezing the last ounce of performance out of any product is going to come with a steep learning curve. Flitting from product to product without putting in the time to learn each thoroughly will probably not lead to better results.