Postgresql枚举类型有哪些优缺点？

Question

Postgresql枚举类型有哪些优缺点？

databasepostgresql

48

我工作的地方使用PostgreSQL数据库（目前是8.3版本，即将迁移到8.4版本）。目前有一个小辩论关于在数据库中使用枚举。个人而言，我不喜欢数据库中的枚举类型。它会将应用程序逻辑放置在数据库中，并且可能导致代码和数据之间的不匹配。

我想知道除了可读性之外，PostgreSQL枚举类型的优点是什么？还有哪些缺点？

- Jasper Floor

1

为什么在DBMS中拥有业务（/应用）逻辑是不好的呢？其坏处很明显：如果您需要通过JDBC连接直接使用数据或使用另一个前端，而您的业务逻辑位于DBMS中，则无需进行任何更改，您将不必打破DRY原则。 - Mihail Gershkovich

7个回答

11

在PostgreSQL 13中，btree索引现在支持去重。如果我们采用以下真实世界的例子，在日志表中使用ENUM来表示HTTP方法，该表有1亿行：

public | test_http_enum_idx | index | postgres | test | permanent   | 789 MB  | 
public | test_http_test_idx | index | postgres | test | permanent   | 789 MB  |

我们可以看到，两者的索引大小相同。对于一个非规范化的表来说，每行节省几个字节并不能弥补其劣势。

PG 13+的经验法则: 使用ENUM约束列为一组固定/静态值；不要使用它们来节省磁盘空间。

可能有例外: 如果静态值的ENUM将帮助您避免昂贵的JOIN或FK-请继续;只需确保避免过早优化，并在生产中测量您的结果。

~~在做出决策时，请考虑到像Metabase这样的流行BI工具不支持枚举过滤，但是它们可以在TEXT列上正常工作。~~ @solaris：报告称，Metabase 0.42.1及更高版本支持对ENUM值进行过滤。

- Jeff

但是在BI工具中，ENUM字段不是像文本字段一样处理的吗？索引大小与实际表大小有什么关系？ - Mihail Gershkovich

@MihailGershkovich：索引和表都占用实际磁盘空间，但会分别报告。您可能会看到一个表的总大小表示，其中可能包括索引的大小，这取决于底层查询。每个BI工具都不同，有些将以不同方式处理ENUM，并在搜索和过滤时提供“下拉”而不是文本字段，而其他工具可能根本不处理它，如Metabase。 - Jeff

我们有一些表格，通过使用ENUMS将其真实大小从~64 GB降至<21，并且我们删除了一些查找表。现在最终的表格更加规范化，查询速度也变得更快（由于表格大小的减小）。如果您查看仅使用索引的查询，则不会有影响。我们拥有混合OLTP + OLAP环境，即使对于OLTP，我们通常一次提取数百行数据。对我们来说，ENUMS真正产生了巨大的影响。 - Mihail Gershkovich

@MihailGershkovich：有趣。这是哪个版本的PostgreSQL？您是否删除/重新创建索引以利用新的BTREE去重功能？ - Jeff

1

我刚刚在Metabase（v0.42.1）中成功地按ENUM列进行了筛选。它为我提供了一个多选下拉菜单以进行筛选，因此似乎具有适当的值发现功能。 - solaris

@solaris：谢谢，我已经更新了我的答案以反映这一点。 - Jeff

11

枚举类型将 int 类型和字符串类型的优势结合起来：它们像 int 类型一样小巧快速，又像字符串一样易读，此外还具有额外的安全性（您无法拼错枚举类型）。

但是，如果您不关心可读性，那么 int 类型就跟枚举类型一样好用。

- Denise

5

作为优势，您还可以进行数据库检查，以确保在列中没有其他枚举值。对我来说最大的缺点是，只能通过将值添加到末尾来修改枚举，但自从Postgres 9.1以来，这已经过去了：https://dev59.com/0nI-5IYBdhLWcg3wm5lH#7834949

- Grigory Kislin

1

优点

减少存储空间：当定义了255个或更少的ENUM元素时，Postgres每个元组只使用1个字节，对于256~65535个元素则使用2个字节。这是因为，Postgres不是存储值的常量文字，而是存储该值在有序集合中的索引。对于非常大的表格，这可能会节省大量的存储空间。
任意排序：

CREATE TABLE opening_hours(
    week_day ENUM ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'),
    opening_time TIME,
    closing_time TIME
);

如果按照星期几排序，它将按照您指定的顺序排序，在上述情况下非常方便。

廉价约束：枚举检查只有某些值以廉价的方式添加，而不是在应用程序代码或一些复杂的数据库约束中进行检查。

缺点

选项列表无法由最终用户控制，因为ENUM是模式的一部分
需要额外的查询才能查看选项列表
字符串操作和函数不能在ENUM上工作这是由于ENUM是与内置数据类型（如NUMERIC或TEXT）不同的单独数据类型。可以通过在操作时将ENUM值转换为TEXT来克服此问题。但是，在使用ORM时可能会很麻烦。

- villy393

如果你理解了Web应用程序或GUI用户是最终用户 - 那么创建一个可以管理ENUM的简单小程序有什么难度呢？如果你在谈论DBMS角色，那么授权他们访问并进行操作有什么问题呢？唯一的借口就是缺乏知识...但如果这是问题，可能就不应该处理数据。 - Mihail Gershkovich

3

Postgres 14的文档指出，枚举类型占用4个字节的磁盘空间：https://www.postgresql.org/docs/14/datatype-enum.html - Morteza Milani

这个答案中的优点部分似乎已经过时，截至PG 13。不过在缺点方面有一些好观点。 - Jeff

0

我更喜欢使用带有外键的文本字段，而不是枚举字段或没有外键的文本字段。

带有外键的文本字段的优点：

查找返回文本值，无需连接。
插入强制执行引用完整性。
定义外键可能值的表可以轻松修改。

缺点：

与枚举字段相比，文本字段需要额外的存储空间。在大多数情况下，这不太可能成为问题。
插入需要额外的查找来强制执行引用完整性。在大多数情况下，这不太可能成为问题。

示例：

create table example_table_example_type (
  example_type text primary key
);

create table example_table (
  example_type text not null references example_table_example_type(example_type),
  ...other fields...
);

- Monica For CEO

-4

问题在于，如果允许应用程序执行DDL操作，它们更有可能导致阻塞或冲突。最好将DDL操作离线执行，即在单用户模式下执行。

- Simon Clinch

2

这个回答与真正的问题无关，甚至与PostgreSQL更无关。PostgreSQL在运行简单事务时非常擅长运行DDL。实际上，许多其他数据库也是如此。你只需要知道如何做。 - Mihail Gershkovich

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mark · Accepted Answer

枚举类型的优点包括：

性能更好。您可以直接显示核心表中获取的内容，而不需要有一个单独的查找表来将代码转换为值，或者有应用程序逻辑来将代码转换为值。这在数据仓库应用程序中尤其有用。
编写即席SQL更容易

缺点包括：

将显示值编码到数据库DDL中是不好的做法。如果您在应用程序代码中将枚举值转换为不同的显示值，那么您将失去使用枚举的许多优势。
添加值需要DDL更改
使语言本地化变得困难
降低了数据库的可移植性