Amazon Aurora DSQL がGAされたので料金について調べてみた

FROM postgres:15

# システム情報を確認
RUN uname -m

# AWS CLI v2をアーキテクチャに応じてインストール
RUN apt-get update && apt-get install -y curl unzip \
    && ARCH=$(uname -m) \
    && if [ "$ARCH" = "aarch64" ]; then \
         curl "https://awscli.amazonaws.com/awscli-exe-linux-aarch64.zip" -o "awscliv2.zip"; \
       else \
         curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"; \
       fi \
    && unzip awscliv2.zip \
    && ./aws/install \
    && rm -rf awscliv2.zip aws \
    && apt-get clean

WORKDIR /app

docker run --rm -it \
  -v ${HOME}/.aws:/root/.aws:ro \
  -e AWS_PROFILE=container \
  postgre-in-aws-cred:latest \
  bash

psql --dbname postgres --username admin --host <エンドポイント名>

root@f061078ce848:/app# psql --dbname postgres --username admin --host ***.dsql.ap-northeast-1.on.aws
Password for user admin: 
psql (15.13 (Debian 15.13-1.pgdg120+1), server 16.9)
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, compression: off)
Type "help" for help.

postgres=> SELECT version();
    version    
---------------
 PostgreSQL 16
(1 row)

postgres=> SELECT 'INSERT Test Start: ' || NOW() as test_start;
                    test_start                    
--------------------------------------------------
 INSERT Test Start: 2025-05-27 23:58:43.012188+00
(1 row)

postgres=> CREATE TABLE dpu_test_insert (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    value INTEGER
);
CREATE TABLE

-- 3,000件INSERT
postgres=> INSERT INTO dpu_test_insert (id, name, value)
SELECT 
    generate_series,
    'name_' || generate_series,
    (random() * 1000)::INTEGER
FROM generate_series(1, 3000);
INSERT 0 3000

postgres=> SELECT 'INSERT Test End: ' || NOW() as test_end;
                   test_end                    
-----------------------------------------------
 INSERT Test End: 2025-05-27 23:58:57.40972+00
(1 row)

-- 結果確認
postgres=> SELECT COUNT(*) as inserted_count FROM dpu_test_insert;
 inserted_count 
----------------
           3000
(1 row)

postgres=> SELECT 'SELECT Test Start: ' || NOW() as test_start;
                    test_start                    
--------------------------------------------------
 SELECT Test Start: 2025-05-28 00:01:18.075355+00
(1 row)

-- 全件検索
postgres=> SELECT COUNT(*) FROM dpu_test_insert;
 count 
-------
  3000
(1 row)

-- 条件付き検索
postgres=> SELECT COUNT(*) FROM dpu_test_insert WHERE value > 500;
 count 
-------
  1546
(1 row)

-- 範囲検索
postgres=> SELECT * FROM dpu_test_insert WHERE id BETWEEN 500 AND 1000;

-- ソート処理
postgres=> SELECT * FROM dpu_test_insert ORDER BY value DESC LIMIT 100;

-- 文字列検索
postgres=> SELECT COUNT(*) FROM dpu_test_insert WHERE name LIKE 'name_1%';
 count 
-------
  1111
(1 row)

postgres=> SELECT 'SELECT Test End: ' || NOW() as test_end;
                    test_end                    
------------------------------------------------
 SELECT Test End: 2025-05-28 00:01:58.554174+00
(1 row)

postgres=> SELECT 'AGGREGATION Test Start: ' || NOW() as test_start;
                      test_start                       
-------------------------------------------------------
 AGGREGATION Test Start: 2025-05-28 00:04:36.413594+00
(1 row)

-- 基本統計
postgres=> SELECT 
    COUNT(*) as total_count,
    AVG(value) as avg_value,
    MIN(value) as min_value,
    MAX(value) as max_value,
    STDDEV(value) as std_dev
FROM dpu_test_insert;
 total_count |      avg_value       | min_value | max_value |     std_dev      
