使用 ALTER TABLE tbl ENABLE ROW LEVEL SECURITY 启用。创建策略：CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())。内置的基于用户的行级访问控制。
约束
主键：隐式 UNIQUE + NOT NULL；创建 B-tree 索引。
外键：指定 ON DELETE/UPDATE 操作（CASCADE、RESTRICT、SET NULL、SET DEFAULT）。在引用列上添加显式索引——加速连接并防止父表删除/更新时的锁定问题。对于在事务结束时检查的循环外键依赖，使用 DEFERRABLE INITIALLY DEFERRED。
唯一约束：创建 B-tree 索引；允许多个 NULL 值，除非使用 NULLS NOT DISTINCT（PG15+）。标准行为：允许 (1, NULL) 和 (1, NULL)。使用 NULLS NOT DISTINCT：只允许一个 (1, NULL)。优先使用 NULLS NOT DISTINCT，除非你特别需要重复的 NULL 值。
检查约束：行级约束；NULL 值通过检查（三值逻辑）。示例：CHECK (price > 0) 允许 NULL 价格。与 NOT NULL 结合以强制执行：price NUMERIC NOT NULL CHECK (price > 0)。
排除约束：使用操作符防止值重叠。EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) 防止房间重复预订。需要适当的索引类型（通常是 GiST）。
索引
B-tree：默认用于相等/范围查询（=、<、>、BETWEEN、ORDER BY）
复合索引：顺序很重要——如果最左前缀相等则使用索引（WHERE a = ? AND b > ? 使用 (a,b) 上的索引，但 WHERE b = ? 不使用）。将选择性最高/最频繁筛选的列放在前面。
覆盖索引：CREATE INDEX ON tbl (id) INCLUDE (name, email) - 包含非键列以实现仅索引扫描，无需访问表。
部分索引：用于热点子集（WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'）。任何带有 status = 'active' 的查询都可以使用此索引。
表达式索引：用于计算的搜索键（CREATE INDEX ON tbl (LOWER(email))）。WHERE 子句中的表达式必须完全匹配：WHERE LOWER(email) = 'user@example.com'。
范围分区：常用于时序数据（PARTITION BY RANGE (created_at)）。创建分区：CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')。TimescaleDB 通过保留策略和压缩自动进行基于时间或 ID 的分区。
列表分区：用于离散值（PARTITION BY LIST (region)）。示例：FOR VALUES IN ('us-east', 'us-west')。
哈希分区：当没有自然键时用于均匀分布（PARTITION BY HASH (user_id)）。使用模数创建 N 个分区。
使用约束来限制列中允许的 JSONB 值，例如 config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')
示例
用户表
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()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);
订单表
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
total NUMERIC(10,2) NOT NULL CHECK (total > 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);
JSONB 表
CREATE TABLE profiles (
user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
attrs JSONB NOT NULL DEFAULT '{}',
theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);
Define a PRIMARY KEY for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness/opacity is needed.
Normalize first (to 3NF) to eliminate data redundancy and update anomalies; denormalize only for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden.
Add NOT NULL everywhere it’s semantically required; use DEFAULT s for common values.
Create indexes for access paths you actually query : PK/unique (auto), FK columns (manual!) , frequent filters/sorts, and join keys.
Prefer TIMESTAMPTZ for event time; NUMERIC for money; TEXT for strings; BIGINT for integer values, DOUBLE PRECISION for floats (or NUMERIC for exact decimal arithmetic).
PostgreSQL “Gotchas”
Identifiers : unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use snake_case for table/column names.
Unique + NULLs : UNIQUE allows multiple NULLs. Use UNIQUE (...) NULLS NOT DISTINCT (PG15+) to restrict to one NULL.
FK indexes : PostgreSQL does not auto-index FK columns. Add them.
No silent coercions : length/precision overflows error out (no truncation). Example: inserting 999 into NUMERIC(2,0) fails with error, unlike some databases that silently truncate or round.
Sequences/identity have gaps (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.
Heap storage : no clustered PK by default (unlike SQL Server/MySQL InnoDB); CLUSTER is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.
MVCC : updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.
Data Types
IDs : BIGINT GENERATED ALWAYS AS IDENTITY preferred (GENERATED BY DEFAULT also fine); UUID when merging/federating/used in a distributed system or for opaque IDs. Generate with uuidv7() (preferred if using PG18+) or gen_random_uuid() (if using an older PG version).
Integers : prefer BIGINT unless storage space is critical; INTEGER for smaller ranges; avoid SMALLINT unless constrained.
Floats : prefer DOUBLE PRECISION over REAL unless storage space is critical. Use for exact decimal arithmetic.
Do not use the following data types
DO NOT use timestamp (without time zone); DO use timestamptz instead.
DO NOT use char(n) or varchar(n); DO use text instead.
DO NOT use money type; DO use numeric instead.
DO NOT use timetz type; DO use timestamptz instead.
DO NOT use timestamptz(0) or any other precision specification; DO use timestamptz instead
Table Types
Regular : default; fully durable, logged.
TEMPORARY : session-scoped, auto-dropped, not logged. Faster for scratch work.
UNLOGGED : persistent but not crash-safe. Faster writes; good for caches/staging.
Row-Level Security
Enable with ALTER TABLE tbl ENABLE ROW LEVEL SECURITY. Create policies: CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id()). Built-in user-based access control at the row level.
Constraints
PK : implicit UNIQUE + NOT NULL; creates a B-tree index.
FK : specify ON DELETE/UPDATE action (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Add explicit index on referencing column—speeds up joins and prevents locking issues on parent deletes/updates. Use DEFERRABLE INITIALLY DEFERRED for circular FK dependencies checked at transaction end.
UNIQUE : creates a B-tree index; allows multiple NULLs unless NULLS NOT DISTINCT (PG15+). Standard behavior: (1, NULL) and (1, NULL) are allowed. With : only one allowed. Prefer unless you specifically need duplicate NULLs.
Indexing
B-tree : default for equality/range queries (=, <, >, BETWEEN, ORDER BY)
Composite : order matters—index used if equality on leftmost prefix (WHERE a = ? AND b > ? uses index on (a,b), but WHERE b = ? does not). Put most selective/frequently filtered columns first.
Covering : CREATE INDEX ON tbl (id) INCLUDE (name, email) - includes non-key columns for index-only scans without visiting table.
Partial : for hot subsets ( → ). Any query with can use this index.
Partitioning
Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date).
Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically
RANGE : common for time-series (PARTITION BY RANGE (created_at)). Create partitions: CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'). TimescaleDB automates time-based or ID-based partitioning with retention policies and compression.
LIST : for discrete values (PARTITION BY LIST (region)). Example: FOR VALUES IN ('us-east', 'us-west').
HASH : for even distribution when no natural key (PARTITION BY HASH (user_id)). Creates N partitions with modulus.
Constraint exclusion : requires CHECK constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).
Special Considerations
Update-Heavy Tables
Separate hot/cold columns —put frequently updated columns in separate table to minimize bloat.
Usefillfactor=90 to leave space for HOT updates that avoid index maintenance.
Avoid updating indexed columns —prevents beneficial HOT updates.
Partition by update patterns —separate frequently updated rows in a different partition from stable data.
Insert-Heavy Workloads
Minimize indexes —only create what you query; every index slows inserts.
UseCOPY or multi-row INSERT instead of single-row inserts.
UNLOGGED tables for rebuildable staging data—much faster writes.
Defer index creation for bulk loads—>drop index, load data, recreate indexes.
Partition by time/hash to distribute load. TimescaleDB automates partitioning and compression of insert-heavy data.
Use a natural key for primary key such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all.
If you do need a surrogate key, PreferBIGINT GENERATED ALWAYS AS IDENTITY over UUID.
Upsert-Friendly Design
Requires UNIQUE index on conflict target columns—ON CONFLICT (col1, col2) needs exact matching unique index (partial indexes don't work).
UseEXCLUDED.column to reference would-be-inserted values; only update columns that actually changed to reduce write overhead.
DO NOTHING faster than DO UPDATE when no actual update needed.
Safe Schema Evolution
Transactional DDL : most DDL operations can run in transactions and be rolled back—BEGIN; ALTER TABLE...; ROLLBACK; for safe testing.
Concurrent index creation : CREATE INDEX CONCURRENTLY avoids blocking writes but can't run in transactions.
Volatile defaults cause rewrites : adding NOT NULL columns with volatile defaults (e.g., now(), gen_random_uuid()) rewrites entire table. Non-volatile defaults are fast.
Drop constraints before columns : ALTER TABLE DROP CONSTRAINT then DROP COLUMN to avoid dependency issues.
Function signature changes : CREATE OR REPLACE with different arguments creates overloads, not replacements. DROP old version if no overload desired.
Generated Columns
... GENERATED ALWAYS AS (<expr>) STORED for computed, indexable fields. PG18+ adds VIRTUAL columns (computed on read, not stored).
Extensions
pgcrypto : crypt() for password hashing.
uuid-ossp : alternative UUID functions; prefer pgcrypto for new projects.
pg_trgm : fuzzy text search with % operator, similarity() function. Index with GIN for LIKE '%pattern%' acceleration.
citext : case-insensitive text type. Prefer expression indexes on LOWER(col) unless you need case-insensitive constraints.
JSONB Guidance
Prefer JSONB with GIN index.
Default: CREATE INDEX ON tbl USING GIN (jsonb_col); → accelerates:
Heavy @> workloads: consider opclass for smaller/faster containment-only indexes:
Examples
Users
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()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);
Orders
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
total NUMERIC(10,2) NOT NULL CHECK (total > 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);
JSONB
CREATE TABLE profiles (
user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
attrs JSONB NOT NULL DEFAULT '{}',
theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);
Strings : prefer TEXT; if length limits needed, use CHECK (LENGTH(col) <= n) instead of VARCHAR(n); avoid CHAR(n). Use BYTEA for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: PLAIN (no TOAST), EXTENDED (compress + out-of-line), EXTERNAL (out-of-line, no compress), MAIN (compress, keep in-line if possible). Default EXTENDED usually optimal. Control with ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy and ALTER TABLE tbl SET (toast_tuple_target = 4096) for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on LOWER(col) (preferred unless column needs case-insensitive PK/FK/UNIQUE) or CITEXT.
Money : NUMERIC(p,s) (never float).
Time : TIMESTAMPTZ for timestamps; DATE for date-only; INTERVAL for durations. Avoid TIMESTAMP (without timezone). Use now() for transaction start time, clock_timestamp() for current wall-clock time.
Booleans : BOOLEAN with NOT NULL constraint unless tri-state values are required.
Enums : CREATE TYPE ... AS ENUM for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) → use TEXT (or INT) + CHECK or lookup table.
Arrays : TEXT[], INTEGER[], etc. Use for ordered lists where you query elements. Index with GIN for containment (@>, <@) and overlap (&&) queries. Access: arr[1] (1-indexed), arr[1:3] (slicing). Good for tags, categories; avoid for relations—use junction tables instead. Literal syntax: '{val1,val2}' or ARRAY[val1,val2].
Range types : daterange, numrange, tstzrange for intervals. Support overlap (&&), containment (@>), operators. Index with GiST. Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer [) (inclusive/exclusive) by default.
Network types : INET for IP addresses, CIDR for network ranges, MACADDR for MAC addresses. Support network operators (<<, >>, &&).
Geometric types : POINT, LINE, POLYGON, CIRCLE for 2D spatial data. Index with GiST. Consider PostGIS for advanced spatial features.
Text search : TSVECTOR for full-text search documents, TSQUERY for search queries. Index tsvector with GIN. Always specify language: to_tsvector('english', col) and to_tsquery('english', 'query'). Never use single-argument versions. This applies to both index expressions and queries.
Domain types : CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$') for reusable custom types with validation. Enforces constraints across tables.
Composite types : CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) for structured data within columns. Access with (col).field syntax.
JSONB : preferred over JSON; index with GIN. Use only for optional/semi-structured attrs. ONLY use JSON if the original ordering of the contents MUST be preserved.
Vector types : vector type by pgvector for vector similarity search for embeddings.
DO NOT use serial type; DO use generated always as identity instead.
NULLS NOT DISTINCT
(1, NULL)
NULLS NOT DISTINCT
CHECK : row-local constraints; NULL values pass the check (three-valued logic). Example: CHECK (price > 0) allows NULL prices. Combine with NOT NULL to enforce: price NUMERIC NOT NULL CHECK (price > 0).
EXCLUDE : prevents overlapping values using operators. EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) prevents double-booking rooms. Requires appropriate index type (often GiST).
WHERE status = 'active'
CREATE INDEX ON tbl (user_id) WHERE status = 'active'
status = 'active'
Expression : for computed search keys (CREATE INDEX ON tbl (LOWER(email))). Expression must match exactly in WHERE clause: WHERE LOWER(email) = 'user@example.com'.
BRIN : very large, naturally ordered data (time-series)—minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after CLUSTER).
Prefer declarative partitioning or hypertables. Do NOT use table inheritance.
Limitations : no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers.
btree_gin/btree_gist: enable mixed-type indexes (e.g., GIN index on both JSONB and text columns).
hstore : key-value pairs; mostly superseded by JSONB but useful for simple string mappings.
timescaledb : essential for time-series—automated partitioning, retention, compression, continuous aggregates.
postgis : comprehensive geospatial support beyond basic geometric types—essential for location-based applications.
pgvector : vector similarity search for embeddings.
pgaudit : audit logging for all database activity.
jsonb_path_ops
CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
Trade-off : loses support for key existence (?, ?|, ?&) queries—only supports containment (@>)
Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression):
ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;
CREATE INDEX ON tbl (price);
Prefer queries like WHERE price BETWEEN 100 AND 500 (uses B-tree) over WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500 without index.
Arrays inside JSONB: use GIN + @> for containment (e.g., tags). Consider jsonb_path_ops if only doing containment.
Keep core relations in tables; use JSONB for optional/variable attributes.
Use constraints to limit allowed JSONB values in a column e.g. config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')
