AWS EC2实例及应用服务器运行状态存储PostgreSQL Schema设计咨询

Great question! Let's break this down step by step, since this is a common monitoring data modeling scenario with clear best practices to follow.

1. Don't Create a Table Per Instance (Critical!) #

First off: never create a separate table for each EC2 instance. This is an anti-pattern that will make your system impossible to scale and maintain as you add more instances. Imagine having 10+ instances—you'd have to write messy UNION queries to aggregate status data, and any schema changes would require updating dozens of tables. Stick to a normalized, scalable structure instead.

2. Core Schema Design #

We'll use 4 tables to separate static metadata from time-series status logs, and properly link apps to their parent instances:

a. ec2_instances (Static Instance Metadata) #

Stores the fixed, long-term info about your EC2 instances (only updated when instances are added/renamed):

CREATE TABLE ec2_instances (
    instance_id VARCHAR(20) PRIMARY KEY, -- AWS Instance IDs like i-0abc123def456
    instance_name VARCHAR(100) NOT NULL, -- The friendly name tag from AWS
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger to auto-update updated_at when instance name changes
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_ec2_instances_update
BEFORE UPDATE ON ec2_instances
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

b. instance_status_logs (Time-Series Instance Status) #

Stores the minute-by-minute status scans for each instance. We avoid using instance_id as a single primary key because we need multiple records per instance (one per minute):

CREATE TABLE instance_status_logs (
    id SERIAL PRIMARY KEY, -- Optional auto-increment ID for easy linking
    instance_id VARCHAR(20) NOT NULL REFERENCES ec2_instances(instance_id),
    status VARCHAR(50) NOT NULL, -- e.g., 'running', 'stopped', 'port_unreachable'
    recorded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- Ensure no duplicate scans for the same instance at the same minute
    UNIQUE (instance_id, DATE_TRUNC('minute', recorded_at))
);

-- Index for fast time-range queries on a single instance
CREATE INDEX idx_instance_status_instance_time ON instance_status_logs(instance_id, recorded_at DESC);

c. application_servers (App-to-Instance Mapping) #

Links each app server (PUMA, NGINX, etc.) to its parent EC2 instance, so we don't repeat instance context in every status log:

CREATE TABLE application_servers (
    id SERIAL PRIMARY KEY,
    instance_id VARCHAR(20) NOT NULL REFERENCES ec2_instances(instance_id),
    application_name VARCHAR(50) NOT NULL, -- e.g., 'PUMA', 'NGINX', 'REDIS'
    description TEXT, -- Optional: notes about the app's role
    -- Ensure no duplicate app names on the same instance
    UNIQUE (instance_id, application_name)
);

-- Index to quickly find all apps on a specific instance
CREATE INDEX idx_app_server_instance ON application_servers(instance_id);

d. application_status_logs (Time-Series App Status) #

Stores the minute-by-minute status scans for each app server, linked back to the application_servers table:

CREATE TABLE application_status_logs (
    id SERIAL PRIMARY KEY,
    application_id INT NOT NULL REFERENCES application_servers(id),
    status VARCHAR(50) NOT NULL, -- e.g., 'running', 'down', 'high_cpu'
    recorded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- Ensure no duplicate scans for the same app at the same minute
    UNIQUE (application_id, DATE_TRUNC('minute', recorded_at))
);

-- Index for fast time-range queries on a single app
CREATE INDEX idx_app_status_app_time ON application_status_logs(application_id, recorded_at DESC);

3. Answering Your Specific Questions #

Q: Should I create a separate table for each instance? #

No—as explained earlier, this is an anti-pattern. The schema above scales seamlessly: add a new instance, insert one row into ec2_instances, map its apps in application_servers, and the status logs will automatically associate with the right instance without any schema changes.

Q: How to link app servers to their corresponding instances? #

We use the application_servers table as a bridge:

• Each app has an instance_id foreign key pointing to ec2_instances.instance_id
• To get all apps for an instance: SELECT * FROM application_servers WHERE instance_id = 'i-0abc123def456';
• To get an app's full context (instance name + app status): join application_status_logs → application_servers → ec2_instances

Q: If I set Instance ID as the primary key, I can't store duplicate values—how to fix this? #

Instance IDs are unique per instance, but you need multiple status records per instance (one per minute). The solution is:

• Use a composite unique constraint (like UNIQUE (instance_id, DATE_TRUNC('minute', recorded_at))) to prevent duplicate scans for the same instance at the same time
• Use an auto-increment id as the primary key for the status log tables (or use the composite pair as the primary key if you prefer—both work, but auto-increment IDs are easier for joining other tables later)

4. Optional Optimizations #

• Use Enums for Status Values: Restrict valid statuses to avoid typos (e.g., CREATE TYPE instance_status AS ENUM ('running', 'stopped', 'port_unreachable'); then set status instance_status NOT NULL in instance_status_logs)
• Partition Large Log Tables: For long-term storage, partition instance_status_logs and application_status_logs by recorded_at (monthly or weekly partitions) to speed up queries on historical data
• Archive Old Data: Move logs older than 3-6 months to a separate archive table to keep the main tables small and fast

内容的提问来源于stack exchange，提问作者Codeleys