{"id":61627,"date":"2026-01-18T08:41:49","date_gmt":"2026-01-18T00:41:49","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/61627.html"},"modified":"2026-01-18T08:41:49","modified_gmt":"2026-01-18T00:41:49","slug":"%e5%9c%a8postgresql%e4%b8%ad%e5%b9%b6%e5%8f%91%e4%bf%9d%e5%ad%98%e4%b8%8a%e4%ba%bf%e4%b8%aa%e5%9b%be%e7%89%87%e9%93%be%e6%8e%a5%e5%92%8c%e5%9b%be%e7%89%87md5%e7%9a%84%e5%af%b9%e5%ba%94%e5%85%b3","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/61627.html","title":{"rendered":"\u5728PostgreSQL\u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u4e2a\u56fe\u7247\u94fe\u63a5\u548c\u56fe\u7247md5\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u5982\u4f55\u505a\u6700\u5408\u9002\uff1f"},"content":{"rendered":"<\/p>\n<h4>\u6587\u7ae0\u76ee\u5f55<\/h4>\n<ul>\n<li>\n<ul>\n<li>\u4e00\u3001\u5f15\u8a00&#xff1a;\u95ee\u9898\u80cc\u666f\u4e0e\u6311\u6218<\/li>\n<li>\u4e8c\u3001\u5982\u4f55\u8bbe\u8ba1&#xff1f;<\/li>\n<li>\n<ul>\n<li>2.1 \u6838\u5fc3\u539f\u5219&#xff1a;\u4ee5\u4e1a\u52a1\u8bbf\u95ee\u6a21\u5f0f\u9a71\u52a8\u8bbe\u8ba1<\/li>\n<li>2.2 \u4e3a\u4ec0\u4e48\u4e0d\u9700\u8981\u81ea\u589eID<\/li>\n<li>2.3 \u8bbe\u8ba1\u5efa\u8bae<\/li>\n<\/ul>\n<\/li>\n<li>\u4e09\u3001\u8868\u7ed3\u6784\u8bbe\u8ba1&#xff1a;\u7cbe\u7b80\u3001\u9ad8\u6548\u3001\u65e0\u5197\u4f59<\/li>\n<li>\n<ul>\n<li>3.1 \u5b57\u6bb5\u7c7b\u578b\u9009\u62e9<\/li>\n<li>3.2 \u4e3b\u952e\u4e0e\u7ea6\u675f<\/li>\n<li>3.3 \u662f\u5426\u9700\u8981 URL \u552f\u4e00\u7d22\u5f15&#xff1f;<\/li>\n<\/ul>\n<\/li>\n<li>\u56db\u3001\u5199\u5165\u6027\u80fd\u4f18\u5316&#xff1a;\u6279\u91cf &#043; \u5e42\u7b49 &#043; \u5f02\u6b65<\/li>\n<li>\n<ul>\n<li>4.1 \u6279\u91cf\u63d2\u5165&#xff08;Batch Insert&#xff09;<\/li>\n<li>4.2 \u5e42\u7b49\u5199\u5165&#xff1a;&#096;ON CONFLICT DO NOTHING&#096;<\/li>\n<li>4.3 \u5f02\u6b65\u5199\u5165\u67b6\u6784&#xff08;Python \u793a\u4f8b&#xff09;<\/li>\n<li>4.4 \u907f\u514d ORM \u6279\u91cf\u9677\u9631<\/li>\n<\/ul>\n<\/li>\n<li>\u4e94\u3001\u5e76\u53d1\u63a7\u5236\u4e0e\u6570\u636e\u4e00\u81f4\u6027<\/li>\n<li>\n<ul>\n<li>5.1 \u9ad8\u5e76\u53d1\u5199\u5165\u5b89\u5168<\/li>\n<li>5.2 \u5206\u5e03\u5f0f\u573a\u666f\u4e0b\u7684\u5e42\u7b49\u6027<\/li>\n<li>5.3 \u9519\u8bef\u91cd\u8bd5\u673a\u5236<\/li>\n<\/ul>\n<\/li>\n<li>\u516d\u3001PostgreSQL \u914d\u7f6e\u8c03\u4f18<\/li>\n<li>\n<ul>\n<li>6.1 \u5173\u952e\u53c2\u6570\u8c03\u6574&#xff08;&#096;postgresql.conf&#096;&#xff09;<\/li>\n<li>6.2 \u81ea\u52a8\u6e05\u7406&#xff08;AUTOVACUUM&#xff09;<\/li>\n<li>6.3 \u7d22\u5f15\u521b\u5efa\u7b56\u7565<\/li>\n<li>6.4 \u5173\u952e\u4f18\u5316\u63aa\u65bd&#xff08;\u4ebf\u7ea7\u5fc5\u5907&#xff09;<\/li>\n<\/ul>\n<\/li>\n<li>\u4e03\u3001\u8d85\u5927\u89c4\u6a21\u6269\u5c55\u65b9\u6848&#xff08;&gt;5\u4ebf\u884c&#xff09;<\/li>\n<li>\n<ul>\n<li>7.1 \u5206\u533a\u8868&#xff08;Partitioning&#xff09;<\/li>\n<li>7.2 \u5206\u5e03\u5f0f\u6570\u636e\u5e93&#xff08;Citus&#xff09;<\/li>\n<li>7.3 \u6269\u5c55\u5efa\u8bae<\/li>\n<\/ul>\n<\/li>\n<li>\u516b\u3001\u76d1\u63a7\u4e0e\u8fd0\u7ef4<\/li>\n<li>\n<ul>\n<li>8.1 \u5173\u952e\u76d1\u63a7\u6307\u6807<\/li>\n<li>8.2 \u5b9a\u671f\u7ef4\u62a4\u4efb\u52a1<\/li>\n<\/ul>\n<\/li>\n<li>\u4e5d\u3001\u5b8c\u6574\u4ee3\u7801\u793a\u4f8b&#xff08;\u5f02\u6b65\u6279\u91cf\u5199\u5165&#xff09;<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>\u4e00\u3001\u5f15\u8a00&#xff1a;\u95ee\u9898\u80cc\u666f\u4e0e\u6311\u6218<\/h3>\n<p>\u5728\u73b0\u4ee3\u6570\u636e\u5bc6\u96c6\u578b\u5e94\u7528\u4e2d&#xff0c;\u5982\u5185\u5bb9\u53bb\u91cd\u7cfb\u7edf\u3001\u56fe\u50cf\u641c\u7d22\u5f15\u64ce\u3001CDN \u7f13\u5b58\u7ba1\u7406\u6216\u7535\u5546\u53cd\u722c\u76d1\u63a7\u5e73\u53f0&#xff0c;\u5e38\u5e38\u9700\u8981\u5b58\u50a8\u6d77\u91cf\u56fe\u7247\u7684 URL \u4e0e\u5176\u5185\u5bb9\u54c8\u5e0c&#xff08;\u5982 MD5&#xff09;\u7684\u6620\u5c04\u5173\u7cfb\u3002\u5f53\u6570\u636e\u89c4\u6a21\u8fbe\u5230 \u4e0a\u4ebf\u6761&#xff08;100M&#043;&#xff09;\u751a\u81f3\u5341\u4ebf\u7ea7\u65f6&#xff0c;\u4f20\u7edf\u7684\u6570\u636e\u5e93\u8bbe\u8ba1\u548c\u64cd\u4f5c\u65b9\u5f0f\u5c06\u9762\u4e34\u4e25\u5cfb\u6311\u6218&#xff1a;<\/p>\n<ul>\n<li>\u5199\u5165\u541e\u5410\u74f6\u9888&#xff1a;\u5355\u7ebf\u7a0b\u63d2\u5165\u901f\u5ea6\u8fdc\u4f4e\u4e8e\u6570\u636e\u4ea7\u751f\u901f\u5ea6&#xff1b;<\/li>\n<li>\u5b58\u50a8\u81a8\u80c0&#xff1a;\u5197\u4f59\u5b57\u6bb5\u3001\u4f4e\u6548\u7d22\u5f15\u5bfc\u81f4\u78c1\u76d8\u5360\u7528\u7ffb\u500d&#xff1b;<\/li>\n<li>\u67e5\u8be2\u5ef6\u8fdf&#xff1a;\u4e3b\u952e\/\u7d22\u5f15\u8bbe\u8ba1\u4e0d\u5f53\u4f7f\u201cMD5 \u67e5 URL\u201d\u54cd\u5e94\u53d8\u6162&#xff1b;<\/li>\n<li>\u5e76\u53d1\u51b2\u7a81&#xff1a;\u9ad8\u5e76\u53d1\u5199\u5165\u5f15\u53d1\u9501\u7ade\u4e89\u3001\u6b7b\u9501\u6216\u552f\u4e00\u6027\u51b2\u7a81&#xff1b;<\/li>\n<li>\u8fd0\u7ef4\u590d\u6742\u5ea6&#xff1a;VACUUM \u538b\u529b\u5927\u3001WAL \u65e5\u5fd7\u7206\u70b8\u3001\u5907\u4efd\u56f0\u96be\u3002<\/li>\n<\/ul>\n<p>\u672c\u6587\u5c06\u8bb2\u8ff0\u5982\u4f55\u5728 PostgreSQL \u4e2d\u5b89\u5168\u3001\u9ad8\u6548\u3001\u53ef\u6269\u5c55\u5730\u5904\u7406\u4e0a\u4ebf\u7ea7\u56fe\u7247-MD5 \u6620\u5c04\u6570\u636e&#xff0c;\u5e76\u7ed9\u51fa\u7ecf\u8fc7\u751f\u4ea7\u9a8c\u8bc1\u7684\u5b8c\u6574\u6280\u672f\u65b9\u6848\u3002<\/p>\n<h3>\u4e8c\u3001\u5982\u4f55\u8bbe\u8ba1&#xff1f;<\/h3>\n<h4>2.1 \u6838\u5fc3\u539f\u5219&#xff1a;\u4ee5\u4e1a\u52a1\u8bbf\u95ee\u6a21\u5f0f\u9a71\u52a8\u8bbe\u8ba1<\/h4>\n<p>\u5728\u52a8\u624b\u5efa\u8868\u524d&#xff0c;\u5fc5\u987b\u660e\u786e \u6570\u636e\u5982\u4f55\u88ab\u4f7f\u7528\u3002\u5178\u578b\u573a\u666f\u5305\u62ec&#xff1a;<\/p>\n<table>\n<tr>\u8bbf\u95ee\u6a21\u5f0f\u5360\u6bd4\u5bf9\u8bbe\u8ba1\u7684\u5f71\u54cd<\/tr>\n<tbody>\n<tr>\n<td>\u7ed9\u5b9a MD5&#xff0c;\u67e5\u5bf9\u5e94 URL<\/td>\n<td>80%~95%<\/td>\n<td>MD5 \u5fc5\u987b\u662f\u9ad8\u6548\u7d22\u5f15&#xff08;\u6700\u597d\u662f\u4e3b\u952e&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u7ed9\u5b9a URL&#xff0c;\u67e5\u5176 MD5<\/td>\n<td>5%~20%<\/td>\n<td>\u9700\u4e3a URL \u5efa\u7acb\u552f\u4e00\u7d22\u5f15<\/td>\n<\/tr>\n<tr>\n<td>\u63d2\u5165\u65b0 (MD5, URL)<\/td>\n<td>\u9ad8\u9891\u5199\u5165<\/td>\n<td>\u9700\u652f\u6301\u5e42\u7b49\u3001\u9ad8\u5e76\u53d1\u3001\u6279\u91cf\u63d0\u4ea4<\/td>\n<\/tr>\n<tr>\n<td>\u66f4\u65b0 MD5&#xff08;\u5982\u8865\u5168&#xff09;<\/td>\n<td>\u6781\u5c11<\/td>\n<td>\u53ef\u5ffd\u7565\u6216\u5355\u72ec\u5904\u7406<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u7ed3\u8bba&#xff1a; MD5 \u662f\u5929\u7136\u7684\u4e1a\u52a1\u4e3b\u952e\u2014\u2014\u5168\u5c40\u552f\u4e00\u3001\u56fa\u5b9a\u957f\u5ea6\u3001\u4e0d\u53ef\u53d8\u3002\u4e0d\u5e94\u5f15\u5165\u65e0\u610f\u4e49\u7684\u81ea\u589e ID\u3002<\/p>\n<h4>2.2 \u4e3a\u4ec0\u4e48\u4e0d\u9700\u8981\u81ea\u589eID<\/h4>\n<p>\u5728 PostgreSQL \u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u7ea7&#xff08;100M&#043;&#xff09;\u56fe\u7247\u94fe\u63a5\u4e0e MD5 \u7684\u5bf9\u5e94\u5173\u7cfb&#xff0c;\u6838\u5fc3\u76ee\u6807\u662f&#xff1a;\u9ad8\u6027\u80fd\u5199\u5165 &#043; \u9ad8\u6548\u67e5\u8be2 &#043; \u5b58\u50a8\u4f18\u5316 &#043; \u5e76\u53d1\u5b89\u5168\u3002\u4e0d\u9700\u8981\u81ea\u589e ID&#xff01; \u4e3b\u952e\u5e94\u8bbe\u4e3a md5 \u5b57\u6bb5\u672c\u8eab&#xff08;\u6216 (md5, url) \u8054\u5408\u4e3b\u952e&#xff09;&#xff0c;\u7406\u7531\u5982\u4e0b&#xff1a;<\/p>\n<ul>\n<li>MD5 \u672c\u8eab\u662f \u5168\u5c40\u552f\u4e00\u3001\u56fa\u5b9a\u957f\u5ea6&#xff08;32 \u5b57\u7b26&#xff09;\u3001\u4e0d\u53ef\u53d8 \u7684\u54c8\u5e0c\u503c<\/li>\n<li>\u81ea\u589e ID \u4f1a\u5e26\u6765 \u989d\u5916\u5b58\u50a8\u5f00\u9500\u3001\u7d22\u5f15\u81a8\u80c0\u3001\u65e0\u4e1a\u52a1\u610f\u4e49<\/li>\n<li>\u67e5\u8be2\u573a\u666f\u901a\u5e38\u662f \u201c\u7ed9\u5b9a MD5 \u67e5 URL\u201d \u6216 \u201c\u7ed9\u5b9a URL \u67e5 MD5\u201d&#xff0c;\u65e0\u9700 ID<\/li>\n<\/ul>\n<table>\n<tr>\u95ee\u9898\u81ea\u589e ID \u8868\u65e0 ID \u8868&#xff08;MD5 \u4e3b\u952e&#xff09;<\/tr>\n<tbody>\n<tr>\n<td>\u5b58\u50a8\u5f00\u9500<\/td>\n<td>\u591a 4~8 \u5b57\u8282\/\u884c&#xff08;INT\/BIGINT&#xff09;<\/td>\n<td>0 \u989d\u5916\u5f00\u9500<\/td>\n<\/tr>\n<tr>\n<td>\u4e3b\u952e\u7d22\u5f15\u5927\u5c0f<\/td>\n<td>\u7ea6 800MB&#xff08;1\u4ebf\u884c \u00d7 8B&#xff09;<\/td>\n<td>\u7ea6 3.2GB&#xff08;1\u4ebf \u00d7 32B&#xff09;&#xff0c;\u4f46\u66f4\u7d27\u51d1&#xff08;CHAR vs TEXT&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u63d2\u5165\u6027\u80fd<\/td>\n<td>\u9700\u7ef4\u62a4\u5e8f\u5217 &#043; \u552f\u4e00\u7ea6\u675f<\/td>\n<td>\u76f4\u63a5\u63d2\u5165&#xff0c;\u51b2\u7a81\u5373\u5931\u8d25&#xff08;\u5929\u7136\u5e42\u7b49&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u67e5\u8be2\u6548\u7387<\/td>\n<td>\u9700\u5148\u67e5 ID \u518d\u5173\u8054<\/td>\n<td>\u76f4\u63a5\u901a\u8fc7 MD5 \u5b9a\u4f4d&#xff08;\u4e00\u6b21\u7d22\u5f15\u626b\u63cf&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u4e1a\u52a1\u610f\u4e49<\/td>\n<td>\u65e0<\/td>\n<td>MD5 \u5373\u4e1a\u52a1\u4e3b\u952e<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u5b9e\u6d4b\u6570\u636e&#xff08;1\u4ebf\u884c&#xff09;&#xff1a;<\/p>\n<ul>\n<li>\u81ea\u589e ID \u8868\u603b\u5927\u5c0f&#xff1a;\u2248 12 GB<\/li>\n<li>MD5 \u4e3b\u952e\u8868\u603b\u5927\u5c0f&#xff1a;\u2248 10 GB&#xff08;\u56e0\u7701\u53bb ID &#043; \u66f4\u9ad8\u6548 TOAST \u5b58\u50a8&#xff09;<\/li>\n<\/ul>\n<h4>2.3 \u8bbe\u8ba1\u5efa\u8bae<\/h4>\n<table>\n<tr>\u7ef4\u5ea6\u63a8\u8350\u65b9\u6848<\/tr>\n<tbody>\n<tr>\n<td>\u8868\u7ed3\u6784<\/td>\n<td>md5 CHAR(32) PRIMARY KEY, url TEXT<\/td>\n<\/tr>\n<tr>\n<td>\u7d22\u5f15<\/td>\n<td>\u4e3b\u952e&#xff08;MD5&#xff09;&#043; \u552f\u4e00\u7d22\u5f15&#xff08;URL&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u5199\u5165\u65b9\u5f0f<\/td>\n<td>\u6279\u91cf &#043; ON CONFLICT DO NOTHING &#043; \u5f02\u6b65<\/td>\n<\/tr>\n<tr>\n<td>\u5e76\u53d1\u63a7\u5236<\/td>\n<td>\u4f9d\u8d56 MVCC &#043; \u552f\u4e00\u7ea6\u675f&#xff0c;\u65e0\u9700\u5e94\u7528\u5c42\u9501<\/td>\n<\/tr>\n<tr>\n<td>\u914d\u7f6e\u8c03\u4f18<\/td>\n<td>\u589e\u5927 shared_buffers\u3001work_mem&#xff0c;\u542f\u7528 WAL \u538b\u7f29<\/td>\n<\/tr>\n<tr>\n<td>\u6269\u5c55\u65b9\u6848<\/td>\n<td>&gt;5\u4ebf\u884c \u2192 \u54c8\u5e0c\u5206\u533a&#xff1b;&gt;10\u4ebf\u884c \u2192 Citus \u5206\u5e03\u5f0f<\/td>\n<\/tr>\n<tr>\n<td>\u8fd0\u7ef4\u91cd\u70b9<\/td>\n<td>\u76d1\u63a7\u81a8\u80c0\u7387\u3001\u786e\u4fdd autovacuum \u53ca\u65f6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u5efa\u8bae&#xff1a; \u201c\u7528 MD5 \u505a\u4e3b\u952e&#xff0c;\u6279\u91cf\u63d2\u5165\u5e26\u51b2\u7a81\u5ffd\u7565&#xff0c;\u5148\u704c\u6570\u636e\u518d\u5efa\u7d22\u5f15&#xff0c;\u914d\u7f6e\u8c03\u4f18\u4fdd\u541e\u5410\u201d \u2014\u2014 \u8fd9\u56db\u70b9\u662f\u4ebf\u7ea7\u6570\u636e\u9ad8\u6548\u5165\u5e93\u7684\u6838\u5fc3\u3002<\/p>\n<p>\u63a8\u8350\u8bbe\u8ba1&#xff1a;\u4ee5 md5 \u4e3a\u4e3b\u952e&#xff08;99% \u573a\u666f\u9002\u7528&#xff09;<\/p>\n<p><span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> image_md5_url <span class=\"token punctuation\">(<\/span><br \/>\n    md5   <span class=\"token keyword\">CHAR<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">32<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">PRIMARY<\/span> <span class=\"token keyword\">KEY<\/span><span class=\"token punctuation\">,<\/span>      <span class=\"token comment\">&#8212; 32\u4f4d\u5c0f\u5199MD5&#xff0c;\u65e0\u7d22\u5f15\u81a8\u80c0<\/span><br \/>\n    url   <span class=\"token keyword\">TEXT<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span>              <span class=\"token comment\">&#8212; \u56fe\u7247URL&#xff0c;\u53ef\u80fd\u5f88\u957f<\/span><br \/>\n<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u4ec5\u5f53\u9700\u8981\u201cURL \u2192 MD5\u201d\u67e5\u8be2\u65f6&#xff0c;\u6dfb\u52a0\u4ee5\u4e0b\u7d22\u5f15<\/span><br \/>\n<span class=\"token comment\">&#8212; \u4e3a\u53cd\u5411\u67e5\u8be2&#xff08;URL \u2192 MD5&#xff09;\u5efa\u552f\u4e00\u7d22\u5f15&#xff08;\u5982\u679c\u9700\u8981&#xff09;<\/span><br \/>\n<span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">UNIQUE<\/span> <span class=\"token keyword\">INDEX<\/span> CONCURRENTLY idx_image_url <span class=\"token keyword\">ON<\/span> image_md5_url <span class=\"token punctuation\">(<\/span>url<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u4f18\u52bf\u603b\u7ed3&#xff1a;<\/p>\n<ul>\n<li>\u2705 \u96f6\u5197\u4f59\u5b57\u6bb5<\/li>\n<li>\u2705 \u63d2\u5165\u5929\u7136\u5e42\u7b49<\/li>\n<li>\u2705 \u67e5\u8be2 MD5 \u2192 URL \u6781\u5feb&#xff08;\u4e3b\u952e\u8986\u76d6&#xff09;<\/li>\n<li>\u2705 \u5b58\u50a8\u7a7a\u95f4\u6700\u5c0f\u5316<\/li>\n<li>\u2705 \u65e0\u5e8f\u5217\u9501\u7ade\u4e89&#xff08;\u9ad8\u5e76\u53d1\u53cb\u597d&#xff09;<\/li>\n<\/ul>\n<p>\u901a\u8fc7\u4ee5\u4e0a\u8bbe\u8ba1&#xff0c;PostgreSQL \u5b8c\u5168\u80fd\u591f\u80dc\u4efb \u4e0a\u4ebf\u7ea7\u56fe\u7247-MD5 \u6620\u5c04\u5b58\u50a8 \u7684\u9700\u6c42&#xff0c;\u517c\u5177\u9ad8\u6027\u80fd\u3001\u9ad8\u53ef\u9760\u3001\u4f4e\u6210\u672c\u7684\u4f18\u52bf&#xff0c;\u65e0\u9700\u8fc7\u65e9\u5f15\u5165\u590d\u6742\u7684\u5927\u6570\u636e\u6808&#xff08;\u5982 HBase\u3001Cassandra&#xff09;\u3002<\/p>\n<h3>\u4e09\u3001\u8868\u7ed3\u6784\u8bbe\u8ba1&#xff1a;\u7cbe\u7b80\u3001\u9ad8\u6548\u3001\u65e0\u5197\u4f59<\/h3>\n<h4>3.1 \u5b57\u6bb5\u7c7b\u578b\u9009\u62e9<\/h4>\n<table>\n<tr>\u5b57\u6bb5\u63a8\u8350\u7c7b\u578b\u7406\u7531<\/tr>\n<tbody>\n<tr>\n<td>md5<\/td>\n<td>CHAR(32)<\/td>\n<td>&#8211; \u56fa\u5b9a 32 \u5b57\u8282&#xff0c;\u65e0\u957f\u5ea6\u524d\u7f00\u5f00\u9500- \u6bd4 VARCHAR(32) \u8282\u7701 1 \u5b57\u8282\/\u884c- \u6bd4 BYTEA \u66f4\u6613\u8c03\u8bd5&#xff08;\u53ef\u8bfb&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>url<\/td>\n<td>TEXT<\/td>\n<td>&#8211; URL \u957f\u5ea6\u4e0d\u56fa\u5b9a&#xff08;\u53ef\u80fd &gt; 2KB&#xff09;- PostgreSQL \u81ea\u52a8\u4f7f\u7528 TOAST \u5b58\u50a8\u5927\u5b57\u6bb5&#xff0c;\u4e0d\u5f71\u54cd\u4e3b\u8868\u6027\u80fd<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u5b58\u50a8\u5bf9\u6bd4&#xff08;1\u4ebf\u884c&#xff09;&#xff1a;<\/p>\n<ul>\n<li>CHAR(32) &#043; TEXT&#xff1a;\u2248 10 GB<\/li>\n<li>BIGINT(id) &#043; VARCHAR(32) &#043; TEXT&#xff1a;\u2248 12.5 GB&#xff08;\u591a\u51fa 2.5GB \u65e0\u7528 ID&#xff09;<\/li>\n<\/ul>\n<h4>3.2 \u4e3b\u952e\u4e0e\u7ea6\u675f<\/h4>\n<p><span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> image_md5_url <span class=\"token punctuation\">(<\/span><br \/>\n    md5 <span class=\"token keyword\">CHAR<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">32<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">PRIMARY<\/span> <span class=\"token keyword\">KEY<\/span><span class=\"token punctuation\">,<\/span><br \/>\n    url <span class=\"token keyword\">TEXT<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span><br \/>\n<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<ul>\n<li>\u4e3b\u952e &#061; MD5&#xff1a;\u76f4\u63a5\u652f\u6301 O(1) \u67e5\u8be2 WHERE md5 &#061; &#039;&#8230;&#039;<\/li>\n<li>\u65e0\u81ea\u589e ID&#xff1a;\u907f\u514d\u5e8f\u5217\u9501\u3001\u51cf\u5c11\u7d22\u5f15\u5927\u5c0f\u3001\u6d88\u9664\u65e0\u7528\u5b57\u6bb5<\/li>\n<li>NOT NULL&#xff1a;\u786e\u4fdd\u6570\u636e\u5b8c\u6574\u6027<\/li>\n<\/ul>\n<p>\u6ce8\u610f&#xff1a;MD5 \u5e94\u7edf\u4e00\u8f6c\u4e3a\u5c0f\u5199\u5b58\u50a8&#xff08;\u5e94\u7528\u5c42\u5904\u7406&#xff09;&#xff0c;\u907f\u514d\u5927\u5c0f\u5199\u4e0d\u4e00\u81f4\u5bfc\u81f4\u91cd\u590d\u3002<\/p>\n<h4>3.3 \u662f\u5426\u9700\u8981 URL \u552f\u4e00\u7d22\u5f15&#xff1f;<\/h4>\n<ul>\n<li>\u82e5\u4e00\u4e2a URL \u53ea\u5bf9\u5e94\u4e00\u4e2a MD5 \u2192 \u5efa\u552f\u4e00\u7d22\u5f15&#xff1a;<span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">UNIQUE<\/span> <span class=\"token keyword\">INDEX<\/span> CONCURRENTLY idx_image_url <span class=\"token keyword\">ON<\/span> image_md5_url <span class=\"token punctuation\">(<\/span>url<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span>\n <\/li>\n<li>\u82e5\u5141\u8bb8\u591a\u4e2a MD5 \u6307\u5411\u540c\u4e00 URL&#xff08;\u7f55\u89c1&#xff09; \u2192 \u6539\u7528\u666e\u901a\u7d22\u5f15\u6216\u4e0d\u5efa<\/li>\n<\/ul>\n<p>\u5efa\u8bae&#xff1a;\u7edd\u5927\u591a\u6570\u573a\u666f\u4e0b&#xff0c;URL \u4e0e MD5 \u662f\u4e00\u4e00\u5bf9\u5e94\u7684&#xff0c;\u5e94\u5efa\u552f\u4e00\u7d22\u5f15\u4ee5\u652f\u6301\u53cd\u5411\u67e5\u8be2\u5e76\u9632\u6b62\u6570\u636e\u5f02\u5e38\u3002<\/p>\n<hr \/>\n<h3>\u56db\u3001\u5199\u5165\u6027\u80fd\u4f18\u5316&#xff1a;\u6279\u91cf &#043; \u5e42\u7b49 &#043; \u5f02\u6b65<\/h3>\n<h4>4.1 \u6279\u91cf\u63d2\u5165&#xff08;Batch Insert&#xff09;<\/h4>\n<p>\u5355\u6761 INSERT \u7684\u7f51\u7edc\u5f80\u8fd4\u548c\u4e8b\u52a1\u5f00\u9500\u5de8\u5927\u3002\u5fc5\u987b\u6279\u91cf\u63d0\u4ea4&#xff1a;<\/p>\n<ul>\n<li>\u63a8\u8350\u6279\u6b21\u5927\u5c0f&#xff1a;1,000 ~ 10,000 \u884c\/\u6279<\/li>\n<li>\u8fc7\u5927&#xff1a;\u4e8b\u52a1\u65e5\u5fd7\u8fc7\u5927&#xff0c;\u56de\u6eda\u6210\u672c\u9ad8<\/li>\n<li>\u8fc7\u5c0f&#xff1a;\u65e0\u6cd5\u644a\u8584\u5f00\u9500<\/li>\n<\/ul>\n<h4>4.2 \u5e42\u7b49\u5199\u5165&#xff1a;ON CONFLICT DO NOTHING<\/h4>\n<p>\u7531\u4e8e\u6570\u636e\u6e90\u53ef\u80fd\u5b58\u5728\u91cd\u590d&#xff0c;\u63d2\u5165\u65f6\u9700\u81ea\u52a8\u8df3\u8fc7\u5df2\u5b58\u5728\u8bb0\u5f55&#xff1a;<\/p>\n<p><span class=\"token keyword\">INSERT<\/span> <span class=\"token keyword\">INTO<\/span> image_md5_url <span class=\"token punctuation\">(<\/span>md5<span class=\"token punctuation\">,<\/span> url<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">VALUES<\/span> <span class=\"token punctuation\">(<\/span><span class=\"token string\">&#039;d41d&#8230;&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#039;2026-01-184reybucvdjb.jpg&#039;<\/span><span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token keyword\">ON<\/span> CONFLICT <span class=\"token punctuation\">(<\/span>md5<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">DO<\/span> NOTHING<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u4f18\u52bf&#xff1a;<\/p>\n<ul>\n<li>\u65e0\u9700\u5148 SELECT \u5224\u65ad&#xff0c;\u51cf\u5c11 50% \u67e5\u8be2\u91cf<\/li>\n<li>\u5929\u7136\u652f\u6301\u5e76\u53d1\u5199\u5165&#xff08;\u65e0\u6b7b\u9501\u98ce\u9669&#xff09;<\/li>\n<li>\u7b26\u5408\u201c\u63d2\u5165\u5373\u53bb\u91cd\u201d\u4e1a\u52a1\u8bed\u4e49<\/li>\n<\/ul>\n<h4>4.3 \u5f02\u6b65\u5199\u5165\u67b6\u6784&#xff08;Python \u793a\u4f8b&#xff09;<\/h4>\n<p>\u4f7f\u7528 SQLAlchemy 2.0&#043; &#043; asyncpg \u5b9e\u73b0\u9ad8\u5e76\u53d1\u5199\u5165&#xff1a;<\/p>\n<p><span class=\"token comment\"># \u6838\u5fc3\u903b\u8f91&#xff1a;\u6279\u91cf &#043; \u51b2\u7a81\u5ffd\u7565<\/span><br \/>\n<span class=\"token keyword\">async<\/span> <span class=\"token keyword\">def<\/span> <span class=\"token function\">save_batch<\/span><span class=\"token punctuation\">(<\/span>session<span class=\"token punctuation\">,<\/span> batch<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">:<\/span><br \/>\n    stmt <span class=\"token operator\">&#061;<\/span> text<span class=\"token punctuation\">(<\/span><span class=\"token triple-quoted-string string\">&#034;&#034;&#034;<br \/>\n        INSERT INTO image_md5_url (md5, url)<br \/>\n        VALUES (:md5, :url)<br \/>\n        ON CONFLICT (md5) DO NOTHING<br \/>\n    &#034;&#034;&#034;<\/span><span class=\"token punctuation\">)<\/span><br \/>\n    <span class=\"token keyword\">await<\/span> session<span class=\"token punctuation\">.<\/span>execute<span class=\"token punctuation\">(<\/span>stmt<span class=\"token punctuation\">,<\/span> <span class=\"token punctuation\">[<\/span><br \/>\n        <span class=\"token punctuation\">{<\/span><span class=\"token string\">&#034;md5&#034;<\/span><span class=\"token punctuation\">:<\/span> md5<span class=\"token punctuation\">.<\/span>lower<span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#034;url&#034;<\/span><span class=\"token punctuation\">:<\/span> url<span class=\"token punctuation\">}<\/span> <span class=\"token keyword\">for<\/span> md5<span class=\"token punctuation\">,<\/span> url <span class=\"token keyword\">in<\/span> batch<br \/>\n    <span class=\"token punctuation\">]<\/span><span class=\"token punctuation\">)<\/span><br \/>\n    <span class=\"token keyword\">await<\/span> session<span class=\"token punctuation\">.<\/span>commit<span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><\/p>\n<p>\u5173\u952e\u53c2\u6570&#xff1a;<\/p>\n<ul>\n<li>\u8fde\u63a5\u6c60\u5927\u5c0f&#xff1a;pool_size&#061;20, max_overflow&#061;30<\/li>\n<li>\u6279\u6b21\u5927\u5c0f&#xff1a;BATCH_SIZE&#061;5000<\/li>\n<li>\u5de5\u4f5c\u534f\u7a0b\u6570&#xff1a;MAX_WORKERS&#061;10<\/li>\n<\/ul>\n<h4>4.4 \u907f\u514d ORM \u6279\u91cf\u9677\u9631<\/h4>\n<ul>\n<li>\u4e0d\u8981\u7528 session.add_all() &#043; commit()&#xff1a;\u65e0\u6cd5\u5904\u7406\u51b2\u7a81<\/li>\n<li>\u5fc5\u987b\u7528\u539f\u751f SQL &#043; ON CONFLICT&#xff1a;\u6027\u80fd\u63d0\u5347 3~5 \u500d<\/li>\n<\/ul>\n<h3>\u4e94\u3001\u5e76\u53d1\u63a7\u5236\u4e0e\u6570\u636e\u4e00\u81f4\u6027<\/h3>\n<h4>5.1 \u9ad8\u5e76\u53d1\u5199\u5165\u5b89\u5168<\/h4>\n<p>PostgreSQL \u7684 MVCC&#xff08;\u591a\u7248\u672c\u5e76\u53d1\u63a7\u5236&#xff09; \u5929\u7136\u652f\u6301\u9ad8\u5e76\u53d1\u8bfb\u5199&#xff0c;\u4f46\u9700\u6ce8\u610f&#xff1a;<\/p>\n<ul>\n<li>\u552f\u4e00\u7d22\u5f15\u51b2\u7a81&#xff1a;ON CONFLICT \u81ea\u52a8\u5904\u7406&#xff0c;\u65e0\u9700\u5e94\u7528\u5c42\u91cd\u8bd5<\/li>\n<li>\u957f\u4e8b\u52a1\u95ee\u9898&#xff1a;\u5355\u4e8b\u52a1\u4e0d\u8981\u8d85\u8fc7 10 \u4e07\u884c&#xff0c;\u907f\u514d\u963b\u585e VACUUM<\/li>\n<li>\u8fde\u63a5\u6c60\u8017\u5c3d&#xff1a;\u5408\u7406\u8bbe\u7f6e max_connections \u548c\u5e94\u7528\u8fde\u63a5\u6570<\/li>\n<\/ul>\n<h4>5.2 \u5206\u5e03\u5f0f\u573a\u666f\u4e0b\u7684\u5e42\u7b49\u6027<\/h4>\n<p>\u82e5\u6570\u636e\u6765\u81ea\u591a\u4e2a\u91c7\u96c6\u8282\u70b9&#xff1a;<\/p>\n<ul>\n<li>\u6bcf\u4e2a\u8282\u70b9\u72ec\u7acb\u6279\u91cf\u63d0\u4ea4<\/li>\n<li>\u4f9d\u8d56\u6570\u636e\u5e93\u552f\u4e00\u7ea6\u675f\u53bb\u91cd&#xff08;\u800c\u975e\u5e94\u7528\u5c42\u7f13\u5b58&#xff09;<\/li>\n<li>\u65e0\u9700\u5206\u5e03\u5f0f\u9501&#xff1a;PostgreSQL \u552f\u4e00\u7d22\u5f15\u4fdd\u8bc1\u6700\u7ec8\u4e00\u81f4\u6027<\/li>\n<\/ul>\n<h4>5.3 \u9519\u8bef\u91cd\u8bd5\u673a\u5236<\/h4>\n<p>\u5bf9\u4e34\u65f6\u9519\u8bef&#xff08;\u5982\u7f51\u7edc\u8d85\u65f6&#xff09;\u8fdb\u884c\u6307\u6570\u9000\u907f\u91cd\u8bd5&#xff1a;<\/p>\n<p><span class=\"token keyword\">for<\/span> attempt <span class=\"token keyword\">in<\/span> <span class=\"token builtin\">range<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">3<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">:<\/span><br \/>\n    <span class=\"token keyword\">try<\/span><span class=\"token punctuation\">:<\/span><br \/>\n        <span class=\"token keyword\">await<\/span> save_batch<span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">.<\/span><span class=\"token punctuation\">.<\/span><span class=\"token punctuation\">.<\/span><span class=\"token punctuation\">)<\/span><br \/>\n        <span class=\"token keyword\">break<\/span><br \/>\n    <span class=\"token keyword\">except<\/span> <span class=\"token punctuation\">(<\/span>OperationalError<span class=\"token punctuation\">,<\/span> TimeoutError<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">:<\/span><br \/>\n        <span class=\"token keyword\">await<\/span> asyncio<span class=\"token punctuation\">.<\/span>sleep<span class=\"token punctuation\">(<\/span><span class=\"token number\">2<\/span> <span class=\"token operator\">**<\/span> attempt<span class=\"token punctuation\">)<\/span><\/p>\n<p>\u6ce8\u610f&#xff1a;\u552f\u4e00\u51b2\u7a81&#xff08;UniqueViolation&#xff09;\u4e0d\u5e94\u91cd\u8bd5&#xff0c;\u5e94\u89c6\u4e3a\u6210\u529f\u3002<\/p>\n<h3>\u516d\u3001PostgreSQL \u914d\u7f6e\u8c03\u4f18<\/h3>\n<h4>6.1 \u5173\u952e\u53c2\u6570\u8c03\u6574&#xff08;postgresql.conf&#xff09;<\/h4>\n<table>\n<tr>\u53c2\u6570\u63a8\u8350\u503c\u8bf4\u660e<\/tr>\n<tbody>\n<tr>\n<td>shared_buffers<\/td>\n<td>\u603b\u5185\u5b58 25%&#xff08;\u5982 8GB&#xff09;<\/td>\n<td>\u7f13\u5b58\u70ed\u6570\u636e<\/td>\n<\/tr>\n<tr>\n<td>effective_cache_size<\/td>\n<td>\u603b\u5185\u5b58 50%~75%<\/td>\n<td>\u544a\u77e5\u89c4\u5212\u5668 OS \u7f13\u5b58\u5927\u5c0f<\/td>\n<\/tr>\n<tr>\n<td>work_mem<\/td>\n<td>256MB<\/td>\n<td>\u6392\u5e8f\/\u54c8\u5e0c\u64cd\u4f5c\u5185\u5b58<\/td>\n<\/tr>\n<tr>\n<td>maintenance_work_mem<\/td>\n<td>2GB<\/td>\n<td>VACUUM\/\u7d22\u5f15\u521b\u5efa\u5185\u5b58<\/td>\n<\/tr>\n<tr>\n<td>wal_compression<\/td>\n<td>on<\/td>\n<td>\u51cf\u5c11 WAL \u4f53\u79ef<\/td>\n<\/tr>\n<tr>\n<td>checkpoint_timeout<\/td>\n<td>30min<\/td>\n<td>\u51cf\u5c11 checkpoint I\/O \u5cf0\u503c<\/td>\n<\/tr>\n<tr>\n<td>max_wal_size<\/td>\n<td>8GB<\/td>\n<td>\u5141\u8bb8\u66f4\u591a\u810f\u9875\u79ef\u7d2f<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p># postgresql.conf<br \/>\nshared_buffers &#061; 4GB          # \u603b\u5185\u5b58 25%<br \/>\neffective_cache_size &#061; 12GB   # OS \u7f13\u5b58\u9884\u4f30<br \/>\nwork_mem &#061; 256MB              # \u6392\u5e8f\/\u54c8\u5e0c\u5185\u5b58<br \/>\nmax_connections &#061; 200         # \u907f\u514d\u8fc7\u591a\u8fde\u63a5\u7ade\u4e89<br \/>\nwal_compression &#061; on          # \u51cf\u5c11 WAL \u4f53\u79ef<\/p>\n<h4>6.2 \u81ea\u52a8\u6e05\u7406&#xff08;AUTOVACUUM&#xff09;<\/h4>\n<p>\u4ebf\u7ea7\u8868\u9700\u66f4\u6fc0\u8fdb\u7684 VACUUM \u7b56\u7565&#xff1a;<\/p>\n<p><span class=\"token comment\">&#8212; \u9488\u5bf9\u5927\u8868\u5355\u72ec\u8bbe\u7f6e<\/span><br \/>\n<span class=\"token keyword\">ALTER<\/span> <span class=\"token keyword\">TABLE<\/span> image_md5_url <span class=\"token keyword\">SET<\/span> <span class=\"token punctuation\">(<\/span><br \/>\n    autovacuum_vacuum_scale_factor <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">0.01<\/span><span class=\"token punctuation\">,<\/span>  <span class=\"token comment\">&#8212; 1% \u53d8\u5316\u5373\u89e6\u53d1<\/span><br \/>\n    autovacuum_vacuum_cost_delay <span class=\"token operator\">&#061;<\/span> <span class=\"token number\">0<\/span>        <span class=\"token comment\">&#8212; \u4e0d\u9650\u901f<\/span><br \/>\n<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u76ee\u6807&#xff1a;\u907f\u514d\u8868\u81a8\u80c0&#xff08;bloat&#xff09;&#xff0c;\u4fdd\u6301\u7d22\u5f15\u6548\u7387\u3002<\/p>\n<h4>6.3 \u7d22\u5f15\u521b\u5efa\u7b56\u7565<\/h4>\n<ul>\n<li>\u5148\u5bfc\u5165\u6570\u636e&#xff0c;\u518d\u5efa\u7d22\u5f15&#xff1a;\u6bd4\u8fb9\u63d2\u8fb9\u5efa\u5feb 5~10 \u500d<\/li>\n<li>\u4f7f\u7528 CONCURRENTLY&#xff1a;\u907f\u514d\u9501\u8868&#xff08;\u4f46\u8017\u65f6\u66f4\u957f&#xff09;<span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">UNIQUE<\/span> <span class=\"token keyword\">INDEX<\/span> CONCURRENTLY idx_image_url <span class=\"token keyword\">ON<\/span> image_md5_url <span class=\"token punctuation\">(<\/span>url<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span>\n <\/li>\n<\/ul>\n<h4>6.4 \u5173\u952e\u4f18\u5316\u63aa\u65bd&#xff08;\u4ebf\u7ea7\u5fc5\u5907&#xff09;<\/h4>\n<p>1\u3001\u4f7f\u7528 CHAR(32) \u800c\u975e VARCHAR \u6216 TEXT \u5b58 MD5<\/p>\n<ul>\n<li>CHAR(32) \u56fa\u5b9a\u957f\u5ea6&#xff0c;\u65e0\u957f\u5ea6\u524d\u7f00\u5f00\u9500&#xff0c;\u7d22\u5f15\u66f4\u7d27\u51d1<\/li>\n<li>\u5f3a\u5236\u5c0f\u5199\u5b58\u50a8&#xff08;\u5e94\u7528\u5c42\u5904\u7406&#xff09;&#xff1a;md5 &#061; lower(md5_value)<\/li>\n<\/ul>\n<p>2\u3001URL \u4f7f\u7528 TEXT \u7c7b\u578b<\/p>\n<ul>\n<li>URL \u957f\u5ea6\u4e0d\u56fa\u5b9a&#xff08;\u53ef\u80fd &gt; 2KB&#xff09;&#xff0c;TEXT \u652f\u6301 TOAST \u81ea\u52a8\u538b\u7f29\u5927\u5b57\u6bb5<\/li>\n<\/ul>\n<p>3\u3001\u6279\u91cf\u63d2\u5165 &#043; \u5e76\u53d1\u63a7\u5236<\/p>\n<p><span class=\"token comment\"># Python \u793a\u4f8b&#xff08;asyncpg \u6216 psycopg3&#xff09;<\/span><br \/>\n<span class=\"token keyword\">async<\/span> <span class=\"token keyword\">def<\/span> <span class=\"token function\">insert_batch<\/span><span class=\"token punctuation\">(<\/span>records<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">:<\/span><br \/>\n    <span class=\"token comment\"># records: [(md5, url), &#8230;]<\/span><br \/>\n    <span class=\"token keyword\">await<\/span> conn<span class=\"token punctuation\">.<\/span>executemany<span class=\"token punctuation\">(<\/span><br \/>\n        <span class=\"token string\">&#034;INSERT INTO image_md5_url (md5, url) VALUES ($1, $2) ON CONFLICT DO NOTHING&#034;<\/span><span class=\"token punctuation\">,<\/span><br \/>\n        records<br \/>\n    <span class=\"token punctuation\">)<\/span><\/p>\n<ul>\n<li>ON CONFLICT DO NOTHING&#xff1a;\u5929\u7136\u5e42\u7b49&#xff0c;\u907f\u514d\u91cd\u590d\u63d2\u5165\u62a5\u9519<\/li>\n<li>\u6279\u91cf\u63d0\u4ea4&#xff08;1k~10k\/\u6279&#xff09;&#xff1a;\u51cf\u5c11\u4e8b\u52a1\u5f00\u9500<\/li>\n<\/ul>\n<p>4\u3001\u5206\u533a\u8868&#xff08;\u53ef\u9009&#xff0c;&gt;5\u4ebf\u884c\u8003\u8651&#xff09;<\/p>\n<p><span class=\"token comment\">&#8212; \u6309 MD5 \u524d\u4e24\u4f4d\u54c8\u5e0c\u5206\u533a&#xff08;256 \u5206\u533a&#xff09;<\/span><br \/>\n<span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> image_md5_url <span class=\"token punctuation\">(<\/span><br \/>\n    md5 <span class=\"token keyword\">CHAR<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">32<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span><span class=\"token punctuation\">,<\/span><br \/>\n    url <span class=\"token keyword\">TEXT<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span><span class=\"token punctuation\">,<\/span><br \/>\n    <span class=\"token keyword\">PRIMARY<\/span> <span class=\"token keyword\">KEY<\/span> <span class=\"token punctuation\">(<\/span>md5<span class=\"token punctuation\">)<\/span><br \/>\n<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">PARTITION<\/span> <span class=\"token keyword\">BY<\/span> <span class=\"token keyword\">HASH<\/span> <span class=\"token punctuation\">(<\/span>md5<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u9002\u7528\u4e8e&#xff1a;\u5355\u8868 &gt; 5 \u4ebf\u884c&#xff0c;\u4e14\u78c1\u76d8 I\/O \u6210\u74f6\u9888<\/p>\n<h3>\u4e03\u3001\u8d85\u5927\u89c4\u6a21\u6269\u5c55\u65b9\u6848&#xff08;&gt;5\u4ebf\u884c&#xff09;<\/h3>\n<p>\u5f53\u5355\u8868\u8d85\u8fc7 5 \u4ebf\u884c\u65f6&#xff0c;\u8003\u8651\u4ee5\u4e0b\u6269\u5c55&#xff1a;<\/p>\n<h4>7.1 \u5206\u533a\u8868&#xff08;Partitioning&#xff09;<\/h4>\n<p>\u6309 MD5 \u54c8\u5e0c\u5206\u533a&#xff0c;\u5206\u6563 I\/O \u538b\u529b&#xff1a;<\/p>\n<p><span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> image_md5_url <span class=\"token punctuation\">(<\/span><br \/>\n    md5 <span class=\"token keyword\">CHAR<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">32<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span><span class=\"token punctuation\">,<\/span><br \/>\n    url <span class=\"token keyword\">TEXT<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span><br \/>\n<span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">PARTITION<\/span> <span class=\"token keyword\">BY<\/span> <span class=\"token keyword\">HASH<\/span> <span class=\"token punctuation\">(<\/span>md5<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; \u521b\u5efa 256 \u4e2a\u5206\u533a&#xff08;md5 \u524d\u4e24\u4f4d&#xff09;<\/span><br \/>\n<span class=\"token keyword\">DO<\/span> $$<br \/>\n<span class=\"token keyword\">BEGIN<\/span><br \/>\n  <span class=\"token keyword\">FOR<\/span> i <span class=\"token operator\">IN<\/span> <span class=\"token number\">0.<\/span><span class=\"token number\">.255<\/span> <span class=\"token keyword\">LOOP<\/span><br \/>\n    <span class=\"token keyword\">EXECUTE<\/span> <span class=\"token function\">format<\/span><span class=\"token punctuation\">(<\/span><span class=\"token string\">&#039;<br \/>\n      CREATE TABLE image_md5_url_p%s PARTITION OF image_md5_url<br \/>\n      FOR VALUES WITH (MODULUS 256, REMAINDER %s)<br \/>\n    &#039;<\/span><span class=\"token punctuation\">,<\/span> i<span class=\"token punctuation\">,<\/span> i<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><br \/>\n  <span class=\"token keyword\">END<\/span> <span class=\"token keyword\">LOOP<\/span><span class=\"token punctuation\">;<\/span><br \/>\n<span class=\"token keyword\">END<\/span> $$<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u4f18\u52bf&#xff1a;<\/p>\n<ul>\n<li>\u5355\u5206\u533a\u6570\u636e\u91cf\u53ef\u63a7&#xff08;~400 \u4e07\u884c\/\u5206\u533a&#xff09;<\/li>\n<li>VACUUM\/\u5907\u4efd\u53ef\u5e76\u884c<\/li>\n<li>\u67e5\u8be2\u4ecd\u8d70\u5168\u5c40\u7d22\u5f15&#xff08;\u900f\u660e&#xff09;<\/li>\n<\/ul>\n<h4>7.2 \u5206\u5e03\u5f0f\u6570\u636e\u5e93&#xff08;Citus&#xff09;<\/h4>\n<p>\u4f7f\u7528 Citus&#xff08;PostgreSQL \u5206\u5e03\u5f0f\u63d2\u4ef6&#xff09;\u6309 MD5 \u54c8\u5e0c\u5206\u7247\u3002\u5c06 PostgreSQL \u6269\u5c55\u4e3a\u5206\u5e03\u5f0f\u96c6\u7fa4&#xff1a;<\/p>\n<p><span class=\"token comment\">&#8212; \u5728 Citus \u4e2d\u5206\u5e03\u8868<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> create_distributed_table<span class=\"token punctuation\">(<\/span><span class=\"token string\">&#039;image_md5_url&#039;<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#039;md5&#039;<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u9002\u7528\u573a\u666f&#xff1a;<\/p>\n<ul>\n<li>\u6570\u636e\u91cf &gt; 10 \u4ebf<\/li>\n<li>\u9700\u8981\u6c34\u5e73\u6269\u5c55\u5199\u5165\u541e\u5410<\/li>\n<li>\u6709\u4e13\u804c DBA \u8fd0\u7ef4<\/li>\n<\/ul>\n<h4>7.3 \u6269\u5c55\u5efa\u8bae<\/h4>\n<p>1\u3001\u662f\u5426\u9700\u8981 TTL&#xff08;\u81ea\u52a8\u8fc7\u671f&#xff09;&#xff1f;<\/p>\n<ul>\n<li>\u82e5\u56fe\u7247\u94fe\u63a5\u6709\u65f6\u6548\u6027&#xff0c;\u53ef\u52a0 created_at TIMESTAMP \u5b57\u6bb5 &#043; \u5206\u533a\u6309\u65f6\u95f4<\/li>\n<li>\u914d\u5408 pg_cron \u5b9a\u671f\u5220\u9664\u65e7\u6570\u636e<\/li>\n<\/ul>\n<p>2\u3001\u662f\u5426\u9700\u8981\u7edf\u8ba1\u4fe1\u606f&#xff1f;<\/p>\n<ul>\n<li>\u5982\u201c\u6bcf\u4e2a MD5 \u88ab\u5f15\u7528\u6b21\u6570\u201d&#xff0c;\u53ef\u5355\u72ec\u5efa\u8ba1\u6570\u8868&#xff1a;<span class=\"token keyword\">CREATE<\/span> <span class=\"token keyword\">TABLE<\/span> image_ref_count <span class=\"token punctuation\">(<\/span><br \/>\n    md5 <span class=\"token keyword\">CHAR<\/span><span class=\"token punctuation\">(<\/span><span class=\"token number\">32<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">PRIMARY<\/span> <span class=\"token keyword\">KEY<\/span><span class=\"token punctuation\">,<\/span><br \/>\n    count <span class=\"token keyword\">INT<\/span> <span class=\"token operator\">NOT<\/span> <span class=\"token boolean\">NULL<\/span> <span class=\"token keyword\">DEFAULT<\/span> <span class=\"token number\">1<\/span><br \/>\n<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span>\n <\/li>\n<\/ul>\n<h3>\u516b\u3001\u76d1\u63a7\u4e0e\u8fd0\u7ef4<\/h3>\n<h4>8.1 \u5173\u952e\u76d1\u63a7\u6307\u6807<\/h4>\n<table>\n<tr>\u6307\u6807\u5de5\u5177\u544a\u8b66\u9608\u503c<\/tr>\n<tbody>\n<tr>\n<td>\u8868\u81a8\u80c0\u7387&#xff08;Bloat&#xff09;<\/td>\n<td>pg_bloat_check<\/td>\n<td>&gt; 30%<\/td>\n<\/tr>\n<tr>\n<td>WAL \u751f\u6210\u901f\u7387<\/td>\n<td>pg_stat_wal<\/td>\n<td>\u7a81\u589e 200%<\/td>\n<\/tr>\n<tr>\n<td>\u7d22\u5f15\u547d\u4e2d\u7387<\/td>\n<td>pg_stat_user_indexes<\/td>\n<td>&lt; 99%<\/td>\n<\/tr>\n<tr>\n<td>\u9501\u7b49\u5f85\u65f6\u95f4<\/td>\n<td>pg_locks<\/td>\n<td>&gt; 1s<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>8.2 \u5b9a\u671f\u7ef4\u62a4\u4efb\u52a1<\/h4>\n<ul>\n<li>\u6bcf\u5468&#xff1a;REINDEX TABLE image_md5_url&#xff08;\u82e5\u7d22\u5f15\u788e\u7247 &gt; 20%&#xff09;<\/li>\n<li>\u6bcf\u65e5&#xff1a;\u68c0\u67e5 autovacuum \u662f\u5426\u53ca\u65f6\u8fd0\u884c<\/li>\n<li>\u6bcf\u5b63\u5ea6&#xff1a;\u8bc4\u4f30\u662f\u5426\u9700\u8981\u65b0\u589e\u5206\u533a<\/li>\n<\/ul>\n<h3>\u4e5d\u3001\u5b8c\u6574\u4ee3\u7801\u793a\u4f8b&#xff08;\u5f02\u6b65\u6279\u91cf\u5199\u5165&#xff09;<\/h3>\n<p><span class=\"token comment\"># database.py<\/span><br \/>\n<span class=\"token keyword\">from<\/span> sqlalchemy<span class=\"token punctuation\">.<\/span>ext<span class=\"token punctuation\">.<\/span>asyncio <span class=\"token keyword\">import<\/span> create_async_engine<span class=\"token punctuation\">,<\/span> AsyncSession<br \/>\n<span class=\"token keyword\">from<\/span> sqlalchemy<span class=\"token punctuation\">.<\/span>orm <span class=\"token keyword\">import<\/span> sessionmaker<\/p>\n<p>engine <span class=\"token operator\">&#061;<\/span> create_async_engine<span class=\"token punctuation\">(<\/span><br \/>\n    <span class=\"token string\">&#034;postgresql&#043;asyncpg:\/\/user:pass&#064;localhost\/db&#034;<\/span><span class=\"token punctuation\">,<\/span><br \/>\n    pool_size<span class=\"token operator\">&#061;<\/span><span class=\"token number\">20<\/span><span class=\"token punctuation\">,<\/span> max_overflow<span class=\"token operator\">&#061;<\/span><span class=\"token number\">30<\/span><span class=\"token punctuation\">,<\/span> pool_pre_ping<span class=\"token operator\">&#061;<\/span><span class=\"token boolean\">True<\/span><br \/>\n<span class=\"token punctuation\">)<\/span><br \/>\nAsyncSessionLocal <span class=\"token operator\">&#061;<\/span> sessionmaker<span class=\"token punctuation\">(<\/span>engine<span class=\"token punctuation\">,<\/span> class_<span class=\"token operator\">&#061;<\/span>AsyncSession<span class=\"token punctuation\">,<\/span> expire_on_commit<span class=\"token operator\">&#061;<\/span><span class=\"token boolean\">False<\/span><span class=\"token punctuation\">)<\/span><\/p>\n<p><span class=\"token comment\"># main.py<\/span><br \/>\n<span class=\"token keyword\">import<\/span> asyncio<br \/>\n<span class=\"token keyword\">from<\/span> sqlalchemy <span class=\"token keyword\">import<\/span> text<\/p>\n<p><span class=\"token keyword\">async<\/span> <span class=\"token keyword\">def<\/span> <span class=\"token function\">worker<\/span><span class=\"token punctuation\">(<\/span>queue<span class=\"token punctuation\">,<\/span> worker_id<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">:<\/span><br \/>\n    <span class=\"token keyword\">async<\/span> <span class=\"token keyword\">with<\/span> AsyncSessionLocal<span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">as<\/span> session<span class=\"token punctuation\">:<\/span><br \/>\n        batch <span class=\"token operator\">&#061;<\/span> <span class=\"token punctuation\">[<\/span><span class=\"token punctuation\">]<\/span><br \/>\n        <span class=\"token keyword\">while<\/span> <span class=\"token boolean\">True<\/span><span class=\"token punctuation\">:<\/span><br \/>\n            <span class=\"token keyword\">try<\/span><span class=\"token punctuation\">:<\/span><br \/>\n                item <span class=\"token operator\">&#061;<\/span> <span class=\"token keyword\">await<\/span> asyncio<span class=\"token punctuation\">.<\/span>wait_for<span class=\"token punctuation\">(<\/span>queue<span class=\"token punctuation\">.<\/span>get<span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span> timeout<span class=\"token operator\">&#061;<\/span><span class=\"token number\">2.0<\/span><span class=\"token punctuation\">)<\/span><br \/>\n                <span class=\"token keyword\">if<\/span> item <span class=\"token keyword\">is<\/span> <span class=\"token boolean\">None<\/span><span class=\"token punctuation\">:<\/span> <span class=\"token keyword\">break<\/span><br \/>\n                batch<span class=\"token punctuation\">.<\/span>append<span class=\"token punctuation\">(<\/span>item<span class=\"token punctuation\">)<\/span><\/p>\n<p>                <span class=\"token keyword\">if<\/span> <span class=\"token builtin\">len<\/span><span class=\"token punctuation\">(<\/span>batch<span class=\"token punctuation\">)<\/span> <span class=\"token operator\">&gt;&#061;<\/span> <span class=\"token number\">5000<\/span><span class=\"token punctuation\">:<\/span><br \/>\n                    <span class=\"token keyword\">await<\/span> save_batch<span class=\"token punctuation\">(<\/span>session<span class=\"token punctuation\">,<\/span> batch<span class=\"token punctuation\">)<\/span><br \/>\n                    batch<span class=\"token punctuation\">.<\/span>clear<span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><br \/>\n            <span class=\"token keyword\">except<\/span> asyncio<span class=\"token punctuation\">.<\/span>TimeoutError<span class=\"token punctuation\">:<\/span><br \/>\n                <span class=\"token keyword\">if<\/span> batch<span class=\"token punctuation\">:<\/span> <span class=\"token keyword\">await<\/span> save_batch<span class=\"token punctuation\">(<\/span>session<span class=\"token punctuation\">,<\/span> batch<span class=\"token punctuation\">)<\/span><br \/>\n                <span class=\"token keyword\">break<\/span><\/p>\n<p><span class=\"token keyword\">async<\/span> <span class=\"token keyword\">def<\/span> <span class=\"token function\">save_batch<\/span><span class=\"token punctuation\">(<\/span>session<span class=\"token punctuation\">,<\/span> batch<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">:<\/span><br \/>\n    stmt <span class=\"token operator\">&#061;<\/span> text<span class=\"token punctuation\">(<\/span><span class=\"token triple-quoted-string string\">&#034;&#034;&#034;<br \/>\n        INSERT INTO image_md5_url (md5, url)<br \/>\n        VALUES (:md5, :url)<br \/>\n        ON CONFLICT (md5) DO NOTHING<br \/>\n    &#034;&#034;&#034;<\/span><span class=\"token punctuation\">)<\/span><br \/>\n    <span class=\"token keyword\">await<\/span> session<span class=\"token punctuation\">.<\/span>execute<span class=\"token punctuation\">(<\/span>stmt<span class=\"token punctuation\">,<\/span> <span class=\"token punctuation\">[<\/span><span class=\"token punctuation\">{<\/span><span class=\"token string\">&#034;md5&#034;<\/span><span class=\"token punctuation\">:<\/span> m<span class=\"token punctuation\">.<\/span>lower<span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span> <span class=\"token string\">&#034;url&#034;<\/span><span class=\"token punctuation\">:<\/span> u<span class=\"token punctuation\">}<\/span> <span class=\"token keyword\">for<\/span> m<span class=\"token punctuation\">,<\/span> u <span class=\"token keyword\">in<\/span> batch<span class=\"token punctuation\">]<\/span><span class=\"token punctuation\">)<\/span><br \/>\n    <span class=\"token keyword\">await<\/span> session<span class=\"token punctuation\">.<\/span>commit<span class=\"token punctuation\">(<\/span><span class=\"token punctuation\">)<\/span><\/p>\n<p>\u6027\u80fd\u5b9e\u6d4b&#xff08;16C32G &#043; NVMe SSD&#xff09;&#xff1a;<\/p>\n<ul>\n<li>1 \u4ebf\u6761\u63d2\u5165&#xff1a;38 \u5206\u949f<\/li>\n<li>\u5e73\u5747\u5199\u5165\u901f\u5ea6&#xff1a;44,000 \u6761\/\u79d2<\/li>\n<li>\u78c1\u76d8\u5360\u7528&#xff1a;10.2 GB<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u6587\u7ae0\u76ee\u5f55\u4e00\u3001\u5f15\u8a00&#xff1a;\u95ee\u9898\u80cc\u666f\u4e0e\u6311\u6218\u4e8c\u3001\u5982\u4f55\u8bbe\u8ba1&#xff1f;2.1 \u6838\u5fc3\u539f\u5219&#xff1a;\u4ee5\u4e1a\u52a1\u8bbf\u95ee\u6a21\u5f0f\u9a71\u52a8\u8bbe\u8ba12.2 \u4e3a\u4ec0\u4e48\u4e0d\u9700\u8981\u81ea\u589eID2.3 \u8bbe\u8ba1\u5efa\u8bae\u4e09\u3001\u8868\u7ed3\u6784\u8bbe\u8ba1&#xff1a;\u7cbe\u7b80\u3001\u9ad8\u6548\u3001\u65e0\u5197\u4f593.1 \u5b57\u6bb5\u7c7b\u578b\u9009\u62e93.2 \u4e3b\u952e\u4e0e\u7ea6\u675f3.3 \u662f\u5426\u9700\u8981 URL \u552f\u4e00\u7d22\u5f15&#xff1f;\u56db\u3001\u5199\u5165\u6027\u80fd\u4f18\u5316&#xff1a;\u6279\u91cf  \u5e42\u7b49  \u5f02\u6b654.1 \u6279\u91cf\u63d2\u5165&#xff08;Batch Insert&#xff09;4.2 \u5e42\u7b49\u5199\u5165&#xff1a;ON CONFL<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[954,100],"topic":[],"class_list":["post-61627","post","type-post","status-publish","format-standard","hentry","category-server","tag-postgresql","tag-100"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>\u5728PostgreSQL\u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u4e2a\u56fe\u7247\u94fe\u63a5\u548c\u56fe\u7247md5\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u5982\u4f55\u505a\u6700\u5408\u9002\uff1f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.wsisp.com\/helps\/61627.html\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"\u5728PostgreSQL\u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u4e2a\u56fe\u7247\u94fe\u63a5\u548c\u56fe\u7247md5\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u5982\u4f55\u505a\u6700\u5408\u9002\uff1f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"og:description\" content=\"\u6587\u7ae0\u76ee\u5f55\u4e00\u3001\u5f15\u8a00&#xff1a;\u95ee\u9898\u80cc\u666f\u4e0e\u6311\u6218\u4e8c\u3001\u5982\u4f55\u8bbe\u8ba1&#xff1f;2.1 \u6838\u5fc3\u539f\u5219&#xff1a;\u4ee5\u4e1a\u52a1\u8bbf\u95ee\u6a21\u5f0f\u9a71\u52a8\u8bbe\u8ba12.2 \u4e3a\u4ec0\u4e48\u4e0d\u9700\u8981\u81ea\u589eID2.3 \u8bbe\u8ba1\u5efa\u8bae\u4e09\u3001\u8868\u7ed3\u6784\u8bbe\u8ba1&#xff1a;\u7cbe\u7b80\u3001\u9ad8\u6548\u3001\u65e0\u5197\u4f593.1 \u5b57\u6bb5\u7c7b\u578b\u9009\u62e93.2 \u4e3b\u952e\u4e0e\u7ea6\u675f3.3 \u662f\u5426\u9700\u8981 URL \u552f\u4e00\u7d22\u5f15&#xff1f;\u56db\u3001\u5199\u5165\u6027\u80fd\u4f18\u5316&#xff1a;\u6279\u91cf \u5e42\u7b49 \u5f02\u6b654.1 \u6279\u91cf\u63d2\u5165&#xff08;Batch Insert&#xff09;4.2 \u5e42\u7b49\u5199\u5165&#xff1a;ON CONFL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.wsisp.com\/helps\/61627.html\" \/>\n<meta property=\"og:site_name\" content=\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"article:published_time\" content=\"2026-01-18T00:41:49+00:00\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/61627.html\",\"url\":\"https:\/\/www.wsisp.com\/helps\/61627.html\",\"name\":\"\u5728PostgreSQL\u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u4e2a\u56fe\u7247\u94fe\u63a5\u548c\u56fe\u7247md5\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u5982\u4f55\u505a\u6700\u5408\u9002\uff1f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"isPartOf\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\"},\"datePublished\":\"2026-01-18T00:41:49+00:00\",\"dateModified\":\"2026-01-18T00:41:49+00:00\",\"author\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/61627.html#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.wsisp.com\/helps\/61627.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/61627.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.wsisp.com\/helps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"\u5728PostgreSQL\u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u4e2a\u56fe\u7247\u94fe\u63a5\u548c\u56fe\u7247md5\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u5982\u4f55\u505a\u6700\u5408\u9002\uff1f\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\",\"url\":\"https:\/\/www.wsisp.com\/helps\/\",\"name\":\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"description\":\"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"contentUrl\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"caption\":\"admin\"},\"sameAs\":[\"http:\/\/wp.wsisp.com\"],\"url\":\"https:\/\/www.wsisp.com\/helps\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"\u5728PostgreSQL\u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u4e2a\u56fe\u7247\u94fe\u63a5\u548c\u56fe\u7247md5\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u5982\u4f55\u505a\u6700\u5408\u9002\uff1f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.wsisp.com\/helps\/61627.html","og_locale":"zh_CN","og_type":"article","og_title":"\u5728PostgreSQL\u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u4e2a\u56fe\u7247\u94fe\u63a5\u548c\u56fe\u7247md5\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u5982\u4f55\u505a\u6700\u5408\u9002\uff1f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","og_description":"\u6587\u7ae0\u76ee\u5f55\u4e00\u3001\u5f15\u8a00&#xff1a;\u95ee\u9898\u80cc\u666f\u4e0e\u6311\u6218\u4e8c\u3001\u5982\u4f55\u8bbe\u8ba1&#xff1f;2.1 \u6838\u5fc3\u539f\u5219&#xff1a;\u4ee5\u4e1a\u52a1\u8bbf\u95ee\u6a21\u5f0f\u9a71\u52a8\u8bbe\u8ba12.2 \u4e3a\u4ec0\u4e48\u4e0d\u9700\u8981\u81ea\u589eID2.3 \u8bbe\u8ba1\u5efa\u8bae\u4e09\u3001\u8868\u7ed3\u6784\u8bbe\u8ba1&#xff1a;\u7cbe\u7b80\u3001\u9ad8\u6548\u3001\u65e0\u5197\u4f593.1 \u5b57\u6bb5\u7c7b\u578b\u9009\u62e93.2 \u4e3b\u952e\u4e0e\u7ea6\u675f3.3 \u662f\u5426\u9700\u8981 URL \u552f\u4e00\u7d22\u5f15&#xff1f;\u56db\u3001\u5199\u5165\u6027\u80fd\u4f18\u5316&#xff1a;\u6279\u91cf \u5e42\u7b49 \u5f02\u6b654.1 \u6279\u91cf\u63d2\u5165&#xff08;Batch Insert&#xff09;4.2 \u5e42\u7b49\u5199\u5165&#xff1a;ON CONFL","og_url":"https:\/\/www.wsisp.com\/helps\/61627.html","og_site_name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","article_published_time":"2026-01-18T00:41:49+00:00","author":"admin","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"admin","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"6 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.wsisp.com\/helps\/61627.html","url":"https:\/\/www.wsisp.com\/helps\/61627.html","name":"\u5728PostgreSQL\u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u4e2a\u56fe\u7247\u94fe\u63a5\u548c\u56fe\u7247md5\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u5982\u4f55\u505a\u6700\u5408\u9002\uff1f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","isPartOf":{"@id":"https:\/\/www.wsisp.com\/helps\/#website"},"datePublished":"2026-01-18T00:41:49+00:00","dateModified":"2026-01-18T00:41:49+00:00","author":{"@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41"},"breadcrumb":{"@id":"https:\/\/www.wsisp.com\/helps\/61627.html#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.wsisp.com\/helps\/61627.html"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.wsisp.com\/helps\/61627.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.wsisp.com\/helps"},{"@type":"ListItem","position":2,"name":"\u5728PostgreSQL\u4e2d\u5e76\u53d1\u4fdd\u5b58\u4e0a\u4ebf\u4e2a\u56fe\u7247\u94fe\u63a5\u548c\u56fe\u7247md5\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u5982\u4f55\u505a\u6700\u5408\u9002\uff1f"}]},{"@type":"WebSite","@id":"https:\/\/www.wsisp.com\/helps\/#website","url":"https:\/\/www.wsisp.com\/helps\/","name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","description":"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41","name":"admin","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/","url":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","contentUrl":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","caption":"admin"},"sameAs":["http:\/\/wp.wsisp.com"],"url":"https:\/\/www.wsisp.com\/helps\/author\/admin"}]}},"_links":{"self":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/61627","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/comments?post=61627"}],"version-history":[{"count":0,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/61627\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/media?parent=61627"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/categories?post=61627"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/tags?post=61627"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/topic?post=61627"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}