连接

温热的连接池能够减少反复建立 PostgreSQL 连接，但空闲连接在池里等待期间也可能消失。服务器结束会话、网络故障或中间设备的空闲策略，都可能让客户端仍握着一个已经不能使用的套接字。复用方案必须包含失效识别和替换；对象还在池里，只说明应用保存了它，不能证明下一条查询一定成功。 坏掉的样子 TCP 保活能够识别原本空闲连接上的部分故障，却不会替 SQL 设置执行截止时间，也不证明数据库能够完成某个具体请求，更不会处理结果未知的提交。客户端连接参数只作用于 TCP，使用 Unix 域套接字时相关选项会被忽略；服务器端保活设置控制的是另一端。具体操作系统是否支持某个选项，也会影响最终行为，不能只看参数字符串存在就宣称故障检测已经发生。 借出前健康检查，可以在把连接交给调用方之前识别已经失效的连接。Psycopg 默认不执行这种检查，但提供回调让应用选择启用。多一次探测意味着额外往返，也仍然无法阻止连接在探测成功后立刻出故障。因此，建立连接、等待租用和执行语句，都需要各自的预算与错误处理，不能用一个“保活开启”覆盖全部阶段。 快速验证 准备 Python 3.10 或更新版本，通过 TCP 连接使用默认零值语句超时的 PostgreSQL 18，并安装本次验证版本：python3 -m pip install "psycopg[binary]==3.3.5" "psycopg-pool==3.3.1"。把 PG_DSN 设置为可丢弃数据库，使用允许结束自己后台连接的角色。保存为 liveness-demo.py 后运行。程序先记录每个测试池刚创建的连接标识，然后只结束那条已经归还、处于空闲状态的连接： import os import psycopg from psycopg.errors import QueryCanceled from psycopg_pool import ConnectionPool dsn = os.environ['PG_DSN'] with psycopg.connect(dsn, autocommit=True) as admin: for checked in (False, True): with ConnectionPool( dsn, min_size=1, max_size=1, timeout=3, open=True, check=ConnectionPool.check_connection if checked else None, kwargs={ 'connect_timeout': 2, 'keepalives': 1, 'keepalives_idle': 30, 'keepalives_interval': 10, 'keepalives_count': 3, }, ) as pool: pool.wait(timeout=3) with pool.connection() as conn: assert conn.execute('SELECT inet_server_addr() IS NOT NULL').fetchone()[0] original_pid = conn.info.backend_pid parameters = conn.info.get_parameters() assert [parameters[k] for k in ( 'keepalives', 'keepalives_idle', 'keepalives_interval', 'keepalives_count' )] == ['1', '30', '10', '3'] # Terminate only the idle connection just created by this fixture. assert admin.execute('SELECT pg_terminate_backend(%s,2000)', (original_pid,)).fetchone()[0] if not checked: try: with pool.connection() as conn: conn.execute('SELECT 1') except psycopg.OperationalError as error: print('unchecked_first_use', error.sqlstate) else: raise AssertionError('terminated connection unexpectedly worked') with pool.connection() as conn: replacement_pid = conn.info.backend_pid assert replacement_pid != original_pid assert conn.execute('SELECT 1').fetchone()[0] == 1 print('checked', checked, 'replacement', True) try: with pool.connection() as conn: conn.execute("SET LOCAL statement_timeout='50ms'") conn.execute('SELECT pg_sleep(0.2)') except QueryCanceled as error: assert error.sqlstate == '57014' print('statement_timeout', error.sqlstate) else: raise AssertionError('long statement did not time out') with pool.connection() as conn: assert conn.info.backend_pid == replacement_pid assert conn.execute('SHOW statement_timeout').fetchone()[0] == '0' assert conn.execute('SELECT 1').fetchone()[0] == 1 print('healthy_after_rollback', True) 两种情况都配置了客户端保活参数，并核对有效值，不打印整个连接串。第一种没有借出前检查。空闲后端被服务器结束后，下次读取遭遇操作错误；本次这种明确的服务器关闭返回 57P01。池随后丢弃坏连接，下一次借用得到不同的后端标识，并成功返回一。 ...

