More efficient to do SELECT and compare in Java or DELETE and INSERT I am hitting a REST API to get data from a service. I transform this data and store it in a database. I will have to do this on some interval, 15 minutes, and then make sure this database has latest information.
I am doing this in a Java program. I am wondering if it would be better, after I have queried all data, to do:


SELECT statements and compare vs transformed data and do UPDATEs (DELETE all associated records to what was changed and INSERT new)


OR


DELETE ALL and INSERT ALL every time.


Option 1 has potential to be a lot less transactions, guaranteed SELECT on all records because we are comparing, but potentially not a lot of UPDATEs since I don't expect data to be changing much. But it has downside of doing comparisons on all records to detect a change

I am planning on doing this using Spring Boot, JPA layer and possibly postgres
Neither of your proposals can be considered efficient. MERGE was introduced a long time ago (SQL99 or SQL2003) and will outperform both of your alternatives:

MERGE INTO T as x
USING (
    VALUES (?, ...., ?)
) as y (c1, c2, ...,cn)
    ON x.&lt;key&gt; = y.&lt;key&gt;
WHEN MATCHED AND (x.c1, x.c2, x....) &lt;&gt; (y.c1,y.c2, y...) THEN 
    UPDATE (...)
    VALUES (y..)
WHEN NOT MATCHED THEN
    INSERT (...)
    VALUES (...)