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ıyı 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;

Soft deletes vs. Journal tables

Örnek

create_journal_table() metodu şöyledir. Bu metod EVENT TRIGGER HANDLER içindir.

CREATE OR REPLACE FUNCTION create_journal_table()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    obj record;
    schema_name text;
    table_name text;
    journal_table_name text;
BEGIN
    FOR obj IN
        SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF obj.command_tag = 'CREATE TABLE' AND obj.object_type = 'table' THEN
            schema_name := split_part(obj.object_identity, '.', 1);
            table_name  := split_part(obj.object_identity, '.', 2);

            schema_name := trim(both '"' from schema_name);
            table_name  := trim(both '"' from table_name);

            -- Skip journal tables to avoid recursion
            IF right(table_name, 8) = '_journal' THEN
                CONTINUE;
            END IF;

            journal_table_name := format('%I.%I_journal', schema_name, table_name);

            EXECUTE format('CREATE TABLE IF NOT EXISTS %s (
                id bigserial PRIMARY KEY,
                action text NOT NULL,
                changed_at timestamptz DEFAULT now(),
                old_data jsonb,
                new_data jsonb
            )', journal_table_name);

            EXECUTE format(
                'CREATE TRIGGER %I_to_journal
                 AFTER INSERT OR UPDATE OR DELETE ON %I.%I
                 FOR EACH ROW
                 EXECUTE FUNCTION log_to_journal()',
                table_name, schema_name, table_name
            );
        END IF;
    END LOOP;
END;
$$;  

Bu metodu register ederiz. Metod şöyledir. Bu metod CREATE EVENT TRIGGER içindir.

CREATE EVENT TRIGGER create_journal_after_table
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION create_journal_table();

log_to_journal() metodu şöyledir. Bu metod row level logging yapar

CREATE OR REPLACE FUNCTION log_to_journal()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    journal_table text;
BEGIN
    journal_table := format('%I.%I_journal', TG_TABLE_SCHEMA, TG_TABLE_NAME);

    IF TG_OP = 'INSERT' THEN
        EXECUTE format(
            'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
            journal_table
        )
        USING TG_OP, NULL, to_jsonb(NEW);

    ELSIF TG_OP = 'UPDATE' THEN
        EXECUTE format(
            'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
            journal_table
        )
        USING TG_OP, to_jsonb(OLD), to_jsonb(NEW);

    ELSIF TG_OP = 'DELETE' THEN
        EXECUTE format(
            'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
            journal_table
        )
        USING TG_OP, to_jsonb(OLD), NULL;
    END IF;

    RETURN NULL;  -- AFTER trigger
END;
$$;

JSON_TABLE

Giriş

Açıklaması şöyle

Use JSON_TABLE() to transform JSON data into tabular format.

Örnek

Şöyle yaparız

SELECT *
FROM JSON_TABLE(
  '[{"name":"Alice","age":30},{"name":"Bob","age":25}]',
  '$[*]'
  COLUMNS(name TEXT PATH '$.name', age INT PATH '$.age')
) AS jt;

MERGE ... RETURNING - Postgres 17 İle Geliyor

Giriş

Açıklaması şöyle

You can now use it MERGE ... RETURNING to retrieve rows that were inserted, updated, or deleted—all in one go.

Örnek

Şöyle yaparız

MERGE INTO employees AS e
USING new_data AS d
ON e.id = d.id
WHEN MATCHED THEN
  UPDATE SET salary = d.salary
WHEN NOT MATCHED THEN
  INSERT (id, salary) VALUES (d.id, d.salary)
RETURNING *;

postgresql.conf Dosyası - Log Ayarları

log_min_duration_statement

Açıklaması şöyle

log_min_duration_statement: just logging the statements whose duration is more than this value in milliseconds. This is very useful for detecting slow queries.

Açıklaması şöyle

It sets the minimum execution time in milliseconds (ms) above which all statements will be logged.

The default value for the log_min_duration_statement parameter is -1, which disables logging statements.

