I wrote a migration for a web application that ran CREATE INDEX as part of its deploy. When it took too long to create, our deployment system ruled it as a failed deploy, killed the deployment, and tried again. It did this until our deployment system gave up on deploying it altogether.

This led to about 20 CREATE INDEX processes on a large table, which was locking rows and pegging the CPU/IO, so we killed the pids.

Now, outside of this deploy process, I'm running a (more responsible, with time to run) CREATE INDEX CONCURRENTLY, but getting:

ERROR:  could not write to file "base/pgsql_tmp/pgsql_tmp18746.4": No space left on device

It could very well be that we're just out of space, but I suspect brutally killing ~20 large index creation processes left some large files that Postgres isn't able to use.

  • Is there a way to inspect/remove such files, if they exist?
  • Any other poor states I should look for?
  • 1
    How was the killing done? – jjanes Feb 6 at 17:32
  • what is the size of base/pgsql_tmp directory ? – Sahap Asci Feb 6 at 19:00
  • 1
    Do you have access to the filesystem? If so, look to see where the space is being used, no sense in speculating when you can measure. And if no access, then what can you do about it anyway, find someone who does have access. – jjanes Feb 7 at 2:34
  • Do you have any result from the query SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;? Those are the invalid indexes. – Sotis Feb 15 at 18:47

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.