Database table fragmentation

Many if not all common relational database management systems (DBMS) can suffer from an issue generally known as fragmentation. Although that phenomenon may emerge differently or is referred to differently on different DBMS, it is usually caused by excessive data manipulation activity, i.e. inserting and deleting data. Fragmentation can affect tables, indexes, and tablespaces. It refers to non-contiguous data chunks and blocks of unused space within the tablespace and fragmentation within the table rows.

Fragmentation can cause different problems:

• Wasted disk space. Fragmented data naturally leads to wasted disk space. Although mechanisms to shrink databases exist, they may not be effective in the long run if the databases will likely grow again.
• Performance issues. Fragmentation can also have negative impact on performance when performing full scans and large index range scans.

In the context of OneSpan Authentication Server, the persistent cache data (vdsPersistentCache table) in particular is susceptible to fragmentation, since it is intensively used with frequent insert/delete operations. If you are the database administrator, you should be aware of this issue and plan to mitigate it as part of your regular database maintenance. Consider to verify and clean up that table once a month.

Depending on the type of fragmentation, there are different methods to fix it, including:

• Reorganizing indexes
• Rebuilding indexes
• Reorganizing tables
• Deallocating unused space
• Shrinking tables

Each method is done differently depending on the DBMS used. It is beyond the scope of this guide to provide detailed procedures regarding this topic. For more information and detailed instructions, refer to the product documentation of the respective DBMS and additional resources on the web, including the following (last accessed in January 2024):

• Defragmenting InnoDB tablespaces (MariaDB): https://mariadb.com/kb/en/defragmenting-innodb-tablespaces/
• OPTIMIZE TABLE (MariaDB): https://mariadb.com/kb/en/optimize-table/
• ALTER TABLE XXX SHRINK, ALTER TABLE XXX DEALLOCATE UNUSED SPACE (Oracle Database): https://docs.oracle.com/database/121/SQLRF/statements_3001.htm#CJAHHIBI
• ALTER TABLESPACE XXX COALESCE (Oracle Database): https://docs.oracle.com/database/121/SQLRF/statements_3002.htm#SQLRF53577
• Optimize index maintenance to improve query performance and reduce resource consumption (Microsoft SQL Server): https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15