CODEWITHSUNDEEP
sqlpostgresqlsql-insert
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5246

How to speed up the INSERT request in the PostgreSQL?

In PostgreSQL database I have such table:

CREATE TABLE IF NOT EXISTS layers (ID NUMERIC, GEOM GEOMETRY, CITY VARCHAR, AGGREGATION_METHOD VARCHAR, INFO JSON);

I am tring to insert to that table ~700 records with such one query:

INSERT INTO layers (ID, GEOM, CITY, AGGREGATION_METHOD, INFO) VALUES
(107509018555, '0203000020E61000000100000005000000000000808531534000000020749D4540000000808531534000000080159E4540000000201731534000000080159E4540000000201731534000000020749D4540000000808531534000000020749D4540', 'New York', 'MONTHLY', '[{"time":"2019-10-01T00:00:00Z","gender_details":[{"gender":"Male"},{"gender":"Female"}]}]'),
(107509018556, '0303000020E610000001000000050000000000008085315340000000A0D29A4640000000808531534000000020749D4540000000201731534000000020749D45400000002017315340000000A0D29C45400000008085315340000000A0D29C4540', 'London', 'MONTHLY', '[{"time":"2019-10-01T00:00:00Z","gender_details":[{"gender":"Male"},{"gender":"Female"}]}]')
... other 698 values

That query runs for 9-10 seconds. Is there a way to speed up the insert request in the PostgreSQL database?

PostgreSQL version: 11.0 (running on Red Hat)

Query Plan: enter image description here

Upvotes: 0

Views: 434

Answers (1)

pensnarik
pensnarik

Reputation: 1252

  1. If there any indeces or foreign keys on your table they will definetely slow down the execution. You might consider insert data into table without indeces and create ones later. Check if there any triggers as well.
  2. You can also try to load data using COPY command. It's faster than INSERT.
  3. If you have slow disks this might be an issue, you can try to use an UNLOGGED table in order to generate less WAL segments during the load operation. But please, be careful, this method has downsides as well.

Off topic: consider using JSONB type instead of JSON.

Upvotes: 3

Related Questions