public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v4] Do not use RelationNeedsWAL to identify relation persistence 3+ messages / 3 participants [nested] [flat]
* [PATCH v4] Do not use RelationNeedsWAL to identify relation persistence @ 2021-01-18 05:47 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Kyotaro Horiguchi @ 2021-01-18 05:47 UTC (permalink / raw) RelationNeedsWAL() may return false for a permanent relation when wal_level=minimal and the relation is created or truncated in the current transaction. Directly examine relpersistence instead of using the function to know relation persistence. --- src/backend/catalog/pg_publication.c | 2 +- src/backend/optimizer/util/plancat.c | 3 ++- src/include/storage/bufmgr.h | 8 +++++++- src/include/utils/rel.h | 2 +- src/include/utils/snapmgr.h | 2 +- 5 files changed, 12 insertions(+), 5 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 5f8e1c64e1..84d2efcfd2 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -67,7 +67,7 @@ check_publication_add_relation(Relation targetrel) errdetail("System tables cannot be added to publications."))); /* UNLOGGED and TEMP relations cannot be part of publication. */ - if (!RelationNeedsWAL(targetrel)) + if (targetrel->rd_rel->relpersistence != RELPERSISTENCE_PERMANENT) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("table \"%s\" cannot be replicated", diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index da322b453e..177e6e336a 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -126,7 +126,8 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, relation = table_open(relationObjectId, NoLock); /* Temporary and unlogged relations are inaccessible during recovery. */ - if (!RelationNeedsWAL(relation) && RecoveryInProgress()) + if (relation->rd_rel->relpersistence != RELPERSISTENCE_PERMANENT && + RecoveryInProgress()) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot access temporary or unlogged relations during recovery"))); diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index fb00fda6a7..e641174798 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -14,6 +14,7 @@ #ifndef BUFMGR_H #define BUFMGR_H +#include "access/xlog.h" #include "storage/block.h" #include "storage/buf.h" #include "storage/bufpage.h" @@ -278,12 +279,17 @@ TestForOldSnapshot(Snapshot snapshot, Relation relation, Page page) { Assert(relation != NULL); + /* + * While wal_level=minimal, early pruning can happen without updating page + * LSN. Don't take the fast-return path while wal_level=minimal so that we + * don't miss early pruning. + */ if (old_snapshot_threshold >= 0 && (snapshot) != NULL && ((snapshot)->snapshot_type == SNAPSHOT_MVCC || (snapshot)->snapshot_type == SNAPSHOT_TOAST) && !XLogRecPtrIsInvalid((snapshot)->lsn) - && PageGetLSN(page) > (snapshot)->lsn) + && (!XLogIsNeeded() || PageGetLSN(page) > (snapshot)->lsn)) TestForOldSnapshot_impl(snapshot, relation); } diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 10b63982c0..f58d65cf28 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -554,7 +554,7 @@ typedef struct ViewOptions /* * RelationNeedsWAL - * True if relation needs WAL. + * True if relation needs WAL at the time. * * Returns false if wal_level = minimal and this relation is created or * truncated in the current transaction. See "Skipping WAL for New diff --git a/src/include/utils/snapmgr.h b/src/include/utils/snapmgr.h index 579be352c5..c21ee3c289 100644 --- a/src/include/utils/snapmgr.h +++ b/src/include/utils/snapmgr.h @@ -37,7 +37,7 @@ */ #define RelationAllowsEarlyPruning(rel) \ ( \ - RelationNeedsWAL(rel) \ + (rel)->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT \ && !IsCatalogRelation(rel) \ && !RelationIsAccessibleInLogicalDecoding(rel) \ ) -- 2.27.0 ----Next_Part(Wed_Jan_20_17_34_44_2021_328)---- ^ permalink raw reply [nested|flat] 3+ messages in thread
* Parallelizing startup with many databases @ 2026-01-02 07:55 Babak Ghadiri <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Babak Ghadiri @ 2026-01-02 07:55 UTC (permalink / raw) To: [email protected] Hello, I hope you are doing well. In PostgreSQL 16, startup appears to initialize databases sequentially and primarily uses a single CPU core. In clusters with a very large number of databases (around 5,000 in our case), this results in noticeably long startup times after restarts or crash recovery. I would like to ask: - Is the largely single-threaded startup behavior a fundamental architectural constraint (e.g. catalog dependencies, locking, recovery ordering), or mainly an unimplemented optimization? - Are there any existing discussions, patches, versions (18+) to parallelize parts of startup or otherwise improve startup scalability with many databases? - Are there any PostgreSQL configuration settings known to dramatically reduce startup time, or is startup performance mostly fixed by architecture in this scenario? I understand that having many databases in a single cluster is not the most common or recommended multi-tenant model, but this is an existing system and I’m trying to better understand the current limits and future direction. Thank you for your time and insights. Best regards. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Parallelizing startup with many databases @ 2026-01-02 23:38 Andreas Karlsson <[email protected]> parent: Babak Ghadiri <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Andreas Karlsson @ 2026-01-02 23:38 UTC (permalink / raw) To: Babak Ghadiri <[email protected]>; [email protected] On 1/2/26 8:55 AM, Babak Ghadiri wrote: > In PostgreSQL 16, startup appears to initialize databases sequentially and > primarily uses a single CPU core. In clusters with a very large number of > databases (around 5,000 in our case), this results in noticeably long > startup times after restarts or crash recovery. Have you measured what is actually causing the slow startup? Without knowing what is actually slow it is hard to say if threading would even help. How slow are we talking about and have you managed to create a minimal case for reproducing the issue? > - Is the largely single-threaded startup behavior a fundamental > architectural > constraint (e.g. catalog dependencies, locking, recovery ordering), > or mainly > an unimplemented optimization? PostgreSQL does not support threading, it uses a multi-process model to implement for example parallel queries. And there is no way threading would be introduced just to improved startup performance. > - Are there any existing discussions, patches, versions (18+) to > parallelize parts of startup or otherwise improve startup scalability > with many databases? Not as far as I am aware but you can search our archives. > - Are there any PostgreSQL configuration settings known to dramatically > reduce startup time, or is startup performance mostly fixed by > architecture in this scenario? I would first start trying to figure out why startup is slow before doing anything else. Andreas ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-01-02 23:38 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-01-18 05:47 [PATCH v4] Do not use RelationNeedsWAL to identify relation persistence Kyotaro Horiguchi <[email protected]> 2026-01-02 07:55 Parallelizing startup with many databases Babak Ghadiri <[email protected]> 2026-01-02 23:38 ` Re: Parallelizing startup with many databases Andreas Karlsson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox