{"id":442306,"date":"2025-12-18T15:56:45","date_gmt":"2025-12-18T07:56:45","guid":{"rendered":"https:\/\/idc.net\/help\/?p=442306"},"modified":"2025-12-11T16:01:42","modified_gmt":"2025-12-11T08:01:42","slug":"%e9%a6%99%e6%b8%af%e6%9c%8d%e5%8a%a1%e5%99%a8postgresql%e6%80%a7%e8%83%bd%e8%b0%83%e4%bc%98%e7%bb%88%e6%9e%81%e6%8c%87%e5%8d%97%ef%bc%9a%e6%9c%80%e6%96%b0%e5%ae%9e%e6%88%98%e5%8f%82%e6%95%b0%e5%85%a8","status":"publish","type":"post","link":"https:\/\/idc.net\/help\/442306\/","title":{"rendered":"\u9999\u6e2f\u670d\u52a1\u5668PostgreSQL\u6027\u80fd\u8c03\u4f18\u7ec8\u6781\u6307\u5357\uff1a\u6700\u65b0\u5b9e\u6218\u53c2\u6570\u5168\u89e3"},"content":{"rendered":"

\u8d8a\u6765\u8d8a\u591a\u7684\u5f00\u53d1\u8005\u9009\u62e9\u5728\u9999\u6e2f\u670d\u52a1\u5668\u4e0a\u90e8\u7f72PostgreSQL\uff0c\u539f\u56e0\u65e0\u4ed6\uff1a\u514d\u5907\u6848\u3001CN2 GIA\u4f4e\u5ef6\u8fdf\u3001\u5168\u6839\u72ec\u7acb\u63a7\u5236\u6743\u3002\u4f46\u539f\u751f\u6027\u80fd\u51e0\u4e4e\u65e0\u635f\u8017\u3002\u7136\u800c\uff0c\u5f88\u591a\u4eba\u5728\u5168\u65b0\u9999\u6e2f\u670d\u52a1\u5668\u4e0a\u76f4\u63a5yum install postgresql\u540e\uff0c\u53d1\u73b0\u5e76\u53d1\u53ea\u6709\u51e0\u5343\u3001\u6162\u67e5\u8be2\u5806\u79ef\u3001CPU\u5e38\u5e74100%\u3002\u672c\u6587\u7cfb\u7edf\u5206\u4eab\u4e00\u5957\u7ecf\u8fc7\u5927\u89c4\u6a21\u751f\u4ea7\u9a8c\u8bc1\u7684PostgreSQL\u6027\u80fd\u8c03\u4f18\u65b9\u6848\uff0c\u5168\u90e8\u53c2\u6570\u5747\u9002\u7528\u4e8e\u9999\u6e2f\u670d\u52a1\u5668\u7269\u7406\u673a\u73af\u5883\uff0c\u53ef\u76f4\u63a5\u590d\u5236\u4f7f\u7528\uff0c\u8ba9\u4f60\u7684\u6570\u636e\u5e93\u6027\u80fd\u63d0\u53475-20\u500d\u3002<\/p>\n

\u4e00\u3001\u9999\u6e2f\u670d\u52a1\u5668\u786c\u4ef6\u9009\u578b\u5bf9PostgreSQL\u7684\u51b3\u5b9a\u6027\u5f71\u54cd<\/h4>\n

PostgreSQL\u662f\u5178\u578b\u7684\u201c\u5403\u5185\u5b58+\u5403\u5355\u6838\u201d\u7684\u6570\u636e\u5e93\uff0c\u786c\u4ef6\u76f4\u63a5\u51b3\u5b9a\u6027\u80fd\u5929\u82b1\u677f\uff1a<\/p>\n

\n
\n
<\/div>\n<\/div>\n
\n
<\/div>\n\n\n\n\n\n\n\n\n\n
\u786c\u4ef6\u7ef4\u5ea6<\/th>\n\u5bf9PostgreSQL\u5177\u4f53\u5f71\u54cd<\/th>\n\u9999\u6e2f\u670d\u52a1\u5668\u63a8\u8350\u89c4\u683c\uff08\u5bf9\u5e94\u540e\u6d6a\u4e91\u5957\u9910\uff09<\/th>\n<\/tr>\n<\/thead>\n
CPU\u5355\u6838\u777f\u9891<\/td>\n\u5f71\u54cd\u590d\u6742\u67e5\u8be2\u3001JSONB\u89e3\u6790\u3001\u6392\u5e8f\u7b49\u5355\u7ebf\u7a0b\u64cd\u4f5c<\/td>\n\u81f3\u5c113.3GHz\u777f\u9891\uff08E3-1230 v2\u53ca\u4ee5\u4e0a\uff09<\/td>\n<\/tr>\n
CPU\u7269\u7406\u6838\u5fc3\u6570<\/td>\n\u51b3\u5b9a\u6700\u5927\u5e76\u884c\u67e5\u8be2\u5ea6\u4e0e\u8fde\u63a5\u5e76\u53d1<\/td>\n\u63a8\u835016\u6838+\uff08\u53ccE5-2660\u6216E5-2650 v2\u7cfb\u5217\uff09<\/td>\n<\/tr>\n
\u5185\u5b58\u5bb9\u91cf<\/td>\nshared_buffers\u3001work_mem\u3001effective_cache_size\u7684\u57fa\u77f3<\/td>\n\u6700\u4f4e32GB\uff0c\u63a8\u835064GB-128GB<\/td>\n<\/tr>\n
\u78c1\u76d8IOPS\u4e0e\u7c7b\u578b<\/td>\n\u51b3\u5b9aWAL\u5199\u5165\u901f\u5ea6\u3001Checkpoint\u9891\u7387\u3001VACUUM\u6548\u7387<\/td>\n\u5fc5\u987b\u5168SSD\uff0c\u9ad8\u7aef\u5efa\u8baeNVMe + RAID 10<\/td>\n<\/tr>\n
\u7f51\u7edc\u7ebf\u8def<\/td>\n\u5f71\u54cd\u5927\u9646\u7528\u6237\u67e5\u8be2\u5ef6\u8fdf\u4e0e\u541e\u5410<\/td>\n\u5fc5\u987bCN2 GIA\uff08\u540e\u6d6a\u4e91\u5168\u7cfb\u6807\u914d\uff09<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n<\/div>\n<\/div>\n

\u5b9e\u6d4b\u6570\u636e\uff1a\u5728\u76f8\u540c\u914d\u7f6e\u4e0b\uff0cCN2 GIA\u7ebf\u8def\u6bd4\u666e\u901aBGP\u5ef6\u8fdf\u964d\u4f4e60-90ms\uff0cTPS\u63d0\u534720-35%\u3002<\/p>\n

\u4e8c\u3001PostgreSQL\u6838\u5fc3\u53c2\u6570\u6df1\u5ea6\u8c03\u4f18\uff08\u9999\u6e2f\u670d\u52a1\u5668\u4e13\u7528\u7248\uff09<\/h4>\n

\u4ee5\u4e0b\u53c2\u6570\u5728\u540e\u6d6a\u4e91\u9999\u6e2f\u670d\u52a1\u5668\uff08CentOS 7\/8\/9 + PostgreSQL 13-16\uff09\u4e0a\u7ecf\u8fc7\u5343\u4e07\u7ea7PV\u9879\u76ee\u9a8c\u8bc1\uff0c\u53ef\u76f4\u63a5\u8986\u76d6\uff1a<\/p>\n

    \n
  1. \u5185\u5b58\u53c2\u6570\uff08\u6027\u80fd\u63d0\u5347\u5360\u6bd460%+\uff09 shared_buffers = 12GB\u81f332GB\uff08\u7269\u7406\u5185\u5b58\u768425%-35%\uff0c\u4e0d\u8981\u8d85\u8fc740%\uff09 work_mem = 16MB\u81f364MB\uff08\u590d\u6742\u67e5\u8be2\u53ef\u4e34\u65f6\u63d0\u5347\u81f3128MB\uff09 maintenance_work_mem = 2GB\u81f34GB\uff08\u7528\u4e8eVACUUM\u3001REINDEX\u3001CREATE INDEX\uff09 effective_cache_size = 48GB\u81f396GB\uff08\u5185\u5b58\u768470%-80%\uff0c\u8ba9\u4f18\u5316\u5668\u66f4\u4fe1\u4efb\u7d22\u5f15\uff09 temp_buffers = 64MB\u81f3128MB\uff08\u6bcf\u4e2a\u4f1a\u8bdd\u4e34\u65f6\u8868\u7f13\u51b2\uff09<\/li>\n
  2. \u8fde\u63a5\u4e0e\u5e76\u884c max_connections = 300\u81f32000\uff08\u6bcf\u8fde\u63a5\u7ea610MB\u5185\u5b58\uff0c\u5efa\u8bae\u9884\u755920%\u4f59\u91cf\uff09 max_parallel_workers_per_gather = 4\u81f38 max_parallel_workers = 16\u81f332\uff08\u4e0eCPU\u7269\u7406\u6838\u5fc3\u6570\u5339\u914d\uff09 parallel_setup_cost = 100.0 parallel_tuple_cost = 0.01<\/li>\n
  3. WAL\u4e0eCheckpoint\u4f18\u5316\uff08\u89e3\u51b3\u5199\u5165\u74f6\u9888\uff09 wal_level = replica wal_buffers = 64MB wal_writer_delay = 20ms checkpoint_timeout = 15min\u81f330min min_wal_size = 4GB max_wal_size = 32GB checkpoint_completion_target = 0.9 wal_compression = on\uff08PostgreSQL 13+\uff09<\/li>\n
  4. \u81ea\u52a8\u771f\u7a7a\u4e0e\u9632\u81a8\u80c0 autovacuum = on autovacuum_max_workers = 6\u81f310 autovacuum_vacuum_scale_factor = 0.05\u81f30.1 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_cost_delay = 10ms autovacuum_vacuum_cost_limit = 4000\u81f38000<\/li>\n
  5. \u5176\u4ed6\u5173\u952e\u53c2\u6570 random_page_cost = 1.1\uff08\u5168SSD\u73af\u5883\uff09 jit = on\uff08PostgreSQL 11+\uff0c\u6570\u636e\u91cf>50GB\u65f6\u660e\u663e\u63d0\u901f\uff09 default_statistics_target = 500\u81f31000\uff08\u63d0\u5347\u4f18\u5316\u5668\u51c6\u786e\u6027\uff09<\/li>\n<\/ol>\n

    \u4e09\u3001\u9999\u6e2f\u670d\u52a1\u5668\u7cfb\u7edf\u7ea7\u6df1\u5ea6\u4f18\u5316<\/h4>\n
      \n
    1. \u6587\u4ef6\u7cfb\u7edf\u9009\u62e9 \u5f3a\u70c8\u63a8\u8350XFS\uff08\u6bd4ext4\u5728\u9ad8\u5e76\u53d1\u968f\u673a\u5199\u573a\u666fIOPS\u9ad830-50%\uff09 mkfs.xfs -n ftype=1 \/dev\/sdX<\/li>\n
    2. IO\u8c03\u5ea6\u5668 SSD\u78c1\u76d8\u63a8\u8350\uff1a echo noop > \/sys\/block\/sdX\/queue\/scheduler NVMe\u78c1\u76d8\u63a8\u8350\uff1a echo none > \/sys\/block\/nvme0n1\/queue\/scheduler<\/li>\n
    3. \u5173\u952e\u5185\u6838\u53c2\u6570\uff08\u5199\u5165\/etc\/sysctl.conf\uff09 vm.swappiness = 1 vm.dirty_ratio = 5 vm.dirty_background_ratio = 2 vm.dirty_expire_centisecs = 500 vm.overcommit_memory = 1 kernel.sem = 250 640000 1000 25600 net.core.somaxconn = 65535 net.ipv4.tcp_max_syn_backlog = 16384 net.core.netdev_max_backlog = 5000<\/li>\n
    4. \u900f\u660e\u5927\u9875\uff08Transparent Huge Pages\uff09 echo always > \/sys\/kernel\/mm\/transparent_hugepage\/enabled echo always > \/sys\/kernel\/mm\/transparent_hugepage\/defrag<\/li>\n
    5. CPU\u8c03\u4f18 \u5173\u95edCPU\u8282\u80fd\uff1a cpupower frequency-set -g performance<\/li>\n<\/ol>\n

      \u56db\u3001\u9ad8\u7ea7\u7279\u6027\u6df1\u5ea6\u5e94\u7528<\/h4>\n
        \n
      1. \u8868\u5206\u533a\uff08Partitioning\uff09 \u63a8\u8350\u4f7f\u7528\u58f0\u660e\u5f0f\u5206\u533a\uff08PostgreSQL 10+\uff09\u6216pg_partman\u63d2\u4ef6\uff0c\u6309\u65f6\u95f4\u6216\u54c8\u5e0c\u5206\u533a<\/li>\n
      2. \u7d22\u5f15\u4f18\u5316 \u4f18\u5148\u521b\u5efaBRIN\u7d22\u5f15\uff08\u8d85\u5927\u8868\uff09\u3001GIN\u7d22\u5f15\uff08JSONB\u3001\u6570\u7ec4\uff09\u3001GiST\uff08\u5730\u7406\u4f4d\u7f6e\uff09 \u4f7f\u7528\u8986\u76d6\u7d22\u5f15\uff08PostgreSQL 11+\uff09\uff1a CREATE INDEX ON orders (customer_id) INCLUDE (order_date, total);<\/li>\n
      3. \u8fde\u63a5\u6c60\u5fc5\u5907 \u5f3a\u70c8\u5efa\u8bae\u4f7f\u7528PgBouncer\uff08Transaction\u6a21\u5f0f\uff09\u90e8\u7f72\u5728\u540c\u4e00\u53f0\u9999\u6e2f\u670d\u52a1\u5668\uff0c\u7aef\u53e36432<\/li>\n
      4. \u76d1\u63a7\u5fc5\u88c5\u63d2\u4ef6 pg_stat_statements + check_postgres.pl + Grafana\u5b9e\u65f6\u76d1\u63a7<\/li>\n<\/ol>\n

        \u4e94\u3001\u4e0d\u540c\u4e1a\u52a1\u573a\u666f\u63a8\u8350\u914d\u7f6e\u4e00\u89c8\u8868<\/h4>\n
        \n
        \n
        <\/div>\n<\/div>\n
        \n
        <\/div>\n\n\n\n\n\n\n\n\n
        \u4e1a\u52a1\u7c7b\u578b<\/th>\n\u63a8\u8350\u9999\u6e2f\u670d\u52a1\u5668\u914d\u7f6e\uff08\u540e\u6d6a\u4e91\uff09<\/th>\n\u5173\u952e\u53c2\u6570\u91cd\u70b9\u8c03\u6574<\/th>\n<\/tr>\n<\/thead>\n
        \u4e2d\u5c0f\u578bSaaS\/\u4f01\u4e1aERP<\/td>\nE5-2660 \/ 16GB \/ 240GB SSD \/ 10Mbps CN2 GIA<\/td>\nshared_buffers=6GB\uff0cwork_mem=32MB<\/td>\n<\/tr>\n
        \u5927\u578b\u7535\u5546\/\u91d1\u878d\u98ce\u63a7<\/td>\n\u53ccE5-2660 \/ 32-64GB \/ 480GB+ SSD \/ CN2 GIA<\/td>\nshared_buffers=16GB\uff0cJIT\u5f00\u542f<\/td>\n<\/tr>\n
        \u6570\u636e\u5206\u6790\/\u62a5\u8868\u5e73\u53f0<\/td>\n\u53ccE5 \/ 128GB\u5185\u5b58 \/ NVMe SSD \/ \u767e\u5146CN2<\/td>\neffective_cache_size=100GB+<\/td>\n<\/tr>\n
        \u9ad8\u5e76\u53d1API\u7f51\u5173<\/td>\nE-2126G \/ 16GB \/ 240GB SSD \/ \u767e\u5146CN2<\/td>\nmax_connections=2000\uff0cPgBouncer<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
        <\/div>\n<\/div>\n<\/div>\n

        \u516d\u3001\u540e\u6d6a\u4e91\u9999\u6e2f\u670d\u52a1\u5668\u8fd0\u884cPostgreSQL\u7684\u72ec\u7279\u4f18\u52bf<\/h4>\n

        \u540e\u6d6a\u4e91\uff08https:\/\/idc.net\/hk\uff09\u4e3aPostgreSQL\u7528\u6237\u91cf\u8eab\u6253\u9020\u4e86\u5b8c\u7f8e\u73af\u5883\uff1a<\/a><\/p>\n