PostgreSQL 是否像 Oracle 一样缓存 Prepared Statements [英] Does PostgreSQL cache Prepared Statements like Oracle

问题描述

在 Oracle 工作了几年后，我刚刚转向 PostgreSQL.我一直在研究使用 PostgreSQL 数据库的应用程序(Java、JDBC)中准备好的语句的一些性能问题.

I have just moved to PostgreSQL after having worked with Oracle for a few years. I have been looking into some performance issues with prepared statements in the application (Java, JDBC) with the PostgreSQL database.

Oracle 在其 SGA 中缓存准备好的语句 - 准备好的语句池在数据库连接之间共享.

Oracle caches prepared statements in its SGA - the pool of prepared statements is shared across database connections.

PostgreSQL 文档似乎没有说明这一点.这是文档中的片段(https://www.postgresql.org/文档/当前/静态/sql-prepare.html) -

PostgreSQL documentation does not seem to indicate this. Here's the snippet from the documentation (https://www.postgresql.org/docs/current/static/sql-prepare.html) -

准备好的语句只持续当前数据库的持续时间会议.当会话结束时，准备好的语句被遗忘，所以在再次使用之前必须重新创建它.这也意味着一个准备好的语句不能被多个同时使用数据库客户端；然而，每个客户都可以创建自己的准备要使用的语句.

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use.

我只是想确保我理解这一点是正确的，因为对于数据库来说，实现某种常用的准备好的语句的公共池似乎非常基础.

I just want to make sure that I am understanding this right, because it seems so basic for a database to implement some sort of common pool of commonly executed prepared statements.

如果 PostgreSQL 不缓存这些，则意味着每个需要大量数据库事务的应用程序都需要开发某种可以跨连接重用的准备好的语句池.

If PostgreSQL does not cache these that would mean every application that expects a lot of database transactions needs to develop some sort of prepared statement pool that can be re-used across connections.

如果您以前使用过 PostgreSQL，我将不胜感激.

If you have worked with PostgreSQL before, I would appreciate any insight into this.

推荐答案

是的，你的理解是正确的.通常，如果您有一组非常重要的准备好的查询，那么您可以让应用程序调用自定义函数来在连接时设置它们.

Yes, your understanding is correct. Typically if you had a set of prepared queries that are that critical then you'd have the application call a custom function to set them up on connection.

出现这种错误的主要原因有以下三个:

There are three key reasons for this afaik:

1. 有一个很长的待办事项列表，当开发人员有兴趣/付费来解决它们时，它们就会完成.大概没有人认为值得资助或想出一种有效的方法.

1. There's a long todo list and they get done when a developer is interested/paid to tackle them. Presumably no-one has thought it worth funding yet or come up with an efficient way of doing it.

PostgreSQL 在比 Oracle 更广泛的环境中运行.我猜 99% 的已安装系统不会从中受益.有很多设置没有高事务性能要求，或者 DBA 需要注意它是否需要.

PostgreSQL runs in a much wider range of environments than Oracle. I would guess that 99% of installed systems wouldn't see much benefit from this. There are an awful lot of setups without high-transaction performance requirement, or for that matter a DBA to notice whether it's needed or not.

计划查询并不总能带来胜利.在延迟规划/使缓存失效方面做了大量工作，以尽可能地适应实际数据和查询参数.

Planned queries don't always provide a win. There's been considerable work done on delaying planning/invalidating caches to provide as good a fit as possible to the actual data and query parameters.

我怀疑添加此类内容的最佳位置是在其中一个连接池 (pgbouncer/pgpool) 中，但我上次检查时没有这样的功能.

I'd suspect the best place to add something like this would be in one of the connection pools (pgbouncer/pgpool) but last time I checked such a feature wasn't there.

HTH

这篇关于PostgreSQL 是否像 Oracle 一样缓存 Prepared Statements的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！