> On Sep 22, 2021, at 12:20 PM, David G. Johnston <david.g.johns...@gmail.com> > wrote: > > On Wed, Sep 22, 2021 at 1:05 PM Israel Brewster <ijbrews...@alaska.edu > <mailto:ijbrews...@alaska.edu>> wrote: > To work around the issue, I created a materialized view that I can update > periodically, and of course I can query said view in no time flat. However, > I’m concerned that as the dataset grows, the time it takes to refresh the > view will also grow (correct me if I am wrong there). > > I'd probably turn that index into a foreign key that just ensures that every > (station,channel) that appears in the data table also appears on the lookup > table. Grouping and array-ifying the lookup table would be trivial. Either > modify the application code or add a trigger to populate the lookup table as > needed.
Makes sense. I was actually considering this approach (albeit without the foreign key - that’s a nice additional safety measure), but was concerned about the overhead that adding said trigger would have on inserts - thus my thought to try the materialized view. As a reference, this database is receiving 1Hz data from around 170 stations, with up to three channels of data per station. So something like 350-500 inserts per second, although the data is “grouped” into 10 minute batches. I’ll give it another look. > The parentheses around channel in "array_agg(distinct(channel))" are > unnecessary - you are invoking composite-type syntax, which is ignored in the > single column case unless you write the optional ROW keyword, i.e., distinct > ROW(channel) Good information, thanks! > David J. --- Israel Brewster Software Engineer Alaska Volcano Observatory Geophysical Institute - UAF 2156 Koyukuk Drive Fairbanks AK 99775-7320 Work: 907-474-5172 cell: 907-328-9145
