Postgresql 11 – slow insert performance when table reaches ~5 million records

performancepostgresqlpostgresql-11postgresql-performance

I have a table with ~6 million records that gets loaded on a postgresql instance in a small machine (with standard PC HDD).

It seems to be a I/O problem, since there are free memory and processing power.

The table has an UUID (random, v4) primary key and gets the records from a central server – it's a local copy.

Loading the table starts very fast (~ 1000 records each 500ms) and as the table fills with records it gets very slow (~1000 records each 30s).

First thought was "indexes", so I dropped all indexes on the table (kept the primary key) – very little improvement.

AFAIK, postgresql does not have a clustered index like SQL Server which might cause page splits on random UUID inserts.

What else might be a problem?

UPDATE:
The same process and settings are not a problem on a machine with SSD instead of HDD (cannot change this on 3000+ machines).

Settings:

shared_buffers = 256MB
work_mem = 10MB
maintenance_work_mem = 256MB
dynamic_shared_memory_type = posix
effective_io_concurrency = 2
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2
wal_level = minimal
max_wal_size = 1GB
min_wal_size = 100MB
checkpoint_completion_target = 0.5
max_wal_senders = 0
random_page_cost = 4.0
effective_cache_size = 1GB
autovacuum = on
autovacuum_max_workers = 1

EDIT:

Did some updates to improve performance:

added noatime to /etc/fstab (ext4 partition);
disabled disk write caching;
disabled autovacuum and created a systemd service to do it on shutdown;
set effective_io_concurrency = 1 (the machine was very slow due to high I/O);
changed to UUIDv1 – so it would be sequential and avoid rotation on the primary key index b-tree or any other b-tree;
partitioned the tables with hash technique (tried with 10 partitions and 50 partitions);
did proper fillfactor configuration: fillfactor=75 / 85 depending on how much unordered data it gets;

No noticeable changes..

It fills the tables at ~5 million rows on 500ms to 1sec each 1000 rows and than starts getting very slow, at 1000 rows at 30s minimum.

Tried to REINDEX all to see if would be a filled index issue – no results.

EDIT2:

The tables:

create table customer (
    field1  serial      not null,
    field2  uuid        not null default (uuid_generate_v1()),
    field3  varchar(11) null,
    field4  varchar(2)  not null,
    field5  varchar(1)  not null,
    field6  varchar(40) not null,
    field7  varchar(40) null,
    field8  varchar(40),
    field9  bigint,
    field10 varchar(15),
    field11 varchar(20),
    field12 integer,
    field13 date,
    field14 varchar(1),
    field15 integer,
    field16 varchar(100),
    field17 varchar(100),
    field18 boolean,
    field19 integer,
    field20 varchar(20),
    field21 timestamp,
    field22 timestamp,
    field23 varchar(2),
    field24 varchar(40) not null,
    field25 varchar(40),
    field26 boolean,
    field27 boolean     null
) partition by hash (
    field1
);

-- Fillfactor = 85 because this table is a copy from the original so it might
-- eventually get unordered data
alter table customer
    add constraint customer_pk
        primary key (field1) with (fillfactor = 85);

-- CREATE 10 PARTITIONS...

create index idx_customer_01 on customer (field2) with (fillfactor =75);
create index idx_customer_02 on customer (field9, field14) with (fillfactor =75);
create index idx_customer_03 on customer (field25, field14) with (fillfactor =75);

create table customer_contacts (
    field1 serial,
    field2 uuid default (uuid_generate_v1()),
    field3 uuid    not null,
    field4 integer not null,
    field5 smallint,
    field6 bigint,
    field7 boolean not null
) partition by hash (
    field1
);

alter table customer_contacts
    add constraint customer_contacts_pk
        primary key (field1) with (fillfactor =85);

-- CREATE 10 PARTITIONS...

create index idx_customer_contacts_01 on customer_contacts (field2) with (fillfactor =85);
create index idx_customer_contacts_02 on  customer_contacts (field3, field4) with (fillfactor =85);

EDIT3:

There are no foreign keys and no triggers on the tables.

Best Answer

partition by hash was causing the problem. It causes "random" inserts that do not take advantage of caching/buffers.

Changing it to sequential/range partitions solved the problem.

pgAdmin timing

When you execute a query from the query tool, the message pane shows something like:

Total query runtime: 62 ms.

And the status line shows the same time. I quote pgAdmin help about that:

The status line will show how long the last query took to complete. If a dataset was returned, not only the elapsed time for server execution is displayed, but also the time to retrieve the data from the server to the Data Output page.

If you want to see the time on the server you need to use SQL EXPLAIN ANALYZE or the built in Shift + F7keyboard shortcut or Query -> Explain analyze. Then, at the bottom of the explain output you get something like this:

Total runtime: 0.269 ms

Sql-server – Logical reads amount difference with almost identical indexes

It all depends on the definitions and the key (and non-key) columns defined in the nonclustered index. The clustered index is the actual table data. Therefore it contains all of the data in the data pages, whereas the nonclustered index is only containing columns' data as defined in the index creation DDL.

Let's set up a test scenario:

use testdb;
go

if exists (select 1 from sys.tables where name = 'TestTable')
begin
    drop table TestTable;
end
create table dbo.TestTable
(
    id int identity(1, 1) not null
        constraint PK_TestTable_Id primary key clustered,
    some_int int not null,
    some_string char(128) not null,
    some_bigint bigint not null
);
go

create unique index IX_TestTable_SomeInt
on dbo.TestTable(some_int);
go

declare @i int;
set @i = 0;

while @i < 1000
begin
    insert into dbo.TestTable(some_int, some_string, some_bigint)
    values(@i, 'hello', @i * 1000);

    set @i = @i + 1;
end

So we've got a table loaded with 1000 rows, and a clustered index (PK_TestTable_Id) and a nonclustered index (IX_TestTable_SomeInt). As you've seen in your testing, but just for thoroughness:

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable -- with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- nonclustered index scan (IX_TestTable_SomeInt)
-- logical reads: 4

Here we have a nonclustered index scan on the IX_TestTable_SomeInt index. We have 4 logical reads for this operation. Now let's force the clustered index to be used.

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- clustered index scan (PK_TestTable_Id)
-- logical reads: 22

Here with the clustered index scan we have 22 logical reads. Why? Here's why. It all matters on how many pages that SQL Server has to read in order to grab the entire result set. Get the average row count per page:

select
    object_name(i.object_id) as object_name,
    i.name as index_name,
    i.type_desc,
    ips.page_count,
    ips.record_count,
    ips.record_count / ips.page_count as avg_rows_per_page
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TestTable'), null, null, 'detailed') ips
inner join sys.indexes i
on ips.object_id = i.object_id
and ips.index_id = i.index_id
where ips.index_level = 0;

Take a look at my result set of the above query:

enter image description here

As we can see here, there are an average of 50 rows per page on the leaf pages for the clustered index, and an average of 500 rows per page on the leaf pages for the nonclustered index. Therefore, in order to satisfy the query more pages need to be read from the clustered index.

Best Answer

Related Solutions

Postgresql – Postgres multiple joins slow query, how to store default child record

pgAdmin timing

Sql-server – Logical reads amount difference with almost identical indexes

Related Question