3

We are currently trying to insert a large amount of data, about 27 million records with 200 columns, into a single table in a postgres 9.4 database. (Yes it is probably better to refactor the table into smaller tables but trying this method first)

The insertion is managed by a libpq application that inserts around 300 rows of data at a time using "INSERT INTO ...". We've read about using the COPY command, but for our current usage, the former was a better choice with decent performance. Some columns are arrays, which can be fairly large, but using this setup, we had no problem inserting around 10 million rows of data.

Problem: Whenever the table gets to around 600GB (~20 million rows), the insertion just stops. The pg_stat_activity shows the insertion query as active, and there are no errors in the output nor in the log. We've waited for more than 12 hours for an insertion query that usually takes a few seconds.

We've tried a few things like dropping the only index (primary key), running ANALYZE, and restarting the insertion, but whenever the table gets to around 600GB, the insertion stops. We don't believe we're hitting anything close to the maximum size of a table, so not really sure what is happening.

Does anyone have any idea? Or have some advice on how to track down what is going wrong during the insertion?

Thanks!

Update #1: As stated above, the rows being inserted are 200 columns wide, with some fields being arrays.

When the insertion to the table stopped, we experimented with manual insertion of records:

A typical row would not be inserted and the query would hang as we have previously seen. When we reduced the number of populated columns (e.g. insert a record with only 30 out of the 200 values), then the insertion was successful with no delays/slow downs. By varying the number of populated columns in the query, we found a cutoff point where insertion would hang if the number of columns being inserted became larger than a certain number. (We also played with different combinations of columns, it did not have any effect)

We have no idea on how to directly address this problem, so we are currently proceeding by partitioning the data as suggested into different (smaller) tables. However, this problem is not mentioned in any documentation, so any advice would still be appreciated!

  • OS? Nothing obvious like running out of disk? Can you split your update into several files? Your disk setup? Have you run iostat? Vmstat? You apparently know what you're doing, so I'm just guessing here :-) – Vérace May 19 '16 at 7:07
  • Are you using SAN device or local disks? – Károly Nagy May 19 '16 at 7:30
  • Have you considered partitioning your data? Perhaps in 5-10 million records each? – Ziggy Crueltyfree Zeitgeister May 19 '16 at 9:01
  • @Vérace The database sits on a local disk that is 3.6TB, of which only 1TB is being used; we still have 2.6TB of available space. – jkim May 19 '16 at 15:47
  • What OS and file system? Is it possible you're running into your file system's file size limit? – Darwin von Corax May 20 '16 at 1:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.