英文:
How to identify unused Postgres views?
问题
为了进行数据库清理工作，希望能够识别哪些 Postgres 15 数据库视图在一段时间内没有被查询。
虽然我已经找到了如何识别在 Postgres 15 中未使用的表和索引的方法，但我还没有找到一种方法来识别视图（包括物化视图和非物化视图）。
英文:
In an effort to do database house cleaning, would like to be able to identify which Postgres 15 database views have not been queried in some time.
While I have found how to identify unused tables and indexes in Postgres 15, I have not found a way to do this with views (materialized and non-materialized views)
答案1
得分: 0
由于在 PostgreSQL 15 中，视图作为查询模板具有独特的性质，因此需要采取战略性的方法来识别未使用的视图。视图不像表格那样具有单独的数据存储，因此需要特定于视图的评估策略。通过设置 log_statement = 'all'，可以提供有关很少使用的视图的信息，从而提供了查询活动的全面图像。发现视图使用模式涉及使用诸如 pgBadger 或 pgFouine 这样的程序来解析和分析日志。尽管没有精确的使用信息，但 pg_stat_user_functions 视图提供了有关用户函数的见解，特别是与视图相关的函数。在视图表格中，添加一个名为 "accessed_at" 的时间戳列，并在每次查询时更新它，可以提供有关积极跟踪的有机使用见解，但前提是需要进行编码更改。
应用程序的源代码、报告和查询都可以进行手动分析，以获得可操作的上下文特定见解。探索第三方监控工具也可以包括评估视图的实用性，从而制定精确和准确地找到未充分利用的视图的全面策略。
英文:
Due to the distinct nature of views as query templates in PostgreSQL 15, identifying unused views requires a strategic approach. Views don't have separate data storage like tables do, so an evaluation strategy that is specific to views is required. A comprehensive picture of query activity is provided by setting log_statement = 'all', which may provide information about rarely used views. Uncovering patterns of view usage involves parsing and analysing logs using programmes like pgBadger or pgFouine. Despite not having precise usage information, the pg_stat_user_functions view offers insights into user functions, especially those connected to views. In view tables, adding a "accessed_at" timestamp column and updating it during each query can provide organic usage insights for proactive tracking, subject to coding changes.
Applications' source code, reports, and queries can all be manually analysed to yield actionable context-specific insights. Exploring third-party monitoring tools may also include evaluating the usefulness of the views, leading to a thorough strategy to precisely and accurately find underutilised views.
答案2
得分: 0
这种方法最终效果很好：
- 通过设置Postgres的设置
log_statement = 'all'来记录执行的所有查询。这将在文件系统上生成基于文件的日志。 - 等待足够长的时间，例如30天，视图预计会被应用程序、最终用户等访问。
- 将日志文件导入临时表中。
- 从临时表中执行左连接到
INFORMATION_SCHEMA.views表，比较所有数据库的视图（这些视图都在元数据表INFORMATION_SCHEMA.views中找到）与在过去30天内访问的视图名称列表。对于在导入到临时表的日志数据中找不到的任何视图，这些视图在过去30天内未被访问/使用，可能可以移除。
英文:
This method ended up working well:
- Log all queries that are executed by setting the Postgres setting
log_statement = 'all'. This will generate file-based logs on the file system. - Wait a sufficient period of time e.g. 30 days where a view is expected to be accessed were it still used by the application, end user, etc.
- Import logs file into a temp table
- Do a left join from this table
INFORMATION_SCHEMA.viewsto the temp table, comparing all of the databases’s views (which are all found in the metadata tableINFORMATION_SCHEMA.views) to the list of view names that were accessed in the last of 30 days. For any views not found in the logs data that you imported into the temp table, those are the views that were not accessed/used in the last 30 days and can be potentially removed.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论