大量データベースの設計について

Question

最近データベースを学んでいるものです。
現在テスト中のデータベースの対象テーブルは以下のように時系列と計測器の計測値データとなっております。

テーブル名：table01

計測時間	計測器1	計測器2	…	計測器1000
2021-01-01 0:00:00	1.01	2.01	…	1.41
2021-01-01 0:01:00	1.01	2.01	…	1.41
…	…	…	…	…

横持ちにしているのはデータ容量を少なくするためです。
計測ピッチは短いところで１分以下の時もあり、1年で20万行ほどになります(最大で10年程度)。
ここから例えば計測器1の1年分のデータを抽出しようとすると、どうしても初回(おそらく共有メモリに対象テーブルがないとき)に数秒～十数秒かかってしまいます。
ただ実際には初回からもっと高速にデータの抽出をして、時系列グラフの描画をしたいと考えております。
よく使うsql文は任意の期間を抽出する以下のようなものです。

select 測定器1 from table01 where 計測時間 >= '2021-01-01 0:00:00' and 計測時間 < '2022-01-01 0:00:00';

例えば以下のサイトの日経平均のチャートを見ると、"１日"、"1年"、"10年"等のタブを切り替えても、とても早くチャートが描画されます(１日の1分足が選択できるので、元データはかなり大きなデータだと思っております)。
日経平均 - マーケット｜SBI証券

そこで質問なのですが、
こういったデータベースのテーブルの設計をする際は、高速にアクセスするためにそもそも初めから"１日"、"1年"、"10年"のテーブルを分けているのか、"10年"の大量の元データからその都度特別な方法で高速に"1年"、"5年"等のデータを抽出しているのか、どちらなのでしょうか。それとも他の方法があるのでしょうか。教えていただければ幸いです。

Comments

[退会済みユーザー]

計測器データがあとから変更が加わることがないという前提があれば、使用頻度に応じたデータの集約がなされても良さそうな気がしますが。年単位でしか使わなければ年単位に集約したデータを用意するのは当然。

[kikukiku]

そこを考えるのがプログラマの仕事かと思います。
横軸が１０年だったとして、横軸のドット数が１００ドットの場合、
１００個以上のデータをDBから取得したとしても
それを表示する能力が画面にないということになります。
ということは無駄ということですので、
描画を早くすることができる部分ではないかと思います。
DBから表示に最低限必要な１００個のデータのみ取得するのであれば
高速になるでしょう。
また自身では書いていますが、１０年用のテーブルを準備することも
表示に速さにつながると思います。
まだまだいろいろなアイデアがあると思います。
自身でアイデアを出してみて、それを検証し、満足な性能がでればOkですし、
満足しなければ、自身のアイデアを提示したうえで
もっと改善するためにはどうしたらいいかアイデアを募ってみるのが良いと思いました。

[shimotani1028]

いろいろな方法を試してみたいと思います。
ありがとうございます。

[kikukiku]

頑張ってください。
データの削減を考え始めると、どのように削減するのかという疑問がでてくると思います。
１年分のデータから１個のデータを抽出したい場合、
最大値なのか、最小値なのか、平均なのか、そのほかなのか。
それはどのような目的のグラフを表示したいのかということに行きつくと思います。
楽しいと思うので、いろいろと考えてみると良いと思います。

Answer 1

測定機NOはずっと固定でしょうか？何ヶ月か毎に変わりますか？測定機が1000を超えたら対応できますか？
計測時間をPRIMARY KEYとして測定値を横に並べた時と
計測時間と測定機NOをPRIMARY KEYと測定値を持った場合で実際に使われるSQLを書いてみては？
テスト的に1年や2年分のデータを用意して実行時間を実測すれば自ずと分かるのでは？
データベースの正規化とは？ で通常は第1正規化で繰り返し(今回は質問の横並び)を排除します。
ディスクの価格がどんどん下がっていますから、最近は容量を気にするより、パフォーマンスやメンテナンスビリティを優先します。

Comments

[shimotani1028]

