This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
api_logs=> CREATE OR REPLACE FUNCTION logs_insert_trigger() | |
RETURNS TRIGGER AS $$ | |
BEGIN | |
IF ( NEW.created_at < '2017-10-01T00:00:00' ) THEN | |
INSERT INTO logs_201709 VALUES (NEW.*); | |
ELSIF ( NEW.created_at >= '2017-10-01T00:00:00' AND | |
NEW.created_at < '2017-11-01T00:00:00' ) THEN | |
INSERT INTO logs_201710 VALUES (NEW.*); | |
ELSE | |
RAISE EXCEPTION 'Date out of range. Fix the logs_insert_trigger() function!'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
QUERY PLAN | |
-------------------------------------------------------------------- | |
Aggregate (cost=8.18..8.19 rows=1 width=0) | |
-> Append (cost=0.00..8.17 rows=2 width=0) | |
-> Seq Scan on logs (cost=0.00..0.00 rows=1 width=0) | |
Filter: ((statuscode >= 500) AND ("created_at" >= '2017-10-20 00:00:00'::timestamp without time zone) AND ("created_at" < '2017-10-21 00:00:00'::timestamp without time zone) AND ((api_key)::text = 'SEM3XXXXXXXXXYYYYYYYY'::text)) | |
-> Index Scan using logs_201710_api_key_created_at_idx on logs_201710 (cost=0.15..8.17 rows=1 width=0) | |
Index Cond: (((api_key)::text = 'SEM3XXXXXXXXXYYYYYYYY'::text) AND ("created_at" >= '2017-10-20 00:00:00'::timestamp without time zone) AND ("created_at" < '2017-10-21 00:00:00'::timestamp without time zone)) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
EXPLAIN SELECT count(*) FROM logs | |
WHERE api_key = 'SEM3XXXXXXXXXYYYYYYYY' | |
AND statuscode >= 500 | |
AND created_at >= '2017–10–20' AND created_at < '2017–10–21'; | |
QUERY PLAN | |
-------------------------------------------------------------------- | |
Aggregate (cost=16.36..16.37 rows=1 width=0) | |
-> Append (cost=0.00..16.35 rows=3 width=0) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Note that the legacy logs table already exists and logs_main is intended to be the master table | |
api_logs=> CREATE TABLE logs_main (LIKE logs INCLUDING DEFAULTS INCLUDING INDEXES); | |
api_logs=> ALTER TABLE logs INHERIT logs_main; | |
api_logs=> BEGIN; | |
-- logs_201709 contains the logs from the beginning of time till September, 2017 | |
api_logs=> ALTER TABLE logs RENAME TO logs_201709; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT count(*) FROM logs | |
WHERE api_key = 'SEM3XXXXXXXXXYYYYYYYY' | |
AND statuscode >= 500 | |
AND created_at::date >= '2017–10–20' AND created_at::date < '2017–10–21'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
api_logs=> \d logs | |
Table "public.logs" | |
Column | Type | Modifiers | |
-----------+-----------------------------+----------------- | |
id | bigint | not null | |
api_key | character varying | not null | |
statuscode | integer | | |
latency | integer | | |
method | character varying | | |
created_at | timestamp without time zone | default now() |
NewerOlder