连接策略负责组合两路输入，并不是某段 SQL 永久绑定的属性。只查询三个客户时，通过索引反复定位购买记录可能很合算；查询所有购买记录时，扫描一遍并构建哈希表可能更合适。PostgreSQL 根据预计工作、可用访问路径、顺序和内存预期，在合法候选中选择。表有多大确实重要，但过滤后还剩多少行，比表的名字或它平时被称为“大表”更重要。 反复查找与复用查找表 嵌套循环每取一条外层记录，就利用这条记录执行一次内层路径。内层并不一定扫描整张表，也可以进行索引或位图查找。外层结果很少、内层定位足够精确时，重复执行的成本可能很低。读计划时，应把内层节点的每轮行数与 loops 放在一起看，才能知道实际重复了多少工作，而不是看到循环就直接判断它低效。 哈希连接从一路输入建立临时哈希表，再用另一边的记录探测匹配。这是执行期间的数据结构，与 USING hash 建立的持久索引没有直接关系；不需要先建一个哈希索引才能使用哈希连接。真正的构建侧是计划中 Hash 节点下方的子树。应查看这个位置，不能假设 SQL 里写在左边或右边的表必然负责构建，也不能只比较两张基础表的原始大小。 如果两路输入已经具备适当顺序，或者排序的成本值得支付，合并连接也是候选。因此，嵌套循环和哈希连接并没有穷尽规划器的全部选择。不同过滤条件、输出要求和合法连接条件，都可能改变哪些方案值得考虑。算法名称只是计划的一部分，不能代替对输入与结果规模的分析。 坏掉的样子 外层结果被低估时，原本看似便宜的单次内层查找会被重复过多次数。哈希连接也可能因为超出内存预算而分成多批，产生临时读写，并不保证始终完整留在内存中。应检查批次数和临时缓冲区活动，不能看见 Hash 就推导出“恒定的单次查找成本”或“所有规模下都线性且很快”。 大量重复连接键还可能产生巨大的结果集合，无论使用哪种查找策略都需要产生查询要求的那些行。意外的多对多连接，应通过修正关系或查询语义解决，而不是只把连接节点换个名字。如果两侧每个键都重复很多次，乘出来的输出规模本身就可能是主要瓶颈。 原生 PostgreSQL 不会解释 /*+ HashJoin */ 这样的优化器提示注释。扩展可以提供提示功能，但那属于另外的环境契约。规划器开关可以支持受控对比实验，不过不能因为一次小数据实验，就永久在全局关闭某种连接方式；其他查询的输入和收益可能完全不同。 快速验证 在 PostgreSQL 18 的 psql 中执行下面事务。它创建五百个客户和五万条购买记录，每个客户对应一百条购买记录，并在引用列上建立普通 B-tree 索引。两条查询计算同一种连接关系的金额总和，区别只在于是否限制客户范围。 \set ON_ERROR_STOP on BEGIN; CREATE TEMP TABLE customers (id integer PRIMARY KEY); CREATE TEMP TABLE purchases (id integer PRIMARY KEY,customer_id integer,amount integer); INSERT INTO customers SELECT g FROM generate_series(1,500) AS g; INSERT INTO purchases SELECT g,1+(g-1)%500,10 FROM generate_series(1,50000) AS g; CREATE INDEX purchases_customer ON purchases(customer_id); ANALYZE customers; ANALYZE purchases; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id WHERE c.id<=3; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id; DO $$ BEGIN IF (SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id WHERE c.id<=3) IS DISTINCT FROM 3000::bigint THEN RAISE EXCEPTION 'wrong selective join'; END IF; IF (SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id) IS DISTINCT FROM 500000::bigint THEN RAISE EXCEPTION 'wrong broad join'; END IF; END $$; ROLLBACK; PostgreSQL 18.6 实测中，限定客户编号不超过三时采用嵌套循环。内层位图堆扫描每轮返回一百条购买记录，一共执行三轮。连接输出三百行，金额总和为三千。嵌套循环里出现位图扫描是正常组合：扫描节点描述内层记录怎样被取到，连接节点描述怎样反复利用外层记录组合结果，两者不是互斥选项。 ...

