public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v16 3/8] Row pattern recognition patch (rewriter). 6+ messages / 4 participants [nested] [flat]
* [PATCH v16 3/8] Row pattern recognition patch (rewriter). @ 2024-04-12 06:49 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Tatsuo Ishii @ 2024-04-12 06:49 UTC (permalink / raw) --- src/backend/utils/adt/ruleutils.c | 103 ++++++++++++++++++++++++++++++ 1 file changed, 103 insertions(+) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 24e3514b00..c204565cc1 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -426,6 +426,10 @@ static void get_rule_groupingset(GroupingSet *gset, List *targetlist, bool omit_parens, deparse_context *context); static void get_rule_orderby(List *orderList, List *targetList, bool force_colno, deparse_context *context); +static void get_rule_pattern(List *patternVariable, List *patternRegexp, + bool force_colno, deparse_context *context); +static void get_rule_define(List *defineClause, List *patternVariables, + bool force_colno, deparse_context *context); static void get_rule_windowclause(Query *query, deparse_context *context); static void get_rule_windowspec(WindowClause *wc, List *targetList, deparse_context *context); @@ -6487,6 +6491,67 @@ get_rule_orderby(List *orderList, List *targetList, } } +/* + * Display a PATTERN clause. + */ +static void +get_rule_pattern(List *patternVariable, List *patternRegexp, + bool force_colno, deparse_context *context) +{ + StringInfo buf = context->buf; + const char *sep; + ListCell *lc_var, + *lc_reg = list_head(patternRegexp); + + sep = ""; + appendStringInfoChar(buf, '('); + foreach(lc_var, patternVariable) + { + char *variable = strVal((String *) lfirst(lc_var)); + char *regexp = NULL; + + if (lc_reg != NULL) + { + regexp = strVal((String *) lfirst(lc_reg)); + + lc_reg = lnext(patternRegexp, lc_reg); + } + + appendStringInfo(buf, "%s%s", sep, variable); + if (regexp !=NULL) + appendStringInfoString(buf, regexp); + + sep = " "; + } + appendStringInfoChar(buf, ')'); +} + +/* + * Display a DEFINE clause. + */ +static void +get_rule_define(List *defineClause, List *patternVariables, + bool force_colno, deparse_context *context) +{ + StringInfo buf = context->buf; + const char *sep; + ListCell *lc_var, + *lc_def; + + sep = " "; + Assert(list_length(patternVariables) == list_length(defineClause)); + + forboth(lc_var, patternVariables, lc_def, defineClause) + { + char *varName = strVal(lfirst(lc_var)); + TargetEntry *te = (TargetEntry *) lfirst(lc_def); + + appendStringInfo(buf, "%s%s AS ", sep, varName); + get_rule_expr((Node *) te->expr, context, false); + sep = ",\n "; + } +} + /* * Display a WINDOW clause. * @@ -6624,6 +6689,44 @@ get_rule_windowspec(WindowClause *wc, List *targetList, appendStringInfoString(buf, "EXCLUDE GROUP "); else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES) appendStringInfoString(buf, "EXCLUDE TIES "); + /* RPR */ + if (wc->rpSkipTo == ST_NEXT_ROW) + appendStringInfoString(buf, + "\n AFTER MATCH SKIP TO NEXT ROW "); + else if (wc->rpSkipTo == ST_PAST_LAST_ROW) + appendStringInfoString(buf, + "\n AFTER MATCH SKIP PAST LAST ROW "); + else if (wc->rpSkipTo == ST_FIRST_VARIABLE) + appendStringInfo(buf, + "\n AFTER MATCH SKIP TO FIRST %s ", + wc->rpSkipVariable); + else if (wc->rpSkipTo == ST_LAST_VARIABLE) + appendStringInfo(buf, + "\n AFTER MATCH SKIP TO LAST %s ", + wc->rpSkipVariable); + else if (wc->rpSkipTo == ST_VARIABLE) + appendStringInfo(buf, + "\n AFTER MATCH SKIP TO %s ", + wc->rpSkipVariable); + + if (wc->initial) + appendStringInfoString(buf, "\n INITIAL"); + + if (wc->patternVariable) + { + appendStringInfoString(buf, "\n PATTERN "); + get_rule_pattern(wc->patternVariable, wc->patternRegexp, + false, context); + } + + if (wc->defineClause) + { + appendStringInfoString(buf, "\n DEFINE\n"); + get_rule_define(wc->defineClause, wc->patternVariable, + false, context); + appendStringInfoChar(buf, ' '); + } + /* we will now have a trailing space; remove it */ buf->len--; } -- 2.25.1 ----Next_Part(Fri_Apr_12_16_09_08_2024_262)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v16-0004-Row-pattern-recognition-patch-planner.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write @ 2026-02-27 11:43 陈宗志 <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: 陈宗志 @ 2026-02-27 11:43 UTC (permalink / raw) To: wenhui qiu <[email protected]>; +Cc: Robert Treat <[email protected]>; Jakub Wartak <[email protected]>; pgsql-hackers Hi wenhui, Here are the latest benchmark results for the Double Write Buffer (DWB) proposal. In this round of testing, I have included the two-phase checkpoint batch fsync optimization and evaluated the impact of wal_compression (lz4) on both FPW and DWB. Test Environment: - PostgreSQL: 19devel (with DWB patch applied) - Hardware: Linux 5.10, x86_64 - Configuration: * shared_buffers = 1GB * max_wal_size = 32MB (to stress checkpoint frequency) * wal_compression = lz4 * double_write_buffer_size = 128MB (for DWB mode) - Workload: sysbench 1.1.0, 10 tables x 1,000,000 rows (~2.3GB dataset) - Method: 16 threads, 60 seconds per run, each mode tested independently (only one instance running at a time to eliminate I/O contention). Three modes compared: - FPW: io_torn_pages_protection = full_pages (current default) - DWB: io_torn_pages_protection = double_writes - OFF: io_torn_pages_protection = off (no protection, baseline) Results with wal_compression = lz4 ---------------------------------- 1. oltp_write_only (pure write transactions: UPDATE + DELETE + INSERT) Mode TPS vs FPW vs OFF ---- ------ ------ ------ FPW 13,772 - -64.3% DWB 20,660 +50.0% -46.5% OFF 38,588 +180.2% - 2. oltp_update_non_index (single UPDATE per transaction) Mode TPS vs FPW vs OFF ---- ------ ------ ------ FPW 59,427 - -57.5% DWB 104,328 +75.6% -25.4% OFF 139,870 +135.4% - 3. oltp_read_write (mixed: 70% reads + 30% writes) Mode TPS vs FPW vs OFF ---- ------ ------ ------ FPW 6,232 - -9.0% DWB 4,408 -29.3% -35.6% OFF 6,845 +9.8% - Results without wal_compression (for comparison) ------------------------------------------------ Workload FPW DWB DWB vs FPW -------- ------ ------ ---------- oltp_write_only 9,651 22,111 +129.1% oltp_update_non_index 48,624 98,356 +102.3% oltp_read_write 5,414 5,275 -2.6% Key Observations: 1. Write-heavy workloads: DWB outperforms FPW by +50% to +76% even with lz4 compression enabled. Without lz4, the advantage grows to +102% to +129% because uncompressed full-page images cause severe WAL bloat. 2. lz4 compression significantly helps FPW: For oltp_write_only, lz4 boosts FPW from 9,651 to 13,772 TPS (+43%), while DWB sees minimal change (22,111 -> 20,660). This is expected -- lz4 compresses the 8KB full-page images that FPW writes to WAL, but DWB doesn't generate FPIs at all, so lz4 has little effect on DWB's WAL volume. 3. Read-heavy mixed workloads: DWB shows a regression (-29%) in oltp_read_write with lz4. This workload is 70% reads with only 4 write operations per transaction, so FPW overhead is minimal. Meanwhile, DWB incurs additional I/O overhead from writing pages to the double write buffer file, which outweighs the WAL savings in this scenario. 4. Batch fsync optimization is critical for DWB: The two-phase checkpoint approach (batch all DWB writes in Phase 1 -> single fsync -> data file writes in Phase 2) reduces checkpoint DWB fsyncs from millions to ~hundreds. For example, in oltp_write_only: 1,157,729 DWB page writes -> only 148 fsyncs. Summary: DWB provides substantial performance benefits for write-intensive workloads with frequent checkpoints, which is the scenario where FPW overhead is most pronounced. The advantage is most significant without WAL compression (+100~130%), and remains strong (+50~76%) even with lz4 enabled. For read-dominated mixed workloads, DWB currently shows overhead that needs further optimization (reducing non-checkpoint DWB fsync costs). Regards, Baotiao ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write @ 2026-02-27 12:38 wenhui qiu <[email protected]> parent: 陈宗志 <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: wenhui qiu @ 2026-02-27 12:38 UTC (permalink / raw) To: 陈宗志 <[email protected]>; +Cc: Robert Treat <[email protected]>; Jakub Wartak <[email protected]>; pgsql-hackers Hi This test is completely meaningless. Just as you wouldn't set innodb_redo_log_capacity=Minimum Value, innodb_max_dirty_pages_pct=Minimum Value You used an extreme example to prove the double write.Why didn't you compare using best practices? Thank On Fri, Feb 27, 2026 at 7:43 PM 陈宗志 <[email protected]> wrote: > Hi wenhui, > > Here are the latest benchmark results for the Double Write Buffer (DWB) > proposal. In this round of testing, I have included the two-phase > checkpoint batch fsync optimization and evaluated the impact of > wal_compression (lz4) on both FPW and DWB. > > Test Environment: > - PostgreSQL: 19devel (with DWB patch applied) > - Hardware: Linux 5.10, x86_64 > - Configuration: > * shared_buffers = 1GB > * max_wal_size = 32MB (to stress checkpoint frequency) > * wal_compression = lz4 > * double_write_buffer_size = 128MB (for DWB mode) > - Workload: sysbench 1.1.0, 10 tables x 1,000,000 rows (~2.3GB dataset) > - Method: 16 threads, 60 seconds per run, each mode tested > independently (only one instance running at a time to eliminate > I/O contention). > > Three modes compared: > - FPW: io_torn_pages_protection = full_pages (current default) > - DWB: io_torn_pages_protection = double_writes > - OFF: io_torn_pages_protection = off (no protection, baseline) > > Results with wal_compression = lz4 > ---------------------------------- > 1. oltp_write_only (pure write transactions: UPDATE + DELETE + INSERT) > > Mode TPS vs FPW vs OFF > ---- ------ ------ ------ > FPW 13,772 - -64.3% > DWB 20,660 +50.0% -46.5% > OFF 38,588 +180.2% - > > 2. oltp_update_non_index (single UPDATE per transaction) > > Mode TPS vs FPW vs OFF > ---- ------ ------ ------ > FPW 59,427 - -57.5% > DWB 104,328 +75.6% -25.4% > OFF 139,870 +135.4% - > > 3. oltp_read_write (mixed: 70% reads + 30% writes) > > Mode TPS vs FPW vs OFF > ---- ------ ------ ------ > FPW 6,232 - -9.0% > DWB 4,408 -29.3% -35.6% > OFF 6,845 +9.8% - > > > Results without wal_compression (for comparison) > ------------------------------------------------ > Workload FPW DWB DWB vs FPW > -------- ------ ------ ---------- > oltp_write_only 9,651 22,111 +129.1% > oltp_update_non_index 48,624 98,356 +102.3% > oltp_read_write 5,414 5,275 -2.6% > > > Key Observations: > > 1. Write-heavy workloads: DWB outperforms FPW by +50% to +76% even > with lz4 compression enabled. Without lz4, the advantage grows > to +102% to +129% because uncompressed full-page images cause > severe WAL bloat. > > 2. lz4 compression significantly helps FPW: For oltp_write_only, lz4 > boosts FPW from 9,651 to 13,772 TPS (+43%), while DWB sees minimal > change (22,111 -> 20,660). This is expected -- lz4 compresses the > 8KB full-page images that FPW writes to WAL, but DWB doesn't > generate FPIs at all, so lz4 has little effect on DWB's WAL volume. > > 3. Read-heavy mixed workloads: DWB shows a regression (-29%) in > oltp_read_write with lz4. This workload is 70% reads with only 4 > write operations per transaction, so FPW overhead is minimal. > Meanwhile, DWB incurs additional I/O overhead from writing pages > to the double write buffer file, which outweighs the WAL savings > in this scenario. > > 4. Batch fsync optimization is critical for DWB: The two-phase > checkpoint approach (batch all DWB writes in Phase 1 -> single > fsync -> data file writes in Phase 2) reduces checkpoint DWB > fsyncs from millions to ~hundreds. For example, in > oltp_write_only: 1,157,729 DWB page writes -> only 148 fsyncs. > > Summary: > > DWB provides substantial performance benefits for write-intensive > workloads with frequent checkpoints, which is the scenario where FPW > overhead is most pronounced. The advantage is most significant without > WAL compression (+100~130%), and remains strong (+50~76%) even with > lz4 enabled. For read-dominated mixed workloads, DWB currently shows > overhead that needs further optimization (reducing non-checkpoint > DWB fsync costs). > > Regards, > Baotiao > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write @ 2026-02-27 19:11 陈宗志 <[email protected]> parent: 陈宗志 <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: 陈宗志 @ 2026-02-27 19:11 UTC (permalink / raw) To: [email protected]; +Cc: Robert Treat <[email protected]>; Jakub Wartak <[email protected]>; pgsql-hackers Hi Tony, > Personally believe that the Double Write is very smart for MySQL InnoDB, > but not a good ideal for Postgres, currently, WAL is the best solution > for Postgres, > maybe the next generation log system for Postgres could use OrioleDB's > storage engine. Just to clarify from a technical perspective, both MySQL and PostgreSQL use Write-Ahead Logging (WAL) as their fundamental transaction logging mechanism, so there is no difference in that regard. The comparison here is specifically between Full-Page Writes (FPW) and the Double Write Buffer (DWB). Neither of these concepts conflicts with or replaces the core WAL design. Instead, both are simply different techniques implemented to solve the exact same issue: preventing torn pages during a crash. My proposal is aimed at discussing the performance tradeoffs and implementation details between these two specific torn-page protection mechanisms, rather than replacing WAL itself. Regards, Baotiao ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write @ 2026-02-27 19:15 陈宗志 <[email protected]> parent: wenhui qiu <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: 陈宗志 @ 2026-02-27 19:15 UTC (permalink / raw) To: wenhui qiu <[email protected]>; +Cc: Robert Treat <[email protected]>; Jakub Wartak <[email protected]>; pgsql-hackers Hi, > This test is completely meaningless. Just as you wouldn't set > innodb_redo_log_capacity=Minimum Value, > innodb_max_dirty_pages_pct=Minimum Value > You used an extreme example to prove the double write.Why didn't you > compare using best practices? I wouldn't be so quick to dismiss these results. The configuration was deliberately chosen to trigger more frequent checkpoints. As I mentioned in my initial email, more frequent checkpoints strictly bound the amount of WAL that needs to be replayed, resulting in much faster crash recovery. The entire ARIES paper heavily emphasizes optimizing crash recovery time in database design. Minimizing recovery time is a fundamental database capability, and we shouldn't rely solely on High Availability (HA) switchovers to mask or solve crash recovery problems. Actually, I have always felt that PostgreSQL's minimum limit of 30s for `checkpoint_timeout` is a bit too restrictive. Ideally, the system should allow for even higher frequency checkpoints. Setting it to a lower value, such as 10s, could achieve the exact same effect of strictly bounding recovery time. This test simulates an environment where a very aggressive RTO (Recovery Time Objective) is required, which is a highly practical scenario, not just an extreme edge case. Regards, Baotiao ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write @ 2026-02-28 00:44 Tony ZHU <[email protected]> parent: 陈宗志 <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Tony ZHU @ 2026-02-28 00:44 UTC (permalink / raw) To: 陈宗志 <[email protected]>; +Cc: Robert Treat <[email protected]>; Jakub Wartak <[email protected]>; pgsql-hackers Hi ZongZhi, Thanks for feedback. I have read prior thread on this email, and know your settings, when did the testing, checkpoint_timeout set to 30s is too small, it's not a reasonable setting , the value of checkpoint_timeout is determined by shared_buffers and the workload, in real product environment, we usually set it to 30min or longer, even 1 hours, when the setting is correct, FPW will not be a problem or issue. other issue is introduced by double write that is the recovery procedure and replications, it is not a small project. if you really focus on the write or read latency, I would like to advice you to take a look for OrioleDB storage engine, I believe that's the correct direction. it is more efficient reads and writes, resolving many known overheads and issues in PostgreSQL Regards Tony On 2026/2/28 03:11, 陈宗志 wrote: > Hi Tony, > >> Personally believe that the Double Write is very smart for MySQL InnoDB, >> but not a good ideal for Postgres, currently, WAL is the best solution >> for Postgres, >> maybe the next generation log system for Postgres could use OrioleDB's >> storage engine. > Just to clarify from a technical perspective, both MySQL and PostgreSQL > use Write-Ahead Logging (WAL) as their fundamental transaction logging > mechanism, so there is no difference in that regard. > > The comparison here is specifically between Full-Page Writes (FPW) and > the Double Write Buffer (DWB). Neither of these concepts conflicts with > or replaces the core WAL design. Instead, both are simply different > techniques implemented to solve the exact same issue: preventing torn > pages during a crash. > > My proposal is aimed at discussing the performance tradeoffs and > implementation details between these two specific torn-page protection > mechanisms, rather than replacing WAL itself. > > Regards, > Baotiao ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-02-28 00:44 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-04-12 06:49 [PATCH v16 3/8] Row pattern recognition patch (rewriter). Tatsuo Ishii <[email protected]> 2026-02-27 11:43 Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write 陈宗志 <[email protected]> 2026-02-27 12:38 ` Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write wenhui qiu <[email protected]> 2026-02-27 19:15 ` Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write 陈宗志 <[email protected]> 2026-02-27 19:11 ` Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write 陈宗志 <[email protected]> 2026-02-28 00:44 ` Re: [PROPOSAL] Doublewrite Buffer as an alternative torn page protection to Full Page Write Tony ZHU <[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