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.
Why is Index rebuild required in Oracle or PostgreSQL ?
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 -
- To Improve time consuming queries upon removing the fragmentation from indexes due to frequent UPDATE and DELETE operations.
- To reclaim space from fragmented indexes that are unnecessarily occupying excessive space.
- To help queries re-use the corrupted Indexes that may be skipped by the Planner/Optimizer until they are rebuilt.
MigOps can help you migrate from Oracle to PostgreSQL
MigOps can support with Performance Tuning and DBA services for PostgreSQL.
Contact Us today
How Does Index Rebuild Work in Oracle
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.
Oracle provides two options for rebuilding an index
- Online Index Rebuild
- Normal Index Rebuild
Online Index Rebuild in Oracle
When we rebuild an index online in Oracle, It locks the table in shared mode and creates a snapshot log for the table to hold any DML activities that are carried out on the table. It does a full table scan and builds the new index. Once the new index is created, it then applies the changes that occurred during the index rebuild process, from the snapshot log. Oracle then replaces the Old Index with the newly created Index.
During an online index rebuild, since the entire table is scanned by the Oracle, it is recommended to use the PARALLEL option to make the rebuilding of the index faster. This feature is again available only on the Enterprise Edition of Oracle.
To rebuild an index online, you can use the same "ALTER INDEX" statement with an additional clause. For example, to rebuild the index "myindex" on the table "mytable" in the schema “myschema” using ONLINE method, you would use the following syntax:
ALTER INDEX “myschema”.”myindex” REBUILD ONLINE;
A sample execution of the REBUILD ONLINE command is provided in the following section.
[oracle@migops ~]$ sqlplus / as sysdba Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database Enterprise Edition Release SQL> ALTER INDEX "myschema"."mytable" REBUILD ONLINE PARALLEL NOLOGGING; Index altered. SQL> exit |
Points To Note:
- Although rebuilding an index online allows DMLs on its table, we cannot have any DDLs on that table until the Index rebuild is complete.
- Compared to the offline rebuild, when an index is rebuilt online it may take more time and resources for it to complete.
How Does Index Rebuild Work in PostgreSQL
Performing a REINDEX on an existing index of PostgreSQL is more similar to dropping and recreating the same index as it happens in Oracle. Similar to Oracle PostgreSQL also provides 2 options to rebuild an index -
- Normal Index Rebuild
- Online Index Rebuild
Index Rebuild in PostgreSQL
During an Index rebuild using REINDEX, Postgres acquires an ACCESS EXCLUSIVE lock on the specific index that's being rebuilt. For this reason, Postgres only allows only read queries to execute and blocks all the write queries on the respective table of the index being rebuilt. Since it's an ACCESS EXCLUSIVE lock on the Index, the read queries that are referencing the indexed column and which choose to use this index will still be blocked for read queries.
Online Index Rebuild in PostgreSQL
When an index is rebuilt using CONCURRENTLY with REINDEX command, PostgreSQL performs an Index rebuild ONLINE. When we use concurrently with reindex, Postgres performs two sequence scans (full table scans) on the table of the respective index. The reindex waits for any existing transactions that are currently accessing the index.
In the initial step, PostgreSQL makes a temporary entry for the index that’s going to be created in the pg_index catalog table and marks the column indisready and "indisvalid" as false. It then takes a SHARED UPDATE EXCLUSIVE lock at the table level to prevent any other DDL activities on the table.
In the next step, it performs a sequence scan (full table scan) of the table and builds the new index. Once this step is completed, Postgres updates the pg_index catalog entry by setting the column indisready to true. Postgres then performs the second sequential scan on the table and adds all the changes to tables with the rows that are added/modified since the first scan of the table.
In the final step, all the constraints that were pointed to the old index are now pointed to the new index. Once this is done, PostgreSQL updates the "indisready" column of pg_index to true and modify the "indisready" to false for the old index. In addition to that the cache invalidation is done for the existing connections that are already using this index. The old index is also dropped and the SHARED UPDATE EXCLUSIVE lock is released to unlock any DDL changes on the respective table.
To rebuild an index online in PostgreSQL, you can use the "REINDEX (CONCURRENTLY) INDEX" statement. For example, to rebuild the index "myindex" on the table "mytable" in the schema “myschema”, you would use the following syntax:
REINDEX (CONCURRENTLY) INDEX myschema.myindex;
A sample execution of the REINDEX CONCURRENTLY command is provided below
manisankar@migops:/Users/manisankar:psql -d migops Password for user manisankar: psql (15.1)Type "help" for help. migops=# REINDEX (CONCURRENTLY,VERBOSE) INDEX myschema.myindex; INFO: index "myschema.myindex" was reindexed DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. REINDEX migops=# |
Similar to Oracle, rebuilding the index with the CONCURRENTLY option takes a longer time to complete and additional resources are used as it needs to do multiple sequence scans of the table along with waits due to locks and synchornization of changes.
MigOps can help you migrate from Oracle to PostgreSQL
MigOps can support with Performance Tuning and DBA services for PostgreSQL.
Contact Us today
Conclusion
Oracle allows us to rebuild an Index ONLINE only with Enteprise Edition. Whereas with PostgreSQL, there exists no commercial license as it is Open Source and a 100% community driven project. One of such features helps us understand not only the capabilities of Postgres but also the freedom of commercial license and the cost involved in Oracle for some of such features. Contact MigOps today to see if you can safely migrate from Oracle to PostgreSQL and enjoy the benefits of Postgres. You may fill the following form if you are in the process of migration and need support from Migration experts and PostgreSQL experts like MigOps.
