On Tue, 17 Sept 2024 at 21:24, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
Which means you need to on Flink end:
1) Use Flink async I/O .
2) Find a client that supports async or fake it by using multiple
synchronous clients.
On Postgres end there is this:
https://www.postgresql.org/docs/current/wal-async-commit.html
That will return a success signal to the client quicker if
synchronous_commit is set to off. Though the point of the Flink async
I/O is not to wait for the response before moving on, so I am not sure
how much synchronous_commit = off would help.
Got it. So it means their suggestion was to set the asynch_io at flink level but not DB level, so that the application will not wait for the commit response from the database. But in that case , won't it overload the DB with more and more requests if database will keep doing the commit ( with synchronous_commit=ON) and waiting for getting the response back from its storage for the WAL's to be flushed to the disk, while the application will not wait for its response back(for those inserts) and keep flooding the database with more and more incoming Insert requests?
Additionally as I mentioned before, we see that from "pg_stat_database" from the column "xact_commit" , it's almost matching with the sum of "tup_inserted", "tup_updated", "tup_deleted" column. And also we verified in pg_stats_statements the "calls" column is same as the "rows" column for the INSERT queries, so it means also we are inserting exactly same number of rows as the number of DB calls, so doesn't it suggest that we are doing row by row operations/dmls.
Also after seeing above and asking application team to do the batch commit ,we are still seeing the similar figures from pg_stat_database and pg_stat_statements, so does it mean that we are looking into wrong stats? or the application code change has not been done accurately? and we see even when no inserts are running from the application side, we do see "xact_commit" keep increasing along with "tup_fetched" , why so?
Finally we see in postgres here, even if we just write a DML statement it does commit that by default, until we explicitly put it in a "begin... end" block. Can that be the difference between how a "commit" gets handled in postgres vs other databases?
