Altering big table in Postgres 11.16

[amol.gupta]

Hi

We are currently running RDS postgres 11.16 version and we have a table which is in size approximately 161 GB. There is a requirement to add new columns as well as drop and re create indexes and add a unique constraint on one of the columns.

Can you please let me know if we can do this online or concurrently without affecting concurrent transactions.?

Thanks

Amol

[Shivam_Dhapatkar]

Hi,

REINDEX is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch.

REINDEX CONCURRENTLY option is not available in the Postgres 11.16 version. It is available from Postgres version 12.

When the CONCURRENTLY option is used, PostgreSQL will rebuild the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index rebuild locks out writes (but not reads) on the table until it’s done.

[Robert_Bernier]

Hi,

The previous answer given by Shivam is spot on so I’ll see if I can address those other issues that you brought up.

In regards to indexes: it’s an old trick, but you can easily create new indexes in order to replace existing ones without blocking any activity to your table as they are not seen until after they have been created. Once created however, you can drop the older indexes that you are replacing without any issue but, depending on the load, there may be a delay until an exclusive lock has been made in order to remove them.

In regards to adding columns: Whether or not tables are locked while columns are created is dependent upon the DEFAULT columnar value. If they are NULL typically the tables are created very quickly. The problem occurs when they are not NULL as for example when providing a default value of “1” for an integer column. In this case, the table gets an exclusive lock preventing any other activity on the table in question while the new column is populated with this default value. Alternatively, and depending upon the schema design of your table, you can take advantage of partitioning, using either inheritance or in specific use-cases declarative. You can refer to this blog for further information.

Hope this helps