1

I have a table which roughly looks like this 

uuid uuid, 
id id, 
data text,
last_updated timestamp

Each row has an associated unique uuid. However, the key of interest is the id (indexed). Whenever we do an update, it happens that a row with the same id is inserted, the timestamp is the moment of putting it in the database, and it gets its own uuid. I would like to retrieve several of these rows in one query, but retrieving only the latest version.

So far I have 

SELECT * FROM table WHERE id IN (16638051, 14727883) 
GROUP BY id, uuid;

(ids are examples). However, I'm curious how to efficiently retrieve only the rows with the latest timestamp in that query?

Or would doing a single row at a time and retrieving it as a transaction be better?

UPDATE

This seems to do the trick (from another question).

SELECT * FROM (
   SELECT DISTINCT ON (id) *
   FROM   table
   WHERE  id IN (14727883, 16638051) 
   ORDER  BY id, last_updated DESC NULLS LAST
   ) sub
ORDER BY last_updated DESC NULLS LAST

However, I wonder if a materialized view wouldn't be easier. It's a read heavy & low write database with a couple million rows, with the updates only happening daily.

Improve this question
1
  • If your workload is read-biased, as you say, and especially if you most often are interested in the latest record for each ID, consider using a "history table" approach: keep only the most recent record in your "main" table and move older records to the history table as new records are inserted. It will affect write performance, of course, but it sounds like in your case it may be an acceptable price to pay for efficient reads.
    – mustaccio
    May 16, 2016 at 17:54

0

Reset to default