PostgreSQL — Performans Odaklı Index Seçimleri

Merhabalar, bu çalışmada PostgreSQL üzerinde özellikle SELECT sorguları için kullanabileceğimiz ve PostgreSQL’in desteklediği index tiplerini inceleyip performans ve diğer kazanımlar açısından incelemeye çalışacağım.

Veritabanında index seçimi aslında performans için çok önemli bir konudur ve bu nedenle de veritabanına gelecek sorguların analizini yapmak çok önem kazanmaktadır. Özellikle veritabanı boyutu büyüdükçe doğru ya da efektif index seçimi yapmak farkı daha da gözler önüne serecektir.

Bu çalışma kapsamında,alışılagelmiş b-tree index yapısının detayları yerine BRIN ve HASH index tiplerinin farklarına çalışacağım. Bu sayede de b-tree indexlere daha efektif alternatifler üretmek için çalışacağım.

Konu başlıklarını ve akışı aşağıda bulabilirsiniz.

1. PostgreSQL Sorgu Planı Incelenmesi ve Gözlemlenmesi
2. BRIN Index Kullanımı ve B-Tree ile Karşılaştırılması
3. HASH Index Kullanımı ve B-Tree ile Karşılaştırılması
4. Referanslar

PostgreSQL Sorgu Planı Incelenmesi ve Gözlemlenmesi

Başlamadan önce PostgreSQL üzerinde, sorgu performanslarına kısaca ya da kabaca en yalın hali ile nasıl bakacağımızı da açıklamakta fayda var. Database üzerinde bir sorgunun planına ve muhtemel performansını incelemek için EXPLAIN bloğundan faydalanmamız gerekmektedir. EXPLAIN bloğu ile bir sorgunun sahip olduğu çalışma planı ve buna bağlı olarak muhtemel tamamlanma süresi yani cevap süresini görebilmek mümkündür.

Aşağıdaki kullanımdaki gibi eğer PostgreSQL üzerinde çalıştırmak istediğimiz bir sorgunun nasıl çalışacağı(planının nasıl olacağı) ve muhtemel çalışma süresini kontol edebiliriz.

index_demo=# explain analyze (select * from scans);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on scans  (cost=0.00..516934.94 rows=31579294 width=20) (actual time=0.009..1615.680 rows=31579201 loops=1)
Planning Time: 0.042 ms
Execution Time: 2382.075 ms
(3 rows)

Kullanım formatı ise,

explain anaylze(sorgu betiği);

şeklindedir.

Bundan sonrasında PostgreSQL’de B-Tree index’e alternatif indexleri incelemeye başlayabiliriz.

BRIN Index Kullanımı ve B-Tree ile Karşılaştırılması

PostgreSQL BRIN index B-Tree index’e alternatif olabilecek bir baska index tipidir. Bu index tipi ile de veri sorgulama yapabiliriz. BRIN index’ler ilgili sütunun indexlenmesinde index içerisindeki her bir blokta minimum ve maximum değerlerini saklar. Bu sayede, aralık belirten sorgularda kullanışlı olabilir. Özellikle belirli bir zaman aralığındaki veri setini çekmek isteyen sorgularda ya da doğrudan aralık belirten sorgularda kullanışlı olabilir. Ancak yine diğer index tipleri gibi veri modeline ve sorguya göre kesinlikle analiz edilmelidir. Test etmek gerekiyor. Örnek olarak aşağıdaki gibi bir sorgu BRIN index için uygun olabilir.

SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2020-02-01 0:00' AND '2021-02-28 11:59:59'
GROUP BY 1
ORDER BY 1)

BRIN index için kullandığım veri setini oluşturmak için aşağdaki örneği kullanabilirsiniz. Ek olarak, çalışmalarımda kullandığım tüm referansları da yazı sonunda bulabilirsiniz. Burada, scans isminde bir tablo oluşturup içine 31.579.201 adet kayıt eklenmiştir.

