public inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feed[PATCH] Docs: logical replication examples and autovacuum/XID freeze clarifications
2+ messages / 2 participants
[nested] [flat]
* [PATCH] Docs: logical replication examples and autovacuum/XID freeze clarifications
@ 2025-12-14 19:05 Jhonathan da Rocha da Cruz <j.cruz@acad.ifma.edu.br>
0 siblings, 1 reply; 2+ messages in thread
From: Jhonathan da Rocha da Cruz @ 2025-12-14 19:05 UTC (permalink / raw)
To: pgsql-docs
Hello,
This patch improves the PostgreSQL documentation by adding practical
examples
for logical replication and clarifying autovacuum behavior and transaction
ID
freezing (anti-wraparound).
The changes aim to complement the existing documentation without removing or
altering current content.
Files changed:
- doc/src/sgml/logical-replication.sgml
- doc/src/sgml/maintenance.sgml
- doc/src/sgml/mvcc.sgml
Tests performed:
- make html
Patch attached.
Regards,
Jhonathan Cruz
Attachments:
[application/octet-stream] 0001-docs-add-logical-replication-examples-and-clarify-au.patch (8.7K, 3-0001-docs-add-logical-replication-examples-and-clarify-au.patch)
download | inline diff:
From 9d6b065a25c50211b29d5e26d5f83670200bae65 Mon Sep 17 00:00:00 2001
From: "jhonathan.cruz" <jhonathan.cruz@sankhya.com.br>
Date: Sun, 14 Dec 2025 13:13:36 -0300
Subject: [PATCH] docs: add logical replication examples and clarify autovacuum
and XID freezing behavior
---
doc/src/sgml/logical-replication.sgml | 71 +++++++++++++++++++++++++++
doc/src/sgml/maintenance.sgml | 56 +++++++++++++++++++++
doc/src/sgml/mvcc.sgml | 49 ++++++++++++++++++
3 files changed, 176 insertions(+)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index aa013f348d4..87ac151ec88 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -3543,4 +3543,75 @@ CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICAT
incremental changes to those tables.
</para>
</sect1>
+ <section id="logical-replication-practical-examples">
+ <title>Practical Usage Examples</title>
+
+ <para>
+ While logical replication is conceptually simple, many users struggle with
+ initial configuration because real-world examples are spread across
+ different sections. This part consolidates common usage patterns with
+ complete and reproducible examples.
+ </para>
+
+ <sect2 id="logical-replication-single-table-example">
+ <title>Single-Table Replication Example</title>
+
+ <para>
+ The following example demonstrates a minimal setup where a single table
+ is replicated from publisher to subscriber. This is a common use case for
+ operational reporting and incremental data ingestion by downstream
+ systems.
+ </para>
+
+<programlisting>
+-- On the publisher:
+CREATE TABLE public.sales_events (
+ id bigserial PRIMARY KEY,
+ event_time timestamptz NOT NULL,
+ payload jsonb NOT NULL
+);
+
+ALTER TABLE public.sales_events REPLICA IDENTITY FULL;
+
+CREATE PUBLICATION sales_pub FOR TABLE public.sales_events;
+
+-- On the subscriber:
+CREATE SUBSCRIPTION sales_sub
+ CONNECTION 'host=publisher port=5432 dbname=app user=replicator'
+ PUBLICATION sales_pub;
+</programlisting>
+
+ <para>
+ After the subscription is created, PostgreSQL performs an initial table
+ copy. Users often assume this happens in the background, but it actually
+ uses a separate apply worker. Monitoring can be done via
+ <link linkend="view-pg-stat- subscription">
+ pg_stat_subscription
+ </link>.
+ </para>
+ </sect2>
+
+ <sect2 id="logical-replication-initial-sync-behavior">
+ <title>Clarification of Initial Synchronization Behavior</title>
+ <para>
+ Initial synchronization is commonly misunderstood. Each table is copied in
+ a dedicated worker process, which can lead to higher-than-expected
+ resource usage on large schemas. Administrators may prefer to temporarily
+ disable table copy and load the initial data manually. This can be done
+ using <literal>copy_data = false</literal> during subscription creation.
+ </para>
+ </sect2>
+
+ <sect2 id="logical-replication-conflict-resolution">
+ <title>Conflict Resolution in Apply Workers</title>
+ <para>
+ Conflicts occur when the subscriber contains preexisting data or when
+ concurrent writes race with replicated changes. PostgreSQL always
+ prioritizes local transactions, causing replicated transactions to abort
+ and retry. This behavior is rarely documented clearly, yet it is
+ essential for users handling high write volumes.
+ </para>
+ </sect2>
+
+</section>
</chapter>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 08e6489afb8..7a0a83a0154 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -1232,4 +1232,60 @@ pg_ctl start | rotatelogs /var/log/pgsql_log 86400
files, as well as detection of many other extraordinary conditions.
</para>
</sect1>
+ <section id="autovacuum-advanced-behavior">
+ <title>Understanding Autovacuum Behavior in Depth</title>
+
+ <para>
+ The autovacuum launcher dynamically decides when to create worker processes
+ based on table activity. Although this mechanism operates automatically, it
+ is sensitive to workload patterns, visibility map state, freeze thresholds,
+ and cost-based throttling. This section clarifies these behaviors to help
+ administrators diagnose performance regressions and long-running
+ autovacuum cycles.
+ </para>
+
+ <sect2 id="autovacuum-cost-based-throttling">
+ <title>Cost-Based Throttling</title>
+
+ <para>
+ Autovacuum workers obey cost-based throttling rules that were originally
+ designed for manual VACUUM operations. When the accumulated cost exceeds
+ <literal>autovacuum_vacuum_cost_limit</literal>, the worker sleeps for the
+ duration defined by <literal>autovacuum_vacuum_cost_delay</literal>. In
+ environments with high write throughput, throttling can cause autovacuum
+ to take significantly longer than expected.
+ </para>
+
+ <para>
+ A common misconception is that increasing the number of autovacuum
+ workers solves slow autovacuum cycles. In practice, cost-based throttling
+ often becomes the bottleneck. In such cases, adjusting cost_limit upward,
+ or disabling throttling entirely for specific relations, may be more
+ effective.
+ </para>
+ </sect2>
+
+ <sect2 id="autovacuum-long-running">
+ <title>Diagnosing Long-Running Autovacuum Operations</title>
+
+ <para>
+ Administrators frequently observe autovacuum workers running for hours on
+ tables with large dead tuples or bloated indexes. The primary causes are:
+ </para>
+
+ <itemizedlist>
+ <listitem><para>Heavy write activity preventing visibility map advancement.</para></listitem>
+ <listitem><para>Cost-based throttling limiting processing rate.</para></listitem>
+ <listitem><para>Background worker slots exhausted by other processes.</para></listitem>
+ <listitem><para>Insufficient I/O bandwidth for vacuuming operations.</para></listitem>
+ </itemizedlist>
+
+ <para>
+ Monitoring can be performed using
+ <xref linkend="view-pg-stat-progress-vacuum"/>. If index scans dominate
+ runtime, administrators should consider index deduplication, partitioning,
+ or targeted <literal>VACUUM (INDEX_CLEANUP OFF)</literal>.
+ </para>
+ </sect2>
+</section>
</chapter>
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index 049ee75a4ba..317cdd723b8 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -1944,4 +1944,53 @@ SELECT pg_advisory_lock(q.id) FROM
indexes should be used instead.
</para>
</sect1>
+ <section id="transaction-freeze-deep-dive">
+ <title>Understanding Transaction Freezing and Anti-Wraparound Protection</title>
+
+ <para>
+ PostgreSQL must frequently freeze old tuples to prevent transaction ID
+ wraparound, which would otherwise cause catalog corruption and data loss.
+ Although this mechanism is documented, many details are not fully clear to
+ new users. This section explains the practical impact of freezing,
+ thresholds, and autovacuum interaction.
+ </para>
+
+ <sect2 id="freeze-thresholds">
+ <title>Freeze Thresholds and Vacuum Triggering</title>
+
+ <para>
+ Autovacuum triggers a freeze operation when a relation reaches the
+ threshold defined by <literal>autovacuum_freeze_max_age</literal>. In
+ addition, each table carries its own age counter at the page level, based
+ on the oldest XID present. When this counter crosses
+ <literal>vacuum_freeze_min_age</literal>, tuples are marked as frozen.
+ </para>
+
+ <para>
+ A common misunderstanding is that freezing is expensive. In reality,
+ freezing is often cheaper than regular vacuuming, because frozen tuples
+ no longer require XID visibility checks. High freeze ages only become a
+ problem when autovacuum is unable to keep up, typically due to sustained
+ heavy writes or throttling constraints.
+ </para>
+ </sect2>
+
+ <sect2 id="visibility-map-freeze">
+ <title>Visibility Map and Freeze Operations</title>
+
+ <para>
+ Freezing interacts closely with the visibility map. When all tuples in a
+ page are frozen or visible to all transactions, the page can be marked as
+ all-visible. This reduces scanning overhead for future vacuum operations.
+ However, frequent updates prevent visibility map bits from being set,
+ increasing vacuum and freeze pressure.
+ </para>
+
+ <para>
+ Administrators should monitor visibility map saturation via
+ <literal>pgstattuple</literal> or external tooling to detect tables that
+ are chronically difficult to vacuum.
+ </para>
+ </sect2>
+</section>
</chapter>
--
2.45.1.windows.1
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [PATCH] Docs: logical replication examples and autovacuum/XID freeze clarifications
@ 2025-12-15 06:35 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Jhonathan da Rocha da Cruz <j.cruz@acad.ifma.edu.br>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2025-12-15 06:35 UTC (permalink / raw)
To: Jhonathan da Rocha da Cruz <j.cruz@acad.ifma.edu.br>; pgsql-docs
On Sun, 2025-12-14 at 16:05 -0300, Jhonathan da Rocha da Cruz wrote:
> This patch improves the PostgreSQL documentation by adding practical examples
> for logical replication and clarifying autovacuum behavior and transaction ID
> freezing (anti-wraparound).
>
> The changes aim to complement the existing documentation without removing or
> altering current content.
>
> Files changed:
> - doc/src/sgml/logical-replication.sgml
> - doc/src/sgml/maintenance.sgml
> - doc/src/sgml/mvcc.sgml
>
> Tests performed:
> - make html
>
> Patch attached.
Thank you.
I am wondering if the documentation is the right place for that.
Perhaps there should instead be a tuorial about logical replication in chapter
I. 3. "Advanced Features".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-12-15 06:35 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-14 19:05 [PATCH] Docs: logical replication examples and autovacuum/XID freeze clarifications Jhonathan da Rocha da Cruz <j.cruz@acad.ifma.edu.br>
2025-12-15 06:35 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox