• features
• 池化

如果你正在开发一个 Web 应用或其他需要频繁查询的软件，你将需要使用连接池。

¥If you're working on a web application or other software which makes frequent queries you'll want to use a connection pool.

使用 node-postgres 的最简单且最常见的方法是通过连接池。

¥The easiest and by far most common way to use node-postgres is through a connection pool.

为什么？

¥Why?

• 将新客户端连接到 PostgreSQL 服务器需要握手，这可能需要 20-30 毫秒。在此期间，将协商密码、建立 SSL，并与客户端和服务器共享配置信息。每次我们想要执行查询时都会产生这种成本，这会大大减慢我们的应用速度。

  ¥Connecting a new client to the PostgreSQL server requires a handshake which can take 20-30 milliseconds. During this time passwords are negotiated, SSL may be established, and configuration information is shared with the client & server. Incurring this cost every time we want to execute a query would substantially slow down our application.

• PostgreSQL 服务器只能处理 一次客户端数量有限。根据你的 PostgreSQL 服务器的可用内存，如果你连接无限数量的客户端，甚至可能导致服务器崩溃。注意：很久以前，我在 python 应用中打开新客户端并且从未断开连接，导致 RDS 中一个大型生产 PostgreSQL 服务器实例崩溃。这不好玩。

  ¥The PostgreSQL server can only handle a limited number of clients at a time. Depending on the available memory of your PostgreSQL server you may even crash the server if you connect an unbounded number of clients. note: I have crashed a large production PostgreSQL server instance in RDS by opening new clients and never disconnecting them in a python application long ago. It was not fun.

• PostgreSQL 只能以先进先出的方式在单个连接的客户端上一次处理一个查询。如果你的多租户 Web 应用仅使用单个连接的客户端，则所有同时请求中的所有查询都将按流水线顺序依次执行。不好！

  ¥PostgreSQL can only process one query at a time on a single connected client in a first-in first-out manner. If your multi-tenant web application is using only a single connected client all queries among all simultaneous requests will be pipelined and executed serially, one after the other. No good!

好消息

¥Good news

node-postgres 通过 pg-pool 模块附带内置连接池。

¥node-postgres ships with built-in connection pooling via the pg-pool module.

示例

¥Examples

客户端池允许你拥有一个可重复使用的客户端池，你可以签出、使用和返回。你通常希望应用中有有限数量的客户端，通常只有 1 个。创建无限数量的池完全违背了池化的目的。

¥The client pool allows you to have a reusable pool of clients you can check out, use, and return. You generally want a limited number of these in your application and usually just 1. Creating an unbounded number of pools defeats the purpose of pooling at all.

签出、使用和返回

¥Checkout, use, and return

import pg from 'pg'
const { Pool } = pg
 
const pool = new Pool()
 
// the pool will emit an error on behalf of any idle clients
// it contains if a backend error or network partition happens
pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err)
  process.exit(-1)
})
 
const client = await pool.connect()
const res = await client.query('SELECT * FROM users WHERE id = $1', [1])
console.log(res.rows[0])
 
client.release()

单一查询

¥Single query

如果你不需要事务或只需要运行单个查询，则池有一种便捷方法可以在池中的任何可用客户端上运行查询。如果可以，这是使用 node-postgres 查询的首选方式，因为它消除了泄漏客户端的风险。

¥If you don't need a transaction or you just need to run a single query, the pool has a convenience method to run a query on any available client in the pool. This is the preferred way to query with node-postgres if you can as it removes the risk of leaking a client.

import pg from 'pg'
const { Pool } = pg
 
const pool = new Pool()
 
const res = await pool.query('SELECT * FROM users WHERE id = $1', [1])
console.log('user:', res.rows[0])

关闭

¥Shutdown

要关闭池，请在池上调用 pool.end()。这将等待所有签出的客户端返回，然后关闭所有客户端和池计时器。

¥To shut down a pool call pool.end() on the pool. This will wait for all checked-out clients to be returned and then shut down all the clients and the pool timers.

import pg from 'pg'
const { Pool } = pg
const pool = new Pool()
 
console.log('starting async query')
const result = await pool.query('SELECT NOW()')
console.log('async query finished')
 
console.log('starting callback query')
pool.query('SELECT NOW()', (err, res) => {
  console.log('callback query finished')
})
 
console.log('calling end')
await pool.end()
console.log('pool has drained')

上面的输出将是：

¥The output of the above will be:

starting async query
async query finished
starting callback query
calling end
callback query finished
pool has drained