python - SQLAlchemy "default"与 "server_default"性能对比

Question

使用 default 时是否有性能优势(或劣势)而不是 server_default用于在将 SQLAlchemy 与 PostgreSQL 一起使用时映射表列默认值？

我的理解是 default 呈现 INSERT 中的表达式(通常)和那个 server_default 将表达式放在 CREATE TABLE 中陈述。好像server_default类似于直接在数据库中对默认值的典型处理，例如:

CREATE TABLE example (
    id serial PRIMARY KEY,
    updated timestamptz DEFAULT now()
);

...但我不清楚在 INSERT 上处理默认值是否更有效或通过表创建。

如果 default 中的每一个，行插入是否会有任何性能改进或降级？以下示例中的参数更改为 server_default ?

from uuid import uuid4
from sqlalchemy import Column, Boolean, DateTime, Integer
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func

Base = declarative_base()

class Item(Base):
    __tablename__ = 'item'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    count = Column(Integer, nullable=False, default=0)
    flag = Column(Boolean, nullable=False, default=False)
    updated = Column(DateTime(timezone=True), nullable=False, default=func.now())

注意:迄今为止我找到的关于何时使用 default 的最佳解释而不是 server_default不解决性能问题 ( see Mike Bayer's SO answer on the subject )。我对该解释的过于简化的总结是 default优于 server_default什么时候...

db 无法处理您需要或想要用于默认值的表达式。

您不能或不想直接修改架构。

...所以问题仍然是在 default 之间进行选择时是否应该考虑性能和 server_default ?

Answer 1

不可能给你一个“这更快”的答案，因为性能 每个默认值表达式 可以在服务器和 Python 中变化很大。检索当前时间的函数的行为与标量默认值不同。

接下来，您必须意识到可以在 中提供默认值。五 不同的方式:

Client-side scalar defaults .一个固定值，例如 0或 True .该值用于 INSERT陈述。

Client-side Python function .每次需要默认值时调用，生成要插入的值，从那时起使用与标量默认值相同的方式。这些可以是 context sensitive (可以访问带有要插入的值的当前执行上下文)。

Client-side SQL expression ;这会生成一个额外的 SQL expression然后在查询中使用并在服务器上执行以生成值。

Server-side DLL expression是 SQL 表达式，然后存储在表定义中，因此是架构的一部分。服务器使用这些来填充 INSERT 中省略的任何列的值。语句，或者当列值设置为 DEFAULT 时在 INSERT或 UPDATE陈述。

Server-side implicit defaults or triggers ，其中其他 DLL(例如触发器或特定数据库功能)为列提供默认值。

请注意，当涉及确定默认值的 SQL 表达式时，无论是客户端 SQL 表达式、服务器端 DLL 表达式还是触发器，它与默认值表达式来自的数据库几乎没有区别.查询执行器需要知道如何为给定的列生成值，一旦从 DML 语句或模式定义中解析出来，服务器仍然必须为每一行执行表达式。

在这些选项之间进行选择很少会仅基于性能，性能最多应该只是您考虑的多个方面之一。这里涉及到很多因素:

default使用标量或 Python 函数直接生成 Python 默认值，然后在插入时将新值发送到服务器。在将数据插入数据库之前，Python 代码可以访问默认值。

一个客户端 SQL 表达式，一个 server_default值，服务器端隐式默认值和触发器都让服务器生成默认值，如果您希望能够在同一个 SQLAlchemy session 中访问它，那么必须由客户端获取它。在将对象插入数据库之前，您无法访问该值。

根据确切的查询和数据库支持，SQLAlchemy 可能需要进行额外的 SQL 查询，以在 INSERT 之前生成默认值。声明或运行单独的 SELECT之后获取已插入的默认值。您可以控制何时发生这种情况(直接在插入时或在刷新后第一次访问时，使用 eager_defaults mapper configuration )。

如果您在不同平台上有多个客户端访问同一数据库，则 server_default或附加到模式的其他默认值(例如触发器)确保所有客户端都将使用相同的默认值，无论如何，而其他平台无法访问在 Python 中实现的默认值。

使用 PostgreSQL 时，SQLAlchemy 可以利用 the RETURNING clause for DML statements ，这让客户端只需一步即可访问服务器端生成的默认值。

所以当使用 server_default列默认为每一行计算一个新值(不是标量值)，您可以节省少量 Python 端时间，并节省少量网络带宽，因为您不会将该列的数据发送到数据库。数据库可以更快地创建相同的值，也可以更慢；这在很大程度上取决于操作的类型。如果您需要从 Python 访问生成的默认值，则在同一事务中，您必须等待由 SQLAlchemy 解析出的返回数据流。然而，与围绕插入或更新行发生的所有其他事情相比，所有这些细节都可能变得微不足道。

请了解 ORM 不适合用于高性能批量行插入或更新；引自 SQAlchemy Performance FAQ entry :

The SQLAlchemy ORM uses the unit of work pattern when synchronizing changes to the database. This pattern goes far beyond simple “inserts” of data. It includes that attributes which are assigned on objects are received using an attribute instrumentation system which tracks changes on objects as they are made, includes that all rows inserted are tracked in an identity map which has the effect that for each row SQLAlchemy must retrieve its “last inserted id” if not already given, and also involves that rows to be inserted are scanned and sorted for dependencies as needed. Objects are also subject to a fair degree of bookkeeping in order to keep all of this running, which for a very large number of rows at once can create an inordinate amount of time spent with large data structures, hence it’s best to chunk these.

Basically, unit of work is a large degree of automation in order to automate the task of persisting a complex object graph into a relational database with no explicit persistence code, and this automation has a price.

ORMs are basically not intended for high-performance bulk inserts - this is the whole reason SQLAlchemy offers the Core in addition to the ORM as a first-class component.

因为像 SQLAlchemy 这样的 ORM 带来了高昂的开销，服务器端或 Python 端默认值之间的任何性能差异都会在 ORM 操作的噪音中迅速消失。

因此，如果您担心大量插入或更新操作的性能，您可能希望使用 bulk operations对于那些，并启用 psycopg2 batch execution helpers真正获得速度提升。使用这些批量操作时，我希望服务器端默认值仅通过节省将行数据从 Python 移动到服务器的带宽来提高性能，但多少取决于默认值的确切性质。

如果批量操作之外的 ORM 插入和更新性能对您来说是一个大问题，您需要测试您的特定选项。我会从 SQLAlchemy examples.performance package 开始和 add your own test suite使用仅在一个方面不同的两个模型 server_default和 default配置。