Check the time that PostgreSQL is taking to automatically create existing indexes when you do bulk insert using copy command from Spark

Question

I am doing a bulk insert from the spark to the postgres table. Amount of data that I am ingesting is huge. The number of records is around 120-130 million

I am first saving the records as multiple csv files on distributed storage location i.e. S3 bucket in my use case. Now I am using multiple copy command to copy the data in the PostgreSQL table. The actual PostgreSQL table has four indexes on it.

The copy command takes around 8 hours to save the data. I created a similar table without indexes and the data got saved in around 28-30 minutes. Based on searches that I have done on internet on multiple sites, they mentioned that indexes can slow down the performance and that is definitely seen based on the time difference that I have specified above.

Now the actual question is how I can identify which index creation is taking more time. Is there any utility, query or command that shows the time taken to create the indexes on the table when we are doing bulk inserts. I am using the below query to see the number of multiple copy commands are running on the PostgreSQL instance:

SELECT * FROM pg_stat_activity 
where usename = 'xyz' and application_name ='PostgreSQL JDBC Driver'

Is there something like this query or any tool or command that I can use to see amount of time t is taking to create the indexes. Also how much time is taken by each index?

Any idea, guidance or suggestion are welcome. I am not that familiar with PostgresQL.

Answer 1

As long as all indexes are B-tree indexes, modifying them all should take roughly the same time.

Of course, an index with an expensive expression will take somewhat longer, as well as an index on a string column with an expensive collation.

You could compare the duration of CREATE INDEX statements on a filled table – if an index takes twice as long to build as another one, it will probably also take about twice the time to modify.

If the amount of newly loaded data is large, it may be faster to drop and re-create the indexes. You'll have to experiment.

Answer 2

A terminology issue: already existing indexes don't get "created" when new rows are added, they get "maintained". Using the right term might make internet searching more successful.

PostgreSQL does not provide the instrumentation to do this. One might think that pg_stat_user_indexes should have columns for blk_read_time and blk_write_time, but it does not have them. I don't know if there is reason for this, or if it was just an oversight from when track_io_timing was implemented. EXPLAIN (ANALYZE, BUFFERS) also does not break the block timing out by underlying relation to distinguish a table from its indexes.

In the absence of those, your best bet is probably to just do an experiment, dropping all-but-one index for each index in turn, and loading data into an already large table to see how long it takes.

Once the indexes get very large, the limit is generally going to be reading the index leaf page, so that it can update it for the new tuple. This is generally random IO, and so will be slow. If the rows being added are already sorted (or at least "clumped") according the ordering used in any index, that index will take less time to maintain as the IO pattern will be less random for it. A similar effect is that if all the indexes are small enough to fit in RAM (but not to fit shared_buffers), then you might not need to read each leaf block from disk as it will already be in cache, but you still have to write each one back to disk. The OS can buffer up those writes, but it is generally less willing to buffer up writes than it is to buffer clean pages (for reads), so you fall off the random-block-writes cliff before (in terms of index size) you fall off the random-block-reads cliff.