{"id":174,"date":"2026-11-07T11:28:00","date_gmt":"2026-11-07T11:28:00","guid":{"rendered":"https:\/\/www.akifsen.com.tr\/blog\/?p=174"},"modified":"2026-11-07T11:28:00","modified_gmt":"2026-11-07T11:28:00","slug":"postgresql-table-partitioning","status":"publish","type":"post","link":"https:\/\/www.akifsen.com.tr\/blog\/2026\/11\/07\/postgresql-table-partitioning\/","title":{"rendered":"Tablo Partitioning: B\u00fcy\u00fck Tablolar\u0131 B\u00f6lmek Ne Zaman \u0130\u015fe Yarar?"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Bir uygulaman\u0131n <code>events<\/code> tablosu var. Her kullan\u0131c\u0131 hareketi buraya yaz\u0131l\u0131yor.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u0130lk y\u0131l birka\u00e7 milyon sat\u0131r. \u00dc\u00e7\u00fcnc\u00fc y\u0131l 800 milyon.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Son 7 g\u00fcn\u00fcn verisini sorgulayan raporlar yava\u015flam\u0131\u015f. Eski verileri silmek i\u00e7in \u00e7al\u0131\u015ft\u0131r\u0131lan <code>DELETE<\/code> sorgular\u0131 saatler s\u00fcr\u00fcyor ve tabloyu \u015fi\u015firiyor.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Partitioning tam bu durum i\u00e7in var.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Partitioning nedir?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Bir tabloyu mant\u0131ksal olarak tek, fiziksel olarak birden fazla par\u00e7aya b\u00f6lmektir. PostgreSQL 10&#8217;dan beri bunu deklaratif olarak destekliyor:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE events (\n  id         bigint,\n  user_id    bigint,\n  type       text,\n  created_at timestamptz NOT NULL\n) PARTITION BY RANGE (created_at);\n\nCREATE TABLE events_2026_10 PARTITION OF events\nFOR VALUES FROM ('2026-10-01') TO ('2026-11-01');<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Uygulama h\u00e2l\u00e2 <code>events<\/code> tablosuna yazar ve ondan okur. Veritaban\u0131 her sat\u0131r\u0131 <code>created_at<\/code> de\u011ferine g\u00f6re do\u011fru partition&#8217;a yerle\u015ftirir.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Partition pruning<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Partitioning&#8217;in sorgu performans\u0131na etkisi, partition pruning sayesinde olur.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT count(*) FROM events\nWHERE created_at &gt;= now() - interval '7 days';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Planlay\u0131c\u0131, bu sorgunun sadece son partition&#8217;larla ilgili oldu\u011funu bilir. Di\u011fer partition&#8217;lara hi\u00e7 dokunmaz. 800 milyon sat\u0131r yerine birka\u00e7 milyon sat\u0131r taran\u0131r.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ama bu sadece sorgu partition key&#8217;i kulland\u0131\u011f\u0131nda i\u015fe yarar.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM events WHERE user_id = 42;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Bu sorgu <code>created_at<\/code> i\u00e7ermedi\u011fi i\u00e7in b\u00fct\u00fcn partition&#8217;lara gitmek zorundad\u0131r. Her partition&#8217;daki index ayr\u0131 ayr\u0131 taran\u0131r. Partitioning olmayan bir tablodaki tek bir index taramas\u0131ndan daha yava\u015f olabilir.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">As\u0131l kazan\u00e7: veri ya\u015fam d\u00f6ng\u00fcs\u00fc<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Bence partitioning&#8217;in en b\u00fcy\u00fck faydas\u0131 sorgu h\u0131z\u0131 de\u011fil, eski verinin y\u00f6netimi.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Alt\u0131 aydan eski event&#8217;lerin silinmesi gerekiyor:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DELETE FROM events WHERE created_at &lt; '2026-05-01';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Y\u00fcz milyonlarca sat\u0131r\u0131 tek tek siler. Dead tuple \u00fcretir, WAL \u00fcretir, replica&#8217;lar\u0131 geride b\u0131rak\u0131r ve saatler s\u00fcrer.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Partition&#8217;l\u0131 tabloda:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE events_2026_04;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">An\u0131nda. Dead tuple yok. Bloat yok.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ar\u015fivleme gerekiyorsa, partition tablodan ayr\u0131labilir, d\u0131\u015far\u0131 aktar\u0131labilir ve sonra silinebilir.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Partition key se\u00e7imi<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Partition key, sorgular\u0131n b\u00fcy\u00fck \u00e7o\u011funlu\u011funda bulunan ve verinin ya\u015fam d\u00f6ng\u00fcs\u00fcn\u00fc belirleyen bir kolon olmal\u0131. Zaman serisi verilerinde bu neredeyse her zaman tarih kolonudur.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Multi-tenant sistemlerde tenant&#8217;a g\u00f6re list veya hash partitioning de kullan\u0131labilir. B\u00fcy\u00fck bir m\u00fc\u015fteriyi kendi partition&#8217;\u0131na almak, onun verisini ayr\u0131 y\u00f6netmeyi kolayla\u015ft\u0131r\u0131r.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Partition say\u0131s\u0131 da \u00f6nemli. \u00c7ok fazla k\u00fc\u00e7\u00fck partition, planlama s\u00fcresini art\u0131r\u0131r. G\u00fcnl\u00fck partition&#8217;lar birka\u00e7 y\u0131l i\u00e7inde binlerce tabloya ula\u015fabilir. \u00c7o\u011fu durumda ayl\u0131k veya haftal\u0131k partition&#8217;lar daha dengeli.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">S\u0131n\u0131rlamalar<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Partition&#8217;l\u0131 tablolarda unique constraint&#8217;ler ve primary key, partition key&#8217;i i\u00e7ermek zorunda. <code>id<\/code> tek ba\u015f\u0131na primary key olamaz, <code>(id, created_at)<\/code> olmal\u0131.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Gelecek partition&#8217;lar\u0131n \u00f6nceden olu\u015fturulmas\u0131 gerekir. Ay ba\u015f\u0131nda partition yoksa insert&#8217;ler hata verir. pg_partman gibi bir eklenti veya zamanlanm\u0131\u015f bir job bunu otomatikle\u015ftirir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Mevcut b\u00fcy\u00fck bir tabloyu partition&#8217;l\u0131 yap\u0131ya ge\u00e7irmek ayr\u0131 bir migration projesi. Veri kopyalanmal\u0131 ve ge\u00e7i\u015f kesintisiz yap\u0131lmal\u0131.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Her b\u00fcy\u00fck tablo i\u00e7in de\u011fil<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Partitioning, b\u00fcy\u00fck tablolar i\u00e7in otomatik bir \u00e7\u00f6z\u00fcm de\u011fil. \u0130yi index&#8217;lenmi\u015f 100 milyon sat\u0131rl\u0131k bir tablo, do\u011fru sorgularla gayet h\u0131zl\u0131 \u00e7al\u0131\u015fabilir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Partitioning&#8217;i d\u00fc\u015f\u00fcnmek i\u00e7in as\u0131l sinyaller: zaman bazl\u0131 ve s\u00fcrekli b\u00fcy\u00fcyen veri, eski verinin d\u00fczenli silinmesi ihtiyac\u0131 ve sorgular\u0131n b\u00fcy\u00fck k\u0131sm\u0131n\u0131n belirli bir zaman aral\u0131\u011f\u0131na odaklanmas\u0131.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Bu \u00fc\u00e7\u00fc bir aradaysa, partitioning hem performans\u0131 hem de operasyonel y\u00fck\u00fc ciddi \u015fekilde iyile\u015ftirir.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Y\u00fcz milyonlarca sat\u0131rl\u0131k log ve event tablolar\u0131nda partitioning sorgular\u0131 h\u0131zland\u0131rabilir ve eski veriyi silmeyi kolayla\u015ft\u0131r\u0131r. Ama yanl\u0131\u015f partition key her \u015feyi yava\u015flat\u0131r.<\/p>\n","protected":false},"author":1,"featured_media":173,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-174","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-veritabani"],"_links":{"self":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/174","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/comments?post=174"}],"version-history":[{"count":1,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/174\/revisions"}],"predecessor-version":[{"id":229,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/174\/revisions\/229"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/media\/173"}],"wp:attachment":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/media?parent=174"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/categories?post=174"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/tags?post=174"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}