1

to explain the context, I test postgres with a huge table (I try to have 1TB data) in a docker container for isolation, and I ran my container with a -v parameter in order to mount my dedicated HDD (spinning 1TB). So I ran my ingest process, everything worked fine.

Next, I had to run an other container in which I wanted to ingest only 25M rows to make comparison, BUT I ran my docker container with the same -v parameter ... oups ... so I stopped my container and, big mistake, remove it, so my ingest process has been stopped because the previously was unaccessible, which makes sense I suppose.

So I thought "it's ok, I will restart it an rerun the ingest", which I've done, but I got that error:

psql:/home/inserts24_8MTransac_200_124000_263.sql:124001: ERROR:  XX002: 
index "test_table_pkey" contains unexpected zero page at block 17855039
HINT:  Please REINDEX it.

So I run the reindexing command:

TestDB=# REINDEX INDEX test_table_pkey;

But I have almost 6.5 Billions rows, and it seems it takes so much time, I only have a primary key on (id) and an index on two rows (both are integers). My table contains only 6 rows (One Bigint for the id, Integers or small integers for the rest).

Is it normal the REINDEXING process take a lot of time?

Thanks

  • Hi @benjamin from my past experience REINDEX depends on your machine I/O and core power so it may take time. You can try to REINDEX CONCURRENTLY all your indexes and have some benefits of that core parallelism – Imanol Y. Oct 31 '18 at 10:27
  • 1
    Hi @ImanolY. I tried but it does not work at all:<br/> TestDB=# REINDEX CONCURRENTLY INDEX test_table_pkey; ERROR: syntax error at or near "CONCURRENTLY" LINE 1: REINDEX CONCURRENTLY INDEX test_table_pkey; ^ TestDB=# REINDEX INDEX CONCURRENTLY test_table_pkey; ERROR: syntax error at or near "CONCURRENTLY" LINE 1: REINDEX INDEX CONCURRENTLY test_table_pkey; ^ Do you have an example?? – Benjamin Soulas Oct 31 '18 at 11:00
  • REINDEX VERBOSE TABLE test_table REINDEX VERBOSE DATABASE TestDBwork? – Imanol Y. Oct 31 '18 at 13:11
  • 1
    Finally I dropped my database, because I was afraid it could take too much time, I prefered to do the ingest from scratch, and take care when I run an other container ... But thanks a lot by the way !!! – Benjamin Soulas Oct 31 '18 at 13:58
  • low maintenance_work_mem may cause slow index creation but setting it too high doesn't improve performance. – Sahap Asci Oct 31 '18 at 17:30

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.