1

I have table which is several time bigger than available RAM. Table size is 181 GB, 92 million rows. PostgreSQL 10.

There is field with uuid in the table. And there is need to insert into the table several hundreds rows, from time to time.

What is better, hash index or b-tree index for fast inserts? The table is practically never read, it just store archive information. The index is necessary only to check what there is no row in the table with the same uuid.

Currently, inserts work really slow. Insert of 1 million rows now works for more than 4 hours, still running. Typically, it works for about 5 hours.

The query looks like this: 

insert into TableName (...)
select [list of fields]
from table_data_forinsert;

No where condition, the source table contains only data which needs to be inserted. And how to insert faster?

I think about create btree index, and sort data by uuid before insert. May it help to improve performance for btree index? Or may be it is possible to do something else to improve performance? I not want to make the table unlogged, it is necessary to not lose the data.

Is any approach how to make inserts faster for hash index?

And same question for updates. What is faster for updates, b-tree index or hash index, if there is update of several hundreds of thousands of rows in big table? Are any methods how to make updates faster? Like may be sort data in source table or something else?

Improve this question

3 Answers 3

Reset to default
1

A B-tree index will be the best when it comes to the speed of data modifications.

There is no way to significantly increase the speed of inserting a few hundred rows.

If you need the uniqueness guarantee, you cannot do without the index, so there is not much room for improvement.

Improve this answer
0

  1. for large data operate, you may try copy instead of insert. copy is designed for bulk data load. In postgresql 11 user manual 14.1 populate database, the copy command is recommended. When I using python pandas, the speed difference of using or not using copy command is 10 times. So for you case, if you using copy command, the speed could fast as well. however, the copy command is not as convenient as insert. or you need learn it first

  2. for more speed need, you can drop index first, insert data and then rebuild index.

  3. or try third party buld load tools like pgload and etc.
Improve this answer
3
  • 1
    Copy really faster than insert? Taking into account, it is necessary to select data to different server and send it back via copy?
    – andsm
    Feb 12, 2020 at 9:05
  • The documentation says so, but I think they mostly mean performance vs single insert statements row-by-row, AKA slow-by-slow.
    – Colin 't Hart
    Feb 12, 2020 at 9:22
  • in my practice, copy fast than other ways. Besides, you has postgresql 10 high performance book on hand. you can find the speed rating for postgresql 10. From slowest to fatest: inerst a single record at once, insert a large block of records at one time, use single copy at a time; multi insert processes; Use multiple copy commands at once. Copy is always fast, the fatest way belong to multi copy in parallel.
    – Yong Wang
    Feb 12, 2020 at 9:30
0

There is generally not a great deal of difference between them. The hash index doesn't have to traverse through intermediate pages to get to the leaf pages the way btree does, but since those intermediate pages are almost surely going to be cached, this makes little difference in practice.

The hash index might be smaller, which would make it more cache friendly. You mentioned the table is much larger than RAM, but what about the indexes?

On the other hand, with btree you can sort the records by the UUID before inserting them as you said, which would make it more cache friendly in a different way, as long as the batch size is large. If the batch size is small ("several hundred") it will probably make no difference.

Ultimately the ways to make it faster are either to do away with the unique constraint (how often do you attempt to insert duplicates and get rejected? What are the consequences of having occasional duplicates slip through?), or get faster storage hardware.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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