Pgroll support for creating index on huge table with frequent reads & writes
Hi PgRoll Team,
I’m Azad, and I have a use case where I’d like to understand if PgRoll can provide support. Currently, I’m using Flyway to manage schema migrations, but I recently encountered an issue with the following error:
SQL State : 53400
Error Code : 0
Message : ERROR: temporary file size exceeds temp_file_limit (6387299kB)
The error occurred while attempting to create an index without CONCURRENTLY on a very large table. The table was locked during the operation, preventing updates, and even with CONCURRENTLY, I hit the temp_file_limit due to the table’s size and the inability to store sorted values in temporary files.
I’d like to know if PgRoll can handle this use case effectively.
Looking forward to your insights!
#pgroll
3 Replies
Hi Azad, thanks for reaching out, I saw your post in the community there. Let me take a look 👍
This should be a valid use case for pgroll which supports index creation without downtime or locks. Similarly, the tool works by creating views on tables, not by copying tables and using triggers (as some other tools do). This means you should be able to avoid memory issues as well. https://pgroll.com/docs/latest/operations/create_index
pgroll
pgroll