{"id":249,"date":"2026-03-09T13:28:35","date_gmt":"2026-03-09T13:28:35","guid":{"rendered":"https:\/\/blog.rebalai.com\/ko\/2026\/03\/09\/postgresql-performance-tuning-what-i-learned-optim\/"},"modified":"2026-03-18T22:00:11","modified_gmt":"2026-03-18T22:00:11","slug":"postgresql-performance-tuning-what-i-learned-optim","status":"publish","type":"post","link":"https:\/\/blog.rebalai.com\/ko\/2026\/03\/09\/postgresql-performance-tuning-what-i-learned-optim\/","title":{"rendered":"PostgreSQL 10TB \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ucffc\ub9ac \ucd5c\uc801\ud654\uc5d0\uc11c \ubc30\uc6b4 \uac83\ub4e4"},"content":{"rendered":"<p>\uc0c8\ubcbd 2\uc2dc\uc5d0 \ud398\uc774\uc800\uac00 \uc6b8\ub838\ub2e4. Slack \uc54c\ub9bc\uc774 \uc544\ub2c8\ub77c \uc9c4\uc9dc PagerDuty \uc18c\ub9ac\uc600\ub2e4. \ubaa8\ub2c8\ud130\ub97c \ucf1c\ubcf4\ub2c8 \uba54\uc778 \ub300\uc2dc\ubcf4\ub4dc API\uc758 p99 \uc751\ub2f5 \uc2dc\uac04\uc774 \ud3c9\uc18c 230ms\uc5d0\uc11c 47\ucd08\ub85c \ud280\uc5b4 \uc788\uc5c8\ub2e4. \uc6b0\ub9ac \ud300\uc774 \uc6b4\uc601\ud558\ub294 e-commerce \ud50c\ub7ab\ud3fc \u2014 5\uba85\uc9dc\ub9ac \ud300, PostgreSQL 14.8 \u2014 \uc758 \uc8fc\ubb38 \uc870\ud68c \ucffc\ub9ac\uac00 \uc644\uc804\ud788 \uba48\ucdb0\ubc84\ub9b0 \uac70\uc600\ub2e4.<\/p>\n<p>2025\ub144 3\uc6d4 \uc911\uc21c\uc758 \uc77c\uc774\ub2e4. orders \ud14c\uc774\ube14 \ud63c\uc790 3.2TB, \uc5f0\uad00 \ud14c\uc774\ube14\ub4e4\uae4c\uc9c0 \ud569\uce58\uba74 \ucd1d 10TB\uac00 \ub118\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc600\ub2e4. \uc774\ud6c4 2\uc8fc\ub97c \uac70\uc758 \uc774 \ubb38\uc81c\uc5d0\ub9cc \ub9e4\ub2ec\ub838\uace0, \uc774 \uae00\uc740 \uadf8 \uacfc\uc815\uc5d0\uc11c \ubc30\uc6b4 \uac83\ub4e4\uc744 \uc815\ub9ac\ud55c \uac70\ub2e4.<\/p>\n<h2>\uc0c8\ubcbd\uc5d0 \ucc98\uc74c \ub4e0 \uc624\ud574, \uadf8\ub9ac\uace0 \uc2e4\uc81c \ubc94\uc778<\/h2>\n<p>\uccab \ubc18\uc751\uc740 \uc608\uc0c1 \uac00\ub2a5\ud588\ub2e4. &#8220;\uc778\ub371\uc2a4 \ubb38\uc81c\uaca0\uc9c0.&#8221; \uadf8\ub798\uc11c <code>\\d orders<\/code>\ub85c \uc778\ub371\uc2a4 \ubaa9\ub85d\uc744 \ud655\uc778\ud558\uace0 <code>pg_stat_user_indexes<\/code>\ub97c \ud6d1\uc5b4\ubd24\ub2e4. \uc778\ub371\uc2a4\ub294 \uc788\uc5c8\ub2e4. <code>created_at<\/code>, <code>user_id<\/code>, \uc2ec\uc9c0\uc5b4 \ubcf5\ud569 \uc778\ub371\uc2a4\ub3c4.<\/p>\n<p>\uadf8\ub7f0\ub370 \uc2ac\ub85c\uc6b0 \ucffc\ub9ac \ub85c\uadf8\ub97c \ubcf4\ub2c8 \uc5c9\ub6b1\ud55c \uacf3\uc5d0\uc11c \ubb38\uc81c\uac00 \ud130\uc9c0\uace0 \uc788\uc5c8\ub2e4. <code>pg_stat_statements<\/code>\ub97c \ucf1c\ub1a8\uae30 \ub54c\ubb38\uc5d0 \ubc94\uc778\uc744 \ud2b9\uc815\ud558\ub294 \uac74 \uae08\ubc29\uc774\uc5c8\ub2e4:<\/p>\n<pre><code class=\"language-sql\">SELECT\n  round(total_exec_time::numeric, 2) AS total_ms,\n  calls,\n  round(mean_exec_time::numeric, 2) AS mean_ms,\n  substring(query, 1, 80) AS query_snippet\nFROM pg_stat_statements\nORDER BY mean_exec_time DESC\nLIMIT 10;\n<\/code><\/pre>\n<p>\ud3c9\uade0 \uc2e4\ud589 \uc2dc\uac04\uc774 38\ucd08\uc9dc\ub9ac \ucffc\ub9ac\uac00 \uc788\uc5c8\ub2e4. \ud638\ucd9c \ud69f\uc218\ub294 \uc2dc\uac04\ub2f9 200\ubc88 \uc815\ub3c4. \uc218\ud559\uc774 \ub9de\uc544 \ub5a8\uc5b4\uc84c\ub2e4.<\/p>\n<p>\ubb38\uc81c\uc758 \ucffc\ub9ac\ub294 \uc774\ub7f0 \ubaa8\uc591\uc774\uc5c8\ub2e4:<\/p>\n<pre><code class=\"language-sql\">SELECT o.*, u.email, SUM(oi.quantity * oi.unit_price) AS total\nFROM orders o\nJOIN users u ON o.user_id = u.id\nJOIN order_items oi ON o.id = oi.order_id\nWHERE o.status IN ('pending', 'processing')\n  AND o.created_at &gt; NOW() - INTERVAL '30 days'\nGROUP BY o.id, u.email\nORDER BY o.created_at DESC;\n<\/code><\/pre>\n<p>\ubcc4\ub85c \ubcf5\uc7a1\ud574 \ubcf4\uc774\uc9c0 \uc54a\ub294\ub2e4. \uadf8\ub7f0\ub370 \uc774 \ucffc\ub9ac\uac00 3.2TB \ud14c\uc774\ube14\uc5d0\uc11c sequential scan\uc744 \ud558\uace0 \uc788\uc5c8\ub2e4.<\/p>\n<h2>EXPLAIN ANALYZE\ub97c \ubc18\ucabd\ub9cc \uc77d\uace0 \uc788\uc5c8\ub2e4<\/h2>\n<p>\uc194\uc9c1\ud788 \ub9d0\ud558\uba74, \ub098\ub294 \uba87 \ub144 \ub3d9\uc548 <code>EXPLAIN ANALYZE<\/code>\ub97c \uc798\ubabb \uc4f0\uace0 \uc788\uc5c8\ub2e4. <code>Seq Scan<\/code>\uc774 \ubcf4\uc774\uba74 &#8220;\uc778\ub371\uc2a4 \ucd94\uac00\ud558\uba74 \ub418\uaca0\ub124&#8221; \ud558\uace0 \ub118\uc5b4\uac14\ub294\ub370, \uc9c4\uc9dc \uc911\uc694\ud55c \uac74 <code>BUFFERS<\/code> \uc635\uc158\uc774\uc5c8\ub2e4.<\/p>\n<pre><code class=\"language-sql\">EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT o.*, u.email, SUM(oi.quantity * oi.unit_price) AS total\nFROM orders o\nJOIN users u ON o.user_id = u.id\nJOIN order_items oi ON o.id = oi.order_id\nWHERE o.status IN ('pending', 'processing')\n  AND o.created_at &gt; NOW() - INTERVAL '30 days'\nGROUP BY o.id, u.email\nORDER BY o.created_at DESC;\n<\/code><\/pre>\n<p><code>BUFFERS<\/code> \uc635\uc158\uc744 \ub123\uc73c\uba74 shared hit(\uce90\uc2dc\uc5d0\uc11c \uc77d\uc740 \ube14\ub85d)\uacfc shared read(\ub514\uc2a4\ud06c\uc5d0\uc11c \uc77d\uc740 \ube14\ub85d)\uac00 \uac19\uc774 \ucd9c\ub825\ub41c\ub2e4. \uc6b0\ub9ac \ucf00\uc774\uc2a4\uc5d0\uc11c shared read\uac00 2.1M \ube14\ub85d\uc774\uc5c8\ub2e4. \uac70\uc758 \ubaa8\ub4e0 \uac78 \ub514\uc2a4\ud06c\uc5d0\uc11c \uc77d\uace0 \uc788\uc5c8\ub2e4\ub294 \ub73b\uc774\ub2e4.<\/p>\n<p><code>effective_cache_size<\/code> \uc124\uc815\ub3c4 \ubb38\uc81c\uc600\ub2e4. \uae30\ubcf8\uac12\uc73c\ub85c \uc11c\ubc84 RAM\uc758 \uc808\ubc18\ub3c4 \uc548 \ub418\ub294 \uac12\uc774 \ub4e4\uc5b4\uac00 \uc788\uc5c8\ub2e4. PostgreSQL \ucffc\ub9ac \ud50c\ub798\ub108\ub294 \uc774 \uac12\uc744 \ubcf4\uace0 \uc778\ub371\uc2a4 \uc2a4\uce94\uc774 \uc5bc\ub9c8\ub098 \uc774\ub4dd\uc778\uc9c0 \ud310\ub2e8\ud558\ub294\ub370, \ub0b4\uac00 \uc774 \uc124\uc815\uc744 \ubc29\uce58\ud55c \uac8c \uaf64 \ube44\uc30c\ub2e4.<\/p>\n<p><code>work_mem<\/code>\ub3c4 \uac74\ub4dc\ub838\ub2e4. \uae30\ubcf8\uac12 4MB\ub294 \uc6b0\ub9ac \uc815\ub82c \uc5f0\uc0b0\uc5d0 \ud131\uc5c6\uc774 \ubd80\uc871\ud574\uc11c temporary file\uc774 \uacc4\uc18d \ub9cc\ub4e4\uc5b4\uc9c0\uace0 \uc788\uc5c8\ub2e4. <code>EXPLAIN ANALYZE<\/code> \ucd9c\ub825\uc5d0\uc11c &#8220;Sort Method: external merge Disk&#8221; \uac19\uc740 \ubb38\uad6c\uac00 \ubcf4\uc774\uba74 \uc774\uac8c \uc2e0\ud638\ub2e4. \uc804\uc5ed \uc124\uc815\uc744 \uac74\ub4dc\ub9ac\uba74 \uc5f0\uacb0 \uc218\uac00 \ub9ce\uc744 \ub54c OOM\uc73c\ub85c \uc774\uc5b4\uc9c8 \uc218 \uc788\uc5b4\uc11c, \ud2b9\uc815 \ucffc\ub9ac \uc2e4\ud589 \uc804\uc5d0\ub9cc <code>SET work_mem = '128MB'<\/code>\ub85c \uc138\uc158 \ub2e8\uc704\ub85c \uc870\uc815\ud588\ub2e4.<\/p>\n<p><code>EXPLAIN ANALYZE<\/code> \ucd9c\ub825\uc744 \uc77d\ub294 \uac8c \ucc98\uc74c\uc5d4 \uc815\ub9d0 \uc5b4\ub835\ub2e4. \ub178\ub4dc\ub97c \uc548\ucabd\uc5d0\uc11c \ubc14\uae65\ucabd\uc73c\ub85c \uc77d\uc5b4\uc57c \ud558\uace0, actual rows\uc640 estimated rows \ucc28\uc774\uac00 \ud074\uc218\ub85d \ud1b5\uacc4\uac00 \uc624\ub798\ub410\ub2e4\ub294 \uc2e0\ud638\ub2e4. <code>ANALYZE<\/code> \uba85\ub839\uc5b4\ub85c \ud1b5\uacc4\ub97c \uc218\ub3d9 \uac31\uc2e0\ud558\uace0 \ub098\uc11c \ud50c\ub798\ub108 \ub3d9\uc791\uc774 \ub2ec\ub77c\uc9c4 \uacbd\uc6b0\ub3c4 \uc788\uc5c8\ub2e4.<\/p>\n<h2>\uc778\ub371\uc2a4\ub97c \ub354 \uc313\uc558\ub354\ub2c8 \uc624\ud788\ub824 \ub290\ub824\uc84c\ub2e4<\/h2>\n<p>\uc774\uac8c \uc9c4\uc9dc \uc608\uc0c1 \ubabb \ud55c \ubd80\ubd84\uc774\uc5c8\ub2e4. \ucc98\uc74c\uc5d0 \ub0b4\uac00 \ud55c \uc77c\uc740 partial index\ub97c \ucd94\uac00\ud558\ub294 \uac70\uc600\ub2e4:<\/p>\n<pre><code class=\"language-sql\">-- status \ud544\ud130 + \ub0a0\uc9dc \ud544\ud130 + \uc815\ub82c\uc744 \ud55c \ubc88\uc5d0 \ucee4\ubc84\ud558\ub824\uace0\nCREATE INDEX CONCURRENTLY idx_orders_status_created\nON orders (status, created_at DESC)\nWHERE status IN ('pending', 'processing');\n<\/code><\/pre>\n<p>\uc870\uac74\uc5d0 \ub9de\ub294 \ud589\ub9cc \uc778\ub371\uc2a4\uc5d0 \ub4e4\uc5b4\uac00\uc11c \ud06c\uae30\uac00 \uc791\ub2e4. \uc774\uac74 \uc798 \uc791\ub3d9\ud588\ub2e4. \ucffc\ub9ac \uc2dc\uac04\uc774 38\ucd08\uc5d0\uc11c 4\ucd08\ub300\ub85c \uc904\uc5c8\ub2e4.<\/p>\n<p>\uadf8\ub7f0\ub370 \ubb38\uc81c\ub294 \ub2e4\ub978 \ub370\uc11c \ub0ac\ub2e4. orders \ud14c\uc774\ube14\uc5d0\ub294 \uc774\ubbf8 \uc778\ub371\uc2a4\uac00 11\uac1c \uc788\uc5c8\ub2e4. \uc8fc\ubb38\uc774 \uc0dd\uc131\ub418\uac70\ub098 \uc5c5\ub370\uc774\ud2b8\ub420 \ub54c\ub9c8\ub2e4 11\uac1c \uc778\ub371\uc2a4\ub97c \uc804\ubd80 \uac31\uc2e0\ud574\uc57c \ud55c\ub2e4. <code>pg_stat_user_indexes<\/code>\ub97c \ub2e4\uc2dc \ud655\uc778\ud574\ubcf4\ub2c8 \uadf8 \uc911 3\uac1c\ub294 <code>idx_scan<\/code>\uc774 \uc9c0\ub09c 6\uac1c\uc6d4 \ub3d9\uc548 0\uc774\uc5c8\ub2e4. \uc544\ubb34\ub3c4 \uc4f0\uc9c0 \uc54a\ub294 \uc778\ub371\uc2a4\uac00 write \uc131\ub2a5\uc744 \uac09\uc544\uba39\uace0 \uc788\uc5c8\ub358 \uac70\ub2e4.<\/p>\n<p>\uc4f0\uc9c0 \uc54a\ub294 \uc778\ub371\uc2a4 3\uac1c\ub97c \uc0ad\uc81c\ud588\ub354\ub2c8 \uc8fc\ubb38 \uc0dd\uc131 API \uc751\ub2f5 \uc2dc\uac04\uc774 18% \uc904\uc5c8\ub2e4. \uc791\uc740 \uc22b\uc790\ucc98\ub7fc \ubcf4\uc774\uc9c0\ub9cc, \ud2b8\ub798\ud53d\uc774 \ubab0\ub9b4 \ub54c\ub294 \uccb4\uac10\uc774 \ub410\ub2e4.<\/p>\n<p>\uc778\ub371\uc2a4 bloat \ubb38\uc81c\ub3c4 \uc788\uc5c8\ub2e4. <code>pgstattuple<\/code> \uc775\uc2a4\ud150\uc158\uc73c\ub85c \ud655\uc778\ud574\ubcf4\ub2c8 \uc77c\ubd80 \uc778\ub371\uc2a4\ub294 dead tuple\uc774 35%\ub97c \ub118\uace0 \uc788\uc5c8\ub2e4. <code>REINDEX CONCURRENTLY<\/code>\ub85c \uc7ac\uad6c\uc131\ud558\uace0 \ub098\uc11c \uc778\ub371\uc2a4 \ud06c\uae30\uac00 \ub208\uc5d0 \ub744\uac8c \uc904\uc5c8\ub2e4. autovacuum\uc774 \uc788\ub294\ub370 \uc65c \uc774\ub807\uac8c \ub410\ub0d0\uba74 \u2014 \uae30\ubcf8 <code>autovacuum_vacuum_scale_factor<\/code>\uac00 0.2, \uc989 \ud14c\uc774\ube14 \ud589\uc758 20%\uac00 dead tuple\uc774 \ub3fc\uc57c \uccad\uc18c\uac00 \uc2dc\uc791\ub41c\ub2e4\ub294 \ub73b\uc778\ub370, \uc218\uc2ed\uc5b5 \ud589\uc9dc\ub9ac \ud14c\uc774\ube14\uc5d0\uc11c 20%\uba74 \uc5b4\ub9c8\uc5b4\ub9c8\ud55c \uc591\uc774\ub2e4(\uba87\uc5b5 \uac74). \uc774\uac78 0.01\ub85c \ub0ae\ucd94\uace0 \ub098\uc11c\uc57c \uc815\uc0c1\uc801\uc73c\ub85c \uccad\uc18c\uac00 \ub410\ub2e4.<\/p>\n<h2>\ud30c\ud2f0\uc154\ub2dd\uc5d0 \uac78\uc5c8\ub358 \uae30\ub300\uc640 \uc2e4\uc81c\ub85c \uc5bb\uc740 \uac83<\/h2>\n<p>\uc774 \uc2dc\uc810\uc5d0\uc11c &#8220;\ud30c\ud2f0\uc154\ub2dd\uc744 \ud558\uba74 \uadfc\ubcf8\uc801\uc73c\ub85c \ud574\uacb0\ub418\uc9c0 \uc54a\uc744\uae4c&#8221;\ub77c\ub294 \uc0dd\uac01\uc774 \ub4e4\uc5c8\ub2e4. orders \ud14c\uc774\ube14\uc744 <code>created_at<\/code> \uae30\uc900 \ubd84\uae30\ubcc4 \ud30c\ud2f0\uc158\uc73c\ub85c \ucabc\uac1c\ub294 \uacc4\ud68d\uc774\uc5c8\ub2e4.<\/p>\n<p>\ub9c8\uc774\uadf8\ub808\uc774\uc158 \uc790\uccb4\ub294 \uace8\uce58 \uc544\ud320\ub2e4. 3.2TB\ub97c <code>pg_dump<\/code> \ubc1b\uc544 \ud30c\ud2f0\uc158 \ud14c\uc774\ube14\ub85c \ubcf5\uc6d0\ud558\ub294 \ub370 \uac70\uc758 \uc774\ud2c0\uc774 \uac78\ub838\ub2e4. \uad6c\uc870\ub294 \uc774\ub807\uac8c \ub9cc\ub4e4\uc5c8\ub2e4:<\/p>\n<pre><code class=\"language-sql\">CREATE TABLE orders (\n  id BIGSERIAL,\n  user_id BIGINT NOT NULL,\n  status VARCHAR(20) NOT NULL,\n  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n  PRIMARY KEY (id, created_at)  -- \ud30c\ud2f0\uc158 \ud0a4\uac00 PK\uc5d0 \ubc18\ub4dc\uc2dc \ud3ec\ud568\ub418\uc5b4\uc57c \ud568\n) PARTITION BY RANGE (created_at);\n\nCREATE TABLE orders_2025_q1 PARTITION OF orders\n  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');\n\nCREATE TABLE orders_2025_q2 PARTITION OF orders\n  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');\n<\/code><\/pre>\n<p>\uadf8\ub7f0\ub370 \uc5ec\uae30\uc11c \uc608\uc0c1 \ubabb \ud55c \ubb38\uc81c\uac00 \uc788\uc5c8\ub2e4. WHERE \uc808\uc5d0 <code>created_at<\/code>\uc774 \uc788\uc5b4\uc57c\ub9cc partition pruning\uc774 \uc791\ub3d9\ud55c\ub2e4. \ub2f9\uc5f0\ud55c \uc598\uae30 \uac19\uc9c0\ub9cc, \ucf54\ub4dc\ubca0\uc774\uc2a4 \uc5ec\uae30\uc800\uae30 \ud769\uc5b4\uc9c4 \ucffc\ub9ac\ub4e4 \uc911\uc5d0 <code>created_at<\/code> \ud544\ud130\uac00 \uc5c6\ub294 \uac8c \uaf64 \ub410\ub2e4. <code>EXPLAIN<\/code>\uc744 \ub3cc\ub824\ubcf4\uba74 \uc5ec\uc804\ud788 \ubaa8\ub4e0 \ud30c\ud2f0\uc158\uc744 \uc2a4\uce94\ud558\ub294 \uacbd\uc6b0\uac00 \uc788\uc5c8\ub2e4. \ud30c\ud2f0\uc154\ub2dd\uc744 \ud574\ub3c4 \ucffc\ub9ac\uac00 \ud30c\ud2f0\uc158 \ud0a4\ub97c \uc4f0\uc9c0 \uc54a\uc73c\uba74 \uadf8\ub0e5 \ub2e4 \uc77d\ub294\ub2e4.<\/p>\n<p>\ub354 \ud669\ub2f9\ud588\ub358 \uac74 PK \uc124\uc815\uc774\uc5c8\ub2e4. \ud30c\ud2f0\uc154\ub2dd\ub41c \ud14c\uc774\ube14\uc5d0\uc11c \uae00\ub85c\ubc8c unique constraint\ub294 \ud30c\ud2f0\uc158 \ud0a4\ub97c \ud3ec\ud568\ud574\uc57c \ud55c\ub2e4. \uc989 <code>id<\/code>\ub9cc\uc73c\ub85c\ub294 unique\ub97c \ubcf4\uc7a5\ud560 \uc218 \uc5c6\uace0, <code>(id, created_at)<\/code> \uc870\ud569\uc774 PK\uac00 \ub3fc\uc57c \ud55c\ub2e4. \uc774\uac78 \ubaa8\ub974\uace0 \uc2dc\uc791\ud588\ub2e4\uac00 orders\ub97c \ucc38\uc870\ud558\ub358 \ub2e4\ub978 \ud14c\uc774\ube14\ub4e4 \uc678\ub798 \ud0a4\ub97c \uc804\ubd80 \uc190\ub314\ub2e4 \u2014 PostgreSQL \ubb38\uc11c \uc5b4\ub518\uac00\uc5d0 \ubd84\uba85\ud788 \uc368\uc788\ub294 \ub0b4\uc6a9\uc778\ub370, \uc9c1\uc811 \ub2f9\ud574\ubd10\uc57c \uba38\ub9ac\uc5d0 \ubc15\ud788\ub294 \uc885\ub958\uc758 \uad50\ud6c8\uc774\ub2e4. \uc2a4\ud0a4\ub9c8 \ub9c8\uc774\uadf8\ub808\uc774\uc158\ub9cc \uba70\uce60\uc774 \uac78\ub838\ub2e4.<\/p>\n<p>\ud30c\ud2f0\uc154\ub2dd \ud6c4 30\uc77c\uce58 \uc870\ud68c \ucffc\ub9ac\ub294 4\ucd08\uc5d0\uc11c 0.3\ucd08\ub85c \uc904\uc5c8\ub2e4. \uc774\uac74 \ud30c\ud2f0\uc154\ub2dd \ub355\ubd84\uc774\uae30\ub3c4 \ud558\uc9c0\ub9cc, \ud30c\ud2f0\uc158\ubcc4\ub85c \uc778\ub371\uc2a4\ub97c \uc0c8\ub85c \ub9cc\ub4e4\uba74\uc11c bloat\ub3c4 \uac19\uc774 \ud574\uc18c\ub41c \ud6a8\uacfc\uac00 \uc11e\uc5ec \uc788\ub2e4.<\/p>\n<h2>\ub2e4\uc74c\uc5d0 \ub2e4\uc2dc \ud55c\ub2e4\uba74, \uc774 \uc21c\uc11c\ub85c \ud558\uaca0\ub2e4<\/h2>\n<p>\uadf8\ub798\uc11c \ubb58 \uba3c\uc800 \ud574\uc57c \ud558\ub0d0\uace0? \ub0b4 \uacbd\ud5d8\uc0c1 \uc21c\uc11c\uac00 \uc911\uc694\ud558\ub2e4.<\/p>\n<p><strong>\uccab \ubc88\uc9f8.<\/strong> <code>pg_stat_statements<\/code>\ub97c \ucf1c\uace0 2-3\uc77c \ub370\uc774\ud130\ub97c \ubaa8\uc544\ub77c. \ucd94\uce21\uc73c\ub85c \ucd5c\uc801\ud654\ud558\uc9c0 \ub9d0\uace0, \uc2e4\uc81c \ub290\ub9b0 \ucffc\ub9ac\ub97c \ub370\uc774\ud130\ub85c \ud655\uc778\ud574\ub77c. \ubc94\uc778\uc774 \ud2b9\uc815\ub418\uba74 <code>EXPLAIN (ANALYZE, BUFFERS)<\/code>\ub85c \uc2e4\ud589 \uacc4\ud68d\uc744 \ud655\uc778\ud55c\ub2e4. <code>BUFFERS<\/code> \uc5c6\uc774 \ubcf4\ub294 \uac74 \uc808\ubc18\ub9cc \ubcf4\ub294 \uac70\ub2e4.<\/p>\n<p><strong>\ub450 \ubc88\uc9f8.<\/strong> \ud604\uc7ac \uc778\ub371\uc2a4\ub97c \uac10\uc0ac\ud574\ub77c. <code>pg_stat_user_indexes<\/code>\uc5d0\uc11c <code>idx_scan = 0<\/code>\uc778 \uc778\ub371\uc2a4\ub97c \ucc3e\uc544\ub77c \u2014 \ub2e8, \ucd5c\uc18c 2\uc8fc \uc774\uc0c1\uc758 \ub370\uc774\ud130\ub97c \uae30\uc900\uc73c\ub85c.<\/p>\n<pre><code class=\"language-sql\">SELECT\n  tablename,\n  indexname,\n  idx_scan,\n  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\n  AND schemaname = 'public'\nORDER BY pg_relation_size(indexrelid) DESC;\n<\/code><\/pre>\n<p><strong>\uc138 \ubc88\uc9f8.<\/strong> autovacuum \uc124\uc815\uc744 \ud14c\uc774\ube14 \ud06c\uae30\uc5d0 \ub9de\uac8c \uc870\uc815\ud574\ub77c. \ub300\ud615 \ud14c\uc774\ube14\uc740 <code>autovacuum_vacuum_scale_factor<\/code>\ub97c \ub0ae\ucdb0\uc57c \ud55c\ub2e4.<\/p>\n<pre><code class=\"language-sql\">ALTER TABLE orders SET (\n  autovacuum_vacuum_scale_factor = 0.01,\n  autovacuum_analyze_scale_factor = 0.005\n);\n<\/code><\/pre>\n<p><strong>\ub124 \ubc88\uc9f8.<\/strong> \ud30c\ud2f0\uc154\ub2dd\uc740 \uc815\ub9d0 \ud544\uc694\ud55c\uc9c0 \uba3c\uc800 \ub530\uc838\ubd10\ub77c. \ub370\uc774\ud130 \uc811\uadfc \ud328\ud134\uc774 \uc2dc\uac04 \ubc94\uc704 \uae30\ubc18\uc774\uace0, \ud14c\uc774\ube14\uc774 \ucda9\ubd84\ud788 \ud06c\uace0(500GB \uc774\uc0c1), \ub9c8\uc774\uadf8\ub808\uc774\uc158 \ube44\uc6a9\uc744 \uac10\ub2f9\ud560 \uc218 \uc788\ub2e4\uba74 \uace0\ub824\ud574\ubcfc \ub9cc\ud558\ub2e4. \uadf8\ub807\uc9c0 \uc54a\ub2e4\uba74 partial index + vacuum \ud29c\ub2dd\uc774 \ud6e8\uc52c \uac04\ub2e8\ud558\uace0 \ud6a8\uacfc\uc801\uc774\ub2e4. \uc6b0\ub9ac \ucf00\uc774\uc2a4\ucc98\ub7fc \ucd5c\uadfc \ub370\uc774\ud130 \uc704\uc8fc\ub85c \uc811\uadfc\ud558\ub294 \uacbd\uc6b0\uc5d4 \ud30c\ud2f0\uc154\ub2dd\uc774 \ud655\uc2e4\ud788 \ud6a8\uacfc\uc801\uc774\uc5c8\ub2e4 \u2014 \ub2e4\ub9cc PK \uc81c\uc57d \ubb38\uc81c\ub97c \uc124\uacc4 \ub2e8\uacc4\uc5d0\uc11c \ubbf8\ub9ac \uace0\ub824\ud558\uace0 \ub4e4\uc5b4\uac00\uc57c \ud55c\ub2e4.<\/p>\n<p>PgBouncer\ub3c4 \ube7c\ub193\uc744 \uc218 \uc5c6\ub2e4. \uc5f0\uacb0 \uc218\uac00 \ub9ce\uc544\uc11c connection overhead\uac00 \uc0c1\ub2f9\ud588\ub294\ub370, transaction pooling \ubaa8\ub4dc\ub85c \ubd99\uc774\uace0 \ub098\uc11c DB \uc11c\ubc84 \ubd80\ud558\uac00 \ub208\uc5d0 \ub744\uac8c \uc904\uc5c8\ub2e4. \ucf54\ub4dc \ud55c \uc904 \uc548 \ubc14\uafb8\uace0 \uc778\ud504\ub77c \ub808\ubca8\uc5d0\uc11c \uc5bb\ub294 \uacf5\uc9dc \ucd5c\uc801\ud654\ub2e4.<\/p>\n<p>\ud55c \uac00\uc9c0 \uc911\uc694\ud55c \uc810\uc740 \u2014 \uae08\uc694\uc77c \uc624\ud6c4\uc5d0 \ub300\uaddc\ubaa8 \uc778\ub371\uc2a4 \ubcc0\uacbd\uc740 \uc808\ub300 \ud558\uc9c0 \ub9c8\ub77c. <code>REINDEX CONCURRENTLY<\/code>\uac00 &#8220;\ub3d9\uc2dc&#8221; \uc2e4\ud589\uc774\ub77c \uc548\uc804\ud558\ub2e4\uace0 \ubc29\uc2ec\ud588\ub2e4\uac00 \uae08\uc694\uc77c \uc624\ud6c4\uc5d0 lock \uacbd\uc7c1 \ub54c\ubb38\uc5d0 30\ubd84\uc9dc\ub9ac \uc7a5\uc560\ub97c \ub0c8\ub2e4. CONCURRENTLY\ub294 \ud14c\uc774\ube14 lock\uc744 \uc548 \uc7a1\ub294\ub2e4\ub294 \ub73b\uc774\uc9c0, \ubd80\ud558\uac00 \uc5c6\ub2e4\ub294 \ub73b\uc774 \uc544\ub2c8\ub2e4.<\/p>\n<p>\ucd5c\uc885 \uacb0\uacfc\ub294 \u2014 \ubb38\uc81c \ucffc\ub9ac 47\ucd08\uc5d0\uc11c 0.3\ucd08, write \uc131\ub2a5 18% \ud5a5\uc0c1 \u2014 \uc138 \uac00\uc9c0 \uc870\ud569\uc774\uc5c8\ub2e4: \uc815\ud655\ud55c \uc9c4\ub2e8, \uc778\ub371\uc2a4 \uac10\uc0ac, \ud30c\ud2f0\uc154\ub2dd. \uc5b4\ub290 \ud558\ub098\ub9cc\uc73c\ub85c\ub294 \ubd80\uc871\ud588\ub2e4.<\/p>\n<p><!-- Reviewed: 2026-03-09 | Status: ready_to_publish | Changes: expanded meta_description to 157 chars; punched up PK discovery moment with more personal voice; added parenthetical detail to autovacuum explanation; tightened partitioning conclusion to carry lessons forward --><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\uc0c8\ubcbd 2\uc2dc\uc5d0 \ud398\uc774\uc800\uac00 \uc6b8\ub838\ub2e4. Slack \uc54c\ub9bc\uc774 \uc544\ub2c8\ub77c \uc9c4\uc9dc PagerDuty \uc18c\ub9ac\uc600\ub2e4. \ubaa8\ub2c8\ud130\ub97c \ucf1c\ubcf4\ub2c8 \uba54\uc778 \ub300\uc2dc\ubcf4\ub4dc API\uc758 p99 \uc751\ub2f5 \uc2dc\uac04\uc774 \ud3c9\uc18c 230ms\uc5d0\uc11c 47\ucd08\ub85c \ud280\uc5b4 \uc788\uc5c8\ub2e4.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"class_list":["post-249","post","type-post","status-publish","format-standard","hentry","category-general"],"_links":{"self":[{"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/posts\/249","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/comments?post=249"}],"version-history":[{"count":1,"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/posts\/249\/revisions"}],"predecessor-version":[{"id":264,"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/posts\/249\/revisions\/264"}],"wp:attachment":[{"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/media?parent=249"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/categories?post=249"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.rebalai.com\/ko\/wp-json\/wp\/v2\/tags?post=249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}