PostgreSQL 101: Index Bakımı

Markası ne olursa olsun, her veritabanında dizinler (indexes) yüksek yazma/silme işlemine maruz kalan sistemlerde problem oluşturma potansiyeline sahiptir. Tablolar şişebileceği gibi dizinler de şişebilir. Birincil anahtar olmayan dizinler normal insert işlemlerinde dahi şişebilmektedir. Buna karşın otomatik vakum (autovacuum) bu şişmiş olan dizinleri tespit edemez, yeniden oluşturarak düzeltme işlemi de yapamaz. Dolayısı ile indexlerimizin bakımları için farklı yollar bulmalıyız.

Yeniden indexleme

PostgreSQL indexlerin yeniden oluşturulması için gerekli komutlara sahiptir, doğal olarak. İşletim sistemi komut satırı üzerinden işletebileceğiniz bu komut sayesinde index veya indexlerin tümünün yeniden oluşturulması mümkün olmakla beraber tercih edebileceğimiz veya tavsiye edebileceğim yöntem değildir. Zira, yeniden indexleme işlemi işletilen tablo üzerinde bir kilitleme (full table lock, AccessExclusiveLock) oluşturur. Eğer indexleme işlemi uzun sürecek ise bunu yaptığınız süre boyunca, belki saatler boyunca, ilgili tablolara erişim kısıtlanacağı için hangi tablolarda kullanılacağı ve ne zaman işletileceği dikkatlice seçilmelidir. El kitabı içerisinde bu komut şu şekilde geçiyor:

reindexdb [connection-option...] [option...] [ --schema | -S schema ] ... [ --table | -t table ] ... [ --index | -i index ] ... [dbname]

PostgreSQL aynı özellikte, birden fazla farklı isimde index’in aynı anda aynı tabloda yer alabilmesi özelliğine sahiptir. Bu özellik ve CREATE INDEX CONCURRENTLY kullanarak bu süreci arka planda tabloyu kilitlemeden yapmak mümkün olmaktadır.

Dolayısı ile önerilen yöntem kısaca şu şekilde tarif edilebilir:

Aynı özelliklerde yeni bir index oluşturulur,
bu yapılırken tabloyu kilitlemeyecek bir yöntem kullanılır,
eski index silinir,
yeni oluşturulmuş olan index silinen index’in yerine alınmak üzere yeniden isimlendirilir.

CREATE INDEX CONCURRENTLY yeni_index ON test (category);
BEGIN;
DROP INDEX test_category_idx;
ALTER INDEX yeni_index RENAME TO test_category_idx;
COMMIT;

Not: PostgreSQL 9.3 sürümünde yeni bir işlev olarak REINDEX özelliği eklendi. REINDEX yukarıdaki gibi yeni bir index oluşturup eski indexin yerine alınması şeklinde çalışmakta. Ancak, yukarıda önermiş olduğumuz yapıdaki kilitleme sisteminden tam olarak kurtarmıyor. REINDEX yazma işlemlerine karşı tabloyu kilitliyor sadece, tablo okuma işlemlerine açık durumda. Buna rağmen önermiyor olmamızın temel sebebi ise, bu tablodan yapılan okumaların yani SELECT sorgularının eğer bu index’i kullanıyorlar ise bu index okumalara karşı kilitli olduğu için index’i kullanan sorgular yine kilitlenecektir.

Bu yöntem çok yararlı olsa da, Birincil Anahtar olan indexlerde işe yaramayacaktır, zira aynı tabloda birden fazla birincil anahtar olamaz. Ayrıca, yeni bir index oluşturup mevcut birincil anahtarı atıp yerine yeni index’i birincil anahtar atayamayız zira harici anahtar kısıtlamaları (foreign key constraints) muhtemelen bu birincil anahtarı silmemizi engelleyecek veya veri kaybına sebep olacaktır. Yukarıdaki yönteme benzer ancak biraz daha karmaşık şekilde bunu da şu şekilde yapmak mümkün:

Birincil anahtar ile aynı özelliklerde yeni bir index oluşturulur,
pg_class kataloğundan tablonun dahili tanımlayıcılarına bakarak indexin dosya sistemi/disk üzerindeki dosya adına bakarak bu değeri değiştirerek yeni index ile birincil anahtar index’ini yer değiştireceğiz (Super!)

Önce pg_class kataloğunun ne işe yaradığına ve bu katalogda tabloya dair hangi bilgilerin yer aldığına bir bakalım: https://www.postgresql.org/docs/current/static/catalog-pg-class.html

SELECT oid, relname, relfilenode 
   FROM pg_class 
   WHERE oid in (SELECT indexrelid 
                    FROM pg_index 
                    WHERE indrelid = 'test'::regclass);

 oid  | relname           | relfilenode 
