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?
Best Answer
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.