Given the scale I am working at I thought a specific time scale
database would be overkill but I'll look into it nevertheless. Even
if I do write the queries with the known metrics I am still trying to
figure out how to efficiently query "last known value of this metric
on or before X time" without a correlated subquery which would be a
massively inefficient query.
On Sun, Oct 4, 2020 at 5:11 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Fri, Oct 2, 2020 at 10:45 PM Tim Uckun <timuckun@gmail.com> wrote:
>>
>> I am warehousing my health data in PG. Some of this data comes from my
>> phone/watch but some I enter in manually. Basically this is similar to
>> a time series metric collection type of scenario.
>
>
> My first impression is that you should use a timeseries database for this, not PostgreSQL.
>
> If you do try to use SQL to do this you indeed run into the problem of handling SQL's requirement that every column
beexplicitly defined. You can probably work around this somewhat by writing a query generator instead of hard-coding
queries. Though for your current level of knowledge I'd suggest putting forth working queries for the current model and
forgetabout how easy or hard dealing with change would be. Or at least be more convincing that change adaption is an
importantrequirement.
>
> Your data type issue is self-inflicted. Create a composite data type for blood pressure. Expanding upon that, you
shouldhave a table for each metric - or at least be willing to find middle-ground where some are grouped on one table
(eitheras rows or columns) and some are separate tables.
>
> None of this is impossible in PostgreSQL. Its a matter of development effort and performance. Put forth something
thatworks - or at least kinda works and note where specifically in the code you are stuck - if you want non-conceptual
helpovercoming a specific problem.
>
> David J.