CREATE TABLE scans (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    scan float NOT NULL,
    created_at timestamptz NOT NULL
);
INSERT INTO scans (scan, created_at)
SELECT random(), x
FROM generate_series('2020-01-01 0:00'::timestamptz,
    '2022-01-01 00:00:00'::timestamptz, '2 seconds'::interval) x;

Bu sorgunun ihtiyacı olan index’i eklememiz gerekmektedir. Bu vaka için BRIN index kullanacağız.

CREATE INDEX scans_created_at_brin_idx ON scans USING brin(created_at);

Bu işlemden sonra sorgu planını ve performansını kontrol ettiğimizde aşağıdaki sonucu elde ediyoruz.

Planning Time: 0.177 ms
JIT:
Functions: 30
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 3.100 ms, Inlining 71.880 ms, Optimization 102.626 ms, Emission 67.314 ms, Total 244.920 ms
Execution Time: 291.973 ms

Aynı sorguyu B-tree index ile desteklediğimizde ise sonuç aşağıdaki gibidir.

Planning Time: 0.195 ms
JIT:
Functions: 9
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 1.205 ms, Inlining 0.000 ms, Optimization 0.317 ms, Emission 3.890 ms, Total 5.412 ms
Execution Time: 626.718 ms

Indexlerin boyutunu kontrol ettiğimizde ise arada çok büyük fark olduğunu görüyoruz. BRIN indexin boyutu B-Tree index’e göre çok daha küçüktür.

B-Tree Index Boyutu

index_demo=# SELECT pg_size_pretty(pg_relation_size('scans_created_at_btree_idx'));
pg_size_pretty
- - - - - - - -
676 MB

BRIN Index Boyutu

index_demo=# SELECT pg_size_pretty(pg_relation_size('scans_created_at_brin_idx'));
pg_size_pretty
- - - - - - - -
72 kB

Sorgu performansları yönünden de kullanım alanına ve sisteme gelecek sorguya göre performansları birbirine yakındır ve BRIN index’in daha hızlı olduğu durumlar mevcuttur. BRIN index kullanımı için tarih aralığı gibi filtreye sahip olan sorgular tercih edilmelidir. Ya da belirli bir aralığa göre filtre kullanan sorgular da duruma göre test edilerek tercih edilebilir. Uygun koşullarda B-Tree index’e göre boyutunun çok küçük olması ve aynı yada daha iyi performans vermesi nedeniyle kesinlikle tercih edilmelidir. Çünkü index boyutu ne kadar küçükse, INSERT/UPDATE/DELETE gibi işlemler de buna paralel olarak daha hızlanmaktadır.

HASH Index Kullanımı ve B-Tree ile Karşılaştırılması

PostgreSQL’de index tiplerinden bir tanesidir Hash index. Hash index aslında bir veri saklama yöntemidir. Python’daki dictionary veri modelinin PostgreSQL’e uyarlanmış halidir. Dictionary veri tipleri key ve value değerlerinden oluşur. Bu sayede aslında doğrudan KV istek olarak karşınalabilir istekler. Hash indexlerin kullanım alanları ise sorgulardaki eşitlik ifadesinin olduğu yerlerdir.

Hash index için en uygun kullanım alanı eşitlik ibaresidir. Örnek olarak aşağıdaki sorgu tarzında WHERE filtresinde kullanılan alan doğrudan eşitlik ile aranıyorsa uygundur.

select * from pgbench_accounts where bid=1

Hash indexler ile çalışmak için kullandığım veri seti ve yapısını ise sizde aşağıdaki örnekteki gibi oluşturabilirsiniz. Buraya bırakıyorum. Ayrıca, hash index çalışmasında brin indexteki veri setini kullanmadım. Çünkü hepsinin çözdüğü problemler ve sorgular farklı. Bu nedenle hepsinin özellikle çözdüğü problemlere uygun veri setleri ve yapısı kullanmaya dikkat ettim.

