ldhnam
Công nghệ, kinh tế, và những suy nghĩ rời rạc.Technology, economics, and scattered thoughts.
← PostgreSQL DBA← PostgreSQL DBA
PostgreSQL DBAPostgreSQL DBA19 Th7, 2026Jul 19, 202632 phút đọc26 min read

Schema Design Pattern & Anti-patternSchema Design Patterns & Anti-Patterns

Thuộc bộ kiến thức PostgreSQL DBA Roadmap.

Tổng quan

Lý thuyết normalization cho bạn biết làm sao để có một schema đúng: loại bỏ redundancy, tránh update anomaly, đảm bảo mỗi fact chỉ tồn tại đúng một nơi. Hầu hết DBA học các normal form một lần, vượt qua kỳ thi hay phỏng vấn nào đó cần đến chúng, rồi hiếm khi nghĩ về chúng một cách tường minh nữa — vì trên thực tế, những lỗi xuất hiện trong schema thật hiếm khi là “table này không đạt 3NF.” Chúng cụ thể hơn và tầm thường hơn nhiều: một column âm thầm lưu danh sách ID phân tách bằng dấu phẩy, một table key/value tổng quát được gắn thêm vì “không muốn cứ phải thêm column,” một reference_id trỏ đến table khác nhau tuỳ vào một cờ reference_type, một column price được khai báo kiểu float. Mỗi thứ này đều compile được, chạy được, và thường qua được code review, rồi từng thứ một trở thành nguồn gốc của data corruption, query không thể đánh index, hoặc bug âm thầm nhiều tháng hoặc nhiều năm sau.

Note này giả định bạn đã biết normalization là gì — để xem định nghĩa chính thức và ví dụ đầy đủ từ 1NF đến BCNF, xem Relational Databases trong phần backend. Ở đây, mục tiêu khác: liệt kê các anti-pattern cụ thể lặp đi lặp lại trong các schema PostgreSQL thực tế, cho thấy chúng trông như thế nào bằng SQL thực, và cách khắc phục. Note cũng đề cập đến mặt ngược lại — những trường hợp việc cố tình phá vỡ normalization là quyết định kỹ thuật đúng đắn, không phải sai lầm, miễn là được làm một cách có chủ đích và có kế hoạch.

Kiến thức nền tảng

Normal form, tóm tắt nhanh

Một cái nhìn nhanh, đủ để làm khung cho mọi thứ tiếp theo:

FormQuy tắcKhắc phục
1NFMỗi column chỉ chứa một giá trị đơn, atomic; không có repeating groupTách column đa giá trị thành các row hoặc một table liên quan
2NF1NF, cộng với mọi column không phải key phụ thuộc vào toàn bộ primary key (chỉ liên quan khi có composite key)Loại bỏ partial dependency bằng cách tách các column chỉ phụ thuộc vào một phần của key
3NF2NF, cộng với không có column không phải key nào phụ thuộc vào một column không phải key khác (không có transitive dependency)Đưa các fact phái sinh/phụ thuộc vào table riêng của chúng
BCNFMọi determinant (vế trái của một functional dependency) đều là candidate keyXử lý các edge case mà 3NF bỏ sót khi có nhiều candidate key chồng lấn nhau

Một ví dụ ngắn gọn: một table orders duy nhất với các column order_id, customer_id, customer_email, product_id, product_name, quantity vi phạm 3NF hai lần — customer_email phụ thuộc vào customer_id, không phải vào order_id (transitive dependency), và product_name phụ thuộc vào product_id, không phải vào order_id. Cách khắc phục là cách kinh điển: tách thành orders(order_id, customer_id, product_id, quantity), customers(customer_id, customer_email), và products(product_id, product_name). Một khi mỗi fact chỉ tồn tại đúng một nơi, việc cập nhật email của customer hay tên của product chỉ là một UPDATE một row, thay vì một lần rewrite nhiều row có thể trôi lệch mất đồng bộ.

Phần đó là địa hạt đã quá quen thuộc. Phần còn lại của note này nói về những gì xảy ra khi một schema về mặt kỹ thuật tuân thủ các normal form ở các table “chính” nhưng vẫn sai ở một column cụ thể, hoặc một mối quan hệ cụ thể — các anti-pattern bên dưới không phải là vi phạm 1NF/2NF/3NF theo nghĩa hình thức; chúng là những lựa chọn thiết kế trông hợp lý, compile ổn, và vẫn gây thiệt hại thật.

Denormalization có chủ đích và denormalization vô tình

Denormalization — cố tình lưu dữ liệu redundant hoặc phái sinh để tránh một join hoặc một phép tính lại — không tự động là xấu. Điểm phân biệt quan trọng nằm ở chủ đích và quy trình, không phải ở việc dữ liệu bị trùng lặp:

Những lý do phổ biến, chính đáng để denormalize một cách chủ đích:

Kỷ luật quan trọng là giữ các bản copy đồng bộ một cách chủ đích, thay vì hy vọng code ứng dụng nhớ cập nhật cả hai nơi:

-- Denormalization có chủ đích: products.category_name là bản copy cache của
-- categories.name, được giữ đồng bộ bởi trigger. categories.name là authoritative.
CREATE TABLE categories (
    id   serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE products (
    id            serial PRIMARY KEY,
    name          text NOT NULL,
    category_id   integer NOT NULL REFERENCES categories(id),
    category_name text NOT NULL  -- cache denormalized, được ghi chú rõ ràng
);

CREATE OR REPLACE FUNCTION sync_product_category_name()
RETURNS trigger AS $$
BEGIN
    UPDATE products
       SET category_name = NEW.name
     WHERE category_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_category_name
AFTER UPDATE OF name ON categories
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name)
EXECUTE FUNCTION sync_product_category_name();

Một lựa chọn thay thế cho cache column duy trì bằng trigger là materialized view, refresh theo lịch (REFRESH MATERIALIZED VIEW CONCURRENTLY), thường dễ suy luận hơn cho denormalization kiểu reporting khi độ tươi gần-thời-gian-thực là chấp nhận được. Trigger cho logic thủ tục kiểu này được nói sâu hơn trong Procedures, Functions & Triggers.

Khái niệm chính

Anti-pattern: Jaywalking (danh sách ID phân tách bằng dấu phẩy trong một column)

“Jaywalking” — thuật ngữ từ cuốn SQL Antipatterns của Bill Karwin — là việc lưu một danh sách ID liên quan dưới dạng chuỗi phân cách trong một column duy nhất, thay vì một mối quan hệ many-to-many đúng đắn thông qua junction table. Nó xuất hiện liên tục trong các schema bắt đầu từ prototype: ai đó cần liên kết một product với nhiều tag, thêm một column text, và nối các ID bằng dấu phẩy vì nó nhanh hơn thiết kế junction table tại thời điểm đó.

-- Anti-pattern: danh sách tag ID phân tách bằng dấu phẩy
CREATE TABLE products_bad (
    id       serial PRIMARY KEY,
    name     text NOT NULL,
    tag_ids  text  -- ví dụ '3,7,12'
);

-- Mọi query trở thành thao tác string thay vì join
SELECT * FROM products_bad WHERE tag_ids LIKE '%7%';        -- cũng match '17', '27', '71'...
SELECT * FROM products_bad WHERE '7' = ANY(string_to_array(tag_ids, ','));  -- chạy được, nhưng không đánh index bình thường được, không enforce FK được