Setting the PostgreSQL parameter log_min_duration_statement to 0 will print all statements' durations.

Örnek

Şöyle yaparız

# set log_min_duration_statement=1;
SET

# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 1ms
(1 row)

PostgreSQL log dosyalarındaki statement log cümleleri şöyledir

LOG:  duration: 5.477 ms  statement: insert into abc values(1);
LOG:  duration: 8.656 ms  statement: insert into abc values(2);

log_statement

ALL, MOD,DDL değerlerini alabilir

Açıklaması şöyle

Most fintech companies, who are required to log all user and application activities, set the most verbose option log_statement=all. This is the easiest way to bloat the log files and invite storage issues if storage consumption is not monitored. Pg_audit can be a smarter way of logging user activities, where you can specify which class of activities you want to log, such as READ, WRITE, DDL, or FUNCTION. 

log_statement_sample_rate

Açıklama yaz

log_destination

Bu alanlar için Audit Trails yazısına bakabilirsiniz

log_destination Alanı

PostgreSQL 15 için açıklama şöyle

PostgreSQL supports several methods for logging server messages, including stderr (default), csvlog, and syslog. With this release, jsonlog is also supported which is convenient for exporting logs and debugging. In order to enable this, add the jsonlog under the log_destination inside the postgresql.conf file.

This will be a nice feature to have with exports to logging and monitoring tools like HoneyComb and DataDog etc.

Historical Table

Giriş

Yedek yani backup amaçlı değildir. Sadece eski veriyi farklı bir tabloya taşır

Örnek

Şöyle yaparız

> create table main_table_historical_data
as select * from main_table where create_date < '01-Jan-2020';
> delete from main_table where create_date < '01-Jan-2020';

pg_createsubscriber

Giriş

Açıklaması şöyle

The pg_createsubscriber command allows for easy transformation of physical replicas into logical replicas, making high-availability setups more manageable.

Best Practice: For large retail applications, using pg_createsubscriber helps in maintaining fail-safe backups without overhauling the infrastructure, providing continuous service even during maintenance.

pg_cron Extension

Giriş

Açıklaması şöyle

pg_cron is a PostgreSQL extension that allows scheduling of SQL queries to run at specific times, similar to cron jobs in Linux.

Örnek

Şöyle yaparız

SELECT cron.schedule('0 0 * * *', $$DELETE 

FROM cron.job_run_details 

WHERE end_time < now() - interval '10 days'$$);

pg_combinebackup komutu - Incremental Backup İçindir

Örnek

Önce full backup yapılır. Şöyle yaparız

pg_combinebackup /backups/fullbackup/ /backups/incr_backup1/ -o /combinebackup/

Açıklaması şöyle

In this command:

- /backups/fullbackup/ is the directory containing the full backup.

- /backups/incr_backup1/ is the first incremental backup directory.

- -o specifies the output directory where the combined backup will be stored.

Daha sonra PostgreSQL Service durdurulur. Şöyle yaparız

/usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/17/data/ -l logfile stop

Dizine okuma ve yazma hakkı veriririz. Şöyle yaparız

chmod 700 /combinebackup

PostgreSQL Service tekrar başlatılır. Şöyle yaparız

/usr/pgsql-17/bin/pg_ctl -D /combinebackup/ -l /combinebackup/logfile start

PostgreSQL Service tarafından kullanılan dizini kontrol etmek için şöyle yaparız

SHOW data_directory;
 data_directory
----------------
 /combinebackup
(1 row)

pg_basebackup komutu - Incremental Backup İçindir

Örnek

Önce full backup yapılır. Şöyle yaparız

/usr/pgsql-17/bin/pg_basebackup -D /backups/fullbackup

/* 
The `-D` flag specifies the destination directory for the backup. 
Ensure that the directory exists and has the necessary permissions.
*/

Sonra incremental backup yapılır. Şöyle yaparız

pg_basebackup - incremental=/backups/fullbackup/backup_manifest -D /backups/incr_backup1/

