Using ENUM as partition key when RANGE Partitioning PostgreSQL

Question

I am working with PostgreSQL 14. I have a table called Order with a status column:

CREATE TABLE "Order" (
 "orderId" BIG SERIAL PRIMARY KEY NOT NULL,
 "orderDescription" TEXT NOT NULL,
 "statusId" SMALLINT NOT NULL
) PARTITION BY RANGE ("statusId");

As shown, this table is partitioned by RANGE based on the statusId. We need to place orders that have moved to a closed status in a separate partition. To achieve this, I implemented a simple trick. I defined a table called OrderStatus and assigned open statuses in the range of 10 to 20 and closed statuses in the range of 20 to 30:

CREATE TABLE "OrderStatus" (
 "statusId" SMALLINT PRIMARY KEY NOT NULL,
 "statusName" VARCHAR UNIQUE NOT NULL
);
INSERT INTO "OrderStatus" ("statusId", "statusName") VALUES (11, 'WAITING'); -- open
INSERT INTO "OrderStatus" ("statusId", "statusName") VALUES (12, 'OPEN'); -- open
INSERT INTO "OrderStatus" ("statusId", "statusName") VALUES (13, 'CANCELING'); -- open
INSERT INTO "OrderStatus" ("statusId", "statusName") VALUES (14, 'SENDING'); -- open
INSERT INTO "OrderStatus" ("statusId", "statusName") VALUES (21, 'FINISHED'); -- close
INSERT INTO "OrderStatus" ("statusId", "statusName") VALUES (22, 'CANCELED'); -- close

Based on this, the partitions are defined as follows:

CREATE TABLE "Order_Open" PARTITION OF "Order" FOR VALUES FROM (10) TO (20);
CREATE TABLE "Order_Close" PARTITION OF "Order" FOR VALUES FROM (20) TO (30);

So far, everything works as expected.

Now, we are undergoing a system refactor, and one of the proposed changes is to convert the order statuses to an enum type. However, if we do this, since the numeric values of enums (enumsortorder column in pg_enum table) are defined by PostgreSQL, it seems that the trick we used for categorizing open and closed statuses will no longer be applicable.

Here are the potential solutions I have considered, each with its own explanation, but I'm not sure if they are the best approaches:

1. Add a new boolean column isOpen to indicate whether the order is open or closed, and partition the table based on this column. I feel that having an additional column for this information is not ideal.

2. Manipulate the enumsortorder values of the enum in the pg_enum table to retain our categorization trick (I haven't tried this yet to see if it's possible). This approach seems risky and not quite right to me.

3. Change the partitioning type to LIST. While this does not pose significant issues, if we need to add a new value to the enum in the future, we would have to change the table definitions, which is not appealing.

4. Abandon the idea of using enum altogether and steel use statusId of OrderStatus table. I feel that for cases where we need to partition a table based on a column, using enum might not be suitable, especially for RANGE partitioning.

Maybe there's a best practice for this situation that I'm not aware of. If anyone can provide insights or advice on this, I would greatly appreciate it.

• Laurenz Albe: I recommend that you stick with your original design then and don't use enums. I don't think they will offer an advantage for you.
• Mofarah: Thank you for your thoughtful comment. The main reason we're considering using enums is that it speeds up our developers and makes the code a bit simpler. In terms of storage space, continuing with the previous method is probably more efficient as well. Please post your comment as an answer so I can accept it, and feel free to provide any additional explanation if you see fit. Thank you again.

Answer 1

Enum is a list of values with an order you can fill a enum with dummy values and have so your ranges as needed.

Of course you don't have integers any more, instead you need to use the status texts that you have prior defined

if you need to change the status text from dummy23 to add na new status text for closed you need to update the type only

CREATE TYPE OrdersStatus AS ENUM(
 'dummy', 'dummy1','dummy2', 'dummy3','dummy4', 'dummy5','dummy6', 'dummy7','dummy8', 'dummy9'
 ,'WAITING', 'OPEN','CANCELING', 'SENDING','dummy15', 'dummy16','dummy17', 'dummy18','dummy19', 'dummy20',
 'FINISHED', 'CANCELED', 'dummy23', 'dummy24','dummy25', 'dummy26','dummy27', 'dummy28','dummy29', 'dummy30');
CREATE TABLE "Order"(
 ID integer, 
 OrderStatus OrdersStatus
) PARTITION BY RANGE (OrderStatus);

CREATE TYPE

CREATE TABLE

CREATE TABLE "Order_Open" PARTITION OF "Order" FOR VALUES FROM ('WAITING') TO ('dummy20');
CREATE TABLE "Order_Close" PARTITION OF "Order" FOR VALUES FROM ('FINISHED') TO ('dummy30');

CREATE TABLE

CREATE TABLE

INSERT INTO "Order" VALUEs (1,'OPEN')

INSERT 0 1

SELECT * FROM "Order_Open"

id	orderstatus
1	OPEN

SELECT 1

UPDATE "Order" SET OrderStatus = 'FINISHED' WHERE ID = 1

UPDATE 1

SELECT * FROM "Order_Close"

id	orderstatus
1	FINISHED

SELECT 1

fiddle