CREATE TABLE shorturl (
    id serial primary key,
    key text not null,
    url text not null
);
CREATE INDEX shorturl_key_hash_index ON shorturl USING hash(key);
CREATE UNIQUE INDEX shorturl_key_btree_index ON shorturl USING btree(key);
CREATE INDEX shorturl_url_hash_index ON shorturl USING hash(url);
CREATE INDEX shorturl_url_btree_index ON shorturl USING btree(url);
CREATE EXTENSION "uuid-ossp";
DO $$
BEGIN
    FOR i IN 0..1000000 loop
        INSERT INTO shorturl (key, url) VALUES (
        uuid_generate_v4(),
        'https://www.supercool-url.com/' || round(random() * 10 ^ 6)::text
    );
    if mod(i, 10000) = 0 THEN
        RAISE NOTICE 'rows:%  Hash key%  B-Tree key:%  Hash url:%  B-Tree url:%',
            to_char(i, '9999999999'),
            to_char(pg_relation_size('shorturl_key_hash_index'), '99999999999'),
            to_char(pg_relation_size('shorturl_key_btree_index'), '99999999999'),
            to_char(pg_relation_size('shorturl_url_hash_index'), '99999999999'),
            to_char(pg_relation_size('shorturl_url_btree_index'), '99999999999');
    END IF;
    END LOOP;
END;
$$;

Hash indexler normal şartlar altında B-Tree indexlere göre boyut olarak çok daha küçüktür. Aşağıdaki örnekte görülebileceği üzere, uygun yerde kullanıldığında hash index’ler b-tree indexlerin neredeyse yarısı boyutundadır.

Schema |           Name           | Type  |  Owner   |  Table   | Size  | Description  
--------+--------------------------+-------+----------+----------+-------+------------- 
 public | shorturl_key_btree_index | index | postgres | shorturl | 56 MB |  
 public | shorturl_key_hash_index  | index | postgres | shorturl | 32 MB |  
 public | shorturl_pkey            | index | postgres | shorturl | 21 MB |  
 public | shorturl_url_btree_index | index | postgres | shorturl | 56 MB |  
 public | shorturl_url_hash_index  | index | postgres | shorturl | 32 MB |

Hash Index ve B-Tree Index Performans Karşılaştırması

Hash index ve B-Tree index için performans karşılaştırması yaptığımızda ise, belirli yapılarda ve sorgularda (yukarıdaki örnek ve türevleri gibi) hash index’in de b-tree’ye yakın ve zaman zaman daha iyi performans verdiğini görmekteyiz.

Insert işlemlerinde toplam 1 milyon kayıdı insert etmek için geçen sürede hash index’in daha kısa sürdüğü görülüyor. Select işlemlerinde ise 100.000 kayıt elde etmek için geçen sürede hash index yine daha avantajlı görünüyor.

Hash Index Limitasyonları

1. Hash indexler ile unique constraint kullanılamaz.
2. Hash indexler birden çok column içeremez. Hash indexler tek indexte sadece tek bir column alırlar.
3. Hash indexlerde index içerisinde sıralama yapılamaz.

Hash index sadece çok özel durumları çözmektedir. Key değerine göre value arayan sorgular ve ihtiyaçlar için kullanışlıdır. Boyutu hem B-Tree index’e göre %30-%40 daha azdır hem de B-Tree ile aynı ve daha iyi performans sunabilmektedir. Ancak hash index gibi bir index kullanmadan önce veri modeli ve ilgili sorguya göre kesinlikle analizler yapılmalıdır.

Tüm bu çalışmalar sonucunda aslında performans beklentisi ve kazanımlara yönelik olarak farklı indexler tercih etmenin mümkün olduğunu gördük. Ancak her index her zaman diğerlerinden daha performanslı olur diye bir kural ile ilerlememek gerekiyor. Çünkü, her index tipinin çözdüğü belli sorunlar ve problemler mevcut. Önemli olan bizim ihtiyacımızın ve veri modelimizin bu indexlerden hangisine uyduğudur. Bununla birlikte de en avantajlısını seçmek mümkündür.

Sevgiler.

Demir.

Referans

Re-Introducing Hash Indexes in PostgreSQL

PostgreSQL BRIN Indexes: Big Data Performance With Minimal Storage

PostgreSQL Indexes: Hash Indexes are Faster than Btree Indexes? | EDB

BRIN indexes, what are they and how do you use them?