0

I'm not sure what technology I need because I'm not a DBA, but I'm sure there's a good way to do this.

I have a postgres database with this schema (top two tables exist):

observations

So I'm trying to add the 3rd table to the database. It's a subset of observation: all latest observations by stream_id and target_id with the target_key value already joined into it.

I know upfront that 99% of the queries against the target table are asking for the latest row of a certain stream_id and target_id. so I thought instead of manually building another table with duplicate data, I'll try to leverage the power of the database to make a temporal table, or a foreign data wrapper or something, that is essentially a hard coded query on the database which looks like a table we can query, (and since it's querying a subset of the data, its much faster).

Ok, so that's what I'm looking for but I don't know which technology to use, as I mentioned I've been searching and I found temporal tables, virtual tables and FDW. But I'm a programmer not a DBA so I'm having a hard time telling the difference or understanding which one matches my need.

What technology can I use for this?

MetaStack
  • 103
  • 4

1 Answers1

0

I discovered I'm looking for a view. And I think this'll basically work:

CREATE VIEW latest_observation AS
  SELECT distinct ON (
    SELECT ob.stream_id, ob.target_id
      FROM obseration ob
      GROUP  BY ob.stream_id, ob.target_id
      HAVING count(*) = 1)
    o.id AS observation_id,
    o.stream_id,
    s.source_name AS source,
    s.stream_name AS stream,
    t.target_name AS target,
    o.inserted_at,
    o.value
  FROM observation o
     INNER JOIN target t USING (target_id);
     INNER JOIN stream s USING (stream_id);
  ORDER BY o.inserted_at DESC;
MetaStack
  • 103
  • 4