postgresql.conf Dosyası - work_mem Alanı

Açıklaması şöyle

PostgreSQL uses the work_mem configuration parameter to determine the amount of memory available for sorting and hashing operations. Properly configuring work_mem can greatly impact the performance of queries involving sorting or hash joins.

Configuring the work_mem parameter in PostgreSQL is crucial for optimizing query performance, but it requires finding the right balance. Increasing work_mem on high-performance systems can speed up sorting and hashing operations, leading to faster query execution.

However, setting it too high might cause excessive memory usage and potential memory contention, impacting overall performance. To determine the ideal work_mem value, start conservatively and monitor system performance while gradually increasing it. Keep a close watch on memory consumption and query execution times. Additionally, consider using partitioned queries or limiting result set sizes for resource-intensive queries to prevent excessive memory usage. Striking the right balance ensures PostgreSQL operates efficiently without overwhelming system memory and maximizes the benefits of work_mem for improved query performance.

Örnek

Şöyle yaparız

SET work_mem = "128MB";

Örnek

Şöyle

Sorgum neden bu kadar yavaş diye saatlerce baktın. Sorun sorguda değildi.

EXPLAIN çıktısında "external merge Disk" yazıyordu. PostgreSQL sıralama işlemini RAM'de bitirememiş, diske taşmıştı. work_mem 4MB'da kalmış, varsayılan değerden hiç çıkmamıştık.

PostgreSQL bellek yetmediğinde hata vermez, sessizce diske geçer. Sorgu yine de sonuç döndürür ama 200 milisaniyede bitmesi gereken iş 4 saniyeye uzar. Fark edilmesi zordur, çünkü sistem sizi uyarmaz.

PostgreSQL Health Check List

Giriş

Yazısı buradan aldım

The PostgreSQL health check I run on every new database.

Doesn't matter if it's a fresh install or a production instance someone hands me. Same checklist, every time.

Here's what I look at first:

Autovacuum settings. Is it actually running? Are the thresholds sane for the table sizes? Default scale_factor of 0.2 is fine for small tables but terrible for anything with millions of rows.

Missing indexes. I check pg_stat_user_tables for tables with high sequential scan counts relative to index scans. If a table has 500k seq_scans and 12 idx_scans, something is wrong.

Connection limits. How many connections are configured vs. how many are actually in use? I've seen instances running at 95% of max_connections with no connection pooler in sight.

Replication lag. If there's a replica, how far behind is it? Seconds of lag might be acceptable. Minutes means trouble.

Table bloat. Dead tuples piling up means autovacuum is either misconfigured or can't keep up. This is where most "the database is slow" complaints start.

Idle transactions. Long-running idle-in-transaction sessions hold locks and block autovacuum. They're silent killers.

I got tired of running through this manually on every instance, so I built these checks (and about 50 more) into mydba.dev. It runs them regularly and flags anything that needs attention.

Backup Araçları

Databasus

Açıklaması şöyle

Databasus is an industry standard for PostgreSQL backup tools, offering scheduled backups with compression and encryption for both individual developers and enterprise teams.

pgloader

Örnek

Şöyle yaparız

pgloader mysql://user:pass@10.10.10.10:3306/zabbix \
         postgresql://user:pass@10.10.10.1:31320/zabbix

pg_ivm - PostgreSQL Incremental View Maintenance

Giriş

Açıklaması şöyle

an extension that transforms materialized views into self-updating, always-fresh data structures that update instantly as your base data changes.

Açıklaması şöyle

Under the Hood: How pg_ivm Works

When you create an incremental materialized view, pg_ivm automatically:

1. Installs triggers on all referenced base tables

2. Captures changes through INSERT/UPDATE/DELETE monitoring

3. Calculates deltas to determine the minimal impact

4. Updates the view with only the necessary changes

REFRESH MATERIALIZED VIEW CONCURRENTLY - The Non-Blocking Approach

Giriş

Açıklaması şöyle. 

The Good: Allows queries during refresh (requires a unique index)

The Bad: Still recomputes the entire view from scratch

The Reality: Better UX, but still expensive and slow for large datasets.

Örnek

Şöyle yaparız

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

REFRESH MATERIALIZED VIEW - Manual Refresh

Giriş

Açıklaması şöyle. Alternatif olarak REFRESH MATERIALIZED VIEW CONCURRENTLY kullanılabilir

The Good: Simple and straightforward

The Bad: Locks the entire view during refresh, blocking all queries

The Reality: Your users stare at loading spinners while the view rebuilds

Örnek

Şöyle yaparız

REFRESH MATERIALIZED VIEW sales_summary;