Xrhrft

using Postgres 9.6, pgAdmin 4 (for running the queries) on Windows 10 64-bit with Intel Core i7-6700 CPU @ 3.40GHz, 16GB RAM, 1 TB C drive HDD , 2 TB D drive HDD. Postgres data is stored in D drive

I'm using Postgres to process some dataset that I receive in csv format- the steps involved are as follows

1. 
   Create table schema - 4 tables are created, one for each set of csv files with the same schema as per the fields in the input csv files


2. 
   Import CSVs to Postgres DB - Import around 4 sets of 500 CSV files each of around 4 million rows into own tables - say table01, table02, table03, table04 - I use a batch file to get the list of csv files in each set and use psql -c "copy .. command to import them. This process takes around 30s per csv file to import - so altogether 1000 minutes or ~17hrs . Each table has ~2bill rows and around 350MB per table.


3. 
   Create a subset of valid records and select relevant columns - For each table in #2 above, create a new table (say table01_valid, table02_valid, table03_valid, table04_valid) which has a subset of columns and exclude some rows based on a criteria - after this operation the number of rows comes down to ~1.5bill per table with each table ~200MB in size - this operation takes ~20hrs to execute for the 4 tables


4. 
   Select distinct records and merge - in this step the distinct rows from each table is selected, merge them via union all and a final distinct rows from the merged records is selected - this is saved into a new table say table_valid_distinct_1234 - the table size is ~450mill rows and ~35GB size - this operation takes ~12hrs
   


5. 
   Run transformations on some columns - in this step for each row, two columns are concatenated and selected as id field , two other columns are hashed, then concatenated and selected as name field. A final select is performed on this set to remove any ids with more than one unique name - the table size is ~50GB with ~450mill rows- this operation takes ~5hrs
   

Given the above steps, I wanted to collect feedback on improving processing speed given the number of records, data size and the hardware I'm using. Ideas on improving performance and disk usage in any of the steps from 1 to 5 would be helpful. I could provide additional info needed. Also if there is some sort of standard process to investigate this, that would also be very helpful. If there are hardware related enhancements required - should I be looking at adding more memory, more RAM or something else ?

Also one of the anomalies I see in pgAdmin which I use to run the queries is that it reports query run time as a few minutes for each of the above operations where as in reality it runs for a number of hours.

update

As suggested by @filiprem , adding the schema and query operations below

-- db tables

my_db_201710=# d

                         List of relations

 Schema |                  Name                  | Type  |  Owner

--------+----------------------------------------+-------+----------

 public | data_final                             | table | postgres   -- ~450 mill rows, ~50 GB

 public | data_distinct                          | table | postgres   -- ~450 mill rows, ~35 GB

 public | data_valid01                           | table | postgres   -- ~1.8 bill rows, ~200 GB

 public | data_raw01                             | table | postgres   -- ~2   bill rows, ~350 GB

 public | data_valid02                           | table | postgres   -- ~1.8 bill rows, ~200 GB

 public | data_raw02                             | table | postgres   -- ~2   bill rows, ~350 GB

 public | data_valid03                           | table | postgres   -- ~1.8 bill rows, ~200 GB

 public | data_raw03                             | table | postgres   -- ~2   bill rows, ~350 GB

 public | data_valid04                           | table | postgres   -- ~1.8 bill rows, ~200 GB

 public | data_raw04                             | table | postgres   -- ~2   bill rows, ~350 GB

(10 rows)



-- import csv data into postgres 

--------------

@echo off

setlocal

set PGPASSWORD=admin



for %%f in (*.csv)  do psql -d my_db_201710 -h localhost -U postgres -p 5432 -c "COPY public.data_raw01 FROM '%%~dpnxf' DELIMITER ',' CSV;"



pause

endlocal

--------------



my_db_201710=# d+ data_raw01

                                          Table "public.data_raw01"

                 Column                  |       Type        | Modifiers | Storage  | Stats target | Description

-----------------------------------------+-------------------+-----------+----------+--------------+-------------

 type                                    | integer           |           | plain    |              |

 time                                    | bigint            |           | plain    |              |

 msg_type                                | integer           |           | plain    |              |

 si_id                                   | character varying |           | extended |              |

 mi_id                                   | character varying |           | extended |              |

 ti_id                                   | character varying |           | extended |              |

 session_id                              | character varying |           | extended |              |

 mip_address                             | character varying |           | extended |              |

 eip_address                             | character varying |           | extended |              |

 mm_id                                   | integer           |           | plain    |              |

 en_id                                   | integer           |           | plain    |              |