計測器は増える可能性もあります。なので1600列ぎりぎりまで取っておいて割り当てる方法になるとおもいます。
縦持ちの検討してみようと思います。ありがとうございます。
縦持ちの場合ですが、計測時間を主キー、測定器Noをインデックスにする場合と計測時間を主キーで測定器Noをパーティションで分ける方法とどちらが一般的なのでしょうか。

[shimotani1028]

すみません。postgresはパーティションの上限が100までみたいで、パーティションを使う方法はよくないですね。

[Orlofsky]

>計測器は増える可能性もあります。なので1600列ぎりぎりまで取っておいて割り当てる方法になるとおもいます。
計測器が減ったり、1600を超えた場合は対応できますか？
SELECT KEISOKU_TIME, KEISOKUKI_0001 + KEISOKUKI_0002 + ... KEISOKUKI_1600 AS DAYS ...
なんてコピペを駆使しても書くだけで疲れます。

>すみません。postgresはパーティションの上限が100までみたいで、パーティションを使う方法はよくないですね。
shimotani1028さんの頭の中を読めませんから、質問にCREATE TABLLE文や需要が多そうなSELECT文を載せては？

[shimotani1028]

計測器が減った場合はそのまま残して、1600を超える場合はテーブルを増やすしかないと思っています。
KEISOKUKI_0001 + …の部分はおっしゃる通りです。
create tableはcsvを読み込んでいるので、再現するのは難しいです。
よく使うselect文は追記しました。

Answer 2

横持ちのデータは集計しづらいのでやめたほうがよいかも
千個の機材が年それぞれ20万データをもつということだと、年間2億データってことですかね？
ある程度のタームで仮集計してしまうような工夫が必要だと思います

Comments

[shimotani1028]

そうですね。横持ちをやめるのも考えようと思います。
仮集計とはどういったイメージでしょうか？
一定期間で分けるようなイメージでしょうか？

[yambejp]

一定の集計単位であらかじめ計算しておくことです
どういう集計をしたいか提示がないのでなんともいえません

[shimotani1028]

例えば計算ではなく任意の期間の全データを抽出してグラフ化する必要があるのですが、
その場合は元データを参照しに行くしかないですよね？

[yambejp]

集計ではなく抽出であれば事前の計算はしにくいですね
とはいえ抽出する範囲指定の方法をあらかじめカラムに埋め込んで
インデクスをはっておくとことで抽出時のヒットを高速に行うことは可能でしょう

[shimotani1028]

いまは計測時間を主キーにしているので時間に関する条件は割と早いです。
しかし、そもそも1000カラムあるテーブルから１カラムの計測時間の全期間を抽出(select 計測器1 from テーブル)するだけでも、非常に時間がかかるため、
カラムが多いのが実行時間が遅い原因なのではないかと考えております。
やはり、縦持ちを検討した方がよさそうですね。

[yambejp]

1000カラムすべてが常に埋まっている状態なら別に今のままでも行けそうな気がしますけどね
select 計測器1 from テーブル
とするかぎり、抽出されるデータの容量は１カラム分ですし
カラム同士の相関関係がある場合は微妙ですが、基本的にカラム同士は完全に独立しているのですよね？

[shimotani1028]

埋まっていないと遅くなるのでしょうか？
カラム同士は独立しています。
select 計測器1 from テーブル
が遅い限りこのままでは正直使い物にならないです。
なので、テーブルを計測時間についてパーティション(〇カ月単位等)を区切ることで、
抽出期間が比較的短い場合だけでも実行時間が短くならないかと思って試したのですが。
結局元テーブルも計測時間がインデックスがついているためか、実行時間は今と変わりませんでした。

[yambejp]

> 埋まっていないと遅くなるのでしょうか？

埋まっていないなら、無駄なnullデータが大量に発生するので非効率的ですね
大量データならパーティショニングだろうな・・とはなんとなく思っていましたが
容量にもよるのでなんともいえません。
集計が必要ないならNoSQL的なアプローチになるかもしれません

[shimotani1028]

そうなんですね。
NoSQL、いろいろ調べていたら時系列データに特化したデータベースもあるみたいですね。
そちらも視野に入れて検討したいと思います。
ありがとうございます。