All we need is an easy explanation of the problem, so here it is.
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):
So I’m trying to add the 3rd table to the database. It’s a subset of
observation: all latest observations by
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