连接池让许多应用请求在不同时间借用较少的 PostgreSQL 会话。复用能够摊薄建立连接的成本，也能限制同时借出的数量，但不会让已经过载的数据库自动变快。池本身又增加了一条队列，它的容量、等待时间和取消行为，都属于请求预算的一部分。应把这条队列作为服务行为来设计，而不是把它隐藏在一个“复用更快”的标签后面。 坏掉的样子 数据库后端处于 ClientRead，表示正在等待客户端发数据，并不表示应用请求正在等待借用连接。真正的池内等待者可能还没有数据库连接，因此服务器根本没有对应的后端可供观察。获取连接的等待应在池里测量，查询执行和锁等待应在服务器测量；把它们混成一个所谓数据库池等待事件，会掩盖实际瓶颈的位置。 归还连接也不保证抹掉所有会话状态。Psycopg 的连接上下文在正常退出时提交，在异常离开上下文时回滚；额外重置取决于配置。已经提交的会话级设置，可以继续留给下一个借用者，即使没有泄漏未结束的事务。临时对象和其他会话资源同样需要明确策略，不能因为使用了连接池，就假定所有内容都会自动恢复到初始状态。 快速验证 准备 Python 3.10 或更新版本、PostgreSQL 18 可丢弃数据库，以及允许创建实验模式的角色。安装本次验证过的依赖：python3 -m pip install "psycopg[binary]==3.3.5" "psycopg-pool==3.3.1"。使用已有认证配置，将 PG_DSN 设置为该测试数据库的连接串。把程序保存为 pool-demo.py，在安装依赖的同一个 Python 环境中运行： import os import time from concurrent.futures import ThreadPoolExecutor import psycopg from psycopg_pool import ConnectionPool, PoolTimeout, TooManyRequests dsn = os.environ['PG_DSN'] with psycopg.connect(dsn, autocommit=True) as admin: admin.execute('CREATE SCHEMA pool_demo') try: admin.execute('CREATE TABLE pool_demo.counter (n integer)') admin.execute('INSERT INTO pool_demo.counter VALUES (0)') with ConnectionPool( dsn, min_size=1, max_size=1, max_waiting=1, timeout=3, open=True, kwargs={'application_name': 'pool_example'}, ) as pool: pool.wait(timeout=3) def queued_request(): try: with pool.connection(timeout=1): raise AssertionError('holder should still own the only connection') except PoolTimeout: return 'PoolTimeout' with ThreadPoolExecutor(max_workers=1) as workers: with pool.connection() as held: original_pid = held.info.backend_pid waiting = workers.submit(queued_request) deadline = time.monotonic() + 0.5 while pool.get_stats()['requests_waiting'] != 1: if time.monotonic() >= deadline: raise TimeoutError('request did not enter the pool queue') time.sleep(0.005) state = admin.execute( 'SELECT state,wait_event_type,wait_event ' 'FROM pg_stat_activity WHERE pid=%s', (original_pid,) ).fetchone() assert state == ('idle', 'Client', 'ClientRead'), state print('waiting_in_pool', 1, 'server', state) try: with pool.connection(timeout=0.1): raise AssertionError('queue limit was not applied') except TooManyRequests: print('queue_limit', 'TooManyRequests') assert waiting.result(timeout=2) == 'PoolTimeout' print('queued_request', 'PoolTimeout') with pool.connection() as conn: assert conn.info.backend_pid == original_pid conn.execute("SET application_name='previous_borrower'") try: with pool.connection() as conn: conn.execute('UPDATE pool_demo.counter SET n=99') raise ValueError('simulated request failure') except ValueError as error: assert str(error) == 'simulated request failure' with pool.connection() as conn: value = conn.execute('SELECT n FROM pool_demo.counter').fetchone()[0] setting = conn.execute('SHOW application_name').fetchone()[0] assert value == 0 and setting == 'previous_borrower' assert conn.info.backend_pid == original_pid print('same_backend', True, 'rollback_value', value, 'retained_setting', setting) conn.execute('RESET application_name') finally: admin.execute('DROP SCHEMA pool_demo CASCADE') 池里只有一条连接，最多允许一个请求排队。主线程借走连接但不执行查询，工作线程尝试借用它。池报告一个等待者以后，独立观察连接看到被借走的数据库后端正空闲等待客户端读取事件。两种状态可以同时存在：应用已经排队，数据库会话却没有正在执行的查询。这正好说明为什么不能用服务器的空闲状态代替池的等待指标。 ...

