pg_restoring very large single table using -j option taking several hours

Question

I am working on a single table (with no partitioning) having 700+ million rows. I wanted to load this data to another database so I used following pg_dump command,

pg_dump -Fc --column-inserts --data-only --table='tname' -U 
postgres -d dbname > /root/tname_experiment_inserts_custom_format.dump

On the destination system I used the following command,

pg_restore -d dest_dbname -U postgres -j 7 /root/tname_experiment_inserts_custom_format.dump

The destination database already had the table I was trying to restore so I used TRUNCATE and then deleted all the indexes. The destination system has 32GB physical memory, I did the following settings in the postgres config file,

log_min_duration_statement = -1
autovacuum = off
maintenance_work_memory = 7gb 
wal_level = minimal
fsync = off
full_page_writes= off
synchronous_commit= off
max_wal_size= 20GB
wal_buffers= 16MB

When I time the pg_restore then in an hour only about 16 million rows are getting inserted. Which means it is going to take 40+ hours (!) to restore the data. After that I have to create indexes and foreign constraints that I dropped which might take another several hours. I have a feeling I can do something differently to make this whole process much faster. Please give me any pointers which can help me make this process efficient. I also want to mention that I already looked at COPY but because it doesn't maintain the order of the primary keys this option is not good for me. If I don't know of any peculiar setting of COPY which preserves the order of the data then it would be great to know!

The purpose of whole thing was to change some datatypes of the columns which when done using alter table alter column query was also taking similar amount of time.

Answer 1

Given this table:

---

CREATE TABLE abc(a serial NOT NULL
        ,b text
        ,c DATE NOT NULL
        );
INSERT INTO abc(b,c) VALUES
        ('cow' , '2017-01-01')
        , ('pig' , '2017-01-02')
        , ('dog' , '2017-01-03')
        , ('cat' , '2017-01-04')
        ;

---

pg_dump -U postgres mydb --column-inserts --data-only

will generate this kind of output:

---

--
-- Data for Name: abc; Type: TABLE DATA; Schema: tmp; Owner: postgres
--

INSERT INTO abc (a, b, c) VALUES (1, 'cow', '2017-01-01');
INSERT INTO abc (a, b, c) VALUES (2, 'pig', '2017-01-02');
INSERT INTO abc (a, b, c) VALUES (3, 'dog', '2017-01-03');
INSERT INTO abc (a, b, c) VALUES (4, 'cat', '2017-01-04');

--
-- Name: abc_a_seq; Type: SEQUENCE SET; Schema: tmp; Owner: postgres
--

SELECT pg_catalog.setval('abc_a_seq', 4, true);

---

Omitting the --colum-inserts will produce:

---

--
-- Data for Name: abc; Type: TABLE DATA; Schema: tmp; Owner: postgres
--

COPY abc (a, b, c) FROM stdin;
1   cow 2017-01-01
2   pig 2017-01-02
3   dog 2017-01-03
4   cat 2017-01-04
\.

--
-- Name: abc_a_seq; Type: SEQUENCE SET; Schema: tmp; Owner: postgres
--

SELECT pg_catalog.setval('abc_a_seq', 4, true);

---

So the --column-inserts will generate one insert statement for every row which is very slow. The --data-only flag only suppresses generation of the DDL to create the table(s).

Answer 2

As said above, use the pg_dump which produces a COPY statement.

Also, under heavy write traffic, lots of data will be written to the WAL, so you want your WAL to be on a separate, fast disk or a SSD.

Another option is to set it up so nothing is written to the WAL. This import is an all-or nothing operation. Either it works, or you'll truncate and run it again. So it makes no sense to dedicate resources to WAL to ensure the table is crash-proof and ACID (unless the server is a replication master).

There are two ways to do this:

• DROP the table, then make sure the whole restore happens in one transaction. "BEGIN; CREATE TABLE... COPY... COMMIT." In this case no data will be written to the WAL. This is because if the transaction fails, the table was not created at all. So there is no need for it to be ACID.
• If this is a test server and you just want to play with the data, you can set everything as UNLOGGED, but of course all the data will be wiped out in case of a crash.

Now, the COPY won't be parallelized, but once data is loaded, index and constraint creation can be parallelized. So, if you use pg_restore to restore the indexes, make sure to use the -j option to use your cores. If you don't, simply open several psql windows.

Tuning of maintenance_work_mem also helps a lot for index creation. If the server is doing nothing, just restoring, using the RAM for sorting and index creation really speeds things up.