Navicat 16 and Tablespaces – Part 1

Nov 25, 2022 by Robert Gravelle

Did you know that Navicat 16 supports tablespaces? A table space is a storage structure for tables (as well as indexes, large objects, and long data) that organizes database data into logical storage groupings that relate to where data is stored on the filesystem. It’s main function is to link the physical storage layer and the logical storage layer. By assigning tables to a tablespace you can control the physical storage layout by putting some tables on faster or more redundant disks, or to stripe tables across disks. This series is split into two parts: in the first couple of blogs, we’ll cover the theoretical side, specifically, what sort of advantages tablespaces offer, as well as how they work and. The second part will focus on more practical matters, i.e., how to manage tablespaces in Navicat 16.

Besides the advantages mentioned above, tablespaces offer a few other benefits:

Recoverability

Putting objects into the same tablespace makes backing up and restoring the database easier, since you can backup or restore all the objects within a tablespace with a single command. Moreover, if you have partitioned tables and indexes that are distributed across tablespaces, you have the option of backing up and/or restoring only the data and index partitions that reside in a given tablespace.

Easy to Add More Tables

Although there are limits to the number of tables that can be stored in any one tablespace, should you have a need to store more tables than can be accommodated within a single tablespace, you can easily create additional tablespaces for them using the CREATE TABLESPACE command:

CREATE TABLESPACE tbs1 
   DATAFILE 'tbs1_data.dbf' 
   SIZE 1m;

Automatic Storage Management

Usually, you need to define and manage the tablespace containers yourself. However, certain databases, such as DB2, support automatic storage tablespaces, whereby storage is managed automatically. Creating a tablespace with the automatic storage tablespace option delegates the creation and management of containers to the database manager.

Ability to Isolate Data in Buffer Pools for Improved Performance or Memory Utilization

If you have a set of objects (for example, tables and indexes) that are queried frequently, you can assign the tablespace in which they reside to a buffer pool with a single CREATE or ALTER TABLESPACE statement. Temporary tablespaces can also be assigned to their own buffer pool to increase the performance of certain operations such as sorts or joins. For seldom-accessed data, or for applications that require very random access into a very large table, it might make sense to define smaller buffer pools; the data can be kept in the buffer pool for no longer than a single query.

This first instalment of the Navicat 16 and Tablespaces series presented several advantages offered by tablespaces. In the next blog, we’ll learn more about how tablespaces work. Finally, we’ll move on to working with tablespaces in Navicat 16.