{"id":1976,"date":"2017-10-14T22:25:26","date_gmt":"2017-10-14T19:25:26","guid":{"rendered":"http:\/\/www.profelis.com.tr\/tr\/blog\/?p=1976"},"modified":"2023-02-01T10:58:00","modified_gmt":"2023-02-01T07:58:00","slug":"postgresql-101-index-bakimi","status":"publish","type":"post","link":"https:\/\/profelis.com.tr\/en\/2017\/10\/14\/postgresql-101-index-bakimi\/","title":{"rendered":"PostgreSQL 101: Index Bak\u0131m\u0131"},"content":{"rendered":"<h2>PostgreSQL 101: Index Bak\u0131m\u0131<\/h2>\n<p>Markas\u0131 ne olursa olsun, her veritaban\u0131nda dizinler (<em><strong>indexes<\/strong><\/em>) y\u00fcksek yazma\/silme i\u015flemine maruz kalan sistemlerde problem olu\u015fturma potansiyeline sahiptir. Tablolar \u015fi\u015febilece\u011fi gibi dizinler de \u015fi\u015febilir. Birincil anahtar olmayan dizinler normal insert i\u015flemlerinde dahi \u015fi\u015febilmektedir. Buna kar\u015f\u0131n otomatik vakum (<em><strong>autovacuum<\/strong><\/em>) bu \u015fi\u015fmi\u015f olan dizinleri tespit edemez, yeniden olu\u015fturarak d\u00fczeltme i\u015flemi de yapamaz. Dolay\u0131s\u0131 ile indexlerimizin bak\u0131mlar\u0131 i\u00e7in farkl\u0131 yollar bulmal\u0131y\u0131z.<\/p>\n<h2>Yeniden indexleme<\/h2>\n<p>PostgreSQL indexlerin yeniden olu\u015fturulmas\u0131 i\u00e7in gerekli komutlara sahiptir, do\u011fal olarak. \u0130\u015fletim sistemi komut sat\u0131r\u0131 \u00fczerinden i\u015fletebilece\u011finiz bu komut sayesinde index veya indexlerin t\u00fcm\u00fcn\u00fcn yeniden olu\u015fturulmas\u0131 m\u00fcmk\u00fcn olmakla beraber tercih edebilece\u011fimiz veya <strong>tavsiye edebilece\u011fim y\u00f6ntem de\u011fildir.\u00a0<\/strong>Zira, yeniden indexleme i\u015flemi i\u015fletilen tablo \u00fczerinde bir kilitleme (<em><strong>full table lock,\u00a0AccessExclusiveLock<\/strong><\/em>) olu\u015fturur. E\u011fer indexleme i\u015flemi uzun s\u00fcrecek ise bunu yapt\u0131\u011f\u0131n\u0131z s\u00fcre boyunca, belki saatler boyunca, ilgili tablolara eri\u015fim k\u0131s\u0131tlanaca\u011f\u0131 i\u00e7in hangi tablolarda kullan\u0131laca\u011f\u0131 ve ne zaman i\u015fletilece\u011fi dikkatlice se\u00e7ilmelidir. <a href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/app-reindexdb.html\" target=\"_blank\" rel=\"noopener noreferrer\">El kitab\u0131 i\u00e7erisinde bu komut \u015fu \u015fekilde ge\u00e7iyor<\/a>:<\/p>\n<pre>reindexdb\u00a0[connection-option...] [option...] [\u00a0--schema\u00a0|\u00a0-S\u00a0schema\u00a0] ... [\u00a0--table\u00a0|\u00a0-t\u00a0table\u00a0] ... [\u00a0--index\u00a0|\u00a0-i\u00a0index\u00a0] ... [dbname]<\/pre>\n<p>PostgreSQL ayn\u0131 \u00f6zellikte, birden fazla farkl\u0131 isimde index&#8217;in ayn\u0131 anda ayn\u0131 tabloda yer alabilmesi \u00f6zelli\u011fine sahiptir. Bu \u00f6zellik ve <strong>CREATE INDEX CONCURRENTLY<\/strong> kullanarak bu s\u00fcreci arka planda tabloyu kilitlemeden yapmak m\u00fcmk\u00fcn olmaktad\u0131r.<\/p>\n<p>Dolay\u0131s\u0131 ile \u00f6nerilen y\u00f6ntem k\u0131saca \u015fu \u015fekilde tarif edilebilir:<\/p>\n<ul>\n<li>Ayn\u0131 \u00f6zelliklerde yeni bir index olu\u015fturulur,<\/li>\n<li>bu yap\u0131l\u0131rken tabloyu kilitlemeyecek bir y\u00f6ntem kullan\u0131l\u0131r,<\/li>\n<li>eski index silinir,<\/li>\n<li>yeni olu\u015fturulmu\u015f olan index silinen index&#8217;in yerine al\u0131nmak \u00fczere yeniden isimlendirilir.<\/li>\n<\/ul>\n<pre><code>CREATE INDEX CONCURRENTLY yeni_index ON test (category);\nBEGIN;\nDROP INDEX test_category_idx;\nALTER INDEX yeni_index RENAME TO test_category_idx;\nCOMMIT;\n<\/code><\/pre>\n<blockquote class=\"blockquote-secondary\"><p><strong>Not:<\/strong> PostgreSQL 9.3 s\u00fcr\u00fcm\u00fcnde yeni bir i\u015flev olarak <a href=\"https:\/\/www.postgresql.org\/docs\/9.3\/static\/sql-reindex.html\" target=\"_blank\" rel=\"noopener noreferrer\">REINDEX<\/a> \u00f6zelli\u011fi eklendi.\u00a0<tt>REINDEX<\/tt>\u00a0yukar\u0131daki gibi yeni bir index olu\u015fturup eski indexin yerine al\u0131nmas\u0131 \u015feklinde \u00e7al\u0131\u015fmakta. Ancak, yukar\u0131da \u00f6nermi\u015f oldu\u011fumuz yap\u0131daki kilitleme sisteminden tam olarak kurtarm\u0131yor.\u00a0<tt>REINDEX<\/tt>\u00a0yazma i\u015flemlerine kar\u015f\u0131 tabloyu kilitliyor sadece, tablo okuma i\u015flemlerine a\u00e7\u0131k durumda. Buna ra\u011fmen \u00f6nermiyor olmam\u0131z\u0131n temel sebebi ise, bu tablodan yap\u0131lan okumalar\u0131n yani SELECT sorgular\u0131n\u0131n e\u011fer bu index&#8217;i kullan\u0131yorlar ise bu index okumalara kar\u015f\u0131 kilitli oldu\u011fu i\u00e7in index&#8217;i kullanan sorgular yine kilitlenecektir.<\/p><\/blockquote>\n<p>Bu y\u00f6ntem \u00e7ok yararl\u0131 olsa da, Birincil Anahtar olan indexlerde i\u015fe yaramayacakt\u0131r, zira ayn\u0131 tabloda birden fazla birincil anahtar olamaz. Ayr\u0131ca, yeni bir index olu\u015fturup mevcut birincil anahtar\u0131 at\u0131p yerine yeni index&#8217;i birincil anahtar atayamay\u0131z zira harici anahtar k\u0131s\u0131tlamalar\u0131 (<em><strong>foreign key constraints<\/strong><\/em>) muhtemelen bu birincil anahtar\u0131 silmemizi engelleyecek veya veri kayb\u0131na sebep olacakt\u0131r. Yukar\u0131daki y\u00f6nteme benzer ancak biraz daha karma\u015f\u0131k \u015fekilde bunu da \u015fu \u015fekilde yapmak m\u00fcmk\u00fcn:<\/p>\n<ul>\n<li>Birincil anahtar ile ayn\u0131 \u00f6zelliklerde yeni bir index olu\u015fturulur,<\/li>\n<li><strong>pg_class<\/strong>\u00a0katalo\u011fundan tablonun dahili tan\u0131mlay\u0131c\u0131lar\u0131na bakarak indexin dosya sistemi\/disk \u00fczerindeki dosya ad\u0131na bakarak bu de\u011feri de\u011fi\u015ftirerek yeni index ile birincil anahtar index&#8217;ini yer de\u011fi\u015ftirece\u011fiz (Super!)<\/li>\n<\/ul>\n<p>\u00d6nce <strong>pg_class<\/strong> katalo\u011funun ne i\u015fe yarad\u0131\u011f\u0131na ve bu katalogda tabloya dair hangi bilgilerin yer ald\u0131\u011f\u0131na bir bakal\u0131m:\u00a0<a href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/catalog-pg-class.html\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/www.postgresql.org\/docs\/current\/static\/catalog-pg-class.html<\/a><\/p>\n<pre><code>SELECT oid, relname, relfilenode \n   FROM pg_class \n   WHERE oid in (SELECT indexrelid \n                    FROM pg_index \n                    WHERE indrelid = 'test'::regclass);\n\n oid  | relname           | relfilenode \n------+-------------------+------------- \n16639 | test_pkey         | <strong>16639<\/strong>\n16642 | test_category_idx | 16642\n16643 | yeni_pkey         | <strong>16643<\/strong><\/code><\/pre>\n<p>\u015eimdi olu\u015fturmu\u015f oldu\u011fumuz yeni index&#8217;in relfilenode de\u011feri ile eski pkey&#8217;in relfilenode de\u011ferlerini yer de\u011fi\u015ftirerek sistemi aldataca\u011f\u0131z, bu esnada birincil anahtar ve buna ba\u011fl\u0131 harici anahtar k\u0131s\u0131tlamalar\u0131 ve ili\u015fkiler aktif olarak kalacak. Bu i\u015flem esnas\u0131nda transaction kullanaca\u011f\u0131z ve herhangi bir eri\u015fim ihtimaline kar\u015f\u0131n tabloyu k\u0131sa bir an i\u00e7in kilitleyece\u011fiz, i\u015flem \u00e7ok h\u0131zl\u0131 sonu\u00e7lanacakt\u0131r.<\/p>\n<pre><code>BEGIN;\n   LOCK TABLE test;\n   UPDATE pg_class SET relfilenode = 16643 WHERE oid = 16639;\n   UPDATE pg_class SET relfilenode = 16639 WHERE oid = 16643;\n   DROP INDEX yeni_pkey;\nCOMMIT;\n<\/code><\/pre>\n<p>Yap\u0131lan i\u015flemi pg_class katalo\u011fundan yukar\u0131da bir \u00f6nceki sorgu yard\u0131m\u0131yla do\u011frulayal\u0131m:<\/p>\n<pre><code>oid   | relname           | relfilenode \n------+-------------------+------------- \n16639 | test_pkey         | <strong>16643<\/strong>\n16642 | test_category_idx | 16642\n16643 | yeni_pkey         | <strong>16639<\/strong><\/code><\/pre>\n<p><strong>Bu i\u015flemler s\u0131ras\u0131nda hata yaparsan\u0131z, yedekten geri d\u00f6nmeye haz\u0131r olun, \u00e7\u00fcnk\u00fc riskli bir ileri sistem y\u00f6neticisi i\u015flemi yap\u0131yorsunuz ve bunun genelde telafisi yoktur!<\/strong> Buna kar\u015f\u0131n ba\u015fka \u015fekilde elde edemeyece\u011finiz bir sonuca h\u0131zl\u0131 \u015fekilde ula\u015fman\u0131n hazz\u0131 paha bi\u00e7ilmezdir.<\/p>\n<h2>Kullan\u0131lmayan Indexler<\/h2>\n<p>PostgreSQL her index eri\u015fimini\u00a0<strong>pg_stat_user_indexes<\/strong>\u00a0tablosunda kay\u0131t alt\u0131na al\u0131r. Buna g\u00f6re hi\u00e7 eri\u015filmeyen, yani kullan\u0131lmayan index&#8217;leri basit bir sorgu ile bulmam\u0131z m\u00fcmk\u00fcnd\u00fcr:<\/p>\n<pre><code>SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;\n\nschemaname  | indexrelname             | idx_scan \n------------+--------------------------+----------\n<strong>public      | hesapdm_bid_idx          |        0<\/strong>\npublic      | kimlikno_bid_idx         |    12333\npublic      | tckimlik_bid_idx         |    22981\npublic      | hesaplar_pkey            |   132935\n...<\/code><\/pre>\n<p><strong>idx_scan<\/strong> indexin eri\u015fim\/taranma say\u0131s\u0131d\u0131r. Buna g\u00f6re pg_stat_user_indexes tablosu s\u0131f\u0131rland\u0131ktan sonra hi\u00e7 kullan\u0131lmam\u0131\u015f bir index olan\u00a0<strong>hesapdm_bid_idx<\/strong> indexi mevcuttur. Bu indexin kald\u0131r\u0131l\u0131p kald\u0131r\u0131lmayaca\u011f\u0131 veritaban\u0131 y\u00f6neticisinin daha derin bir incelemesi veya geli\u015ftiricilere dan\u0131\u015fmas\u0131 sonras\u0131 verilecek bir karard\u0131r. Hi\u00e7 kullan\u0131lmam\u0131\u015f olmas\u0131, bundan sonra kullan\u0131lmayaca\u011f\u0131 anlam\u0131na gelmeyebilir. Ayr\u0131ca baz\u0131 index&#8217;ler tahmin edebilece\u011finiz gibi, sadece \u00e7ift veri giri\u015fini engellemek \u00fczere olu\u015fturulmu\u015f tekil (<em><strong>unique<\/strong><\/em>) indexler de olabilir.<\/p>\n<p>Kullan\u0131m say\u0131s\u0131 az olan index&#8217;ler yeni olu\u015fturulmu\u015f olabilir, veya az kullan\u0131lmaktad\u0131rlar. Genelleikle zamanlamal\u0131 g\u00f6revlerle kullan\u0131lan raporlarda veya uygulaman\u0131n \u00e7ok az kullan\u0131lan b\u00f6l\u00fcmlerinde kullan\u0131lan indexler olabilir. Bu t\u00fcr index&#8217;lerin varl\u0131\u011f\u0131 sorgulanmal\u0131, ancak do\u011frudan silinmemelidir.<\/p>\n<h2>\u0130stenmeyen indexlerin Zarars\u0131zca Silinmesi<\/h2>\n<p>Zarars\u0131zca silmek, index&#8217;in silme i\u015fleminden sonra yeniden olu\u015fturma s\u00fcresini beklemeden h\u0131zl\u0131ca ve uygulamaya performans zarar\u0131 vermeden geri getirebilecek bir y\u00f6ntemle silinmesidir. Yani k\u0131saca geri alma i\u015flemi yap\u0131labilmelidir. Bunun i\u00e7in bir drop etme bir de geri alma fonksiyonu tan\u0131mlayal\u0131m:<\/p>\n<pre><code>CREATE OR REPLACE FUNCTION <strong>trial_drop_index<\/strong>(iname TEXT)\n  RETURNS VOID\n  LANGUAGE SQL AS $\n  UPDATE pg_index\n  SET indisvalid = false\n  WHERE indexrelid = $1::regclass;\n  $;\n<\/code><\/pre>\n<p>Silme i\u015flemini deneyelim, e\u011fer bir problem ile kar\u015f\u0131la\u015f\u0131rsak, performans de\u011ferleri olumsuz etkilenirse a\u015fa\u011f\u0131daki geri alma fonksiyonunu kullanaca\u011f\u0131z:<\/p>\n<pre><code>CREATE OR REPLACE FUNCTION <strong>trial_undrop_index<\/strong>(iname TEXT)\n  RETURNS VOID\n  LANGUAGE SQL AS $\n  UPDATE pg_index\n  SET indisvalid = true\n  WHERE indexrelid = $1::regclass;\n  $;\n<\/code><\/pre>\n<p><strong>indisvalid<\/strong> index&#8217;in valid olup olmad\u0131\u011f\u0131n\u0131 belirlemekte, dolay\u0131s\u0131 ile \u00f6nce index&#8217;i drop etmeden sadece devre d\u0131\u015f\u0131 b\u0131rakarak durumu g\u00f6zl\u00fcyor, e\u011fer bir problem olursa tekrar devreye almak \u015feklinde hareket edilmekte. \u0130ndex devre d\u0131\u015f\u0131 b\u0131rak\u0131ld\u0131\u011f\u0131nda silinmiyor, sadece sorgular taraf\u0131ndan kullan\u0131m d\u0131\u015f\u0131 b\u0131rak\u0131l\u0131yor, tabloya yap\u0131lan ekleme ve g\u00fcncellemelerde ve silme i\u015flemlerinde g\u00fcncellenmiyor.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Markas\u0131 ne olursa olsun, her veritaban\u0131nda dizinler (indexes) y\u00fcksek yazma\/silme i\u015flemine maruz kalan sistemlerde problem olu\u015fturma potansiyeline sahiptir. Tablolar \u015fi\u015febilece\u011fi gibi dizinler de \u015fi\u015febilir. Birincil anahtar olmayan dizinler normal insert i\u015flemlerinde dahi \u015fi\u015febilmektedir. Buna kar\u015f\u0131n otomatik vakum (autovacuum) bu \u015fi\u015fmi\u015f olan dizinleri tespit edemez, yeniden olu\u015fturarak d\u00fczeltme i\u015flemi de yapamaz. Dolay\u0131s\u0131 ile indexlerimizin bak\u0131mlar\u0131 i\u00e7in farkl\u0131 yollar bulmal\u0131y\u0131z.<\/p>","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,45],"tags":[48,60],"class_list":["post-1976","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-yazi","tag-enterprisedb","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1976","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/comments?post=1976"}],"version-history":[{"count":0,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1976\/revisions"}],"wp:attachment":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media?parent=1976"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/categories?post=1976"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/tags?post=1976"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}