Inserting binary data very slow
- 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
Assessment
This issue has not been assessed yet.