How to work with database Partitions

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: Not all databases support partitioning, and the level of support and specific functionalities may vary among those that do. Always refer to your specific DBMS documentation to understand the partitioning capabilities it offers.

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).

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");

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);

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 Mac OS), 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.

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 a Partition in DBeaver is simple and can be done via the Database Navigator, the Properties Editor, or the SQL Editor.

Warning: When a Partition is deleted, all the data stored in that Partition is permanently lost. The Partition is also removed from the table's Partitioning scheme. It is always recommended to back up the data before deleting a Partition.

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.

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

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

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