Message-ID: <50b51d926417c0832e24753903710d0fa3311815.camel@cybertec.at>
Subject: Re: Postgres DB design Question (High Level)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Al Grant <bigal.nz@gmail.com>, pgsql-general@lists.postgresql.org
Date: Thu, 19 Jun 2025 07:00:54 +0200
In-Reply-To: <CAODtcdfiMuXAFDiJpB-0QMS6PeUy-WMOHAjaL8q5k-489Fg7SA@mail.gmail.com>
References: 
	<CAODtcdfiMuXAFDiJpB-0QMS6PeUy-WMOHAjaL8q5k-489Fg7SA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/50b51d926417c0832e24753903710d0fa3311815.camel%40cybertec.at>
Precedence: bulk

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.
>=20
> 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).
>=20
> 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