Re: Growth planning - Mailing list pgsql-general
From | Rob Sargent |
---|---|
Subject | Re: Growth planning |
Date | |
Msg-id | 39A75BC4-7F4D-45F9-BD66-891F0294A072@gmail.com Whole thread Raw |
In response to | Growth planning (Israel Brewster <ijbrewster@alaska.edu>) |
Responses |
Re: Growth planning
|
List | pgsql-general |
Guessing the “sd” is "standard deviation”? Any chance those stddevs are easily calculable from base data? Could cut your table size in half (and put those 20 cores to work on the reporting). And I wonder if the last three indices are strictly necessary? They take disc space too.On Oct 4, 2021, at 10:22 AM, Israel Brewster <ijbrewster@alaska.edu> wrote:- and the data table definition:Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+----------------------------------
id | bigint | | not null | nextval('data_id_seq'::regclass)
datetime | timestamp with time zone | | not null |
freq_max1 | double precision | | |
freq_max5 | double precision | | |
freq_max10 | double precision | | |
freq_max20 | double precision | | |
freq_max30 | double precision | | |
freq_max40 | double precision | | |
freq_max50 | double precision | | |
freq_max100 | double precision | | |
sd_freq_max5 | double precision | | |
sd_freq_max10 | double precision | | |
sd_freq_max20 | double precision | | |
sd_freq_max30 | double precision | | |
sd_freq_max40 | double precision | | |
sd_freq_max50 | double precision | | |
sd_freq_max100 | double precision | | |
ssa_max1 | double precision | | |
ssa_max5 | double precision | | |
ssa_max10 | double precision | | |
ssa_max20 | double precision | | |
ssa_max30 | double precision | | |
ssa_max40 | double precision | | |
ssa_max50 | double precision | | |
ssa_max100 | double precision | | |
sd_ssa_max5 | double precision | | |
sd_ssa_max10 | double precision | | |
sd_ssa_max20 | double precision | | |
sd_ssa_max30 | double precision | | |
sd_ssa_max40 | double precision | | |
sd_ssa_max50 | double precision | | |
sd_ssa_max100 | double precision | | |
station | smallint | | not null |
channel | character varying(6) | | not null |
epoch | integer | | |
rsam | double precision | | |
sd_rsam | double precision | | |
Indexes:
"data_pkey" PRIMARY KEY, btree (id)
"date_station_channel_idx" UNIQUE, btree (datetime, station, channel)
"station_channel_epoch_idx" UNIQUE, btree (station, channel, epoch)
"data_station_channel_idx" btree (station, channel)
"station_data_idx" btree (station)
"station_date_idx" btree (station, datetime)
Foreign-key constraints:
"data_station_fkey" FOREIGN KEY (station) REFERENCES stations(id)
Triggers:
update_epoch BEFORE INSERT OR UPDATE OF datetime ON data FOR EACH ROW EXECUTE FUNCTION store_epoch()
But my bet is you’re headed for partitioning on datetime or perhaps station.
pgsql-general by date: