postgresql
Design PostgreSQL Database Schemas
๋ํ ๋ค์์์ ์ฌ์ฉํ ์ ์์ต๋๋ค: 2025Emma,2025Emma
Database schema design errors cause performance issues and data integrity problems. This skill provides PostgreSQL-specific best practices for data types, indexes, constraints, and scalability patterns.
์คํฌ ZIP ๋ค์ด๋ก๋
Claude์์ ์ ๋ก๋
์ค์ โ ๊ธฐ๋ฅ โ ์คํฌ โ ์คํฌ ์ ๋ก๋๋ก ์ด๋
ํ ๊ธ์ ์ผ๊ณ ์ฌ์ฉ ์์
ํ ์คํธํด ๋ณด๊ธฐ
"postgresql" ์ฌ์ฉ ์ค์ ๋๋ค. Design a users table with email, name, and timestamps
์์ ๊ฒฐ๊ณผ:
- CREATE TABLE users (
- user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
- email TEXT NOT NULL UNIQUE,
- name TEXT NOT NULL,
- created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
- updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
- );
- CREATE UNIQUE INDEX ON users (LOWER(email));
"postgresql" ์ฌ์ฉ ์ค์ ๋๋ค. Should I use UUID or BIGINT for primary keys?
์์ ๊ฒฐ๊ณผ:
- Use BIGINT GENERATED ALWAYS AS IDENTITY when:
- - Sequential IDs are acceptable
- - Index performance is critical
- - Smaller index size matters
- ย
- Use UUID when:
- - Global uniqueness is required
- - ID opacity is a security requirement
- - Merging data from multiple sources
๋ณด์ ๊ฐ์ฌ
์์ All 221 static analyzer findings were determined to be false positives. The skill consists entirely of markdown documentation (SKILL.md) with no executable code. Backtick characters are markdown formatting for code examples, not shell execution. References to security features like Row Level Security are PostgreSQL documentation, not Windows SAM access. The skill provides educational guidance for database schema design with no security risks.
ํ์ง ์ ์
๋ง๋ค ์ ์๋ ๊ฒ
New Application Schema Design
Design a complete database schema for a new web application with proper data types, primary keys, foreign key relationships, and indexes for common query patterns.
Schema Review and Optimization
Review existing table designs for performance issues, missing indexes, inappropriate data types, or constraint gaps that could cause data integrity problems.
Migration Planning
Plan safe schema evolution with transactional DDL, concurrent index creation, and strategies for adding columns to large tables without downtime.
์ด ํ๋กฌํํธ๋ฅผ ์ฌ์ฉํด ๋ณด์ธ์
Design a PostgreSQL table for storing user profiles with fields for email, name, registration date, and optional profile settings. Use appropriate data types and constraints.
I have a queries table with columns: id, user_id, status, created_at. Common queries filter by user_id and status, and sort by created_at descending. Recommend an indexing strategy.
Should I store product attributes in a JSONB column or create separate columns? The attributes vary by product category and users frequently search by specific attributes.
I have an events table growing at 10M rows per month. Queries typically filter by event_date and device_id. Recommend a partitioning strategy and explain trade-offs.
๋ชจ๋ฒ ์ฌ๋ก
- Use TIMESTAMPTZ instead of TIMESTAMP for all event timestamps to avoid timezone confusion
- Add explicit indexes on foreign key columns since PostgreSQL does not create them automatically
- Normalize to 3NF first, then denormalize only for proven high-ROI read performance gains
ํผํ๊ธฐ
- Using VARCHAR with length limits instead of TEXT with CHECK constraints
- Creating indexes on every column without analyzing actual query patterns
- Using SERIAL instead of GENERATED ALWAYS AS IDENTITY for auto-increment columns