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.

  • Parallelism is better in v11 than in v10. Particularly, parallel index build didn't exist in v10. – jjanes May 5 '19 at 11:31
  • About collation, SHOW lc_collate yields C.UTF-8. About v11, I will try partitioning before creating the index first. If the select count query still hangs, I'll spin up v11 and try it out. About cpu credits, the CPU utilization is not going beyond 20%, and the CPU credit usage bursts up to 5 during index creation (with CPU credit balance being stable at 400) – shadi May 5 '19 at 11:41
  • Update: added output of explain select... to question – shadi May 5 '19 at 11:47
  • If you have freshly loaded the data and not yet vacuumed the table, the first "select count" is going to be slow as it has to deal with the commit status of every row. So either vacuum, or just expect a horrible warm-up "select count". (If you load the data from scratch, you can try doing it with COPY FREEZE) – jjanes May 5 '19 at 12:08
  • Funny that when I search for COPY FREEZE, you pop up here. Are you an active contributor to postgres? – shadi May 5 '19 at 12:23
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.

  • Thanks for these pointers. Perhaps you can also add your comments about collation, first select count slowness. – shadi May 5 '19 at 12:26

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.