Table of contents

• Creating Partitions
• Creating Partitions using SQL Editor
  • Saving changes
• Working with partitioned tables
• Deleting Partitions
  • Database Navigator
  • Properties Editor
  • SQL Editor

Partitioning is a database design technique when data is split across multiple tables or databases but is logically still one table. This technique is proper when dealing with large tables, as it can improve query performance, manageability, and availability.

Note

may vary among those that do. Always refer to your specific DBMS documentation to understand the partitioning capabilities it offers.

Creating Partitions

1. The first step to creating Partitions is to create a table. Consider a table with the following columns:

   orderid int4,
   customerid int4,
   orderdate date,
   year int4
   

2. Next, go to the Properties tab, and find the Partition by field. Here, you need to specify the Partition criteria. For instance, to Partition the data based on the year column, you can use RANGE (year).

3. Navigate to the Partitions in tabbed Editors. Right-click and select Create New Partition. This action will open a new Partition table window.

4. In the new window, specify the Partition Expression. This expression defines the boundaries for the Partition. For example, to create a Partition for the years 2023 and 2024, you can use FOR VALUES FROM (2023) TO (2024).

Creating Partitions using SQL Editor

You can also create a Partition by using SQL statements. The specific SQL command may vary slightly depending on the type of your database.

1. Here's an example of how to create a partitioned table orders based on a range of Partition on the year column using PostgreSQL syntax:

   CREATE TABLE orders (
       orderid int NOT NULL,
       customerid int NOT NULL,
       orderdate date NOT NULL,
       "year" int NOT NULL
   ) PARTITION BY RANGE ("year");
   

   Note

   MySQL, or SQL Server, it is necessary to use the syntax that corresponds to the specific database system.

2. Next, you would create Partitions within this table. For instance, if you want to create a Partition for the year 2023:

   CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM (2023) TO (2024);
   

Saving changes

Once you have configured the Partition properties, saving these changes to the database is essential. Until you commit your modifications, the new Partition will only exist within DBeaver and will not be added to the actual database table. Here are three options for committing the changes:

• Click on File -> Save -> Persist.
• Select the desired table in the Database Navigator and press Ctrl+S (or CMD+S for macOS), choose Persist to save the changes.
• Utilize the Save button located at the bottom of the Properties editor and press Persist to save the changes.

Working with partitioned tables

After you've created a partitioned table in DBeaver, you can interact with it just like any other table. Remember, though, that the Partition expression will impact which data goes into which Partition.

Consider the table you created before, partitioned by RANGE (year) with a Partition for the years 2023 to 2024.

1. To insert data into the table, you would use a regular INSERT statement:

   INSERT INTO orders (orderid, customerid, orderdate, "year")
   VALUES (1, 101, '2023-01-01', 2023),
          (2, 102, '2023-06-01', 2023),
          (3, 103, '2024-01-01', 2024);
   

   Each row of data would be automatically directed to the appropriate Partition based on the year value. For instance, the first two rows would be stored in the Partition for 2023, and the third would be stored in the Partition for 2024.

2. To retrieve data from your partitioned table, you use a standard SELECT statement:

   SELECT *
   FROM orders
   WHERE year = 2023;
   

   This will return all rows from the 2023 Partition.

Deleting Partitions

Deleting a Partition in DBeaver is simple and can be done via the Database Navigator, the Properties Editor, or the SQL Editor.

Warning

The Partition is also removed from the table's Partitioning scheme. It is always recommended to back up the data before deleting a Partition.

Database Navigator

1. In the Database Navigator, expand the table which contains the Partition you want to delete.

2. Right-click on the Partition, then click Delete.

Properties Editor

1. Go to the Partitions tab of the Properties Editor for your table.

2. Right-click on the Partition, then click Delete.

SQL Editor

You can also delete a Partition by using SQL statements. The specific SQL command may vary slightly depending on the type of your database.