Learning PostgreSQL 11 - Third Edition

Performance improvement

To illustrate the benefits of partitioning, let's see an example. First, create a non-partitioned table to compare the results, with the same structure as the partitioned one:

car_portal=> CREATE TABLE dwh.access_log_not_partitioned   (LIKE dwh.access_log_partitioned);CREATE TABLE

Insert the data into both the partitioned and non-partitioned tables, copying the contents of the dwh.access_log table 1,000 times, as follows:

car_portal=> INSERT INTO dwh.access_log_not_partitioned SELECT ts, url, status_code FROM dwh.access_log, generate_series(1, 1000);INSERT 0 15456000car_portal=> INSERT INTO dwh.access_log_partitioned SELECT ts, url, status_code FROM dwh.access_log, generate_series(1, 1000);INSERT 0 15456000

Now let's ...