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.
SHOW lc_collate
yieldsC.UTF-8
. About v11, I will try partitioning before creating the index first. If theselect 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 to5
during index creation (with CPU credit balance being stable at 400) – shadi May 5 '19 at 11:41explain select...
to question – shadi May 5 '19 at 11:47COPY FREEZE
, you pop up here. Are you an active contributor to postgres? – shadi May 5 '19 at 12:23