PostgreSQL Nedir?

· Postgresql ilişkisel ve ilişkisel olmayan sorgulamayı destekleyen açık kaynaklı ve gelişmiş bir ilişkisel veritabanı sistemidir. DBMS sistemini güvenilir kılmak için büyük katkı sağlayan deneyimli geliştiriciler topluluğu tarafından desteklenmektedir..

PostgreSQL Özellikleri

• Windows, linux gibi geniş işletim sistemlerinde uyumlu bir şekilde desteklemekte ve çalışmaktadır.
• Yazılım dil desteği olarak c#,java,node.js ,ruby gibi programlama dillerini destekler.
• Primitives (String, Numeric, Integer, Boolean), Structured (Dizi, Date/Time, UUID, Range), Geometry (Poligon, Line, Point, Circle), Document gibi geniş veri tiplerini destekler ( XML, JSON/JSONB).

PostgreSQL’in Avantajları Nelerdir?

• Açık kaynaklı veri tabanı olduğundan herhangi bir lisans maliyeti yoktur.
• Açık kaynaklı veritabanı yazılımı olduğundan çıkacak hataları ve geliştirmeleri 25 yılı aşkın süredir postgresql topluluğu yapmaktadır.
• Kendi veri türlerini tanımlama veya veri tabanlarını yeniden derlemeden diğer programlama dillerinde kod yazma yetkisi verilir.
• İşletmelerin bulut hizmetleri, makina öğrenimi ,veri ambarı analitiği vb gibi yeni veri tabanı teknolojilerini uygulamalarına olanak tanır.
• Master to master veya master to slave olarak canlı veritabanlarını sunucu yardımıyla çoğaltarak yedeklilik ve arşivleme olanağı sağlar.
• Gelişmiş veri türlerini ve gelişmiş performans optimizasyonunu destekler.
• Dinamik web sitelerini ve web uygulamalarını LAMP yığını seçeneği olarak çalıştırabilir

PostgreSQL’in Dezavantajları Nelerdir?

• Performans ölçümüne göre postgresql, mysql ‘e göre daha yavaştır.
• Alternatif DBMS yani veri tabanları yönetim sistemleri mysql, mssql, oracla popüler veritabanı yazılımları kadar yazımı kolay değildir. DBMS’i açıklamak gerekirse veri tabanı oluşturma, veri güncelleme ,veri tabanında tablo oluşturma ve daha birçok işlemi gerçekleştirmek için bir arayüz sağlar.
• PostgresSQL işlemci ve cpu üzerinde kaynaklar tüketebilir. Bu sebeplede hız ve performans sorunlarına yol açar.

PostgreSQL’in Index Tipleri Nelerdir?

• BTREE Index:Yeni bir index yaratıldığında index tipi verilmediği durumda default olarak btree index oluşur.Sorguda özellikle where kısmında <,>,ilike,not in ,in ,like,not in ve between gibi ifadeler geçiyorsa BTree index kullanılması sorgunun performans hızını artırmaktadır.Ağaç yapısında tutulmaktadır.

CREATE INDEX ix_productName ON Product (Name);
CREATE INDEX ix_categoryName ON Category USING btree (Name);
Birden fazla kolon için aşağıdaki gibi tanımlanmaktadır.
CREATE INDEX ix_productPrice_Name ON Product USING btree (Name,Price);

• Hash Index: Index tipinin kullanımı PostgreSQL 10 versiyon öncesinde kullanılması önerilmez.Sadece eşitlil işareti olması durumunda kullanılır.Sorguda eşitlik ifadesi geçmesi durumunda hız bakımında BTREE Index’e göre Hash Index kullanılması tavsiye edilir.Hash Index BTree indexe göre memoryde az yer kaplar. Tabloya yeni satırlar eklendikçe linear olarak büyüyen B-Tree indexinin aksine, Hash indexi ani artışlarla büyür.Hash Index flat bir yapıda tutulmaktadır.İndexleme yapılan tabloda cluster kullanımına hash index izin vermemektedir.