/*
The ` - incremental` flag specifies the location of the full backup's 
manifest file, and `-D` is again used to specify the directory for the 
incremental backup.
*/

PostgreSQL Shared Buffer

Giriş

Açıklaması şöyle

When you are sending a read or write request to Postgres, you are never interacting with the files directly. In order to read something, first those pages need to be loaded in shared_buffers! 

Proper Amount for Shared Buffer

Açıklaması şöyle

It is recommended in the docs: If (RAM > 1GB) shared_buffers = 25%

Official Docs: Values larger than 40% of RAM might NOT Help

Açıklaması şöyle

Pro tip: If you are setting a PostgreSQL or MySQL, don't use the default database settings because those are meant for personal computers or notebooks.

One example is the Buffer Pool size:

- For MySQL, increase the innodb_buffer_pool_size

- For PostgreSQL, increase shared_buffers and effective_cache_size to match your OS cache size

Örnek

postgresql.conf dosyasında şöyle yaparız

shared_buffers = 256MB
effective_cache_size = 1GB

Örnek

Şöyle

What's the worst PostgreSQL configuration mistake you've seen in production?

I'll start: shared_buffers = 128 MB on a 64 GB server.

Default PostgreSQL configuration. Running in production. For two years. On a database serving 50,000 queries per second.

The database was using 128 MB of its own cache and relying entirely on the operating system's page cache for everything else. It worked — PostgreSQL is remarkably resilient — but it was leaving enormous performance on the table.

Changed shared_buffers to 16 GB (25% of RAM), adjusted effective_cache_size to 48 GB, and query response times dropped 40% overnight.

Other configuration horrors I've seen:

• 𝗺𝗮𝘅_𝗰𝗼𝗻𝗻𝗲𝗰𝘁𝗶𝗼𝗻𝘀 = 𝟭𝟬𝟬𝟬 with no connection pooler. Each connection using 10 MB of RAM. Server running out of memory under load.

• 𝗿𝗮𝗻𝗱𝗼𝗺_𝗽𝗮𝗴𝗲_𝗰𝗼𝘀𝘁 = 𝟰.𝟬 on an NVMe SSD. The planner thought random reads were 4x more expensive than sequential reads, avoiding index scans that would have been faster. Should have been 1.1.

• 𝘄𝗼𝗿𝗸_𝗺𝗲𝗺 = 𝟰 𝗠𝗕 on an analytics workload. Every complex query spilling to disk for sorts and hash joins. Changed to 256 MB and query times dropped from minutes to seconds.

• 𝗮𝘂𝘁𝗼𝘃𝗮𝗰𝘂𝘂𝗺 = 𝗼𝗳𝗳. Yes, someone turned it off. The table bloat was spectacular.

The PostgreSQL defaults are intentionally conservative. They're designed to run on a Raspberry Pi, not to perform well on your production server. Five settings — shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, random_page_cost — capture 80% of the performance gains.

PG_BUFFERCACHE Extension

Shared Buffer kullanımını görmek için kullanılabilir

postgresql.conf İyileştirmeleri

Örnek

Şöyle yaparız

# Memory settings
shared_buffers = 16GB
work_mem = 64MB
maintenance_work_mem = 2GB
effective_cache_size = 48GB

# Connection settings
max_connections = 500

# Checkpoint settings
checkpoint_segments = 32
checkpoint_completion_target = 0.9

# WAL settings
wal_buffers = 16MB
max_wal_size = 4GB

# Logging settings
log_min_duration_statement = 5000  # Log queries taking longer than 5 seconds  

wal_buffers Nedir

WAL verisi diske yazılmadan önce bellekte kısa bir süre bekler. Bu bekleme alanınnı büyüklüğüdür. Varsayılan değer shared_buffers değerinini %3'üdür.

HAProxy - Advanced Load Balancing, Session Persistence, And Health Checks

Oracle vs Postgres Sütun Tipleri

Şeklen şöyle