Shulou(Shulou.com)05/31 Report--

This article mainly explains "analyzing the new WAL options in PostgreSQL DBA". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "analyze the new WAL options in PostgreSQL DBA".

In PG 13, the explain command adds the WAL option to view the size of the WAL log generated during the execution of the command. For write-sensitive applications, you can use this command to observe the statistics of the WAL log.

New WAL option

[local:/data/run/pg13]: 5130 pg13@masterdb=#\ help explainCommand: EXPLAINDescription: show the execution plan of a statementSyntax:EXPLAIN [(option [ )] statementEXPLAIN [ANALYZE] [VERBOSE] statementwhere option can be one of: ANALYZE [boolean] VERBOSE [boolean] COSTS [boolean] SETTINGS [boolean] BUFFERS [boolean] WAL [boolean] TIMING [boolean] SUMMARY [boolean] FORMAT {TEXT | XML | JSON | YAML} URL: https://www.postgresql.org/docs/devel/sql-explain.html[local:/data/run/pg13]:5130 pg13@masterdb=#

Trial experience

Running SQL,WAL log after executing checkpoint will write full page

[local:/data/run/pg13]: 5130 pg13@masterdb=# checkpoint;CHECKPOINT [local:/data/run/pg13]: 5130 pg13@masterdb=# explain (analyze,wal) update tmp set id = 1 QUERY PLAN -- Update on tmp (cost=0.00..23275.00 rows=1000000 width=10) (actual time=1636.839..1636.839 rows=0 loops=1) WAL: records=1999840 full page writes=8851 bytes=172345779-> Seq Scan on tmp (cost=0.00..23275.00 rows=1000000 width=10) (actual time=4.686..100.211 rows=1000000 loops=1) Planning Time: 0. 149 ms Execution Time: 1636.923 ms (5 rows)

Execute SQL again, no full page is written, and the log size is reduced accordingly

[local:/data/run/pg13]: 5130 pg13@masterdb=# explain (analyze,wal) update tmp set id = 1 QUERY PLAN Update on tmp (cost=0.00..23275.00 rows=1000000 width=10) (actual time=3661.261..3661.261 rows=0 loops=1) WAL: records=1999719 bytes=131982016-> Seq Scan on tmp (cost=0.00..23275.00 rows=1000000 width=10) (actual time=3.920..98.921 rows=1000000 loops=1) Planning Time: 0.088 ms Execution Time: 3661.310 ms (5 rows)

Save 23% of space, 38KB size

[local:/data/run/pg13]: 5130 pg13@masterdb=# select (172345779-131982016) / 172345779 column?-0.23420221391090756 (1 row) [local:/data/run/pg13]: 5130 pg13@masterdb=# select (172345779-131982016) / 1024and1024 ? column?-38 (1 row) Thank you for your reading. The above is the content of "analyzing the new WAL options in PostgreSQL DBA". After the study of this article, I believe you have a deeper understanding of the problem of analyzing the new WAL options in PostgreSQL DBA, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.