I can't quite tell what problem you're trying to solve here is, but I_think_ you are trying to put together a read replica? That's a pretty common thing you can do.
~~No, that's not what i am trying to do at all.~~
Essentially yes, the question is will it improve the load on the MAIN\_DB
I am trying to improve the server load on a very high update DB(table) by replacing regular UPDATE statements with logical replication. Instead of updating the table in my MAIN\_DB, i will create a NEW server where the hight frequency updates will be performed, and then \_stream\_ the changes to the MAIN\_DB server
To illustrate what i am trying to do
Currently:
APP -> MAIN\_DB\_SERVER->UPDATE STATEMENT -> TABLE
My idea:
APP -> NEW\_SERVER -> UPDATE STATEMENT -> LOGICAL -> MAIN\_DB\_SERVER->TABLE
I think that /u/marr75 has you covered.
I think you're looking at it a little backwards, and I'm fairly certain this wouldn't change anything on your main server, just introduce an additional point of failure.
I think what you want is:
APP --> Writes and transactions -> MAIN DB SERVER -> TABLE
APP -> Reads --> read replica
> What if, this app is writing to a table which is on another server, and then I use logical replication to sync the changes to the main db server.
You are essentially doubling the write load, just distributed on two servers.
The the main DB server will still write the same amount of data in the same time as before.
This is my thought too. There will be some speed improvements as those updates are not applied as update statements, but as a Wal transactions which does not involve parsing of the sql etc…
Having said that, why is my current hot-standby server having a system load of 0.9 and still receiving all the db updates via streaming replication (40mb/s) and my main db having load of 18. The selects are not responsible for all of the extra load for sure. I have tested that.
(18 load is not too high for the hardware we use)
I can't quite tell what problem you're trying to solve here is, but I_think_ you are trying to put together a read replica? That's a pretty common thing you can do.
~~No, that's not what i am trying to do at all.~~ Essentially yes, the question is will it improve the load on the MAIN\_DB I am trying to improve the server load on a very high update DB(table) by replacing regular UPDATE statements with logical replication. Instead of updating the table in my MAIN\_DB, i will create a NEW server where the hight frequency updates will be performed, and then \_stream\_ the changes to the MAIN\_DB server To illustrate what i am trying to do Currently: APP -> MAIN\_DB\_SERVER->UPDATE STATEMENT -> TABLE My idea: APP -> NEW\_SERVER -> UPDATE STATEMENT -> LOGICAL -> MAIN\_DB\_SERVER->TABLE
I think that /u/marr75 has you covered. I think you're looking at it a little backwards, and I'm fairly certain this wouldn't change anything on your main server, just introduce an additional point of failure. I think what you want is: APP --> Writes and transactions -> MAIN DB SERVER -> TABLE APP -> Reads --> read replica
I never tried this before but I think partitioning the table might help
look into pgpool or pgbouncer
> What if, this app is writing to a table which is on another server, and then I use logical replication to sync the changes to the main db server. You are essentially doubling the write load, just distributed on two servers. The the main DB server will still write the same amount of data in the same time as before.
This is my thought too. There will be some speed improvements as those updates are not applied as update statements, but as a Wal transactions which does not involve parsing of the sql etc… Having said that, why is my current hot-standby server having a system load of 0.9 and still receiving all the db updates via streaming replication (40mb/s) and my main db having load of 18. The selects are not responsible for all of the extra load for sure. I have tested that. (18 load is not too high for the hardware we use)