Logical Replication

Giriş

Açıklaması şöyle

- PostgreSQL 16 introduces the ability to perform logical replication from a standby instance, enabling the distribution of workloads by using standby to replicate changes to downstream systems.

- Performance enhancements in this release for logical replication include parallel processing for large transactions, the use of B-tree indexes for tables without primary keys, and faster initial table synchronization in binary format.

- Access control improvements in this release encompass the addition of the predefined role “pg_create_subscription” granting users the capability to create logical subscriptions, and the beginning of bidirectional logical replication support, enabling data replication between tables from different publishers.

pgBadger - Log Analyzer

Giriş

PostgreSQL log dosyalarını inceleyerek performans iyileştirmesi için bir rapor hazırlar. Açıklaması şöyle

pgBadger is a powerful log analyzer specifically designed for PostgreSQL logs. It parses the log files and generates detailed reports, including query execution time, slowest queries, and usage patterns. This tool simplifies the process of identifying performance issues and optimizing queries.

HypoPG - Hypothetical Indexes

Giriş

Açıklaması şöyle

We all know how indexes can optimize query plans if used carefully. Since index creation requires resources, it would have been nice to have a way to know beforehand whether postgres will use those indexes or not.

HypoPg, a postgres extension lets you do just that. It can be used to create a hypothetical index without costing you resources and enables you to analyse the index performance.

Kurulum

Şöyle yaparız

CREATE EXTENSION hypopg;

hypopg_create_index metodu

Şöyle yaparız. Sequence Scan veya Index Scan kullanıldığını gösterir

SELECT * FROM hypopg_create_index('CREATE INDEX ON mytable (id)');
EXPLAIN SELECT * FROM mytable WHERE id = 1;    

Diğer

Açıklaması şöyle

hypopg_reset() — to clear the previous effects of other indexes

hypopg_drop_index(indexrelid) — to remove your backend’s hypothetical indexes

hypopg_hide_index(oid) — to hide an index

hypopg_unhide_index(oid) — to restore the index

PG_STAT_USER_INDEXES Sistem Tablosu - Index Hakkında Bilgi Verir

indexrelname Sütunu

Örnek -  unused index

Şöyle yaparız

SELECT relname , indexrelname , idx_scan , idx_tup_read , idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public' and relname = 'foo';

Açıklaması şöyle

relname: The name of the database table.

indexrelname: The name of the user index for which these statistics are relevant.

idx_scan, idx_tup_read & idx_tup_fetch: are different index statistics metrics helps us in determining whether index is being used or not.

indexrelid Sütunu

Örnek

Şöyle yaparız

SELECT
  idx_stat.indexrelid::regclass AS index_name,
  pgstattuple(idx_stat.indexrelid) AS stats
FROM
  pg_stat_user_indexes AS idx_stat

Açıklaması şöyle

The pgstattuple extension in PostgreSQL is used to gather statistical information about the physical tuples (rows) stored in a table. It provides detailed insights into the distribution and characteristics of the tuples, allowing you to analyze the table's data fragmentation, dead tuples, and overall health.

CREATE UNLOGGED TABLE

Giriş

Açıklaması şöyle

An unlogged table in PostgreSQL is a table that is not written to the write-ahead log (WAL). This means that changes to unlogged tables are not persisted to disk until a checkpoint is performed.

Burada PostgreSQL veri tabanını cache olarak kullanmakla ilgili bir yazı var