postgresql index — cost and maintance 👾

Herkese merhabalar , bu yazımda sizlere postgresql’deki indexlerinin nerede kullanılacağına dair özelliklerini ve kullanım alanlarını , bakımlarını index eğilimlerini ,ve kullanmayan indexler cost based gibi alanlarla ilgili bilgi vermeye çalışacağım

Index nedir kısaca hatıralatalım tabii ki :)

Bir “index” veya “indeks,” veritabanı sistemlerinde bilgileri hızlı bir şekilde aramak, sıralamak veya erişmek için kullanılan bir veri yapısıdır. İndeksler, verilerin daha verimli bir şekilde erişilmesini sağlar ve sorgu performansını artırır diyebiliriz

Gönderdiğimiz sorgular peki nasıl çalışıyor ne aşamalardan geçiyor

PostgreSQL’de bir sorgunun işlenmesi üç temel aşamada gerçekleşir: Ayrıştırma, Optimizasyon ve Çalıştırma. İşte bu aşamaların daha detaylı açıklamaları:

Ayrıştırma (Parsing):

Kullanıcı veya uygulama tarafından gönderilen SQL sorgusu, PostgreSQL sunucusuna iletilir.

Sunucu, gelen sorguyu dilbilgisi açısından inceleyip parçalarına ayırır (tokenize eder).

Ayrıştırıcı, sorgunun dilbilgisi yapısını kontrol eder ve bu doğrultuda hata mesajları üretebilir.

Ayrıştırıcı, sorgunun veritabanı tarafından anlaşılabilir bir formata dönüştürülmesini sağlar.

2 Plan (Optimization):

Ayrıştırıcı, ayrıştırdığı sorguyu temel alarak bir sorgu planı oluşturur.

Sorgu planı, sorgunun nasıl çalıştırılacağına dair bir yol haritasıdır. Bu plan, veri erişim yolları, birleştirmeler ve indeks kullanımı gibi faktörleri içerir.

Optimizasyon süreci, sorgunun veritabanındaki verilere en hızlı şekilde erişmesini ve işlemesini amaçlar.

3 Execute (Çalıştırma):

Oluşturulan sorgu planına göre, PostgreSQL sorguyu çalıştırır.

Bu aşamada, veritabanından veri okuma, yazma veya güncelleme gibi işlemler gerçekleştirilir.

Veritabanı, ilgili nesneleri kilitleyebilir ve işlem yapabilir.

Sonuçlar, kullanıcıya veya uygulamaya iletilir.

Sorgularıımızın nasıl çalışıp plandağını görmek istiyorsakta explain ifadesini kullanmamız gerekli

EXPLAIN SELECT * FROM film ;

Sonuç//
Seq Scan on film (cost=0.00..98.00 rows=1000 width=384)

Gelen Sonuçlara Bakalım

Scan Türleri Vardır bizde seq scan olarak verdi tüm tabloyu arrattığımız için diğer scan türlerinide verelim :)

Seq Scan (Sıralı Tara):

• Veritabanındaki bir tabloyu sırasıyla taramak için kullanılır.
• Bu tarama türü, tablonun tamamını taramak gerektiğinde veya indeks kullanılmasının veri maliyetini artıracağı durumlarda kullanılır.

Index Scan (İndeks Tara):

• Bir indeksi kullanarak veritabanında veri aramak için kullanılır.
• İndeks, verileri belirli bir sırayla tutar ve arama işlemini hızlandırır.
• İndeks taraması, sorgu koşullarına uyan verileri bulmak için kullanılır.

Bitmap Index Scan (Bit Haritası İndeks Tara):

• Birden fazla indeksi veya koşulu birleştirmek ve sonuçları optimize etmek için kullanılır.
• İndekslerin birleştirilmesiyle bir bit haritası oluşturulur ve bu harita kullanılarak uygun satırlar seçilir.

Bitmap Heap Scan (Bit Haritası Veri Sayfası Tara):

