citusdata / citusdata/citus

Inserting binary data very slow

Open
#1,471 14 comments 0 reactions 1 assignee Claimed by @lithp View on GitHub
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

When I insert a 100 MiB binary file into a `bytea` column of a distributed table it takes about 4 minutes and 52 seconds (4:52) whereas inserting the same file into a non-distributed table takes only 3 seconds (0:03). I'm using PostgreSQL 9.6.3 and Citus 6.2.2. The problem can be reproduced with the steps below:

1. Create the file:

```
$ dd if=/dev/random of=$PG_DATA/test.bin bs=1M count=100
```

2. Create tables:

```sql
CREATE TABLE example_dist (
key text NOT NULL,
val bytea NOT NULL,
PRIMARY KEY (key)
);
SELECT create_distributed_table('example_dist', 'key');

CREATE TABLE example_normal (
key text NOT NULL,
val bytea NOT NULL,
PRIMARY KEY (key)
);
```

3. Insert data
```sql
INSERT INTO example_dist VALUES ('name', pg_read_binary_file('test.bin'));
```
The above takes 4:52 whereas the insert below takes only 0:03.

```sql
INSERT INTO example_normal VALUES ('name', pg_read_binary_file('test.bin'));
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.