1

I am thinking of using two servers in Master/Slave replication

I want to add following condition: RAM size = dataset size * x, where x > 2.

After that, I plan to force PGSQL to keep all data in RAM cache as long as possible

Question

A write request comes. Which of the following will happen ?

  • Will it be first cached, then replied, then replicated
  • Will writing precede replication, and replication precedes caching

1 Answer 1

4

PostgreSQL doesn't have a result cache. So the only caching that happens is in shared_buffers and the kernel buffer cache - both of which must inhertently be filled in the process of generating the query results, so it must happen before (or while) results are sent.

WAL is written as a transaction executes, and WAL archives get replicated as they're filled. WAL records also get streamed over the streaming replication protocol as they're generated. So replication occurs during the execution of a query, but it's not as simple as "before" or "after" results are sent.

Results can be sent incrementally to the client. Replication can occur incrementally. Really, both happen at the same time.

The only guarantee is that a commit will not be replicated and applied to replicas before it commits on the master.

If you're using synchronous replication there is the additional guarantee that a committed transaction is replicated to all nodes before a successful commit is reported to the client.

I really think you're asking the wrong question here, though. You're making a bunch of assumptions and asking questions based on those assumptions. It would be helpful if you'd take a step back, to the why behind these questions, and explain what you are trying to achieve with this. What's your goal? What're your requirements? What outcome do you seek?

1
  • Well, they were assumptions - I wasn't knowing much on PGSQL's caches till your answer. But, such a detailed explanation.. much wow. Thanks!
    – kagali-san
    Commented Apr 4, 2014 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.