Các vấn đề chồng chất nhanh chóng:

Cách khắc phục là junction table many-to-many tiêu chuẩn:

CREATE TABLE tags (
    id   serial PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE products (
    id   serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE product_tags (
    product_id integer NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    tag_id     integer NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, tag_id)
);

-- Sạch sẽ, đánh index được, đảm bảo referential
SELECT p.* FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE t.name = 'sale';

-- "Product có cả tag 7 và tag 12"
SELECT product_id FROM product_tags
WHERE tag_id IN (7, 12)
GROUP BY product_id
HAVING COUNT(DISTINCT tag_id) = 2;

(Nếu yêu cầu thực sự chỉ là một tập hợp nhỏ, cố định, cardinality thấp các flag thay vì một mối quan hệ many-to-many mở, một column array của PostgreSQL với GIN index — tag_ids integer[] đánh index USING gin (tag_ids) — là một lựa chọn thay thế hợp lệ, query được, cho jaywalking. Khác biệt so với anti-pattern là đây là một kiểu integer[] thật mà database hiểu và đánh index được, chứ không phải một blob text phi cấu trúc bạn tự parse bằng tay.)

Anti-pattern: lạm dụng Entity-Attribute-Value (EAV)

EAV model hoá một object “tổng quát” thành các row có dạng (entity_id, attribute_name, value), để việc thêm một attribute mới không bao giờ cần schema migration — bạn chỉ cần insert row với một attribute_name mới. Nó hấp dẫn vì cảm giác future-proof: “chúng ta không biết product sẽ cần attribute gì, nên đừng khoá mình vào các column cố định.”

-- Anti-pattern: table EAV tổng quát
CREATE TABLE product_attributes_eav (
    entity_id      integer NOT NULL REFERENCES products(id),
    attribute_name text NOT NULL,
    attribute_value text  -- mọi thứ đều là text; không có type safety
);

INSERT INTO product_attributes_eav VALUES
    (1, 'color', 'red'),
    (1, 'weight_kg', '1.5'),
    (1, 'in_stock', 'true');

-- Lấy lại "row" đầy đủ của một product đòi hỏi phải pivot
SELECT
    entity_id,
    MAX(attribute_value) FILTER (WHERE attribute_name = 'color')      AS color,
    MAX(attribute_value) FILTER (WHERE attribute_name = 'weight_kg')  AS weight_kg,
    MAX(attribute_value) FILTER (WHERE attribute_name = 'in_stock')   AS in_stock
FROM product_attributes_eav
WHERE entity_id = 1
GROUP BY entity_id;

Cái giá phải trả là thật và ngày càng chồng chất:

EAV thỉnh thoảng được biện minh khi attribute thực sự do người dùng định nghĩa và không giới hạn về số lượng lẫn kiểu — một form builder nơi end user tạo custom field tuỳ ý, hoặc một catalog sản phẩm trải rộng qua các category cực kỳ đa dạng (điện tử, quần áo, tạp hoá) mà không tập column cố định nào có thể bao quát hợp lý hết được. Ngay cả khi đó, kiểu jsonb của PostgreSQL thường là một cách hiện thực hoá tốt hơn cho cùng ý tưởng so với một table EAV theo nghĩa đen: nó giữ các attribute tuỳ ý, thưa, tiến hoá theo thời gian trong một column duy nhất mỗi entity, hỗ trợ đánh index qua GIN, và tránh được cái giá của pivot/self-join.

-- Cách hiện thực hoá tốt hơn cho "attribute linh hoạt" dùng jsonb
CREATE TABLE products (
    id         serial PRIMARY KEY,
    name       text NOT NULL,
    attributes jsonb NOT NULL DEFAULT '{}'
);

CREATE INDEX idx_products_attributes ON products USING gin (attributes);

INSERT INTO products (name, attributes) VALUES
    ('Widget', '{"color": "red", "weight_kg": 1.5, "in_stock": true}');

SELECT * FROM products WHERE attributes @> '{"color": "red", "in_stock": true}';
SELECT (attributes->>'weight_kg')::numeric FROM products WHERE id = 1;

Nếu dùng vì lười biếng — để né việc viết migration cho một vài attribute thực sự đã biết rõ, cố định — EAV (hay một cột jsonb chứa mọi thứ) là quyết định sai: những attribute đó nên là các column thật, có kiểu, có constraint thật. Bài kiểm tra là liệu tập attribute có thực sự mở và tiến hoá theo từng row, hay chỉ đơn giản là bất tiện khi migrate.

Anti-pattern: polymorphic association

Một polymorphic association là một column có hình dạng foreign key nhưng trỏ đến table khác nhau tuỳ vào giá trị của một column “type” đi kèm — ví dụ, một table comments với commentable_id và commentable_type, trong đó commentable_type = 'post' nghĩa là commentable_id trỏ đến posts.id, còn commentable_type = 'photo' nghĩa là nó trỏ đến photos.id.

-- Anti-pattern: polymorphic association
CREATE TABLE comments_bad (
    id               serial PRIMARY KEY,
    body             text NOT NULL,
    commentable_type text NOT NULL,   -- 'post' hoặc 'photo'
    commentable_id   integer NOT NULL -- trỏ đến posts.id HOẶC photos.id tuỳ type
    -- Không thể tạo FOREIGN KEY ở đây, vì một column duy nhất
    -- không thể reference hai table khác nhau một cách có điều kiện.
);

Điều này phá vỡ relational model ngay từ nền tảng: không thể khai báo một FOREIGN KEY constraint thật, vì FOREIGN KEY phải nhắm đến chính xác một table. Không gì trong database ngăn commentable_id trỏ đến một row không tồn tại, hoặc tồn tại nhưng ở sai table so với type đã khai báo (commentable_type = 'post' nhưng commentable_id thực ra lại là một photos.id hợp lệ, không phải posts.id). Mọi bên tiêu thụ table này phải tự tái tạo logic join bằng CASE/UNION, và cascading delete phải được cài đặt thủ công trong code ứng dụng hoặc trigger thay vì khai báo.

Hai lựa chọn thay thế sạch sẽ:

1. Một join table cho mỗi loại liên kết. Dài dòng nhưng được ràng buộc đầy đủ — mỗi mối quan hệ có foreign key thật:

CREATE TABLE comments (
    id   serial PRIMARY KEY,
    body text NOT NULL
);

CREATE TABLE post_comments (
    comment_id integer PRIMARY KEY REFERENCES comments(id) ON DELETE CASCADE,
    post_id    integer NOT NULL REFERENCES posts(id) ON DELETE CASCADE
);

CREATE TABLE photo_comments (
    comment_id integer PRIMARY KEY REFERENCES comments(id) ON DELETE CASCADE,
    photo_id   integer NOT NULL REFERENCES photos(id) ON DELETE CASCADE
);

-- Comment của một post cụ thể
SELECT c.* FROM comments c
JOIN post_comments pc ON pc.comment_id = c.id
WHERE pc.post_id = 42;

2. Cấu trúc supertype/subtype, khi các thứ “commentable” đủ giống nhau về hình dạng chung để xứng đáng có một supertype table thật:

CREATE TABLE commentables (
    id   serial PRIMARY KEY,
    kind text NOT NULL CHECK (kind IN ('post', 'photo'))
);

CREATE TABLE posts (
    id          integer PRIMARY KEY REFERENCES commentables(id),
    title       text NOT NULL,
    body        text NOT NULL
);

CREATE TABLE photos (
    id       integer PRIMARY KEY REFERENCES commentables(id),
    url      text NOT NULL
);

CREATE TABLE comments (
    id             serial PRIMARY KEY,
    body           text NOT NULL,
    commentable_id integer NOT NULL REFERENCES commentables(id) ON DELETE CASCADE
);

-- Mọi comment đều có một FK thật, được enforce, bất kể nó gắn với thứ gì
SELECT c.* FROM comments c WHERE c.commentable_id = 42;

Cách tiếp cận supertype/subtype thường tốt hơn khi các loại “commentable” mới sẽ được thêm theo thời gian và bạn muốn một bề mặt foreign key duy nhất cho bất cứ thứ gì có thể chứa comment; cách tiếp cận join table theo từng loại tốt hơn khi tập các loại liên kết nhỏ, cố định, và mỗi mối quan hệ có attribute hoặc quy tắc cardinality khác biệt đáng kể.

Anti-pattern: lưu tiền dưới dạng float

Lưu giá trị tiền tệ dưới dạng float/real/double precision là một trong những sai lầm schema phổ biến nhất và dễ tránh nhất, vì floating point IEEE-754 không thể biểu diễn chính xác hầu hết các phân số thập phân — 0.1 + 0.2 nổi tiếng là không bằng 0.3 trong binary floating point, và sự thiếu chính xác này cũng áp dụng cho tiền.

-- Anti-pattern
CREATE TABLE line_items_bad (
    id       serial PRIMARY KEY,
    price    double precision NOT NULL,
    quantity integer NOT NULL
);

INSERT INTO line_items_bad (price, quantity) VALUES (19.99, 3);
SELECT price * quantity FROM line_items_bad;
-- Có thể không in ra chính xác 59.97 một khi bạn bắt đầu cộng nhiều row,
-- và so sánh kiểu `WHERE total = 59.97` có thể âm thầm không match.

Cách khắc phục, không có nhược điểm thực sự nào cho các workload tiền tệ điển hình, là numeric (còn gọi là decimal), lưu một biểu diễn thập phân chính xác thay vì một xấp xỉ nhị phân:

CREATE TABLE line_items (
    id       serial PRIMARY KEY,
    price    numeric(10, 2) NOT NULL CHECK (price >= 0),
    quantity integer NOT NULL CHECK (quantity > 0)
);

INSERT INTO line_items (price, quantity) VALUES (19.99, 3);
SELECT price * quantity AS total FROM line_items;  -- chính xác: 59.97

numeric tốn kém tính toán hơn floating point gốc một chút, nhưng đối với đại đa số ứng dụng, chi phí này không đáng kể so với việc đảm bảo tính đúng đắn. Xem Data Types & Schema Objects để có cái nhìn đầy đủ về các kiểu số của PostgreSQL và khi nào dùng loại nào.

Anti-pattern: SELECT * gắn cứng vào tầng schema

SELECT * trong code ứng dụng là một code smell đã được biết đến rộng rãi, nhưng nó trở thành vấn đề schema cụ thể khi được gắn cứng vào các database object mà thứ khác phụ thuộc vào — phổ biến nhất là một view định nghĩa bằng SELECT * FROM some_table.

-- Anti-pattern: một view re-export toàn bộ column của base table
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE status = 'active';

Vấn đề lộ ra ngay lần đầu tiên ai đó thêm một column vào customers: view âm thầm bắt đầu trả về cả column đó, có thể phơi bày một column nhạy cảm mới (ví dụ, một ssn_last4 hoặc internal_notes vừa được thêm) cho mọi bên tiêu thụ view mà không ai quyết định điều đó nên xảy ra. Ngược lại, đổi tên hoặc xoá một column trên base table làm hỏng view (và mọi thứ xây trên nó) theo cách dễ bị bỏ sót cho đến khi một query downstream lỗi.

-- Khắc phục: khai báo rõ ràng các column mà view được thiết kế để phơi bày
CREATE VIEW active_customers AS
SELECT id, name, email, created_at
FROM customers
WHERE status = 'active';

Một danh sách column tường minh biến view thành một hợp đồng thật, ổn định: thêm column vào base table không làm thay đổi output của view, và xoá/đổi tên một column view phụ thuộc vào sẽ lỗi rõ ràng (định nghĩa view hỏng) thay vì âm thầm thay đổi những gì được phơi bày. Cùng lý luận đó áp dụng cho function và stored procedure trả về SELECT * FROM ... — hãy khai báo tường minh các column trả về.

Anti-pattern: bỏ foreign key “vì hiệu năng”

Một lập luận lặp đi lặp lại để bỏ foreign key constraint là chúng thêm chi phí ghi — mỗi INSERT/UPDATE trên table tham chiếu phải kiểm tra row được tham chiếu có tồn tại, và mỗi DELETE/UPDATE trên table được tham chiếu phải kiểm tra các dependent. Chi phí đó là có thật, nhưng trong đại đa số schema nó chỉ nhỏ (một lookup có index) so với chi phí data corruption mà nó ngăn chặn.

-- "Tối ưu": không có FK, chỉ enforce trong code ứng dụng
CREATE TABLE orders_bad (
    id          serial PRIMARY KEY,
    customer_id integer NOT NULL  -- không phải FK thật
);

Một khi constraint biến mất, không gì trong database ngăn một row orders_bad tham chiếu đến một customer_id đã bị xoá, chưa từng tồn tại, hoặc thuộc về một tenant khác trong một schema multi-tenant. Mọi code path ứng dụng, mọi script backfill, mọi phiên psql ad-hoc, và mọi kỹ sư trong tương lai đều phải tự nhớ giữ tính toàn vẹn một cách độc lập — và trên thực tế, cuối cùng sẽ có một trong số đó không nhớ. Các row mồ côi sau đó xuất hiện dưới dạng bug kiểu null-pointer trong reporting, join bị hỏng, hoặc mất dữ liệu âm thầm được phát hiện nhiều tháng sau, đắt đỏ hơn nhiều để truy vết so với chi phí ghi ban đầu.

-- Mặc định đúng đắn: khai báo constraint, để database enforce nó
CREATE TABLE orders (
    id          serial PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(id)
);

Có những ngoại lệ hẹp, đã được đo đạc — các write path throughput cực cao (hàng triệu insert/giây) nơi một bottleneck cụ thể, đã được profile, được truy vết là do FK check, hoặc kiến trúc partitioned/sharded nơi table được tham chiếu thực sự không nằm trong cùng database và PostgreSQL hoàn toàn không thể enforce constraint. Ngay cả trong những trường hợp đó, câu trả lời thông thường là giữ constraint và xử lý bottleneck cụ thể (ví dụ, batching, deferred constraint checking với DEFERRABLE INITIALLY DEFERRED, hoặc một partitioning key khác) thay vì loại bỏ referential integrity như bước đầu tiên. Bỏ foreign key như một tối ưu hoá mặc định, thay vì phản ứng với một vấn đề đã đo đạc, đánh đổi một chi phí nhỏ, đã hiểu rõ lấy một chi phí mở và lớn hơn nhiều.

Anti-pattern: đánh index mọi column theo kiểu phòng thủ

Hình ảnh ngược lại của việc bỏ foreign key là over-indexing: thêm index vào mọi column “phòng khi có ai query,” hoặc thêm index mới mỗi khi một pattern query mới xuất hiện mà không bao giờ xoá index cũ. Mọi index đều phải được cập nhật ở mỗi INSERT, UPDATE (của column được đánh index), và DELETE, nên một table với mười lăm index phải trả chi phí ghi đó mười lăm lần, và pg_stat_user_indexes trên hầu hết các table production đánh index nặng nề tiết lộ các index chưa từng được scan lần nào. Điều này được nói sâu, gồm cả cách tìm và xoá index không dùng đến, trong Indexing Strategies; phiên bản ngắn ở đây là index nên được thêm một cách có chủ đích để đáp ứng một pattern query mà table thực sự phục vụ — không phải để phòng thủ trước các query giả định trong tương lai.

Anti-pattern trong query đáng được gọi tên

Thiết kế schema và thiết kế query không tách rời hoàn toàn — một số sai lầm “query” gây thiệt hại nhất thực ra là về cách schema bị tiêu thụ, và chúng làm trầm trọng thêm các anti-pattern ở tầng schema nói trên.

Cross join ngầm định do thiếu điều kiện join

Quên một điều kiện join (hoặc viết cú pháp comma-join kiểu cũ và bỏ mất mệnh đề WHERE) âm thầm biến một join dự định thành một cross join đầy đủ — mọi row của một table ghép với mọi row của table kia:

-- Bug: không có điều kiện join giữa orders và customers
SELECT o.id, c.name
FROM orders o, customers c;
-- Trả về (số row trong orders) * (số row trong customers) row, không phải kết quả one-to-one dự định

Điều này đặc biệt nguy hiểm vì nó không báo lỗi — nó chỉ âm thầm trả về một tập kết quả lớn hơn nhiều và sai, có thể không bị phát hiện cho đến khi ai đó nhận ra tổng bị nhân đôi trong một báo cáo. Cách khắc phục luôn là một JOIN ... ON tường minh (hoặc predicate join trong mệnh đề WHERE cho cú pháp comma-join cũ), và chạy EXPLAIN trên bất kỳ query nào chạm nhiều table để kiểm tra sơ bộ số row ước tính trước khi tin vào kết quả.

SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

Pattern N+1 query từ ORM

Một ORM lazy-load các object liên quan có thể biến thứ lẽ ra chỉ là một query thành N + 1: một query để lấy danh sách parent, rồi một query bổ sung cho mỗi parent để lấy các child của nó.

-- Những gì thực sự được gửi đến PostgreSQL cho "orders kèm tên customer",
-- một query danh sách order...
SELECT * FROM orders WHERE status = 'pending';
-- ...theo sau bởi một query cho mỗi row (N+1) thay vì một join duy nhất:
SELECT * FROM customers WHERE id = 101;
SELECT * FROM customers WHERE id = 102;
SELECT * FROM customers WHERE id = 103;
-- ...

Mỗi query riêng lẻ có thể nhanh, nhưng độ trễ round-trip nhân lên theo số row, và pattern này thường không thấy rõ cho đến khi một table vượt quá vài trăm row trong production. Cách khắc phục ở tầng schema không có gì đặc biệt — các table thường ổn — cách khắc phục là ở tầng ứng dụng: eager-load association bằng một join duy nhất hoặc một batch query IN (...), hoặc dùng bất cứ gì ORM gọi là “includes”/“prefetch related” cho mối quan hệ đó.

SELECT * trong query production

Ngoài vấn đề view ở tầng schema nói trên, SELECT * trong query ứng dụng có chi phí riêng: nó lấy về các column không ai cần (trả chi phí I/O và network cho chúng), nó phá vỡ index-only scan (PostgreSQL không thể thoả mãn query chỉ từ index nếu nó cần các column mà index không bao phủ), và nó âm thầm thay đổi hình dạng của mọi result set mỗi khi một column được thêm vào table. Khai báo rõ các column bạn thực sự cần là một trong những chiến thắng rẻ nhất về hiệu năng và tính ổn định.

Đưa một query mới lên table lớn mà không dùng EXPLAIN

Một query trả về kết quả đúng trên database dev 500 row có thể hoạt động hoàn toàn khác trên một table production 50 triệu row — một index bị thiếu, một ước tính số row sai, hoặc một plan flip phụ thuộc dữ liệu (ví dụ, PostgreSQL chọn sequential scan một khi predicate không còn selective) có thể biến một query dưới một mili giây thành một query mất nhiều giây. Chạy EXPLAIN (ANALYZE, BUFFERS) trên dữ liệu kích thước production trước khi đưa một query mới lên — hoặc tối thiểu, trên một khối lượng dữ liệu thực tế — là bảo hiểm rẻ nhất chống lại loại bất ngờ này, và được nói sâu cùng với việc chọn index trong Indexing Strategies.

Best Practices

Pattern đáng áp dụng một cách chủ đích

Không phải mọi thứ lệch khỏi một schema “thuần” normalized, hard-delete đều là anti-pattern — một số độ lệch là pattern đã được hiểu rõ, được lựa chọn có chủ đích với tradeoff đã biết:

Soft delete. Thay vì DELETE FROM orders WHERE id = 42, đánh dấu row bằng một column deleted_at timestamptz và lọc nó ra trong query:

ALTER TABLE orders ADD COLUMN deleted_at timestamptz;

-- "Xoá"
UPDATE orders SET deleted_at = now() WHERE id = 42;

-- Mọi query phải nhớ loại trừ các row đã soft-delete
SELECT * FROM orders WHERE deleted_at IS NULL;

Điều này bảo tồn lịch sử, hỗ trợ “undo,” và giữ nguyên các tham chiếu foreign key thay vì cascading xoá thật qua các table liên quan. Tuy nhiên tradeoff là có thật: mọi query lên table — không chỉ những query rõ ràng, mà mọi join trong tương lai, mọi query reporting, mọi phiên psql ad-hoc — đều phải nhớ thêm WHERE deleted_at IS NULL, và quên nó là một bug về tính đúng đắn âm thầm, không phải một lỗi ồn ào. Một unique constraint trên, chẳng hạn, email cũng cần được suy nghĩ lại, vì nếu không hai row “đã xoá” và một row active có thể xung đột với nhau:

-- Partial unique index: tính duy nhất chỉ được enforce trong các row chưa xoá
CREATE UNIQUE INDEX idx_customers_email_active
    ON customers (email)
    WHERE deleted_at IS NULL;

Index nói chung cũng hưởng lợi từ cách xử lý partial-index tương tự — một CREATE INDEX ON orders (customer_id) thông thường giữ mãi mãi việc đánh index cả các row đã soft-delete, nên CREATE INDEX ... ON orders (customer_id) WHERE deleted_at IS NULL (hoặc một view/row-security policy đóng gói bộ lọc đó) giữ cho cả index lẫn mô hình tư duy nhỏ gọn hơn.

Table audit/history. Thay vì cố tái tạo “cái gì đã thay đổi và khi nào” từ log ứng dụng, một table history duy trì bằng trigger ghi lại trực tiếp mọi thay đổi trong database:

CREATE TABLE orders_history (
    history_id  serial PRIMARY KEY,
    order_id    integer NOT NULL,
    changed_at  timestamptz NOT NULL DEFAULT now(),
    operation   text NOT NULL,   -- 'INSERT', 'UPDATE', 'DELETE'
    old_row     jsonb,
    new_row     jsonb
);

CREATE OR REPLACE FUNCTION log_orders_change()
RETURNS trigger AS $$
BEGIN
    INSERT INTO orders_history (order_id, operation, old_row, new_row)
    VALUES (
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_history
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION log_orders_change();

Pattern này — và các trigger đứng sau nó — được nói đầy đủ trong Procedures, Functions & Triggers. Tradeoff là storage tăng trưởng (table history có thể lớn hơn nhiều so với base table theo thời gian) và cần một chính sách retention/archival, nhưng đối với compliance, debug, và các câu hỏi “ai đã thay đổi cái này và khi nào,” nó thường đáng giá.

Surrogate key và natural key. Một surrogate key là một định danh do database sinh ra, không mang ý nghĩa nghiệp vụ (id serial/GENERATED ALWAYS AS IDENTITY, hoặc một uuid); một natural key là một giá trị mang ý nghĩa nghiệp vụ đã tự định danh duy nhất một row (một mã quốc gia ISO, một số căn cước quốc gia, một địa chỉ email, một SKU).

-- Surrogate key: ổn định ngay cả khi định danh "tự nhiên" thay đổi
CREATE TABLE employees (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_no text NOT NULL UNIQUE,  -- định danh nghiệp vụ, vẫn có thể thay đổi
    name        text NOT NULL
);

-- Natural key: đôi khi hoàn toàn phù hợp
CREATE TABLE countries (
    iso_code char(2) PRIMARY KEY,  -- 'US', 'VN' — thực sự ổn định và có ý nghĩa
    name     text NOT NULL
);

Surrogate key là mặc định an toàn hơn cho hầu hết entity vì các định danh nghiệp vụ có thói quen thay đổi theo những cách không ai lường trước lúc thiết kế — một mã nhân viên bị gán lại sau một vụ sáp nhập, một địa chỉ email thay đổi, một SKU bị tái cấu trúc — và việc thay đổi primary key sẽ lan toả qua mọi foreign key tham chiếu đến nó. Natural key hợp lý khi giá trị thực sự bất biến và đã mang ý nghĩa đáng để phơi bày trực tiếp (mã quốc gia/tiền tệ ISO là ví dụ kinh điển), và khi bạn không muốn thêm lớp gián tiếp của một surrogate cho thứ sẽ không bao giờ cần đến nó.

Bảng tham chiếu anti-pattern

Anti-patternTriệu chứng bạn sẽ nhận raCách khắc phục
Jaywalking (ID dạng CSV trong một column)Không đánh index/join được trên từng ID; LIKE '%7%' false positive; ID mồ côi không có FK để bắt lỗiJunction table (hoặc integer[] + GIN nếu thực sự chỉ là tập flag)
Lạm dụng EAVQuery cần pivot/self-join cho mỗi attribute; không có type safety; dữ liệu sai chỉ lỗi lúc đọcColumn thật, có kiểu cho attribute đã biết; jsonb cho attribute thực sự mở
Polymorphic associationKhông thể tạo FOREIGN KEY thật; tham chiếu mồ côi/sai lệch lọt quaJoin table theo từng loại, hoặc cấu trúc table supertype/subtype
Tiền dưới dạng floatTổng cộng không khớp chính xác; WHERE total = x bỏ sót row do làm trònnumeric(p, s)
SELECT * trong view/functionThêm column vào base table âm thầm thay đổi những gì được phơi bày downstreamDanh sách column tường minh trong định nghĩa view/function
Không có foreign key (“vì hiệu năng”)Row mồ côi, join hỏng, data corruption phát hiện lâu sau đóThêm FOREIGN KEY; xử lý cụ thể một bottleneck đã đo đạc nếu có
Đánh index mọi columnGhi chậm trên diện rộng; pg_stat_user_indexes cho thấy nhiều index không lần nào được scanChỉ đánh index thứ phục vụ một pattern query thực sự; xoá index không dùng
Cross join ngầm địnhSố row lớn hơn nhiều so với dự kiến; tổng bị nhân đôi trong báo cáoJOIN ... ON tường minh; kiểm tra sơ bộ ước tính row bằng EXPLAIN
N+1 query từ ORMSố lượng query tăng theo số row; độ trễ tăng tuyến tính theo kích thước danh sáchEager-load/batch association vào một query duy nhất

Tài liệu tham khảo

Part of the PostgreSQL DBA Roadmap knowledge base.

Overview

Normalization theory tells you how to get a schema correct: eliminate redundancy, avoid update anomalies, make every fact live in exactly one place. Most DBAs learn the normal forms once, pass whatever exam or interview required them, and rarely think about them explicitly again — because in practice, the failures that show up in real schemas are rarely “this table isn’t in 3NF.” They’re much more specific and much more mundane: a column that quietly stores a comma-separated list of IDs, a generic key/value table bolted on because “we didn’t want to keep adding columns,” a reference_id that points to different tables depending on a reference_type flag, a price column typed as float. Each of these compiles, runs, and often survives code review, and each one becomes a source of data corruption, unindexable queries, or silent bugs months or years later.

This note assumes you already know what normalization is — for the formal definitions and worked examples of 1NF through BCNF, see Relational Databases in the backend section. Here, the goal is different: cataloguing the specific anti-patterns that recur across real-world PostgreSQL schemas, showing what each one looks like in actual SQL, and showing the fix. It also covers the flip side — cases where deliberately breaking normalization is the right engineering call, not a mistake, provided it’s done consciously and with a plan.

Fundamentals

Normal forms, briefly

A fast recap, enough to frame everything that follows:

FormRuleFixes
1NFEvery column holds a single, atomic value; no repeating groupsSplits multi-valued columns into rows or a related table
2NF1NF, plus every non-key column depends on the whole primary key (relevant only for composite keys)Removes partial dependencies by splitting off columns that depend on part of the key
3NF2NF, plus no non-key column depends on another non-key column (no transitive dependencies)Removes derived/dependent facts into their own table
BCNFEvery determinant (left side of a functional dependency) is a candidate keyHandles edge cases 3NF misses when multiple overlapping candidate keys exist

A compact example: a single orders table with columns order_id, customer_id, customer_email, product_id, product_name, quantity violates 3NF twice over — customer_email depends on customer_id, not on order_id (transitive dependency), and product_name depends on product_id, not on order_id. The fix is the textbook one: split into orders(order_id, customer_id, product_id, quantity), customers(customer_id, customer_email), and products(product_id, product_name). Once each fact lives in exactly one place, updating a customer’s email or a product’s name is a single-row UPDATE instead of a multi-row rewrite that can drift out of sync.

That part is well-trodden ground. The rest of this note is about what happens when a schema technically respects the normal forms in its “main” tables but still goes wrong in one specific column, or one specific relationship — the anti-patterns below are not violations of 1NF/2NF/3NF in the formal sense; they’re design choices that look reasonable, compile fine, and still cause real damage.

Deliberate denormalization vs accidental denormalization

Denormalization — deliberately storing redundant or derived data to avoid a join or a recomputation — is not automatically bad. The distinction that matters is intent and process, not the raw fact of duplicated data:

Common, legitimate reasons to denormalize on purpose:

The key discipline is keeping the copies in sync deliberately, not hoping application code remembers to update both places:

-- Deliberate denormalization: products.category_name is a cached copy of
-- categories.name, kept in sync by trigger. categories.name is authoritative.
CREATE TABLE categories (
    id   serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE products (
    id            serial PRIMARY KEY,
    name          text NOT NULL,
    category_id   integer NOT NULL REFERENCES categories(id),
    category_name text NOT NULL  -- denormalized cache, documented as such
);

CREATE OR REPLACE FUNCTION sync_product_category_name()
RETURNS trigger AS $$
BEGIN
    UPDATE products
       SET category_name = NEW.name
     WHERE category_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_category_name
AFTER UPDATE OF name ON categories
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name)
EXECUTE FUNCTION sync_product_category_name();

An alternative to a trigger-maintained cache column is a materialized view, refreshed on a schedule (REFRESH MATERIALIZED VIEW CONCURRENTLY), which is often simpler to reason about for reporting-style denormalization where near-real-time freshness is acceptable. Triggers for procedural logic like this are covered in more depth in Procedures, Functions & Triggers.

Key Concepts

Anti-pattern: Jaywalking (comma-separated IDs in a column)

“Jaywalking” — a term from Bill Karwin’s SQL Antipatterns — is storing a list of related IDs as a delimited string in a single column instead of a proper many-to-many relationship through a junction table. It shows up constantly in schemas that started as prototypes: someone needed to associate a product with multiple tags, added a text column, and joined the IDs with commas because it was faster than designing a junction table at the time.

-- Anti-pattern: comma-separated list of tag IDs
CREATE TABLE products_bad (
    id       serial PRIMARY KEY,
    name     text NOT NULL,
    tag_ids  text  -- e.g. '3,7,12'
);

-- Every query becomes string manipulation instead of a join
SELECT * FROM products_bad WHERE tag_ids LIKE '%7%';        -- also matches '17', '27', '71'...
SELECT * FROM products_bad WHERE '7' = ANY(string_to_array(tag_ids, ','));  -- works, but can't be indexed normally, can't enforce FK

The problems compound quickly:

The fix is the standard many-to-many junction table:

CREATE TABLE tags (
    id   serial PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE products (
    id   serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE product_tags (
    product_id integer NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    tag_id     integer NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, tag_id)
);

-- Clean, indexable, referentially sound
SELECT p.* FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE t.name = 'sale';

-- "Products with both tag 7 and tag 12"
SELECT product_id FROM product_tags
WHERE tag_id IN (7, 12)
GROUP BY product_id
HAVING COUNT(DISTINCT tag_id) = 2;

(If the requirement is genuinely a small, fixed, low-cardinality set of flags rather than an open-ended many-to-many relationship, a PostgreSQL array column with a GIN index — tag_ids integer[] indexed USING gin (tag_ids) — is a legitimate, queryable alternative to jaywalking. The difference from the anti-pattern is that it’s a real integer[] type the database understands and can index, not an unstructured text blob you parse by hand.)

Anti-pattern: Entity-Attribute-Value (EAV) overuse

EAV models a “generic” object as rows in a table shaped like (entity_id, attribute_name, value), so that adding a new attribute never requires a schema migration — you just insert rows with a new attribute_name. It’s tempting because it feels future-proof: “we don’t know what attributes products will need, so let’s not lock ourselves into columns.”

-- Anti-pattern: generic EAV table
CREATE TABLE product_attributes_eav (
    entity_id      integer NOT NULL REFERENCES products(id),
    attribute_name text NOT NULL,
    attribute_value text  -- everything is text; no type safety
);

INSERT INTO product_attributes_eav VALUES
    (1, 'color', 'red'),
    (1, 'weight_kg', '1.5'),
    (1, 'in_stock', 'true');

-- Getting a single product's full "row" back requires pivoting
SELECT
    entity_id,
    MAX(attribute_value) FILTER (WHERE attribute_name = 'color')      AS color,
    MAX(attribute_value) FILTER (WHERE attribute_name = 'weight_kg')  AS weight_kg,
    MAX(attribute_value) FILTER (WHERE attribute_name = 'in_stock')   AS in_stock
FROM product_attributes_eav
WHERE entity_id = 1
GROUP BY entity_id;

The costs are real and compounding:

EAV is occasionally justified when attributes are genuinely user-defined and unbounded in number and type — a form builder where end users create arbitrary custom fields, or a product catalog spanning wildly heterogeneous categories (electronics, clothing, groceries) where no fixed column set could reasonably cover them all. Even then, PostgreSQL’s jsonb type is usually a better implementation of the same idea than a literal EAV table: it keeps arbitrary, sparse, evolving attributes in a single column per entity, supports indexing via GIN, and avoids the pivot/self-join tax.

-- A better implementation of "flexible attributes" using jsonb
CREATE TABLE products (
    id         serial PRIMARY KEY,
    name       text NOT NULL,
    attributes jsonb NOT NULL DEFAULT '{}'
);

CREATE INDEX idx_products_attributes ON products USING gin (attributes);

INSERT INTO products (name, attributes) VALUES
    ('Widget', '{"color": "red", "weight_kg": 1.5, "in_stock": true}');

SELECT * FROM products WHERE attributes @> '{"color": "red", "in_stock": true}';
SELECT (attributes->>'weight_kg')::numeric FROM products WHERE id = 1;

Used out of laziness — to dodge writing a migration for a handful of genuinely well-known, fixed attributes — EAV (or a jsonb catch-all) is the wrong call: those attributes should just be real, typed columns with real constraints. The test is whether the attribute set is genuinely open-ended and evolving per-row, or just inconvenient to migrate.

Anti-pattern: polymorphic associations

A polymorphic association is a foreign-key-shaped column that points to different tables depending on the value of a sibling “type” column — for example, a comments table with commentable_id and commentable_type, where commentable_type = 'post' means commentable_id refers to posts.id, and commentable_type = 'photo' means it refers to photos.id.

-- Anti-pattern: polymorphic association
CREATE TABLE comments_bad (
    id               serial PRIMARY KEY,
    body             text NOT NULL,
    commentable_type text NOT NULL,   -- 'post' or 'photo'
    commentable_id   integer NOT NULL -- points to posts.id OR photos.id depending on type
    -- No FOREIGN KEY is possible here, because a single column can't
    -- reference two different tables conditionally.
);

This breaks the relational model at its foundation: a real FOREIGN KEY constraint cannot be declared, because a FOREIGN KEY targets exactly one table. Nothing in the database stops commentable_id from pointing at a row that doesn’t exist, or existing in the wrong table for its stated type (commentable_type = 'post' but commentable_id is actually a valid photos.id, not a posts.id). Every consumer of the table has to re-derive the join logic with a CASE/UNION, and cascading deletes have to be implemented manually in application code or triggers rather than declaratively.

Two clean alternatives:

1. A join table per associated type. Verbose but fully constrained — each relationship gets a real foreign key:

CREATE TABLE comments (
    id   serial PRIMARY KEY,
    body text NOT NULL
);

CREATE TABLE post_comments (
    comment_id integer PRIMARY KEY REFERENCES comments(id) ON DELETE CASCADE,
    post_id    integer NOT NULL REFERENCES posts(id) ON DELETE CASCADE
);

CREATE TABLE photo_comments (
    comment_id integer PRIMARY KEY REFERENCES comments(id) ON DELETE CASCADE,
    photo_id   integer NOT NULL REFERENCES photos(id) ON DELETE CASCADE
);

-- Comments on a given post
SELECT c.* FROM comments c
JOIN post_comments pc ON pc.comment_id = c.id
WHERE pc.post_id = 42;

2. A supertype/subtype structure, when the “commentable” things share enough of a common shape to warrant a real supertype table:

CREATE TABLE commentables (
    id   serial PRIMARY KEY,
    kind text NOT NULL CHECK (kind IN ('post', 'photo'))
);

CREATE TABLE posts (
    id          integer PRIMARY KEY REFERENCES commentables(id),
    title       text NOT NULL,
    body        text NOT NULL
);

CREATE TABLE photos (
    id       integer PRIMARY KEY REFERENCES commentables(id),
    url      text NOT NULL
);

CREATE TABLE comments (
    id             serial PRIMARY KEY,
    body           text NOT NULL,
    commentable_id integer NOT NULL REFERENCES commentables(id) ON DELETE CASCADE
);

-- Every comment has one real, enforced FK, regardless of what it's attached to
SELECT c.* FROM comments c WHERE c.commentable_id = 42;

The supertype/subtype approach is usually preferable when new “commentable” types will be added over time and you want a single foreign key surface for anything that can hold comments; the per-type join table approach is preferable when the set of associated types is small, fixed, and each relationship has meaningfully different attributes or cardinality rules.

Anti-pattern: money as float

Storing monetary values as float/real/double precision is one of the most common and most avoidable schema mistakes, because IEEE-754 floating point cannot represent most decimal fractions exactly — 0.1 + 0.2 famously doesn’t equal 0.3 in binary floating point, and the same imprecision applies to money.

-- Anti-pattern
CREATE TABLE line_items_bad (
    id       serial PRIMARY KEY,
    price    double precision NOT NULL,
    quantity integer NOT NULL
);

INSERT INTO line_items_bad (price, quantity) VALUES (19.99, 3);
SELECT price * quantity FROM line_items_bad;
-- May not print exactly 59.97 once you start summing many rows,
-- and comparisons like `WHERE total = 59.97` can silently fail to match.

The fix, with no real downside for typical monetary workloads, is numeric (a.k.a. decimal), which stores an exact decimal representation rather than a binary approximation:

CREATE TABLE line_items (
    id       serial PRIMARY KEY,
    price    numeric(10, 2) NOT NULL CHECK (price >= 0),
    quantity integer NOT NULL CHECK (quantity > 0)
);

INSERT INTO line_items (price, quantity) VALUES (19.99, 3);
SELECT price * quantity AS total FROM line_items;  -- exact: 59.97

numeric is slightly more expensive computationally than native floating point, but for the vast majority of applications this cost is irrelevant next to the correctness guarantee. See Data Types & Schema Objects for the full rundown of PostgreSQL’s numeric types and when each applies.

Anti-pattern: SELECT * baked into the schema layer

SELECT * inside application code is a well-known code smell, but it becomes a schema problem specifically when it’s baked into database objects that other things depend on — most commonly a view defined as SELECT * FROM some_table.

-- Anti-pattern: a view that re-exports every column of the base table
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE status = 'active';

The problem surfaces the first time someone adds a column to customers: the view silently starts returning it too, potentially exposing a new sensitive column (e.g., a newly added ssn_last4 or internal_notes) to every consumer of the view without anyone deciding that should happen. Conversely, renaming or dropping a column on the base table breaks the view (and everything built on it) in a way that’s easy to miss until a downstream query fails.

-- Fix: name the columns the view is contractually meant to expose
CREATE VIEW active_customers AS
SELECT id, name, email, created_at
FROM customers
WHERE status = 'active';

An explicit column list turns the view into a real, stable contract: adding a column to the base table doesn’t change the view’s output, and removing/renaming a column the view depends on fails loudly (view definition breaks) rather than silently changing what’s exposed. The same reasoning applies to functions and stored procedures that return SELECT * FROM ... — declare the return columns explicitly.

Anti-pattern: skipping foreign keys “for performance”

A recurring argument for dropping foreign key constraints is that they add write overhead — every INSERT/UPDATE on the referencing table has to check that the referenced row exists, and every DELETE/UPDATE on the referenced table has to check for dependents. That overhead is real, but in the overwhelming majority of schemas it’s marginal (an indexed lookup) next to the cost of data corruption it prevents.

-- "Optimized": no FK, enforced only in application code
CREATE TABLE orders_bad (
    id          serial PRIMARY KEY,
    customer_id integer NOT NULL  -- not a real FK
);

Once the constraint is gone, nothing in the database stops an orders_bad row from referencing a customer_id that was deleted, never existed, or belongs to a different tenant in a multi-tenant schema. Every application code path, every backfill script, every ad-hoc psql session, and every future engineer has to independently remember to preserve integrity — and in practice, one of them eventually won’t. Orphaned rows then surface as null-pointer-style bugs in reporting, broken joins, or silent data loss discovered months later, which is far more expensive to track down than the original write overhead would ever have cost.

-- Correct default: declare the constraint, let the database enforce it
CREATE TABLE orders (
    id          serial PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(id)
);

There are narrow, measured exceptions — extremely high-throughput write paths (millions of inserts/second) where a specific, profiled bottleneck has been traced to FK checks, or partitioned/sharded architectures where the referenced table genuinely doesn’t live in the same database and PostgreSQL cannot enforce the constraint at all. Even in those cases, the usual answer is to keep the constraint and address the specific bottleneck (e.g., batching, deferred constraint checking with DEFERRABLE INITIALLY DEFERRED, or a different partitioning key) rather than removing referential integrity as the first move. Dropping foreign keys as a default optimization, rather than a response to a measured problem, trades a small and well-understood cost for an open-ended and much larger one.

Anti-pattern: indexing every column defensively

The mirror image of skipping foreign keys is over-indexing: adding an index to every column “just in case it’s queried,” or adding a new index every time a new query pattern shows up without ever removing old ones. Every index has to be updated on every INSERT, UPDATE (of an indexed column), and DELETE, so a table with fifteen indexes pays that write cost fifteen times over, and pg_stat_user_indexes on most heavily-indexed production tables reveals indexes that have never been scanned. This is covered in depth, including how to find and remove unused indexes, in Indexing Strategies; the short version here is that indexes should be added deliberately in response to a query pattern the table actually serves — not defensively against hypothetical future queries.

Query anti-patterns worth naming

Schema design and query design aren’t fully separable — several of the most damaging “query” mistakes are really about how the schema gets consumed, and they compound the schema-level anti-patterns above.

Implicit cross joins from a missing join condition

Forgetting a join condition (or writing old-style comma-join syntax and dropping a WHERE clause) silently turns an intended join into a full cross join — every row of one table paired with every row of the other:

-- Bug: no join condition between orders and customers
SELECT o.id, c.name
FROM orders o, customers c;
-- Returns (rows in orders) * (rows in customers) rows, not the intended one-to-one match

This is especially dangerous because it doesn’t error — it just silently returns a much larger, wrong result set, which may go unnoticed until someone spots duplicated totals in a report. The fix is always an explicit JOIN ... ON (or WHERE join predicate for legacy comma-join syntax), and running EXPLAIN on any query touching multiple tables to sanity-check the estimated row count before trusting the output.

SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

N+1 query patterns from ORMs

An ORM that lazily loads related objects can turn what should be one query into N + 1: one query to fetch a list of parents, then one additional query per parent to fetch its children.

-- What actually gets sent to PostgreSQL for "orders with their customer name",
-- one order-list query...
SELECT * FROM orders WHERE status = 'pending';
-- ...followed by one query per row (N+1) instead of a single join:
SELECT * FROM customers WHERE id = 101;
SELECT * FROM customers WHERE id = 102;
SELECT * FROM customers WHERE id = 103;
-- ...

Each individual query might be fast, but the round-trip latency multiplies with row count, and the pattern often isn’t visible until a table grows past a few hundred rows in production. The schema-level fix is nothing special — the tables are usually fine — the fix is application-level: eager-load the association with a single join or IN (...) batch query, or use whatever the ORM calls “includes”/“prefetch related” for that relationship.

SELECT * in production queries

Beyond the schema-layer view problem above, SELECT * inside application queries has its own cost: it fetches columns nobody needed (paying I/O and network cost for them), it defeats index-only scans (PostgreSQL can’t satisfy the query from the index alone if it needs columns the index doesn’t cover), and it silently changes the shape of every result set whenever a column is added to the table. Naming the columns you actually need is one of the cheapest performance and stability wins available.

Shipping a new query against a large table without EXPLAIN

A query that returns correct results in a 500-row development database can behave completely differently against a 50-million-row production table — a missing index, a bad row-count estimate, or a data-dependent plan flip (e.g., PostgreSQL choosing a sequential scan once a predicate stops being selective) can turn a sub-millisecond query into a multi-second one. Running EXPLAIN (ANALYZE, BUFFERS) against production-sized data before shipping a new query — or at minimum, against a realistic data volume — is the cheapest insurance against this class of surprise, and it’s covered in depth alongside index selection in Indexing Strategies.

Best Practices

Patterns worth adopting deliberately

Not everything that deviates from a “pure” normalized, hard-delete schema is an anti-pattern — some deviations are well-understood, deliberately-chosen patterns with known tradeoffs:

Soft deletes. Instead of DELETE FROM orders WHERE id = 42, mark the row with a deleted_at timestamptz column and filter it out in queries:

ALTER TABLE orders ADD COLUMN deleted_at timestamptz;

-- "Delete"
UPDATE orders SET deleted_at = now() WHERE id = 42;

-- Every query must remember to exclude soft-deleted rows
SELECT * FROM orders WHERE deleted_at IS NULL;

This preserves history, supports “undo,” and keeps foreign-key references intact instead of cascading real deletes through related tables. The tradeoffs are real, though: every query against the table — not just the obvious ones, but every future join, every reporting query, every ad-hoc psql session — has to remember to add WHERE deleted_at IS NULL, and forgetting it is a silent correctness bug, not a loud error. A unique constraint on, say, email also needs rethinking, since two “deleted” rows and one active row could otherwise collide:

-- Partial unique index: uniqueness only enforced among non-deleted rows
CREATE UNIQUE INDEX idx_customers_email_active
    ON customers (email)
    WHERE deleted_at IS NULL;

Indexes generally benefit from the same partial-index treatment — a plain CREATE INDEX ON orders (customer_id) keeps indexing soft-deleted rows forever, so CREATE INDEX ... ON orders (customer_id) WHERE deleted_at IS NULL (or a view/row-security policy that encapsulates the filter) keeps both the index and the mental model smaller.

Audit/history tables. Rather than trying to reconstruct “what changed and when” from application logs, a trigger-maintained history table captures every change directly in the database:

CREATE TABLE orders_history (
    history_id  serial PRIMARY KEY,
    order_id    integer NOT NULL,
    changed_at  timestamptz NOT NULL DEFAULT now(),
    operation   text NOT NULL,   -- 'INSERT', 'UPDATE', 'DELETE'
    old_row     jsonb,
    new_row     jsonb
);

CREATE OR REPLACE FUNCTION log_orders_change()
RETURNS trigger AS $$
BEGIN
    INSERT INTO orders_history (order_id, operation, old_row, new_row)
    VALUES (
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_history
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION log_orders_change();

This pattern — and the triggers underlying it — is covered in full in Procedures, Functions & Triggers. The tradeoff is storage growth (history tables can dwarf the base table over time) and the need for a retention/archival policy, but for compliance, debugging, and “who changed this and when” questions, it’s usually worth it.

Surrogate keys vs natural keys. A surrogate key is a database-generated identifier with no business meaning (id serial/GENERATED ALWAYS AS IDENTITY, or a uuid); a natural key is a business-meaningful value that already uniquely identifies a row (an ISO country code, a national ID number, an email address, a SKU).

-- Surrogate key: stable even if the "natural" identifier changes
CREATE TABLE employees (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_no text NOT NULL UNIQUE,  -- business identifier, can still change
    name        text NOT NULL
);

-- Natural key: sometimes entirely appropriate
CREATE TABLE countries (
    iso_code char(2) PRIMARY KEY,  -- 'US', 'VN' — genuinely stable and meaningful
    name     text NOT NULL
);

Surrogate keys are the safer default for most entities because business identifiers have a habit of changing in ways nobody expects at design time — an employee number gets reassigned after a merger, an email address changes, a SKU gets restructured — and a primary key change cascades through every foreign key referencing it. Natural keys make sense when the value is genuinely immutable and already carries meaning worth exposing directly (ISO country/currency codes are the canonical example), and when you don’t want the indirection of a surrogate for something that will never need one.

Anti-pattern reference table

Anti-patternSymptom you’ll noticeThe fix
Jaywalking (CSV IDs in a column)Can’t index/join on individual IDs; LIKE '%7%' false positives; orphaned IDs with no FK to catch themJunction table (or integer[] + GIN if truly just a flag set)
EAV overuseQueries need pivots/self-joins per attribute; no type safety; bad data only fails at read timeReal typed columns for known attributes; jsonb for genuinely open-ended ones
Polymorphic associationNo real FOREIGN KEY possible; orphaned/mismatched references slip throughPer-type join tables, or supertype/subtype table structure
Money as floatTotals don’t add up exactly; WHERE total = x misses rows due to roundingnumeric(p, s)
SELECT * in views/functionsAdding a column to the base table silently changes what’s exposed downstreamExplicit column lists in view/function definitions
No foreign keys (“for performance”)Orphaned rows, broken joins, data corruption discovered long after the factAdd the FOREIGN KEY; address a measured bottleneck specifically if one exists
Indexing every columnSlow writes across the board; pg_stat_user_indexes shows many indexes with zero scansIndex only what serves an actual query pattern; drop unused indexes
Implicit cross joinRow counts far larger than expected; duplicated totals in reportsExplicit JOIN ... ON; sanity-check row estimates with EXPLAIN
N+1 queries from an ORMQuery count scales with row count; latency grows linearly with list sizeEager-load/batch the association into one query

References