NDduCMyk

1[Evaluation]
2- Use \copy in RDS PostgreSQL
3- Use Stored Procedure in RDS PostgreSQL
4- Use a partitioned table in RDS PostgreSQL
5
6[Environment]
7- RDS PostgeSQL 9.5.4
8
9â– LOG
10===============================================================================
11â–¼Generate .csv dat
12￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
13$ ruby record_gen.rb 2000 | sed -e 's/"/"""/g' > logs.csv
14
15record_gen.rb
16```ruby
17require 'json'
18require 'time'
19
20year = ARGV.first.to_i
21begin_time = Time.parse("#{year}-01-01")
22expire_time = Time.parse("#{year+1}-01-01")
23
241_000_000.times do
25  puts [
26    'test',
27    {message: 'hogehoge'}.to_json,
28    rand(begin_time...expire_time).to_s
29  ].join(',')
30end
31```
32
33â–¼ Create a new partitioned table
34￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
35$ psql -d postgresql -U admin -h postgresql.aqwsedrftgyhu.us-west-2.rds.amazonaws.com
36
37-- define the paratent talbe for partitioning
38postgresql=> CREATE TABLE logs (
39  tag TEXT,
40  record JSON NOT NULL,
41  time TIMESTAMP NOT NULL,
42  CHECK(time IS NULL) NO INHERIT
43);
44
45postgresql=> CREATE INDEX "logs_time_idx" ON logs(time);
46
47â–¼ Create a stored procedure
48￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
49-- define a function to create a LOGS child table corresponding to input date
50postgresql=> CREATE FUNCTION create_table_monthly_logs(IN TIMESTAMP) RETURNS VOID AS
51  $$
52    DECLARE
53      begin_time TIMESTAMP;
54      expire_time TIMESTAMP;
55    BEGIN
56      begin_time := date_trunc('month', $1);
57      expire_time := begin_time + '1 month'::INTERVAL;
58      EXECUTE 'CREATE TABLE IF NOT EXISTS '
59              || 'logs_'
60              || to_char($1, 'YYYY"_"MM')
61              || '('
62              || 'like logs including indexes, '
63              || 'CHECK('''
64              || begin_time
65              || ''' <= time AND time < '''
66              || expire_time
67              || ''')'
68              || ') INHERITS (logs)';
69    END;
70  $$
71  LANGUAGE plpgsql
72;
73
74-- define a function to insert a new record into LOGS child table
75postgresql=> CREATE FUNCTION insert_into_monthly_logs() RETURNS TRIGGER AS
76  $$
77    BEGIN
78      LOOP
79        BEGIN
80          -- attempt to insert into LOGS child table
81          EXECUTE 'INSERT INTO '
82                  || 'logs_'
83                  || to_char(new.time, 'YYYY"_"MM')
84                  || ' VALUES(($1).*)' USING new;
85          RETURN NULL;
86        EXCEPTION WHEN undefined_table THEN
87
88          -- retry if the corresponding child table
89          PERFORM create_table_monthly_logs(new.time);
90        END;
91      END LOOP;
92    END;
93  $$
94  LANGUAGE plpgsql
95;
96
97-- a trigger to transfer data when a new record is inserted into LOGS table
98postgresql=> CREATE TRIGGER insert_logs_trigger
99               BEFORE INSERT ON logs
100 FOR EACH ROW EXECUTE PROCEDURE insert_into_monthly_logs()
101;
102
103â–¼ insert data
104￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
105postgresql=> \copy logs from 'logs.csv' with csv
106
107postgresql=> \dt;
108           List of relations
109 Schema |     Name     | Type  | Owner
110--------+--------------+-------+-------
111 public | logs         | table | root
112 public | logs_2000_01 | table | root
113 public | logs_2000_02 | table | root
114 public | logs_2000_03 | table | root
115 public | logs_2000_04 | table | root
116 public | logs_2000_05 | table | root
117 public | logs_2000_06 | table | root
118 public | logs_2000_07 | table | root
119 public | logs_2000_08 | table | root
120 public | logs_2000_09 | table | root
121 public | logs_2000_10 | table | root
122 public | logs_2000_11 | table | root
123 public | logs_2000_12 | table | root
124(13 rows)
125
126postgresql=> select count(*) from logs;
127  count
128---------
129 1000000
130(1 row)
131
132Time: 197.673 ms
133postgresql=> select count(*) from logs_2000_01;
134 count
135-------
136 85377
137(1 row)
138
139Time: 8.709 ms
140===============================================================================