On Mon, Sep 16, 2024 at 11:56 PM veem v <veema0000@xxxxxxxxx> wrote:
So what can be the caveats in this approach, considering transactions meant to be ACID compliant as financial transactions.
Financial transactions need to be handled with care. Only you know your business requirements, but as Christophe pointed out, disabling synchronous commit means your application may think a particular transaction has completed when it has not. Usually that's a big red flag for financial applications.
we are using row by row transaction processing for inserting data into the postgres database and commit is performed for each row.
This is a better place to optimize. Batch many rows per transaction. Remove unused indexes.
flushing of the WAL to the disk has to happen anyway(just that it will be delayed now), so can this method cause contention in the database storage side if the speed in which the data gets ingested from the client is not getting written to the disk , and if it can someway impact the data consistency for the read queries?
Not quite clear what you are asking here re data consistency. The data will always be consistent, even if synchronous_commit is disabled. The only danger window is on a server crash.
(Keep in mind that RDS is not Postgres, so take tuning recommendations and advice with a grain of salt.)
Cheers,
Greg
