Sometimes, we see Customers curious about the features of PostgreSQL and see if the features similar to Oracle exist in PostgreSQL. One of such featu

Online rebuild of Indexes – Oracle vs PostgreSQL

submited by

Style Pass

2023-01-23 22:30:21

Sometimes, we see Customers curious about the features of PostgreSQL and see if the features similar to Oracle exist in PostgreSQL. One of such features is the capabilities of Rebuilding an Index ONLINE . Oracle requires you to have an Enterprise License to have the capability of rebuilding an Index online. Whereas PostgreSQL allows us to rebuild Indexes online without needing any license as it is Open Source. In this article, we shall discuss how Indexes can be rebuilt online in PostgreSQL and compare the same with Oracle. Do not forget to read our previous articles explaining Oracle vs PostgreSQL supporting Oracle to PostgreSQL migrations .

The way Oracle manages UNDO and the way MVCC works in PostgreSQL are slightly different when it comes to tables. However, Indexes can still get fragmented and thus require a rebuild. Following are some of the reasons why rebuild of Indexes may be required in both Oracle and PostgreSQL -

An index rebuild is a process of rebuilding an existing index to improve its performance and/or to reclaim space. When an index is rebuilt using the "ALTER INDEX " statement, Oracle creates a new version of the index and then replaces the old index with the new index. The old index is marked as unusable and is eventually removed by the Oracle database's automatic maintenance tasks.