A New Collection of Thoughtful Learning Apps — Now Available on iOS & Android

By -
Image
I’m excited to share a set of mobile apps I’ve recently completed and published on both the Google Play Store and the Apple App Store. These apps are designed with a simple goal in mind: to make meaningful, structured content more accessible, whether you’re studying theology or improving your English vocabulary. 📱 Now Available on Both Platforms All apps are live and available for download: Google Play Developer Page: https://play.google.com/store/apps/dev?id=5835943159853189043 Apple App Store Developer Page: https://apps.apple.com/ca/developer/q-z-l-corp/id1888794100 📖 Theology & Confession Study Apps For those interested in Reformed theology and classical Christian teachings, I’ve developed a series of apps that present foundational texts in a clean, focused reading format: The Belgic Confession Canons of Dort Heidelberg Catechism Westminster Shorter Catechism Each app is designed to provide a distraction-free experience, making it easier to read, reflect, and revisit these im...
Post a Comment

为什么 Vercel 部署后 PostgreSQL 仍然存在 Idle 连接

By -

为什么 Vercel 部署后 PostgreSQL 仍然存在 Idle 连接



如果你在 Vercel 上运行 Next.js， 并且直接连接 PostgreSQL，你很可能遇到过下面的错误： 

remaining connection slots are reserved for roles with the SUPERUSER attribute

这个问题通常在重新部署后更容易出现，即使你的应用流量并不高。

常见现象

  • PostgreSQL 中存在大量 idle 状态的连接
  • Vercel 实例已经关闭，但连接仍未释放
  • 新请求无法获取数据库连接
  • 手动终止连接只能暂时缓解问题

真正的原因

这是 Serverless 架构的正常行为。

Vercel 的工作方式大致如下：

  1. Vercel 根据请求启动多个 Serverless 实例
  2. 每个实例都会创建自己的数据库连接或连接池
  3. 实例结束后，无法保证数据库连接被优雅关闭
  4. PostgreSQL 会继续保留这些连接直到超时

结果就是：应用已经结束，但数据库连接仍然存在。

为什么会导致连接数耗尽

在托管 PostgreSQL 服务中，一部分连接被保留给系统和 SUPERUSER。

当 Serverless 实例频繁创建连接时，很容易用完普通用户可用的连接数， 从而触发该错误。

这不是数据库 bug，而是架构不匹配导致的问题。

PgBouncer 如何解决

PgBouncer 位于应用和数据库之间： 

Vercel → PgBouncer → PostgreSQL
  • 应用连接 PgBouncer，而不是直接连接数据库
  • PgBouncer 使用少量真实数据库连接服务大量请求
  • Serverless 实例关闭时，连接会被立即回收
  • PostgreSQL 不再堆积 idle 连接

对于 Serverless 场景，推荐使用 transaction pooling 模式。

推荐配置

  • 启用 PgBouncer
  • 应用连接 PgBouncer 的 host 和 port
  • Node.js 连接池设置为 1–2
  • idleTimeoutMillis 设置为 5 秒左右
  • 设置 idle_in_transaction_session_timeout

总结

这个问题看起来像是 Vercel 的问题，但实际上是 Serverless + 直连数据库 的经典陷阱。

如果你在 Vercel 上使用 PostgreSQL， PgBouncer 是必需的，而不是可选的。

❤️ Support This Blog

If this post helped you, you can support my writing with a small donation. Thank you for reading.

Coffee ☕ Thanks 🙏 Extra Support ❤️

Comments

Post a Comment

Popular Posts

2023, New Start! New AdSense Account! New Earnings!

By -
Image
It's been a while after I created adsense account to display ads on this blog. A few years later the payment finally reached my payout threshold, I just found the existed adsense account is bind to China. but I moved to Canada 3 years ago. I tried to change the country of it,  but just found   Unfortunately it's not possible to change the country of your payment address in AdSense. If you've moved to a new country or territory, you'll need to  cancel your existing AdSense account  and  create a new AdSense account .            So I created a new adsense account today on 2023-01-12. Though new account is still under reviewing, I believe it will be approved soon. To reach out payout threshold in this year, I set up the below plan: write one blog per week day!!! write one blog per week day!!! write one blog per week day!!! Try ingress more traffic to this blog
Read more

A New Collection of Thoughtful Learning Apps — Now Available on iOS & Android

By -
Image
I’m excited to share a set of mobile apps I’ve recently completed and published on both the Google Play Store and the Apple App Store. These apps are designed with a simple goal in mind: to make meaningful, structured content more accessible, whether you’re studying theology or improving your English vocabulary. 📱 Now Available on Both Platforms All apps are live and available for download: Google Play Developer Page: https://play.google.com/store/apps/dev?id=5835943159853189043 Apple App Store Developer Page: https://apps.apple.com/ca/developer/q-z-l-corp/id1888794100 📖 Theology & Confession Study Apps For those interested in Reformed theology and classical Christian teachings, I’ve developed a series of apps that present foundational texts in a clean, focused reading format: The Belgic Confession Canons of Dort Heidelberg Catechism Westminster Shorter Catechism Each app is designed to provide a distraction-free experience, making it easier to read, reflect, and revisit these im...
Read more

Swagger annotations for API that allows downloading files as zip

By -
Image
  I have built out an API that allows you downloading log files as zip in a sprint boot app. The app also integrated with Swagger 3, so I also want this API can be hit in the Swagger UI. In this post, I will demonstrate how to use @ApiOperation annotation to tell Swagger UI download the response as a zip file. use @ApiOperation for your method The key is set media type to  "application/octet-stream"  which can be recognized by Swagger UI. By doing so, you are set "accept":" application/octet-stream " in the request header. Put below annotation section on top of your API's controller method. @Operation( summary = "", description = "", method="GET", responses = { @ApiResponse(responseCode = "200", description = "", content = { @Content( mediaType = "application/octet-stream" ...
Read more