-- select valid records, columns with some column transformations

create table data_valid01 as (

    select report_data_time as epoch_time, mi_id, si_id,

        substring(lpad(substring(mi_id, 1,14 ), 14, '0'), 1,8 ) as mi_hdr,

        substring(lpad(substring(mi_id, 1,14 ), 14, '0'), 9,6 ) as mi_sn,

        substring(si_id, 1,5 ) as si_hdr,

        substring(si_id, 6,10 ) as si_sn,

        lpad(to_hex(mm_id),8,'0') as mmid, 

        lpad(to_hex(en_id), 6, '0') as enid

    from data_raw01

    where mi_id is not null and mm_id is not null and en_id is not null

    ) ;



my_db_201710=# d+ data_valid01

                           Table "public.data_valid01"

   Column    |       Type        | Modifiers | Storage  | Stats target | Description

-------------+-------------------+-----------+----------+--------------+-------------

 epoch_time  | bigint            |           | plain    |              |

 si_id       | character varying |           | extended |              |

 mi_id       | character varying |           | extended |              |

 si_hdr      | text              |           | extended |              |

 si_sn       | text              |           | extended |              |

 mi_hdr      | text              |           | extended |              |

 mi_sn       | text              |           | extended |              |

 mmid        | text              |           | extended |              |

 enid        | text              |           | extended |              |



-- merge distinct from the 4 valid tables and select distinct rows from that

create table data_distinct as (

    select 

        distinct mmid, enid, si_hdr, si_sn, mm_hdr, mm_sn

        from (

            select *

                from data_valid01

                where epoch_time >= 1510202400000 and epoch_time <= 1510224000000

            union all

            select *

                from data_valid02

                where epoch_time >= 1510202400000 and epoch_time <= 1510224000000

            union all

            select *

                from data_valid03

                where epoch_time >= 1510202400000 and epoch_time <= 1510224000000

            union all

            select *

                from data_valid04

                where epoch_time >= 1510202400000 and epoch_time <= 1510224000000

            ) as a

);





my_db_201710=# d+ data_distinct

         Table "public.data_distinct"

   Column    | Type | Modifiers | Storage  | Stats target | Description

-------------+------+-----------+----------+--------------+-------------

 mmid        | text |           | extended |              |

 enid        | text |           | extended |              |

 si_hdr      | text |           | extended |              |

 si_sn       | text |           | extended |              |

 mi_hdr      | text |           | extended |              |

 mi_sn       | text |           | extended |              |



-- concatenate some cols, hash * concatenate some other colums 

create extension pgcrypto;

create table data_final as (

    SELECT id, simi

    FROM

    (

        SELECT id, simi, COUNT(*) OVER (PARTITION BY id) cnt

        FROM

        (

            select

                mmid || ',' || enid as Id,

                si_hdr || encode(substring(digest(si_sn || 'Maria Sharapovan' , 'sha256') from 17), 'base64') || ',' ||

                    mi_hdr || encode(substring(digest(mi_sn || 'Maria Sharapovan' , 'sha256') from 17), 'base64') as simi

                from data_distinct

        ) t1

    ) t2

    where cnt = 1

); 



my_db_201710=# d+ data_final

            Table "public.data_final"

  Column  | Type | Modifiers | Storage  | Stats target | Description

----------+------+-----------+----------+--------------+-------------

 id       | text |           | extended |              |

 simi     | text |           | extended |              |





my_db_201710=#

Interesting question but also very open one. I'm putting a list of recommendations here - hope it helps.

• Publish the full schema and data samples if you want more people work on this question.




• publish the postgresql.conf




• Follow the official PostgreSQL Performance hints.




• Use parallel processing when possible. On modern 4-core desktop CPU you can start with 2-3 threads. Observe system CPU usage (kernel time vs user time) while running the import. COPY is CPU-intensive.




• If you don't need durability and crash safety, follow Non-Durable Settings




• Consider combining Step 2 and step 3 (filter on the fly) if you don't need the invaliid data in postgres.




• Consider plain UPDATE instead of copying the same data into new tables. It might be faster or slower depending on the structure/constraints.




• Before loading, remove all but absolutely required indexes. Recreate them after loading.




• Re hardware investments, purchasing SSD drive will have highest impact/cost ratio.