Hash index kullanım örneği
CREATE INDEX ix_categoryName ON Category USING HASH(Name);

UniqueContraints olarak tanımlanmaz
CREATE UNIQUE INDEX idx_unique_key ON table USING hash(key);
--------
ERROR:  access method "hash" does not support unique indexes

Birden fazla kolon tanımlanmaz.
CREATE INDEX ix_productPrice_Name ON Product USING hash(Name,Price);
ERROR:  access method "hash" does not support multicolumn indexes

Hash indexleme yapılırken sıralama ifadelerine yer verilemez.
CREATE INDEX idx_key ON table USING hash(key desc);
ERROR:  access method "hash" does not support ASC/DESC options

• GIN Index:Metin aramalarında kullanılır. Dizi değerlerinin bulunduğu kolonlarda GIN Index B-Tree indexe göre daha performanslıdır. Tam metin aramalarında genellikle tsvector veri tipi kullanıldığı için ilgili kolona GIN Index eklenmesi önerilir.

CREATE INDEX idx_test on test USING GIN(data);

• GIST Index: Geometrik veri tipli kolonlarda, zaman tipli kolonlarda ve tam metin aramaları için kullanılır.

CREATE INDEX ON table_name USING GIST (column_name class_name);

• BRIN Index: Büyük veri setlerinde diğer index tiplerine göre daha yararlıdır. İndexin boyutu B-Tree indexe göre oldukça küçük oluşur. Bu nedenle tarih veya şehirlerin zip kodları gibi kolonlarda kullanılması önerilir.

CREATE INDEX idx_btree ON users USING BTREE(test); // Size of index = 20 MB
CREATE INDEX idx_hash ON users USING HASH (test);  // Size of index = 39 MB
CREATE INDEX idx_brin ON users USING BRIN (test); // Size of index = 64 KB

Clustered Index

• Tablodaki tüm verileri belirli bir sıraya göre düzenler.Bu sıralama tablo için yeni bir index oluşturulduğunda belirlenir be sıralana ölçütüne göre her bir satırı sıralayan tek bir index yapısı oluşturulurClustered index kullanımı veritabanı performansını artırmak için en etkin index türüdür.Ancak var olan indexin düzenlenmesi ve yeni index eklenmesi oldukça mailyetlidir.Tablo ilk oluşturulduğunda oluşur.Azalan dan artan düzende primary key olarak tabloda unqiue id tutulur.

CREATE TABLE friends (id INT PRIMARY KEY, name VARCHAR, city VARCHAR);

Non-clustered index,

• Tablodaki verileri sıralamaz. Bunun yerine, tablodaki belirli bir sütuna göre bir index oluşturur ve bu index sütununa hızlı erişim sağlar. Non-clustered index, bir tabloya birden fazla index eklenebilir ve bu sayede birden fazla sütuna hızlı erişim sağlanabilir. Non-clustered index, sadece belirli bir sütuna göre bir index oluşturur ve diğer sütunlarda herhangi bir sıralama veya düzenleme yapmaz. Non-clustered index, genellikle cluster indexe göre daha ucuzdur, ancak performans farklılık gösterebilir.

CREATE INDEX sales_customer_name_idx ON sales (customer_name);

PostgreSQL Explain-Analyze Kavramları

Explain:Sorgunun çalışma planını gösterir.Explain ile çalıştırdığınız sorgu gerçekte çalışmaz,bu yüzden Insert,update,delete gibi yazılmış sorgularında çalışma planına bakabiliriz.EXPLAIN’i aşağıdaki seçeneklerle birlikte kullanabilirsiniz:Indexleme yapmak için önemlidir.

