public inbox for [email protected]  

help / color / mirror / Atom feed

From: Al Grant <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected]
Subject: Re: Postgres DB design Question (High Level)
Date: Thu, 19 Jun 2025 17:08:37 +1200
Message-ID: <CAODtcdcuUMFcOAyKAFjDCtgu244VUcfmE00PXqeOxA_rQi=Q2w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAODtcdfiMuXAFDiJpB-0QMS6PeUy-WMOHAjaL8q5k-489Fg7SA@mail.gmail.com>
	<[email protected]>

Not sure if it makes a big difference to the answer, but MySQL was a
typo - the edge db on the Rpi is Sqlite3.

On Thu, Jun 19, 2025 at 5:00 PM Laurenz Albe <[email protected]> wrote:
>
> On Thu, 2025-06-19 at 16:50 +1200, Al Grant wrote:
> > I want to create a web based dashboard for all the currently detected
> > signals, where the dashboard contains a graph of the daily beep rate
> > for each channel (max 100 channels) over user selectable periods from
> > 1 week to 1 year - that query does not scale well if I query the bpm
> > table.
> >
> > To avoid this I have created a bpm summary table which is generated
> > periodically (hourly) off the bpm table. The bpm summary table
> > contains the dominant beep rate for a given hour (so 2 records per day
> > per channel assuming a signal is detected).
> >
> > Does this summary table approach make sense?
>
> Yes.  Pre-aggregation to reduce the data volume is common in a
> data warehouse.  Ideally, you don't have to scan the entire base
> table to create the summary.  You can consider a trigger for that,
> but more elegant might be a solution based on partitioning, where
> you aggregate a partition once it is complete.
>
> > I have noted that I am periodically syncing from MySQL to the server,
> > and then periodically updating the summary table - its multi stage
> > syncing and I wonder if that makes this approach fragile (although I
> > don't see any alternative).
>
> "MySQL" and "fragile" in the same sentence.  Sounds about right.
>
> Yours,
> Laurenz Albe



-- 
"Beat it punk!"
- Clint Eastwood

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Postgres DB design Question (High Level)
  In-Reply-To: <CAODtcdcuUMFcOAyKAFjDCtgu244VUcfmE00PXqeOxA_rQi=Q2w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox