0

Consider the following setup of a postgres cluster:

  1. Main (write) node - setup to run all in memory using a ramdisk and seting up all tables and such in memory/disabling log writes and generaly free the db to write as fast as possible. I am well aware of the riskiness of that, but also believe/hope it will aquire speed up.
  2. A second (slave) replication node is set on another instance and writes the updates as soon as it gets them (hopefully, that can be cached so not to slow down the main drive?)
  3. If a single slave replication node cannot write all updates fast enough, use more instances while hardcoding separate tables for separate machines (as needed).

Can this kind of setup work fast and reliably?

Consider: 10 minutes of data loss once a month is acceptable.

Improve this question

2 Answers 2

Reset to default
1

Can this kind of setup work fast and reliably?

Not a chance. To wit:

  • Postgres replication uses WAL. By "disabling log writes" you disable replication.

  • If you are unable to tune your primary server to accept the necessary writes, you will be similarly unable to tune your replicas to accept those same writes, so your replication lag will grow until it becomes unsustainable.

  • If your primary server fails, you lose 100% of your data (which is stored in memory and is unrecoverable), not just 10 last minutes.

Improve this answer
3
  • I understand disabling the log won't be feasible. Not an expert DBA myself, but I suspected that. To the second problem of the slaves not being able to write fast enough - that is a problem... question is, if writing to disk is not the problem but the transactions are the problem and the writing to disk is not as fast. shouldn't it provide some speed improvement? or is it neglible? with regard to the data loss - if the database is replicated it can be restored, don't it?
    – rub
    Apr 17 at 18:49
  • Perhaps, instead of asking all these hypothetical questions, you could formulate the actual goal you're trying to achieve and demonstrate how far your current performance numbers are from that goal.
    – mustaccio
    Apr 17 at 19:38
  • I asked hypothetically because I am developing an app relying on postgresql, which is very useful as a db with json/array columns, but might/probably will have performance issues which I want to try and tackle ahead of time. especially if my hypothetical plans are in the twilight zone ...
    – rub
    Apr 17 at 20:06
1

Apart from the fact that you cannot disable WAL if you want replication, as mentioned in the other answer, your setup would theoretically work. But I don't think it is a great idea because

  • If you provide enough RAM to cache the whole database, you won't have any reading I/O, so the I/O load on the primary won't exceed the load on the standby servers anyway. So you won't win anything.

  • If your primary fails, and the standby cannot take over because it cannot handle the I/O load, what's the use of your setup?

Improve this answer
2
  • With regard to the primary/slave - I just want it to make the slave a writer to disk, as a backup in case something disastrous happens to the master. Didn't think much beyond that. Shouldn't it be a form of backup to the master if it DOES backs up? As for the slave being able to write everything... yes. this is too a problem.
    – rub
    Apr 18 at 8:54
  • A standby is not sufficient protection against problems, it cannot substitute a backup. If you drop the wrong table, it will be gone at the standby too.
    – Laurenz Albe
    Apr 18 at 10:07

Your Answer

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

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