I have a table with messages in a Postgres DB.
When the user reads a message or a group of messages that belong together, I collect the message ids and, after the SELECT query, UPDATE the read flag to TRUE.
So it's just two simple queries, still memory-friendly. But I wonder if it could be improved anyhow, maybe by combining the
UPDATE query? I use PHP/PDO for the database access.
There is the
RETURNING clause of the Postgres
UPDATE msg_tbl SET read = TRUE WHERE msg_id = ? RETURNING *;
But that is only an improvement if all rows actually need an
UPDATE. If some already have
read = TRUE that would cause empty updates ending up more expensive than a
SELECT and a separate
UPDATE with an additional
WHERE read = FALSE.