Using EDB Advanced Storage Pack¶
以下は、EDB Advances Storage Pack TAMが役立つシナリオです。
Refdataの例¶
- Refdataが役立つシナリオは、ニューヨーク証券取引所（NYSE）のすべての銘柄記号とその企業名の参照テーブルを作成する場合です。このデータはめったに変更されないことが予想され、市場全体のすべての株式取引を追跡するテーブルから頻繁に参照されます（
高度なオートクラスターの例 など）。ヒープの代わりに Refdata
を使用して、パフォーマンスを向上させることができます。
CREATE SEQUENCE nyse_symbol_id_seq;
CREATE TABLE nyse_symbol (
nyse_symbol_id INTEGER NOT NULL PRIMARY KEY DEFAULT NEXTVAL(nyse_symbol_id_seq),
symbol TEXT NOT NULL,
name TEXT NOT NULL
) USING refdata;
``` ## 自動クラスターの例
自動クラスターが役立つシナリオは、モノのインターネット (IoT) データの場合です。自動クラスターの代わりにヒープを使用する場合、Postgresはこれらの関連する行をクラスター化できないため、行のセットへのアクセスは多くのデータブロックに触れ、非常に遅くなる可能性があり、入出力が重いです。
この例は、60秒ごとに家の温度と温度設定を報告するIoTサーモスタット用です。
```sql
CREATE TABLE iot (
thermostat_id bigint,
recordtime timestamp,
measured_temperature float 4,
temperature_setting float 4,
) USING autocluster;
Autoclusterを使用すると、同じ thermostat_id
を持つ行がクラスター化され、アクセスが簡単になります。
CREATE INDEX ON iot USING btree(thermostat_id);
SELECT autocluster.autocluster(
rel := iot::regclass,
cols := {1},
max_objects := 10000
);
注釈
cols パラメーターは、 USING btree() で指定された列数と一致する必要があります。この場合、thermostat_id のみがリストされているため、値は`{1}` です。 !!!
テーブルにthermostat_id およびrecordtime
データを入力します。
INSERT INTO iot (thermostat_id, recordtime) VALUES (456, 12:01);
INSERT INTO iot (thermostat_id, recordtime) VALUES (8945, 04:55);
INSERT INTO iot (thermostat_id, recordtime) VALUES (456, 15:32);
INSERT INTO iot (thermostat_id, recordtime) VALUES (6785, 01:36);
INSERT INTO iot (thermostat_id, recordtime) VALUES (456, 19:25);
INSERT INTO iot (thermostat_id, recordtime) VALUES (5678, 03:44);
IoT テーブルからデータを選択すると、ctid の場所から、同じ
thermostat_id を持つデータがクラスター化されていることがわかります。
SELECT ctid, thermostat_id, recordtime FROM iot;
__OUTPUT__
ctid | thermostat_id | recordtime
- ------+---------------+------------
(0,1) | 456 | 12:01
(2,2) | 8945 | 04:55
(0,2) | 456 | 15:32
(3,2) | 6785 | 01:36
(0,3) | 456 | 19:25
(2,5) | 5678 | 03:44
(6 rows)
高度なオートクラスターの例¶
- これは、前の例よりも高度なAutoclusterの使用方法です。これには、
Refdataの例 からNYSEテーブルを参照し、株式記号に基づいて行をクラスター化することが含まれます。このアプローチにより、最新の取引数を簡単に見つけることができます。
Refdataの例のNYSEテーブルから始めます。
CREATE SEQUENCE nyse_symbol_id_seq;
CREATE TABLE nyse_symbol (
nyse_symbol_id INTEGER NOT NULL PRIMARY KEY DEFAULT NEXTVAL(nyse_symbol_id_seq),
symbol TEXT NOT NULL,
name TEXT NOT NULL
) USING refdata;
Refdata テーブル内のほとんど静的な株式銘柄を参照し、NYSE の取引を含む高度に更新されたテーブルを作成します。銘柄記号の行をクラスター化して、特定の株の最後のx回の取引を簡単に検索できるようにします。
CREATE TABLE nyse_trade (
nyse_symbol_id INTEGER NOT NULL REFERENCES nyse_symbol(nyse_symbol_id),
trade_time TIMESTAMP NOT NULL DEFAULT NOW(),
trade_price FLOAT8 NOT NULL CHECK(trade_price >= 0.0),
trade_volume BIGINT NOT NULL CHECK(trade_volume >= 1)
); -- USING autocluster;
CREATE INDEX ON nyse_trade USING BTREE(nyse_symbol_id);
SELECT autocluster.autocluster(
rel := nyse_trade::regclass,
cols := {1},
max_objects := 3000
);
autocluster
- ------------
(1 row)
IDではなくシンボル名で挿入しやすいビューを作成します。
CREATE VIEW nyse_trade_symbol AS
SELECT ns.symbol, nt.trade_time, nt.trade_price, nt.trade_volume
FROM nyse_symbol ns
JOIN nyse_trade nt
ON ns.nyse_symbol_id = nt.nyse_symbol_id;
CREATE RULE stock_insert AS ON INSERT TO nyse_trade_symbol
DO INSTEAD INSERT INTO nyse_trade
(SELECT ns.nyse_symbol_id, NEW.trade_time, NEW.trade_price, NEW.trade_volume
FROM nyse_symbol ns
WHERE ns.symbol = NEW.symbol
);
ビューの作成の詳細については、 PostgreSQL documentation を参照してください。
静的データを事前に入力します（簡潔にするために短縮されています）。
INSERT INTO nyse_symbol (symbol, name) VALUES
(A, Agilent Technologies),
(AA, Alcoa Corp),
(AAC, Ares Acquisition Corp Cl A),
(AAIC, Arlington Asset Investment Corp),
(AAIN, Arlington Asset Investment Corp 6.000%),
(AAN, Aarons Holdings Company),
(AAP, Advance Auto Parts Inc),
(AAQC, Accelerate Acquisition Corp Cl A),
(ZTR, Virtus Total Return Fund Inc),
(ZTS, Zoetis Inc Cl A),
(ZUO, Zuora Inc),
(ZVIA, Zevia Pbc Cl A),
(ZWS, Zurn Elkay Water Solutions Corp),
(ZYME, Zymeworks Inc);
ANALYZE nyse_symbol;
2022年11月18日金曜日の指定された時間範囲の株式取引を挿入します（短く省略されています）。
\timing
INSERT INTO nyse_trade_symbol VALUES (NSC, Fri Nov 18 09:51:32 2022, 248.100000, 98778);
Time: 32.349 ms
INSERT INTO nyse_trade_symbol VALUES (BOE, Fri Nov 18 09:51:32 2022, 9.640000, 72973);
Time: 1.055 ms
INSERT INTO nyse_trade_symbol VALUES (LOMA, Fri Nov 18 09:51:32 2022, 6.180000, 41632);
Time: 0.927 ms
INSERT INTO nyse_trade_symbol VALUES (LXP, Fri Nov 18 09:51:32 2022, 10.670000, 85768);
Time: 0.941 ms
INSERT INTO nyse_trade_symbol VALUES (ABBV, Fri Nov 18 09:51:32 2022, 155.000000, 46842);
Time: 0.916 ms
INSERT INTO nyse_trade_symbol VALUES (AGD, Fri Nov 18 09:51:32 2022, 9.360000, 90684);
Time: 0.669 ms
INSERT INTO nyse_trade_symbol VALUES (PAGS, Fri Nov 18 11:14:31 2022, 12.985270, 34734);
Time: 0.849 ms
INSERT INTO nyse_trade_symbol VALUES (KTF, Fri Nov 18 11:14:31 2022, 8.435753, 73719);
Time: 0.679 ms
INSERT INTO nyse_trade_symbol VALUES (AES, Fri Nov 18 11:14:31 2022, 28.072732, 549);
Time: 0.667 ms
INSERT INTO nyse_trade_symbol VALUES (LIN, Fri Nov 18 11:14:31 2022, 334.617829, 39838);
Time: 0.665 ms
INSERT INTO nyse_trade_symbol VALUES (DTB, Fri Nov 18 11:14:31 2022, 18.679245, 55863);
Time: 0.680 ms
ANALYZE nyse_trade;
Time: 73.832 ms
特定の銘柄記号のデータからctidを選択して、出力でどのようにクラスター化されたかを確認します。
SELECT ctid, * FROM nyse_trade WHERE nyse_symbol_id = 1000 ORDER BY trade_time DESC LIMIT 10;
__OUTPUT__
ctid | nyse_symbol_id | trade_time | trade_price | trade_volume
- ----------+----------------+--------------------------+-------------+--------------
(729,71) | 1000 | Fri Nov 18 11:13:51 2022 | 11.265938 | 72662
(729,22) | 1000 | Fri Nov 18 11:08:39 2022 | 11.262747 | 50897
(729,20) | 1000 | Fri Nov 18 11:08:30 2022 | 11.267203 | 37120
(729,9) | 1000 | Fri Nov 18 11:07:21 2022 | 11.269852 | 792
(729,6) | 1000 | Fri Nov 18 11:07:02 2022 | 11.268067 | 46221
(632,123) | 1000 | Fri Nov 18 11:04:46 2022 | 11.272623 | 97874
(632,118) | 1000 | Fri Nov 18 11:04:28 2022 | 11.271794 | 65579
(632,14) | 1000 | Fri Nov 18 10:55:45 2022 | 11.268543 | 8557
(632,2) | 1000 | Fri Nov 18 10:54:45 2022 | 11.26414 | 94078
(506,126) | 1000 | Fri Nov 18 10:54:01 2022 | 11.264657 | 89641
(10 rows)
