6

I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a good performance (around 25k inserts per second) with multi-threading and batching of inserts every few thousand inserts or so, completing a whole run in around 8mins.

However, I noticed that I had a few of the foreign keys which had an index missing, which I would really wish to have both from an analysis point of view to drill down in the data, and also to delete data to a specific run. Unfortunately when I added in these 3 indexes to the table that is getting most inserts, performance dropped down drastically to around 3k per second.

Is there any way to avoid this performance slow down? I know that one option is to drop the indexes before a run and recreate them in the end. Another more clumsy option is to generate the data of the biggest table in a file instead and use COPY. I guess I can only do it on the largest table in the relation, due to the foreign key values which I would need to know (generated through sequences).

Both alternatives seem to be hacks. Is there any other solution, maybe a bit less intrusive on the application? Some setting to tell postgres to defer indexing or something of that sort?

Any ideas welcome.

|improve this question

migrated from stackoverflow.com Nov 15 '12 at 18:25

This question came from our site for professional and enthusiast programmers.

  • Are you generating data or is the data collected from somewhere and needs to be inserted at a fast rate? If you really want to generate and insert data as fast as possible, the way to go is to write it in SQL. generate_series and other like functions help here. – Colin 't Hart Nov 15 '12 at 11:59
  • 2
    Deferred indexing would be nice, but isn't currently supported. Adding indexes has a cost. COPY won't help much if index maintenance is the main issue. What's the use case? does the data need to be durable, or can you afford to lose it on a DB crash (and thus use UNLOGGED tables, etc)? Can you create the indexes after loading data? – Craig Ringer Nov 15 '12 at 12:43
  • 1
    @CraigRinger well I would say that losing the data after a DB crash would not be a huge issue, because the same run can be executed again. The only issue I have that in between runs I would like to have the indexes in place for proper drilling down analysis and fast deletion. If you don't think COPY would help, then the the other alternative of Dropping indexes before a run and Re-adding them after seems to be the only solution? :( – jbx Nov 15 '12 at 13:47
  • 1
    I agree with Craig, either use unlogged tables or (even more risky!!!!) fsync=off – a_horse_with_no_name Nov 15 '12 at 18:31
  • 1
    If you can live with non-durable operation, then this becomes relevant: stackoverflow.com/questions/9407442/… – Craig Ringer Nov 15 '12 at 22:26
6

Deferred indexing would be nice, but isn't currently supported.

Adding indexes has a cost - write performance. They're a trade-off.

COPY won't help much if index maintenance is the main issue.

The simplest solution is to drop the indexes, and re-create them when you're done importing.

Since you can live with losing all your data if the DB crashes, you have a bunch of options to further improve performance, including:

  • Unlogged tables
  • fsync=off
  • Non-durable disk write caching on the drive that hosts the DB

The use of any of the above will eat your data if anything goes wrong. The last option might eat the file-system too.

I wrote more about this in https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing .

|improve this answer
  • 2
    I had a similar performance problem, especially on inserting rows in a table with 100M+ rows and concurrent updates to this table. The biggest improvement was to specify a custom value for the fillfactor of the table itself and the indexes (See postgresql.org/docs/9.2/static/sql-createindex.html for further details). – Oliver F. Nov 30 '12 at 23:27
  • 2
    @OliverF. Excellent point. Consider posting that as an answer. If you do, delete your comment here and post a new one saying you've done so so I get notified; I'll upvote. Please link to /docs/current/static not /docs/9.2/static to avoid stale links accumulating. – Craig Ringer Dec 2 '12 at 8:49
  • 2
    I will add it as answer. Thanks for the hint to link to /docs/current/static. – Oliver F. Dec 13 '12 at 15:07
16

In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables.

I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad.

Tune the storage parameters of your tables and indexes

Besides adding several indexes to the database, I changed the storage parameters of some tables and specified a custom value for the fillfactor of the table itself and the indexes.

Setting a custom value for the fillfactor allows you to instruct PostgreSQL how much space in each page should be reserved for further updates. The same applies to indexes.

See the documentation on CREATE TABLE and the description of the available storage parameters for details.

Monitor your infrastructure

Monitor and analyze your infrastructure. The PostgreSQL wiki lists a lot of usefull tools.

Find long running statements

Enable statement logging by altering the following values in your postgresql.conf file:

  • log_min_duration_statement=x to log all statments which run longer the x milliseconds
  • log_min_messages=level to a level what helps you the understand the statements generated by JPA

See the description of the runtime logging configuration for details

Install pgFounine to analyse your PostgreSQL log file easily.

Be picky

Besides altering the storage parameters I also gained a lot of performance by optimizing all frequently executed statements. In parts I won only 100 or 50 milliseconds for each execution but in total I gained more then 5 seconds for complex operations.

|improve this answer
  • 6
    Oliver - it looks like this is your first answer here and I wanted to tell you I gave it an upvote. I like the way you formatted it, I like the extra information you gave and the effort you gave. This is a great answer and I hope to see more from you here as time goes on. – Mike Walsh Dec 13 '12 at 16:14
  • Could you expand on what exactly you changed for the fillfactor? Did you increase it or did you decrease it? – a_horse_with_no_name Dec 14 '12 at 11:51
  • @a_horse_with_no_name: I decreased it to 50% since I do serval updates to each record. The rule is: If you do serval update to a record choose a lower fillfactor. For read-only or insert-only tables you can set the fillfactor to 100%. – Oliver F. Dec 14 '12 at 15:50
  • @MikeWalsh: Thank you. I simply documented all the things I did recently in my current project. I am glad to share my knowledge as I also benefit from the knowledge shared by other users on this plattform. – Oliver F. Dec 14 '12 at 16:02

Your Answer

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

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