postgresql useful extension

extensionlar veritabanı sisteminin işlevselliğini özelleştirmek, yeni işlevler eklemek, veritabanı yönetimini kolaylaştırmak veya belirli gereksinimleri karşılamak için kullanılan extensionların en güzellerini göreceğiz :)

\dx ile extensionları listeleyebilirsiniz :)

pg_stat_statements : Bu modül, veritabanında çalışan SQL sorgularını izler ve bu sorguların performansı hakkında ayrıntılı bilgi sağlar.

CREATE EXTENSION pg_stat_statements

postgresql.conf dosyasında ki shared_preload_libraries içersine eklememiz
gerekir ardından restart tabii :)

shared_preload_libraries = 'pg_stat_statements'

1 saniye üzerinden gerçekeleşen sorgular

SELECT query, calls, total_time, rows
FROM pg_stat_statements
WHERE total_time > 1000
ORDER BY total_time DESC;

pg_stat_statements kolonları

query: İzlenen SQL sorgusunun metni.

dbid: Sorgunun çalıştığı veritabanının kimliği.

userid: Sorguyu çalıştıran kullanıcının kimliği.

queryid: Sorgunun benzersiz bir kimliği.

calls: Sorgunun kaç kez çalıştırıldığını gösterir.

total_time: Sorgunun toplam çalışma süresi (milisaniye cinsinden).

min_time: Sorgunun en kısa çalışma süresi (milisaniye cinsinden).

max_time: Sorgunun en uzun çalışma süresi (milisaniye cinsinden).

mean_time: Sorgunun ortalama çalışma süresi (milisaniye cinsinden).

stddev_time: Sorgu çalışma süresinin standart sapması.

rows: Sorgunun döndürdüğü toplam satır sayısı.

shared_blks_hit: Sorguda paylaşılan blokların önbelleğe alındığı kez sayısı.

shared_blks_read: Sorguda paylaşılan blokların diskten okunduğu kez sayısı.

shared_blks_dirtied: Sorguda paylaşılan blokların kirletildiği kez sayısı.

shared_blks_written: Sorguda paylaşılan blokların yazıldığı kez sayısı.

local_blks_hit: Sorguda yerel blokların önbelleğe alındığı kez sayısı.

local_blks_read: Sorguda yerel blokların diskten okunduğu kez sayısı.

local_blks_dirtied: Sorguda yerel blokların kirletildiği kez sayısı.

local_blks_written: Sorguda yerel blokların yazıldığı kez sayısı.

temp_blks_read: Sorguda geçici blokların diskten okunduğu kez sayısı.

temp_blks_written: Sorguda geçici blokların yazıldığı kez sayısı.

pg_buffercache : Kısacası shareed pool’daki veriyi anlık olarak görmemize olanak sağlana extension shared pool’a lock koyuyor tabii çok sık çalıştırılması önerilmez

CREATE EXTENSION pg_buffercache;

select c.relname , count(*) as buffers
from pg_buffercache b inner join pg_class c  on b.relfilenode = pg_relation_filenode(c.oid) and b.reldatabase in (0,(select oid from pg_database  where datname = current_database())) group by c.relname order by 2 desc;

pg_buffercache kolonları

lfilenode (integer):

Veri bloğunun ait olduğu tablonun veya ilişkinin tanımlayıcısı (oid).

Bu sütun, veri bloğunun hangi tabloya veya ilişkiye ait olduğunu belirtir.

relname (name):

Veri bloğunun ait olduğu tablonun veya ilişkinin adı.

Bu sütun, veri bloğunun hangi tabloya veya ilişkiye ait olduğunu daha anlaşılır bir şekilde gösterir.

blocknum (bigint):

Veri bloğunun numarası.

Bu sütun, hangi veri bloğunun önbellekte bulunduğunu belirtir.

isdirty (boolean):

Veri bloğunun önbellekte kirli (dirty) olup olmadığını gösterir.

true ise, veri bloğu önbellekte değişiklik yapılmış ve diske yazılması gerekmektedir. false ise, veri bloğu önbellekte değiştirilmemiştir.

usagecount (integer):

Veri bloğunun önbellekte kaç kez kullanıldığını gösterir.

Bu sütun, veri bloğunun ne kadar sık erişildiğini belirtir.

pincount (integer):

Veri bloğunun önbellekte tutulma sayısı.

Bu sütun, veri bloğunun önbellekte kaç defa tutulduğunu gösterir.

hits (bigint):

Veri bloğunun önbellekte bulunduğunda kaç kez doğru bir şekilde erişildiğini gösterir.

Bu sütun, önbellekten yararlanmanın etkinliğini gösterir.

reads (bigint):