• Bitmap Index Scan sonucunda oluşturulan bit haritasını kullanarak veri sayfalarını taramak için kullanılır.
• Bu tarama türü, sorgunun sonuçlarını elde etmek için veri sayfalarını kullanır.

Index Only Scan (Sadece İndeks Tara):

• İndeks üzerinden gelen sorgular için kullanılır ve veri sayfalarına ihtiyaç duymadan sadece indeksi kullanır.
• İlgili sorgunun istediği tüm veriler indeks içinde bulunabilirse, bu tarama türü kullanılır.

Tid Scan (Tid Tara):

• Tuple (veri satırı) kimlik numaralarına (TID) göre doğrudan veri satırlarını taramak için kullanılır.
• Genellikle benzersiz kimlik numaralarını bilmek gerektiğinde kullanılır.

cost: maliyeti gösterir ne kadar düşük olursa bizim için o kadar iyi

startup cost : ilk kısımdır ,Sorgunun çalışmaya başlamak için gereken tahmini maliyeti belirtir.

total cost : iki noktadan sonra olan kısımdır, sorgunun tamamlanması için tahmini toplam maliyeti belirtir.

rows = tahmini getirelin satır sayısı

width = byte değeri

EXPLAIN (ANALYZE ON ) SELECT * FROM film ;
//Sorguyu analiz edecek ve çalıştıracak

EXPLAIN (ANALYZE ON , BUFFERs ON ) SELECT * FROM filmm ;
//Sorgu bufferdan mı geliyorda diye bakmak için

Postgresql’deki indexler nelerdir peki ?

select * from pg_am

B-Tree :

Bir tablonun belirli bir sütununa veya sütun kombinasyonuna dayalı olarak verilerin hızlı bir şekilde aranabilmesini sağlayan bir veri yapısıdır. B-tree, “düğüm ağacı” anlamına gelir ve asıl kullanım gereksinimi aşıağıda ki gibidir

büyüktür, küçüktür, büyük eşittir, küçük eşittir, between (küçük eşittere denk gelir) is in, is not null’da kullaniliir reguler expiration sabittse like kullanılır yani ‘%A’ gibi

CREATE INDEX username_index ON users USING BTREE (username); //Uzun Hali
//
CREATE INDEX username_indexs ON users(username)
//
CREATE INDEX username_indexs ON users(username DESC)
//
CREATE INDEX username_indexs ON users(username ASC)
//
CREATE INDEX username_indexs ON users(username NULLS LAST)
//
CREATE INDEX username_indexs ON users(username NULLS FIRST)


Oluşturduğumuz index'in boyutunu öğrenmek içinse

SELECT pg_size_pretty(pg_relation_size('username_indexs')) // index boyutu
SELECT pg_size_pretty(pg_relation_total_size('t1')) // toplam tablo boyutu
 

evet buraya kadar her şey çok güzeldi ama bir sorunumuz var biz bu indexleri yaratırken tablolarımız lock alıyor “heavyweight lock” hiç bir şekilde index oluşana kadar okuma yazma almıyor bu prod ortamada sıkıntı gibi geliyor :) bunun üstesinden gelmek için

CREATE INDEX CONCURRENTLY username_indexs ON users(username)

“Index concurrently” kullanarak indeks oluştururken veya güncellerken, veritabanı hizmetini durdurmanıza gerek kalmaz. Bu, işlem yükü yüksek uygulamalarda veya sürekli çalışan sistemlerde kesintisiz hizmet sağlamanıza olanak tanır.

Hash :

Kısacası sorgularımızda eşittir durumu varsa :)

CREATE INDEX user_name ON t1 USING HASH(name)

tabii concurently ifadesini kullanmayı unutmayalım :)

CREATE INDEX CONCURRENTLY user_name ON t1 USING HASH(name)

Hemen yazımızın arasına index eğilimini nedir onu koyalım :)

select attname , correlation from pg_stats;