连接上限约束的是某个资源所有者的需求，不会自动约束共享数据库的所有应用。真正有用的预算，是每台后端可能接收到的物理连接总和，其中也包括连接池里暂时空闲的连接。先确认后端可供普通业务使用的名额，再分配到所有调用方与可能重叠的部署状态，而不是给每个服务随手填一个相同数字。 先数清有多少池，再决定每个池多大 对有限配置的 SQLAlchemy QueuePool，同时连接数上限是 pool_size + max_overflow，但这个数字只属于一个池。各自创建独立 QueuePool 的 Engine 才有独立上限；多个工作进程、多个副本，还会继续放大总数。应按真实池计数，派生的 Engine 视图可能仍然共用原池。如果配置相同，可用副本数乘每副本进程数，再乘每进程池数和单池上限；不同调用方的配置不同，则应逐项相加，不能假设它们共享同一份限额。 部署预算应使用最大重叠数量，而不是滚动更新结束后的就绪副本数。新副本已经建连时，旧副本可能仍在排空业务，也可能还保留空闲连接。后台任务、迁移工具、监控连接和其他服务也必须进入清单。不同数据库服务器需要各自计算预算；把主库、其他目标的连接数合成一个看起来宽裕的总量，会掩盖真正受限的后端。 PostgreSQL 的 max_connections 是服务器总上限，并不全部属于某个应用。没有预留名额权限的普通角色，应当为 superuser_reserved_connections 和 reserved_connections 都留出空间；后者提供给具有 pg_use_reserved_connections 权限的角色。还要扣除其他普通客户端和额外运维余量，不应让日常流量依赖紧急预留容量才能运行。这里区分的是权限与预算，不能仅凭连接名字叫“运维”就认定它可以使用某种预留名额。 坏掉的样子 服务完全遵守每个本地池的上限，仍然可能把数据库用满，因为实际池数量超过了预算表里的假设。若允许无限溢出，有限预算更是直接失去依据。反过来，上限过小也可能让应用在后端仍有余量时排队；看到这种等待，并不能直接断定必须增大数据库服务器的总上限。 更多连接不会创造额外 CPU、加速存储，也不会消除事务之间的锁冲突。PostgreSQL 的部分资源分配还直接取决于配置的连接上限，调高它也有资源代价。应同时观察获取连接的等待、实际物理连接数、活跃工作量和事务时长。连接数量受到约束，不代表吞吐一定提高，也不保证服务之间公平，更不能保证锁等待变短。 外部连接池代理会再增加一层计数边界。PgBouncer 的会话池化让服务器连接随客户端会话保留；事务池化则在事务结束后归还服务器连接。因此客户端连接数不能直接当成后端会话数。预算清单需要包含代理的服务器侧分配与池化模式。事务模式会改变部分会话特性的使用前提，引入它不是只修改一个容量数字，还需要核对应用是否依赖那些特性。 快速验证 在 macOS 或 Linux 的空目录执行 python3 -m venv .venv，然后执行 .venv/bin/python -m pip install SQLAlchemy==2.0.52。把代码保存为 pool_probe.py，运行 .venv/bin/python pool_probe.py。本次验证使用 Python 3.14.4 和 SQLite 3.53.0。这里的三个真实连接池位于同一个进程，共用临时本地数据库文件，并没有创建三个生产副本。 ...