-------------+----------------------+-----------+-----------+------------------
        3000 | 504.9780000000000000 |         0 |      1000 | 290.600985018114
(1 row)

-- グループ集計
postgres=> SELECT 
    CASE 
        WHEN value < 250 THEN 'low'
        WHEN value < 500 THEN 'medium_low'
        WHEN value < 750 THEN 'medium_high'
        ELSE 'high'
    END as category,
    COUNT(*) as count,
    AVG(value) as avg_value,
    MIN(value) as min_value,
    MAX(value) as max_value
FROM dpu_test_insert
GROUP BY 
    CASE 
        WHEN value < 250 THEN 'low'
        WHEN value < 500 THEN 'medium_low'
        WHEN value < 750 THEN 'medium_high'
        ELSE 'high'
    END
ORDER BY category;
  category   | count |      avg_value       | min_value | max_value 
-------------+-------+----------------------+-----------+-----------
 high        |   772 | 871.6619170984455959 |       750 |      1000
 low         |   751 | 122.7616511318242344 |         0 |       249
 medium_high |   776 | 628.7152061855670103 |       500 |       749
 medium_low  |   701 | 373.6576319543509272 |       250 |       499
(4 rows)

-- 複雑な集計（パーセンタイル）
postgres=> SELECT 
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY value) as q1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) as median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY value) as q3,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY value) as p90
FROM dpu_test_insert;
 q1  | median | q3  | p90 
-----+--------+-----+-----
 249 |    514 | 755 | 901
(1 row)

postgres=> SELECT 'AGGREGATION Test End: ' || NOW() as test_end;
                      test_end                       
-----------------------------------------------------
 AGGREGATION Test End: 2025-05-28 00:05:02.683022+00
(1 row)

-- 事前に新しいテーブル作成してデータ挿入
postgres=> CREATE TABLE dpu_test_update_fresh (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    value INTEGER
);
CREATE TABLE

postgres=> INSERT INTO dpu_test_update_fresh (id, name, value)
SELECT 
    generate_series,
    'name_' || generate_series,
    (random() * 1000)::INTEGER
FROM generate_series(1, 3000);
INSERT 0 3000

postgres=> SELECT 'UPDATE Test New Table Start: ' || NOW() as test_start;
                         test_start                         
------------------------------------------------------------
 UPDATE Test New Table Start: 2025-05-28 00:19:09.880252+00
(1 row)

-- 小規模UPDATE（500件）
postgres=> UPDATE dpu_test_update_fresh 
SET name = 'updated_' || id 
WHERE id <= 500;
UPDATE 500

-- 中規模UPDATE（1,500件）
postgres=> UPDATE dpu_test_update_fresh 
SET value = value + 100 
WHERE id BETWEEN 501 AND 2000;
UPDATE 1500

-- 大規模UPDATE（残り1,000件）
postgres=> UPDATE dpu_test_update_fresh 
SET name = 'final_update_' || id 
WHERE id > 2000;
UPDATE 1000

postgres=> SELECT 'UPDATE Test End: ' || NOW() as test_end;
                    test_end                    
------------------------------------------------
 UPDATE Test End: 2025-05-28 00:19:32.841752+00
(1 row)

-- 更新結果確認
postgres=> SELECT COUNT(*) as updated_small FROM dpu_test_update_fresh WHERE name LIKE 'updated_%';
 updated_small 
---------------
           500
(1 row)

postgres=> SELECT 'DROP Test Start: ' || NOW() as test_start;
                   test_start                   
------------------------------------------------
 DROP Test Start: 2025-05-28 00:25:05.686845+00
(1 row)

postgres=> DROP TABLE dpu_test_insert;
DROP TABLE

postgres=> DROP TABLE dpu_test_update_fresh;
DROP TABLE

postgres=> SELECT 'DROP Test End: ' || NOW() as test_end;
                   test_end                   
----------------------------------------------
 DROP Test End: 2025-05-28 00:25:42.439273+00
(1 row)