explain with recursive  x as ( select 
 ip_ci."NAME" as "Value", 
 ip_ci."NAME" as "Text", 
 null as "Server", 
 concat(ip_ci."SERVICE_NAME", '') as sn, 
 concat(db_ci."NO", '') as db_ci_no
 from servis
 inner join servisrelation on rel."CIID" =db_ci."ID" 
 inner join servisrelationInfo ip_c) select "Value", "Text", "Server" from x
 where sn = 'xxx' and db_ci_no = 'xxxx'

• ANALYZE [ boolean ]: Sorguyu gerçekten çalıştırır ve tüm kısımlarla ilgili ayrıntılı bilgi verir. INSERT/UPDATE/DELETE sorguları için;
  BEGIN;
  EXPLAIN ANALYZE sorgu;
  ROLLBACK;
  ifadesi içinde kullanılması önerilir.

explain analyze with recursive  x as ( select 
 ip_ci."NAME" as "Value", 
 ip_ci."NAME" as "Text", 
 null as "Server", 
 concat(ip_ci."SERVICE_NAME", '') as sn, 
 concat(db_ci."NO", '') as db_ci_no
 from servis
 inner join servisrelation on rel."CIID" =db_ci."ID" 
 inner join servisrelationInfo ip_c) select "Value", "Text", "Server" from x
 where sn = 'xxx' and db_ci_no = 'xxxx'

• VERBOSE [ boolean ]: Varsayılan değeri FALSE olan bu değer sorgu ile ilgili ayrıntılı bilgi verir.

explain VERBOSE with recursive  x as ( select 
 ip_ci."NAME" as "Value", 
 ip_ci."NAME" as "Text", 
 null as "Server", 
 concat(ip_ci."SERVICE_NAME", '') as sn, 
 concat(db_ci."NO", '') as db_ci_no
 from servis
 inner join servisrelation on rel."CIID" =db_ci."ID" 
 inner join servisrelationInfo ip_c) select "Value", "Text", "Server" from x
 where sn = 'xxx' and db_ci_no = 'xxxx'

• COSTS [ boolean ]: Varsayılan olarak TRUE gelen bu değer sorgunun toplam maliyeti ve her bir parçasının tahmini satır değeri gibi ayrıntıları verir.

explain ( COSTS OFF)   with recursive  x as ( select 
 ip_ci."NAME" as "Value", 
 ip_ci."NAME" as "Text", 
 null as "Server", 
 concat(ip_ci."SERVICE_NAME", '') as sn, 
 concat(db_ci."NO", '') as db_ci_no
 from servis
 inner join servisrelation on rel."CIID" =db_ci."ID" 
 inner join servisrelationInfo ip_c) select "Value", "Text", "Server" from x
 where sn = 'xxx' and db_ci_no = 'xxxx'

b

• BUFFERS [ boolean ]: Varsayılan olarak FALSE gelen ve yalnızca ANALYZE ile kullanılabilen bu değer sorgunun buffer kullanımı ile ilgili bilgi verir.

explain ( analyze,BUFFERS ON)   with recursive  x as ( select 
 ip_ci."NAME" as "Value", 
 ip_ci."NAME" as "Text", 
 null as "Server", 
 concat(ip_ci."SERVICE_NAME", '') as sn, 
 concat(db_ci."NO", '') as db_ci_no
 from servis
 inner join servisrelation on rel."CIID" =db_ci."ID" 
 inner join servisrelationInfo ip_c) select "Value", "Text", "Server" from x
 where sn = 'xxx' and db_ci_no = 'xxxx'

• TIMING [ boolean ]: Varsayılan olarak TRUE gelen ve yalnızca ANALYZE ile kullanılabilen bu değer sorgunun her bir parçasının süresi ile ilgili ayrıntılı bilgi verir.
• SUMMARY [ boolean ]: ANALYZE ile birlikte varsayılan olarak kullanılan bu değer sorgu planının en sonunda plan ve çalışma süreleri ile ilgili süre bilgisi verir.
• FORMAT { TEXT | XML | JSON | YAML }: Varsayılan olarak TEXT gelen bu değer sorgu planının tipini belirlemek için kullanılır.

