Styjun

What prevents a US state from colonizing a smaller state?

Why doesn't SpaceX land boosters in Africa?

How to draw a diagram like this with tikz?

What is the lowest possible AC?

I just started; should I accept a farewell lunch for a coworker I don't know?

Dynamic Sql Query - how to add an int to the code?

A quine of sorts

Journal standards vs. personal standards

How to track mail undetectably?

Why would Dementors torture a Death Eater if they are loyal to Voldemort?

What does 5d4 x 10 gp mean?

What was the first science fiction or fantasy multiple choice book?

Two palindromes are not enough

What are the children of two Muggle-borns called?

Is it OK to throw pebbles and stones in streams, waterfalls, ponds, etc.?

Did the Russian Empire have a claim to Sweden? Was there ever a time where they could have pursued it?

Would skyscrapers tip over if people fell sideways?

Tricolour nonogram

How would one prevent political gerrymandering?

Why are symbols not written in words?

What was the point of separating stdout and stderr?

English idiomatic equivalents of 能骗就骗 (if you can cheat, then cheat)

Why will we fail creating a self sustaining off world colony?

Installed software from source, how to say yum not to install it from package?

How does write ahead logging improve IO performance in Postgres?

How to reset postgres' primary key sequence when it falls out of sync?How to log PostgreSQL queries?InnoDB Bottleneck: Relaxing ACID to Improve PerformanceHow does PostgreSQL perform writes so much faster than SQLite?On Postgres databases, does relationship constraints degrade performance?Improve performance of first queryDisable WAL in influxdb v0.13 or force flushunderstanding unlogged tables, commits and checkpoints postgresWould it possible that postgres Write-Ahead Log DOUBLE RE-APPLY?Can PostgreSQL be configured so that occasional mass updates can run super-fast?

1

I've been reading through the WAL chapter of the Postgres manual and was confused by a portion of the chapter:

Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction.

How is it that continuous writing to WAL more performant than simply writing to the table/index data itself?

As I see it (forgetting for now the resiliency benefits of WAL) postgres need to complete two disk operations; first pg needs to commit to WAL on disk and then you'll still need to change the table data to be consistent with WAL. I'm sure there's a fundamental aspect of this I've misunderstood but it seems like adding an additional step between a client transaction and the and the final state of the table data couldn't actually increase overall performance. Thanks in advance!

postgresql database-performance wal

share|improve this question

edited Mar 25 at 16:24

Laurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

asked Mar 25 at 15:40

RangerRangerRangerRanger

1,31222 gold badges99 silver badges2626 bronze badges

add a comment | 

1

I've been reading through the WAL chapter of the Postgres manual and was confused by a portion of the chapter:

Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction.

How is it that continuous writing to WAL more performant than simply writing to the table/index data itself?

As I see it (forgetting for now the resiliency benefits of WAL) postgres need to complete two disk operations; first pg needs to commit to WAL on disk and then you'll still need to change the table data to be consistent with WAL. I'm sure there's a fundamental aspect of this I've misunderstood but it seems like adding an additional step between a client transaction and the and the final state of the table data couldn't actually increase overall performance. Thanks in advance!

postgresql database-performance wal

share|improve this question

edited Mar 25 at 16:24

Laurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

asked Mar 25 at 15:40

RangerRangerRangerRanger

1,31222 gold badges99 silver badges2626 bronze badges

add a comment | 

I've been reading through the WAL chapter of the Postgres manual and was confused by a portion of the chapter:

Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction.

How is it that continuous writing to WAL more performant than simply writing to the table/index data itself?

As I see it (forgetting for now the resiliency benefits of WAL) postgres need to complete two disk operations; first pg needs to commit to WAL on disk and then you'll still need to change the table data to be consistent with WAL. I'm sure there's a fundamental aspect of this I've misunderstood but it seems like adding an additional step between a client transaction and the and the final state of the table data couldn't actually increase overall performance. Thanks in advance!

postgresql database-performance wal

share|improve this question

edited Mar 25 at 16:24

Laurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

asked Mar 25 at 15:40

RangerRangerRangerRanger

1,31222 gold badges99 silver badges2626 bronze badges

share|improve this question

edited Mar 25 at 16:24

Laurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

asked Mar 25 at 15:40

