1

EDIT:

An important thing that I missed is the fact that I parallelize COPY operations by N number of cores, by splitting the data file into N equal parts, and issuing N requests through separate connections. Turns out, the more I parallelize, the better is index-before-bulk-load's performance, esp. relative to index-after-bulk-load.

Here is some data:

# Cores 1

Index Before:
Total: 44s

Index After:
Load: 11s
Indexing: 15s
Total: 26s

# Cores 2

Index Before:
Total: 24.2s

Index After:
Load: 6s
Indexing: 13.5s
Total: 19.5s

# Cores 4

COPY INDEX BEFORE:
Total: 15.5s

COPY INDEX AFTER:
Load: 3.5s
Indexing: 14s
Total: 17.5s

# Cores 8

COPY INDEX BEFORE:
Total: 8.2s

COPY INDEX AFTER:
Load: 2.2s
Indexing: 14.3s
Total: 16.5s

I'm running benchmark tests for various configurations and methods of bulk-loading data into a Postgres table. Against all conventional wisdom and intuition, COPYing data into a table already containing indexes is much faster than COPYing data into a "plane" table, and creating indexes afterwards.

My code approximately looks like:

-- Configuration (this shouldn't matter much – I get +- same results even with default settings and WAL on) 
ALTER SYSTEM SET archive_mode = off;
ALTER SYSTEM SET max_wal_senders = 0;
ALTER SYSTEM SET wal_level = minimal;
ALTER SYSTEM SET fsync = off;
ALTER SYSTEM SET synchronous_commit = off;
ALTER SYSTEM SET full_page_writes = off;
ALTER SYSTEM SET maintenance_work_mem = '8GB';
ALTER SYSTEM SET max_parallel_maintenance_workers = 8;

-- *Server restart for settings to take place*

CREATE UNLOGGED TABLE IF NOT EXISTS test_table (
  id UUID PRIMARY KEY,
  int_col INTEGER,
  float_col REAL,
  text_col TEXT,
  date_col DATE,
  bool_col BOOLEAN,
  json_col JSONB,
  array_col INTEGER[],
  uuid_col UUID,
  timestamp_col TIMESTAMP WITH TIME ZONE
);

ALTER TABLE test_table SET (parallel_workers = 8);

-- Indexes & constraints

CREATE INDEX idx_int_col ON test_table (int_col);
CREATE INDEX idx_text_col ON test_table (text_col);
CREATE INDEX idx_date_col ON test_table (date_col);
CREATE INDEX idx_json_col ON test_table USING GIN (json_col);
ALTER TABLE test_table ADD CONSTRAINT unique_text_col UNIQUE (text_col);
ALTER TABLE test_table ADD CONSTRAINT check_int_col CHECK (int_col > 0);

-- Bulk load, 10,000,000 rows (same result for 1000 and 100,000,000 rows)

COPY test_table (id,int_col,float_col,text_col,date_col,bool_col,json_col,array_col,uuid_col,timestamp_col)
FROM '/home/autoboss/data_load_performance/data.csv' WITH (FORMAT csv, HEADER true)

Bulk load first, index creation second: 16457ms

Index creation first, bulk load second: 3850ms

How is this even possible?

Postgres version: PostgreSQL 15.7 (Debian 15.7-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

6
  • 1
    Odd ... Your version of Postgres would be an essential detail. Also why only "approximate" code? Commented Jun 27 at 0:38
  • Well my actual code is a 300 line messy Node.js script. I just included all the most important details. Good point about PG version, updated Commented Jun 27 at 7:37
  • 1
    Try running just the SQL through the psql client - without any of the node background and see what happens then? Run the \timing meta-command to obtain times for the different steps. Any triggers on test_table? Double check that all of your GUC (Grand Unified Configuration) parameters have actually been changed. It is puzzling as @ErwinBrandstetter says, no doubt about it - how many records are in the file?
    – Vérace
    Commented Jun 27 at 12:04
  • 1
    Try the code you show. Does the simplified code reproduce the problem. If so, then it is not approximate. If not, then it is not very helpful.
    – jjanes
    Commented Jun 27 at 14:37
  • I have an interesting finding – the more I parallelize the COPY operation, the better it is to index before data load! I updated the question with more details. Commented Jun 27 at 15:20

1 Answer 1

0
-- Configuration (optimized for bulk loading)
ALTER SYSTEM SET archive_mode = off;
ALTER SYSTEM SET max_wal_senders = 0;
ALTER SYSTEM SET wal_level = minimal;
ALTER SYSTEM SET fsync = off;
ALTER SYSTEM SET synchronous_commit = off;
ALTER SYSTEM SET full_page_writes = off;
ALTER SYSTEM SET maintenance_work_mem = '8GB';
ALTER SYSTEM SET max_parallel_maintenance_workers = 8;

-- Restart PostgreSQL for settings to take effect
-- (Ensure to restart PostgreSQL service)

-- Create the table
CREATE UNLOGGED TABLE IF NOT EXISTS test_table (
  id UUID PRIMARY KEY,
  int_col INTEGER,
  float_col REAL,
  text_col TEXT,
  date_col DATE,
  bool_col BOOLEAN,
  json_col JSONB,
  array_col INTEGER[],
  uuid_col UUID,
  timestamp_col TIMESTAMP WITH TIME ZONE
);

ALTER TABLE test_table SET (parallel_workers = 8);

-- Create indexes and constraints
CREATE INDEX idx_int_col ON test_table (int_col);
CREATE INDEX idx_text_col ON test_table (text_col);
CREATE INDEX idx_date_col ON test_table (date_col);
CREATE INDEX idx_json_col ON test_table USING GIN (json_col);
ALTER TABLE test_table ADD CONSTRAINT unique_text_col UNIQUE (text_col);
ALTER TABLE test_table ADD CONSTRAINT check_int_col CHECK (int_col > 0);

-- Bulk load data in parallel (ensure data.csv is split into N parts)
\COPY test_table (id,int_col,float_col,text_col,date_col,bool_col,json_col,array_col,uuid_col,timestamp_col)
FROM '/home/autoboss/data_load_performance/data_part_1.csv' WITH (FORMAT csv, HEADER true) &
\COPY test_table (id,int_col,float_col,text_col,date_col,bool_col,json_col,array_col,uuid_col,timestamp_col)
FROM '/home/autoboss/data_load_performance/data_part_2.csv' WITH (FORMAT csv, HEADER true) &
\COPY test_table (id,int_col,float_col,text_col,date_col,bool_col,json_col,array_col,uuid_col,timestamp_col)
FROM '/home/autoboss/data_load_performance/data_part_3.csv' WITH (FORMAT csv, HEADER true) &
\COPY test_table (id,int_col,float_col,text_col,date_col,bool_col,json_col,array_col,uuid_col,timestamp_col)
FROM '/home/autoboss/data_load_performance/data_part_4.csv' WITH (FORMAT csv, HEADER true) &
-- Add as many parts as needed for your N cores
wait  # Wait for all background processes to complete

-- (Optional) Rebuild indexes if necessary
REINDEX TABLE test_table;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.