This repository has been archived by the owner on Jan 26, 2023. It is now read-only.
/
doc.go
54 lines (42 loc) · 1.81 KB
/
doc.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
// Package storage is a container of all of the database layer implementation.
/*
Package storage implements the database access layer. The underlying database
is Postgres, and the tables are defined as such:
CREATE TABLE aws_resources (
id VARCHAR PRIMARY KEY,
account_id VARCHAR NOT NULL,
region VARCHAR NOT NULL,
type VARCHAR NOT NULL,
meta JSONB
);
We use these simple tables to preserve uniqueness and so we can add columns for additional
metadata when needed without polluting the aws_events_ips_hostnames star table:
CREATE TABLE aws_ips (
ip INET PRIMARY KEY
);
CREATE TABLE aws_hostnames (
hostname VARCHAR PRIMARY KEY
);
Notice "PARTITION BY" below. We're using built-in partitioning.
See https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1/
CREATE TABLE aws_events_ips_hostnames (
ts TIMESTAMP NOT NULL,
is_public BOOLEAN NOT NULL,
is_join BOOLEAN NOT NULL,
aws_resources_id VARCHAR NOT NULL,
FOREIGN KEY (aws_resources_id) REFERENCES aws_resources (id),
aws_ips_ip INET NOT NULL,
FOREIGN KEY (aws_ips_ip) REFERENCES aws_ips (ip),
aws_hostnames_hostname VARCHAR,
FOREIGN KEY (aws_hostnames_hostname) REFERENCES aws_hostnames (hostname))
PARTITION BY
RANGE (
ts
);
And we'll make sure there's an index right away:
CREATE INDEX IF NOT EXISTS aws_events_ips_hostnames_aws_ips_ip_ts_idx ON aws_events_ips_hostnames USING BTREE (aws_ips_ip, ts);
Also, some good advice to follow: https://www.vividcortex.com/blog/2015/09/22/common-pitfalls-go/
The underlying code is careful to create new partition tables and indices when necessary. Future updates to the implementation
would require creation of new tables, done by either a database admin or here in code, perhaps in the Init function.
*/
package storage