PostgreSQL optimizasiyası və performans artırma

PostgreSQL açıq mənbəli və güclü bir verilənlər bazası idarəetmə sistemidir. Lakin böyük həcmli məlumatlarla işləyərkən performansı artırmaq üçün bəzi optimizasiya texnikalarından istifadə etmək vacibdir. Bu məqalədə PostgreSQL-in performansını artırmaq üçün istifadə edilə biləcək müxtəlif üsulları izah edəcəyik.

1.İndekslərdən İstifadə İndekslər sorğuların icrasını sürətləndirən əsas vasitələrdən biridir. Aşağıdakı indeks növlərindən istifadə etməklə performansı yaxşılaşdıra bilərsiniz

• B-Tree indeksləri: Ən çox istifadə edilən indeks növüdür. Bərabər və müqayisəli sorğular üçün uyğundur.
• Hash indeksləri: Dəqiq bərabərlik (=) əməliyyatları üçün istifadə olunur.
• GIN (Generalized Inverted Index): Tam mətn axtarışlarında və JSONB tipli verilənlər üçün idealdır.
• GiST (Generalized Search Tree): Coğrafi məlumat bazaları və interval sorğuları üçün istifadə olunur.

İndeks yaratmaq üçün aşağıdakı komandaya baxaq

CREATE INDEX idx_user_email ON users (email);

2. VACUUM və ANALYZE əməliyyatları PostgreSQL, məlumat bazasında dəyişikliklər edildikcə ölü satırları saxlayır. Bunları təmizləmək üçün VACUUM və statistikaları yeniləmək üçün ANALYZE əməliyyatlarından istifadə edilməlidir

Manuel VACUUM icrası üçün komanda

VACUUM ANALYZE;

Avtomatik optimizasiya üçün autovacuum xidmətindən istifadə edilə bilər

SHOW autovacuum;

3. Səhifələmə və LIMIT İstifadəsi Böyük həcmli məlumatları səhifələmək üçün LIMIT və OFFSET istifadə edilsə də, performans problemi yarada bilər. Bunun əvəzinə keyset pagination metodundan istifadə etmək tövsiyə olunur

SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 50;

4. Advanced konfiqurasiya konfiqləri PostgreSQL-in performansını artırmaq üçün konfiqurasiya faylında (postgresql.conf) aşağıdakı dəyişikliklər edilə bilər

• work_mem: Sorğular zamanı istifadə edilən yaddaş miqdarını təyin edir.
• shared_buffers: PostgreSQL-in yaddaşda saxladığı verilənlər bazası səhifələrinin miqdarını təyin edir.
• effective_cache_size: Sistemin diskdən oxuma əvəzinə önbellekdən (cache) istifadə etməsinə kömək edir.

ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

5. Sorğuların performans analizi Sorğuların performansını analiz etmək üçün EXPLAIN və EXPLAIN ANALYZE istifadə olunur.Bu, sorğunun necə icra edildiyini göstərir və optimizasiya üçün ipucları təqdim edir.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 10;

6. Partitioning və Tablespace İstifadəsi Çox böyük cədvəlləri daha kiçik hissələrə bölmək (Partitioning) verilənlər bazasının performansını artıra bilər. PostgreSQL-də RANGE və LIST partitioning istifadə edilə bilər

CREATE TABLE sales (
id SERIAL PRIMARY KEY,
sale_date DATE NOT NULL,
amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

7. Connection pooling və paralel sorğular  Connection pooling alətləri (PgBouncer, Pgpool-II) çoxlu istifadəçi sorğularını daha effektiv idarə etməyə kömək edir. Bundan əlavə, PostgreSQL paralel sorğuları da dəstəkləyir

SET max_parallel_workers_per_gather = 4;

8. Materialized Views və Caching Əgər tez-tez eyni nəticələri alırsınızsa, MATERIALIZED VIEW istifadə edərək məlumatları öncədən saxlaya bilərsiniz.Bu, mürəkkəb sorğuların təkrar icrasını azaldır və performansı artırır.

CREATE MATERIALIZED VIEW recent_orders AS
SELECT * FROM orders WHERE order_date > now() - interval '7 days';

9. JSONB və NoSQL səstəyi avrdır. Əgər PostgreSQL-də struktursuz məlumatlarla işləyirsinizsə, JSONB tipi istifadə edilə bilər. JSONB üçün GIN indekslərindən istifadə edərək sorğuların sürətini artıra bilərsiniz

CREATE INDEX idx_jsonb_data ON products USING GIN (data);

10. Backup və Replikasiya daimi backup və replikasiya strategiyası ilə verilənlər bazasının yüklənməsini balanslaşdırmaq mümkündür.Streaming replikasiya və WAL (Write-Ahead Logging) istifadə edərək performans artırıla bilər.

pg_dump -U postgres -F c -f backup_file.dump mydatabase

Əlavə qeyd olaraq PostgreSQL-in optimallaşdırılması performans artımına və resursların daha effektiv istifadəsinə kömək edir. Bu məqalədə indekslər, VACUUM, səhifələmə, partitioning, caching və paralel sorğular kimi müxtəlif optimizasiya texnikalarını araşdırdıq. Daha yüksək performans üçün PostgreSQL konfiqurasiyasını və sorğu dizaynını diqqətlə tənzimləmək vacibdir.