PostgreSQL DBA硬扛垃圾 “开发”，“架构师”，滥用PG 你们滚出！（附送定期清理连接脚本）

文摘 2024-10-11 06:01 中国香港

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2400人左右 1 + 2 + 3 + 4 +5 + 6 + 7）(1 2 3 4 5 群均已爆满,6群420人关闭自由申请，新人进7群)

基于PostgreSQL数据库的特殊性，一般熟悉PostgreSQL的数据库架构或DBA，容易在开发不懂PG数据库原理的情况下，和开发起争端，到底数据库管理人员和开发或软件架构师容易在那些地方，打起来，今天我们来捋一捋。

问题1：什么都往PostgreSQL塞！！

在数据库的行业里面，有两种Person，第一种是数据库厂商或数据库行业的从业者，第二种是乙方售后或者甲方企业级别的数据库服务的人员。这二者最大的不同或者说根本的不同，是利益的不同。作为数据库厂商的同学、老师，最容易或者说最爱将自己所属的数据库产品夸大功能，基本上一库传三代，一库顶百库，这符合他们的经济和利益。但作为乙方售后，或者甲方DBA，那么他们是最反感第一类人的，因为夸大的数据库使用维度导致数据库容易出现各种问题，最终这锅是要到他们的头上的，所以这篇文章的角度是站到实际使用数据库的角度，或者最终维护数据库的角度来讲的。

作为甲方的数据库管理人员，说一句，PostgreSQL不是垃圾桶，不要什么都往数据库里面塞。我们最常见的第一种最让人讨厌的用法就是大量的字符塞到PostgreSQL中，这里包含了Json数据，当然我们不是说不能将正常的JSON文件塞到PostgreSQL 但是你要分辨清楚你的数据库工作的模式。

处理OLTP的PostgreSQL核心业务数据库，塞大量的Json,Jsonb,text,bytea类型的数据

这里我们是非常非常非常反感，这样OLTP的PostgreSQL数据库去承载大量的JSON，JSONB，或者bytea，text 等类型的数据。原因是什么，如果懂得PostgreSQL 的原理的情况下，稍微梳理一下就能明白，大家看看有没有道理

PostgreSQL 中影响数据库性能之一是Wal,而上期说过Wal中有大量的页面数据,这也是PostgreSQL原理中为在系统CRASH后，页面受损时的修复页面的一个安全性的功能。具体的源代码可以从下面的链接查看，xlog.c 是 PostgreSQL 源代码中处理 Write-Ahead Logging（WAL）的核心文件之一。它负责管理 WAL 日志的生成、写入和回放，以确保数据的持久性和一致性。在源码中可以看到，其中有检测toast配置有关信息的部分。https://github.com/postgres/postgres/blob/master/src/backend/access/transam/xlog.c

既然是这样的情况，在写入大量的json,jsonb或者text等类型的数据时，会导致WAL日志增大，且里面有大量的是数据，导致你WAL变得更大，里面很大一部分非真正的日志数据，这样会导致连锁反应，比如产生更多IO操作，WAL缓存被使用的容量可能需要更大，在数据库恢复的时候，速度更慢，等等问题。

同时我们还不应该忘记vacuum ,autovacuum等操作，他们会对toast中死行所带的数据进行清理，同时也会清理相关的空间，这都需要更多的CPU资源，内存资源去处理这些信息但这与我们希望vacuum,autovacuum 对系统影响更小的初衷是相悖的。

所以如果懂得原理面对OLTP数据库后期产生的影响及运维人员要付出的及后果，我是100000000% 不建议往PostgreSQL插入大量的JSON,JSONB,TEXT等数据的。同时这也不是光PG这样，ORACLE ,SQL SERVER 都不建议往系统中使用TEXT类型或BLOB类型等，虽然原理不同，但基本的意义都是避免核心的事务OLTP数据库的性能被影响，且不好优化。

如果必须要PostgreSQL来处理JSON,JSONB，等数据，那么完全可以单独开一个POSTGRESQL数据库来处理这些数据，我们也可以针对这样的PG进行特殊的优化和管理，而不是乱搞。

切记，数据库提供的功能，和能使用的功能，且好用的功能是两码事，千万别听数据库销售的话，什么都可以，什么都行，等到不行的时候，你看他搭理你吗？

（就这我还没说你做PG 主从WAL增大给你复制的压力呢！！）

2 胡乱将PG数据库作为，消息通知中心的使用方式

说到这点，那是更让PG的DBA恼火，PG的弱点大家都知道，这里不做赘述，如果你不知道那么就看看MVCC在PostgreSQL中形成的原理。

这张图大致的意思，简单说一下，就是程序不用消息中心了，或通知中心组件了，将数据库的表作为通知信息的写入和查询点，让程序不断地对一行数据进行更新，用另一个程序判断行更新与否，最终判断程序走向和流程。

现在这样操作的少了，如果还这样做的程序员基本上已经被开了，但之前的确有这样的程序员，你说他技术差，还是懒，那我就不好评说了，但这样设计的系统对PostgreSQL数据库产生了极大的压力，如果是多个应用系统都这么设计，那么PostgreSQL数据库中产生的autovacuum的次数将是不可想象的，且随着数据量越来越大，将产生越来越大的性能问题，和突发大表的autovacuum,且成为定时炸弹。

3 应用程序设计连接方式问题，导致连接一断，程序就报错一次

这样的应用程序设计我是见过的，比如你的PostgreSQL的连接长时间处于idel，我们是可以通过参数来进行，回收连接的，但在一些应用程序中，使用的JAVA的连接池有问题，或者设置的参数不对，在postgreSQL 释放了连接后，应用再次访问数据库第一次会报错，导致业务的访问失败，而JAVA会再次进行创建连接与数据库再次建立正常连接。

而这里某些开发人员提出的问题，怎么避免这一次的应用访问失败，实际上这不是什么问题，应用失败1次然后创建新的连接就好了，而奇葩的一些开发和架构师竟然要求 POSTGRESQL的Idle过期时间是365天，说过年才能进行连接的回收。

当然我这也是听说，如果是我遇到这样的奇葩，我早就俩大巴掌上去了，好好和他说到说到。

附送一个脚本，来对数据库中idle的链接进行清理，超过60分钟的（脚本时间可以自行调整，定期回收idle连接有利于数据库运行和资源释放），但保留一个同类型的applicaiton name 的链接。

最后甲方DBA，别人说什么，你就信什么，别人让你干啥你就干啥锅都是你的，光荣和荣誉都是别人的。

WITH inactive_connections AS (
    SELECT
        pid,
        rank() over (partition by client_addr order by backend_start ASC) as rank
    FROM 
        pg_stat_activity
    WHERE
       
        pid <> pg_backend_pid( )
    AND
        
        application_name !~ '(?:psql)|(?:pgAdmin.+)'
    AND
       
        datname = current_database() 
    AND
       
        usename = current_user 
    AND
       
        state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
    AND
       
        current_timestamp - state_change > interval '60 minutes' 
)
SELECT
    pg_terminate_backend(pid)
FROM
    inactive_connections 
WHERE
    rank > 1;