PostgreSQL Materalized View Kullanımı

• View ile aynı mantıktadır view de birden fazla tablodaki kolonları kullanarak view oluşturabilirsiniz. Materalized View ‘in normal view den farkı olarak view için sanal tablo demiştik veri tutulmuyordu fakat materalized view de hazırladığınız sorguyu içeren veriler fiziksel olarak diskte tutulur. Normal viewden farklı olarak Materalized View index atılabilir.

create materialized view appointments_cache
 as select * 
 from appointments_view;
create unique index on appointments_cache(appointment_

• Veri eklendiğinde,çıkarıldığında,güncellendiğinde materalized içerisinde veriler değişmez. Değişen verileri güncellemek için materalized viewi refresh materalized view komutu ile güncellemek gerekir.Refresh komutunu view locklanmaması açısından concurrently olarak çalıştırmamız gerekir.Concurent ile refresh kullanımı bu sayede cache’i de temizleyerek günceller.Uygulama katmanında INSER,UPDATE,DELETE ve SELECT işlemlerini transaction locklanmadan kullanabilirisniz.(PostgreSQL > 9.4)

refresh materialized view concurrently appointments_cache;

PostgreSQL Performans Kazanımları

• Şemada bulunan tablolar arasında örneğin ürün ve ürün kategori tabloları arasında direk olarak foreign key Id performans açısından kullanmayı önermiyoruz. Ancak ürün tablosunda bulunan Id yi ürün kategorisine kolon olarak eklediğinizde inner join sorguyla erişim sağlayabilirsiniz.Tabiki performans kazanmak için clustered index olarak tabloya eklememiz gerekmektedir.Clustered index’i iki tabloyu joinli sorguyu daha performanslı hale getirmesi için kullanmayı öneriyoruz.
• Sorgu kriterlerine göre tablolara clustered index eklenmeside performans kazanımına yol açmaktadır.
• Sorguda birden fazla join ile bağlı tablodan backend tarafında yazılım dili c# kullanarak yüklü veri çektiğimizde yazılımın hızlı ve performanslı çalışmasını sağlamak amacıyla metarialized view kullanmamız uygun olacaktır.

CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

• Metariazeld View’I oluşturduktan sonra verileri aşağıdaki sql komutuyla yenilemeiz gerekmektedir.

REFRESH MATERIALIZED VIEW mymatview;

• Yazılım uygulamamızda ekrandaki açılır veri tipi listesindeki değerleri daha hızlı ve performanslı çalıştırmak adına sorguda optimizasyon yapıldı ve hız kazandırdı . Bunu aşağıdaki gibi olan sorguyu with recursive x as ile değiştirebilirsiniz.10 saniyede çalışan sorgu örneğin bu şekilde 1 saniye kadar performans kazanımı sağlamış oluruz.

select 
 ip_ci."NAME" as "Value", 
 ip_ci."NAME" as "Text", 
 null as "Server", 
 concat(ip_ci."SERVICE_NAME", '') as sn, 
 concat(db_ci."NO", '') as db_ci_no
 from servis
 inner join servisrelation on rel."CIID" =db_ci."ID" 
 inner join servisrelationInfo ip_ci on ip_ci."ID" = rel."SUBCIID"

with recursive x as ( select 
 ip_ci."NAME" as "Value", 
 ip_ci."NAME" as "Text", 
 null as "Server", 
 concat(ip_ci."SERVICE_NAME", '') as sn, 
 concat(db_ci."NO", '') as db_ci_no
 from servis
 inner join servisrelation on rel."CIID" =db_ci."ID" 
 inner join servisrelationInfo ip_c) select "Value", "Text", "Server" from x
 where sn = 'xxx' and db_ci_no = 'xxxx'