Veri bloğunun diskten kaç kez okunduğunu gösterir.

Bu sütun, önbellekten veri bloğunu almanın yerine diskten okuma gerektiğini gösterir.

dirtypages (bigint):

Önbellekte bulunan kirli veri bloklarının sayısını gösterir.

Bu sütun, diske yazılması gereken veri bloklarının sayısını ifade eder.

pg_prewarn : postgresql restart ettikten sonra cachede ki verileirmiz temizleriniz tekrar shared buffer’a gelmesi için tabloların vs kullanılması gerekir buna gerek duymadan direkt istediğimiz tabloyu shared buffer’a atmamızı sağlar

CREATE EXTENSION pg_prewarn;

select pg_prewarn ('tablo adımız','buffer')

pgcrypto: PostgreSQL veritabanında verilerin şifrelenmesi ve şifrelenmiş verilerin işlenmesi için kullanılan bir extension

CREATE EXTENSION pgcrypto;

SELECT crypt('myplaintext', gen_salt('bf'));


SELECT pgp_sym_decrypt(encrypted_column, 'şifre');



INSERT INTO users (username, password)
VALUES ('kullanici_ad', crypt('kullanici_parolasi', gen_salt('bf')));

SELECT username
FROM users
WHERE password = pgp_sym_decrypt('şifrelenmiş_parola', 'kullanici_girisi')

hypopg : postgresql içinde sanal indexler yaratmamıza olanak sağlayan extension’dur index performansını ve kullanabilirliğini test etmek için kullanılır

CREATE EXTENSION hypopg;

// index silme

SELECT hypopg_create_index('public', 'my_table',
 
        'my_hypothetical_index', 'my_column');


SELECT * FROM my_table WHERE my_column = 42;

//index silme

SELECT hypopg_drop_index('my_hypothetical_index');

amcheck : veritabanınızda bulunan endekslerin bütünlüğünü kontrol etmek ve doğrulamak için kullanılır

CREATE EXTENSION amcheck;

SELECT bt_index_check('my_table', 'my_index');

Bu sorgu, "my_table" adlı tabloya ait "my_index" adlı
B-Tree endeksini kontrol eder.

amcheck kolonları

index_name: Bu kolon, kontrol edilen endeksin adını içerir. Hangi endeksin kontrol edildiğini tanımlar.

result: Bu kolon, kontrol işleminin sonucunu ifade eder. Başarılı bir kontrol işlemi “Ok” veya “ok” olarak işaretlenirken, hatalı bir kontrol işlemi “ERROR” olarak işaretlenir.

heap_tuples: Bu kolon, veri tabanındaki ilgili tablo veya veri yapısındaki toplam kayıt (tuple) sayısını içerir.

index_tuples: Bu kolon, endekste bulunan toplam kayıt (tuple) sayısını içerir.

min_removed: Bu kolon, endeksten kaldırılan kayıt (tuple) sayısının minimum tahmini değerini içerir.

max_removed: Bu kolon, endeksten kaldırılan kayıt (tuple) sayısının maksimum tahmini değerini içerir.

amcheck_order: Bu kolon, endeks bütünlüğünün kontrol sırasını ifade eder. Kontrol sırası, endeksin fiziksel yapısı ve endeksin nasıl kontrol edildiği hakkında bilgi sağlar.

max_spacing ve max_padding: Bu kolonlar, endeksteki maksimum boşluk ve dolgu değerlerini içerir. Bu değerler, endeks yapısının doğruluğunu değerlendirmek için kullanılır.

first_offender: Bu kolon, ilk uyarı veya hata durumunda bu hatayı içeren endeks sayfa numarasını içerir.

first_error_message: Bu kolon, ilk hata durumunda oluşan hata mesajını içerir. Hata, endeks bütünlüğüyle ilgili bir sorunu tanımlar.

first_error_at: Bu kolon, ilk hata veya uyarının ne zaman oluştuğunu içerir. Bu, kontrol işleminin belirli bir aşamasındaki hatayı belirler.

error_count: Bu kolon, kontrol işlemi sırasında toplam hata veya uyarı sayısını içerir.

pg_freesspacemap : postgresql veritabanı istemcisidir ve veritabanındaki serbest alan haritasını (free space map) yönetmek ve görüntülemek için kullanılır.

CREATE EXTENSION pg_freesspacemap;


SELECT * FROM pg_freesspacemap('my_table');

pg_repack : postgresql içerisinde çeşitli araçlar kullanılırken sistemde uzun süreli locklara neden olduğundan dolayı geliştirilen bir araçtır.

yum install pg_repack13 //13 version 


shared_preload_libraries = 'pg_repack'

CREATE EXTENSION pg_repack;

pg_repack -d mydatabase