RangerRangerRangerRanger

1,31222 gold badges99 silver badges2626 bronze badges

share|improve this question

edited Mar 25 at 16:24

Laurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

asked Mar 25 at 15:40

RangerRangerRangerRanger

1,31222 gold badges99 silver badges2626 bronze badges

edited Mar 25 at 16:24

Laurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

edited Mar 25 at 16:24

Laurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

asked Mar 25 at 15:40

RangerRangerRangerRanger

1,31222 gold badges99 silver badges2626 bronze badges

asked Mar 25 at 15:40

RangerRangerRangerRanger

1,31222 gold badges99 silver badges2626 bronze badges

1 Answer
1

active

oldest

votes

1

You are fundamentally right: the extra writes to the transaction log will per se not reduce the I/O load.

But a transaction will normally touch several files (tables, indexes etc.). If you force all these files out to storage (“sync”), you will incur more I/O load than if you sync just a single file.

Of course all these files will have to be written and sync'ed eventually (during a checkpoint), but often the same data are modified several times between two checkpoints, and then the corresponding files will have to be sync'ed only once.

share|improve this answer

answered Mar 25 at 16:23

Laurenz AlbeLaurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  but often the same data are modified several times between two checkpoints, and then the corresponding files will have to be sync'ed only once. Ah, excellent. I think that's where my misunderstanding came from. By waiting for a checkpoint you can make multiple changes to the same file rather than continuously retrieving the file and writing to it. Thank you for the clear and concise answer.
  
  – RangerRanger
  Mar 26 at 0:57
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55341457%2fhow-does-write-ahead-logging-improve-io-performance-in-postgres%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

You are fundamentally right: the extra writes to the transaction log will per se not reduce the I/O load.

But a transaction will normally touch several files (tables, indexes etc.). If you force all these files out to storage (“sync”), you will incur more I/O load than if you sync just a single file.

Of course all these files will have to be written and sync'ed eventually (during a checkpoint), but often the same data are modified several times between two checkpoints, and then the corresponding files will have to be sync'ed only once.

share|improve this answer

answered Mar 25 at 16:23

Laurenz AlbeLaurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  but often the same data are modified several times between two checkpoints, and then the corresponding files will have to be sync'ed only once. Ah, excellent. I think that's where my misunderstanding came from. By waiting for a checkpoint you can make multiple changes to the same file rather than continuously retrieving the file and writing to it. Thank you for the clear and concise answer.
  
  – RangerRanger
  Mar 26 at 0:57
  
  

  
  
  
  

add a comment | 

1

You are fundamentally right: the extra writes to the transaction log will per se not reduce the I/O load.

But a transaction will normally touch several files (tables, indexes etc.). If you force all these files out to storage (“sync”), you will incur more I/O load than if you sync just a single file.

Of course all these files will have to be written and sync'ed eventually (during a checkpoint), but often the same data are modified several times between two checkpoints, and then the corresponding files will have to be sync'ed only once.

share|improve this answer

answered Mar 25 at 16:23

Laurenz AlbeLaurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  but often the same data are modified several times between two checkpoints, and then the corresponding files will have to be sync'ed only once. Ah, excellent. I think that's where my misunderstanding came from. By waiting for a checkpoint you can make multiple changes to the same file rather than continuously retrieving the file and writing to it. Thank you for the clear and concise answer.
  
  – RangerRanger
  Mar 26 at 0:57
  
  

  
  
  
  

add a comment | 

You are fundamentally right: the extra writes to the transaction log will per se not reduce the I/O load.

But a transaction will normally touch several files (tables, indexes etc.). If you force all these files out to storage (“sync”), you will incur more I/O load than if you sync just a single file.

Of course all these files will have to be written and sync'ed eventually (during a checkpoint), but often the same data are modified several times between two checkpoints, and then the corresponding files will have to be sync'ed only once.

share|improve this answer

answered Mar 25 at 16:23

Laurenz AlbeLaurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

share|improve this answer

answered Mar 25 at 16:23

Laurenz AlbeLaurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

answered Mar 25 at 16:23

Laurenz AlbeLaurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges

answered Mar 25 at 16:23

Laurenz AlbeLaurenz Albe

58.4k1111 gold badges4141 silver badges6262 bronze badges