Postgresql synchronous_commit off and select queries

Question

If data is inserted into a postgresql database using asynchronous commit, is it possible for a subsequent select query to guarantee that the data it reads has been written to disk?

I am storing generated data in postgresql and since the data can be regenerated, I would like to take advantage of asynchronous commit to increase throughout. But the generated data is periodically synchronised with an external system and it is important that data that has been sent to the external system is not lost.

Answer 1

Have the sync program update a counter or timestamp in some dummy table and commit the update:

begin;
select * from async_inserted;
-- maybe do something in the external system here
update dummy set synctime=now();
commit;
-- or maybe do something external here instead

Of course if the update or commit fails, that could still leave you not knowing what state the external system should be in. You might need to use prepared transactions (aka "2 phase commit") to bond the two systems together atomically.