------+-------------------+------------- 
16639 | test_pkey         | 16639
16642 | test_category_idx | 16642
16643 | yeni_pkey         | 16643

Şimdi oluşturmuş olduğumuz yeni index’in relfilenode değeri ile eski pkey’in relfilenode değerlerini yer değiştirerek sistemi aldatacağız, bu esnada birincil anahtar ve buna bağlı harici anahtar kısıtlamaları ve ilişkiler aktif olarak kalacak. Bu işlem esnasında transaction kullanacağız ve herhangi bir erişim ihtimaline karşın tabloyu kısa bir an için kilitleyeceğiz, işlem çok hızlı sonuçlanacaktır.

BEGIN;
   LOCK TABLE test;
   UPDATE pg_class SET relfilenode = 16643 WHERE oid = 16639;
   UPDATE pg_class SET relfilenode = 16639 WHERE oid = 16643;
   DROP INDEX yeni_pkey;
COMMIT;

Yapılan işlemi pg_class kataloğundan yukarıda bir önceki sorgu yardımıyla doğrulayalım:

oid   | relname           | relfilenode 
------+-------------------+------------- 
16639 | test_pkey         | 16643
16642 | test_category_idx | 16642
16643 | yeni_pkey         | 16639

Bu işlemler sırasında hata yaparsanız, yedekten geri dönmeye hazır olun, çünkü riskli bir ileri sistem yöneticisi işlemi yapıyorsunuz ve bunun genelde telafisi yoktur! Buna karşın başka şekilde elde edemeyeceğiniz bir sonuca hızlı şekilde ulaşmanın hazzı paha biçilmezdir.

Kullanılmayan Indexler

PostgreSQL her index erişimini pg_stat_user_indexes tablosunda kayıt altına alır. Buna göre hiç erişilmeyen, yani kullanılmayan index’leri basit bir sorgu ile bulmamız mümkündür:

SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;

schemaname  | indexrelname             | idx_scan 
------------+--------------------------+----------
public      | hesapdm_bid_idx          |        0
public      | kimlikno_bid_idx         |    12333
public      | tckimlik_bid_idx         |    22981
public      | hesaplar_pkey            |   132935
...

idx_scan indexin erişim/taranma sayısıdır. Buna göre pg_stat_user_indexes tablosu sıfırlandıktan sonra hiç kullanılmamış bir index olan hesapdm_bid_idx indexi mevcuttur. Bu indexin kaldırılıp kaldırılmayacağı veritabanı yöneticisinin daha derin bir incelemesi veya geliştiricilere danışması sonrası verilecek bir karardır. Hiç kullanılmamış olması, bundan sonra kullanılmayacağı anlamına gelmeyebilir. Ayrıca bazı index’ler tahmin edebileceğiniz gibi, sadece çift veri girişini engellemek üzere oluşturulmuş tekil (unique) indexler de olabilir.

Kullanım sayısı az olan index’ler yeni oluşturulmuş olabilir, veya az kullanılmaktadırlar. Genelleikle zamanlamalı görevlerle kullanılan raporlarda veya uygulamanın çok az kullanılan bölümlerinde kullanılan indexler olabilir. Bu tür index’lerin varlığı sorgulanmalı, ancak doğrudan silinmemelidir.

İstenmeyen indexlerin Zararsızca Silinmesi

Zararsızca silmek, index’in silme işleminden sonra yeniden oluşturma süresini beklemeden hızlıca ve uygulamaya performans zararı vermeden geri getirebilecek bir yöntemle silinmesidir. Yani kısaca geri alma işlemi yapılabilmelidir. Bunun için bir drop etme bir de geri alma fonksiyonu tanımlayalım:

CREATE OR REPLACE FUNCTION trial_drop_index(iname TEXT)
  RETURNS VOID
  LANGUAGE SQL AS $
  UPDATE pg_index
  SET indisvalid = false
  WHERE indexrelid = $1::regclass;
  $;

Silme işlemini deneyelim, eğer bir problem ile karşılaşırsak, performans değerleri olumsuz etkilenirse aşağıdaki geri alma fonksiyonunu kullanacağız:

CREATE OR REPLACE FUNCTION trial_undrop_index(iname TEXT)
  RETURNS VOID
  LANGUAGE SQL AS $
  UPDATE pg_index
  SET indisvalid = true
  WHERE indexrelid = $1::regclass;
  $;

indisvalid index’in valid olup olmadığını belirlemekte, dolayısı ile önce index’i drop etmeden sadece devre dışı bırakarak durumu gözlüyor, eğer bir problem olursa tekrar devreye almak şeklinde hareket edilmekte. İndex devre dışı bırakıldığında silinmiyor, sadece sorgular tarafından kullanım dışı bırakılıyor, tabloya yapılan ekleme ve güncellemelerde ve silme işlemlerinde güncellenmiyor.

EnterpriseDB PostgreSQL