//tablosundan yola çıkacğaız

corealation nedir peki ?

PostgreSQL’de “index correlation” postgreSQL’de indekslerin oluşturulması ve sorgu optimizasyonunda kullanılması sırasında bir kavramdır. Bu, PostgreSQL optimizasyon algoritmasının bir parçasıdır ve sorguların hızlı bir şekilde çalışmasını sağlamak amacıyla indekslerin nasıl kullanılacağına yönelik bir tahminde bulunur.

indeks korelasyonu, bir veritabanı tablosundaki verilerin sıralanmış bir indeksle uygun bir şekilde nasıl ilişkilendiğini ölçer. İndeks korelasyonu, -1 ile 1 arasında bir değer alır:

1: İndeks ve tablo verileri mükemmel pozitif bir korelasyona sahiptir. Yani, indeksin sıralaması tablodaki verilerin sıralamasıyla tamamen aynıdır.

0: Korelasyon yok veya çok zayıftır. Bu, indeks ve tablo verileri arasında belirgin bir ilişki olmadığı anlamına gelir.

-1: İndeks ve tablo verileri mükemmel negatif bir korelasyona sahiptir. Yani, indeksin sıralaması tablodaki verilerin sıralamasıyla tam tersidir.

indeks korelasyonu, PostgreSQL optimizasyon algoritması için önemlidir çünkü bu değer, bir sorgunun bir indeksi kullanıp kullanmaması gerektiğini tahmin etmek için kullanılır. Özellikle büyük veritabanları ve karmaşık sorgularla çalışırken, doğru indeks seçimi ve kullanımı sorgu performansını önemli ölçüde etkileyebilir.

Bir sorgu çalıştırıldığında PostgreSQL, indeks korelasyonunu kullanarak hangi indekslerin kullanılacağını tahmin etmeye çalışır ve sorgunun hızlı bir şekilde tamamlanmasını sağlamak için en uygun indeksi seçer. Bu nedenle, indeks korelasyonunun yüksek olduğu senaryolarda, ilgili indeksi kullanmak sorgu performansını artırabilir.

BRIN :

Block range index 8kb olan veri bloklarının en küçük ve en büyük değerlerini alır yani içinde min ve max değerlerini bulur

CREATE INDEX payment_idx on payment USING BRIN(money);

//veya

CREATE INDEX CONCURRENTLY payment_idx ON payment USING BRIN(money)


SELECT pg_size_pretty(pg_relationssize(payment_idx)) // index boyutu

GIN

Sorgularımızda Full text yapıyorsak veya like kullanıyorsanız tam olarak kullanmanız gereken index türüdür.

SELECT name FROM users WHERE name LIKE '%ABC%'

SELECT name FROM users WHERE name LIKE '%ABC%' AND surname LIKE '%ABC%'

Sorgunuz tam olarak böyle bir şeyse veya benzeriyse


CREATE EXTENSION pg_trgm

CREATE INDEX name_idx ON users USING GIN(name gin_trgm_ops)

Indexlerde Vacuum Önemi

Veritabanlarında indekslerle ilişkili olarak “VACUUM” işlemi oldukça önemlidir. VACUUM, veritabanının temizlenmesini, boşlukların serbest bırakılmasını ve indekslerin optimize edilmesini sağlayan bir bakım işlemidir

Vacuum yaparsak satırların üstünden geeçer unsvisible olan satırları visible yapar okunan satırları daha hızlı getirir :) ve bunub bilgisini gitti pg_class’a yazar

SELECT * FROM pg_class WHERE  relname = 'user';

relname: tablo adı

relallvisible: kaçtane page’in visible onu görebilirisniz

relpages : kaç page üzerine yerleştiğini söyler

Tekrarlayan indexleri bulmak içinde

select indrelid::regclass as tablename , array_agg(indexrelid::regclass) as indexes
from pg_index group by indrelid , indkey having count(*) > 1;