# 10 Postgres Schema Design Mistakes That Kill Performance

> Avoid the most common Postgres schema design mistakes — from misused data types and missing indexes to over-normalization and unbounded array columns.

- **Published:** 2025-12-11
- **Author:** Clixo
- **Reading time:** 6 min read
- **Tags:** postgres, schema-design, performance, common-mistakes, database
- **Canonical URL:** https://clixo.sh/blog/postgres-schema-design-mistakes-that-kill-performance

Bad schema design has a delayed cost. The application ships, data accumulates, and six months later queries that once returned in milliseconds now take seconds. The schema decision made at the start of the project is the hardest thing to change. Here are the most common Postgres schema design mistakes that teams discover the hard way — and how to avoid them.

```mermaid
flowchart TD
  A[New Postgres schema] --> B["Use TEXT not VARCHAR(255)"]
  B --> C["Use NUMERIC or BIGINT for money, not FLOAT"]
  C --> D["Index every foreign key column"]
  D --> E["Use IDENTITY columns, not SERIAL"]
  E --> F["Apply NOT NULL constraints aggressively"]
  F --> G["Use TIMESTAMPTZ not TIMESTAMP"]
  G --> H["Add created_at and updated_at with a trigger"]
```

## 1. Using VARCHAR(255) Everywhere

The `VARCHAR(255)` pattern is inherited from older databases where string length constraints had storage implications. In Postgres, `VARCHAR(255)` and `TEXT` use identical storage. There is no performance difference between them.

The problem is that `VARCHAR(255)` creates an arbitrary limit that will eventually be wrong. A name field limited to 255 characters seems safe until an edge case breaks it. Use `TEXT` for variable-length strings unless you have a genuine domain constraint — and if you have one, enforce it with a `CHECK` constraint that communicates intent rather than a magical 255.

## 2. Storing Monetary Values as FLOAT

Floating-point arithmetic is imprecise. Storing prices, fees, or any financial value as `FLOAT` or `DOUBLE PRECISION` introduces rounding errors that accumulate over calculations.

Use `NUMERIC(precision, scale)` for money, or store values as integer cents with a `BIGINT`. `NUMERIC` is exact; `FLOAT` is not.

```sql
-- Avoid
price FLOAT,

-- Use instead
price_cents BIGINT NOT NULL,
-- or
price NUMERIC(12, 2) NOT NULL
```

## 3. Missing Indexes on Foreign Keys

Postgres does not automatically index foreign key columns. When you join `order_items` to `orders` on `order_id`, Postgres will scan every row in `order_items` unless you explicitly create the index.

This is one of the most common and consequential schema oversights. Every foreign key column should have an index unless you have profiled the query and confirmed the table is small enough that a sequential scan is always faster.

```sql
CREATE INDEX ON order_items (order_id);
CREATE INDEX ON payments (order_id);
```

## 4. Using SERIAL Instead of Identity Columns or UUID v7

`SERIAL` is a pseudo-type that creates a sequence and sets a default. It looks clean but has subtle issues: the sequence is not strictly tied to the column, `SERIAL` does not appear in `information_schema.columns` as expected, and dropping and re-adding the column leaves orphaned sequences.

Prefer `GENERATED ALWAYS AS IDENTITY` (SQL standard) or `uuid_generate_v7()` (Postgres 17+) for distributed-safe IDs with natural sort order. If you use sequential integers, `BIGINT GENERATED ALWAYS AS IDENTITY` is the correct form.

## 5. Unbounded Arrays as a Substitute for Relationship Tables

Storing related IDs as a Postgres array is tempting:

```sql
tag_ids INTEGER[]
```

Arrays work for small, static sets that you never need to join or query individually. They break down when you need to:

- Filter rows where a specific tag ID is present (requires a GIN index and a containment query)
- Update a single element (no atomic element update; requires a full array replacement)
- Join tag data to tags table (no clean join syntax)

The correct model is a join table. Use arrays for denormalization only after the join table is already the source of truth and you have a concrete read-performance reason to add the array.

## 6. Columns That Allow NULL When the Domain Does Not

Allowing `NULL` on a column that should never be null adds a category of bugs that the database could have prevented. Application code must handle `NULL` in every query, aggregate function, and comparison. `NULL != NULL` in SQL, so comparisons silently fail.

Apply `NOT NULL` constraints aggressively. Only allow `NULL` when the absence of a value is a meaningful state in your domain — not simply because you were unsure at schema design time.

## 7. Using JSONB as a Replacement for a Proper Schema

JSONB is powerful and appropriate for genuinely dynamic data: user-defined fields, event payloads, third-party API responses. It is a poor substitute for a proper relational schema.

Storing structured, predictable attributes like `first_name`, `email`, and `created_at` inside a JSONB column because it feels flexible creates real problems:

- No type enforcement at the database level
- No foreign key constraints possible on values inside JSON
- GIN indexes are larger and slower to write than B-tree indexes on regular columns
- Query syntax is less readable and more error-prone

Use JSONB for the edge cases it solves. Model predictable data as typed columns.

## 8. Not Using Timestamps with Time Zone

`TIMESTAMP` stores a timestamp without time zone information. `TIMESTAMPTZ` stores UTC and converts to the session's configured timezone on display.

Always use `TIMESTAMPTZ`. Using plain `TIMESTAMP` stores local time with no timezone context, which causes incorrect results when the application runs in different timezones or when daylight saving time changes occur. The storage cost is identical.

```sql
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
```

## 9. Over-Normalizing Hot Read Paths

Third Normal Form is the right starting point. But a schema that requires a five-way join to render a product listing page will be slow and fragile at scale. Normalization reduces redundancy and enforces consistency; it does not mean a join count of zero is wrong but a join count of five is always right.

Profile your hot read paths early. When a specific query pattern is clearly expensive and the access pattern is stable, selective denormalization — a materialized column, a summary table, or a materialized view — is a legitimate and pragmatic choice.

## 10. No Updated_at Trigger or Timestamps at All

Debugging data problems without knowing when a row was last modified is genuinely difficult. Every table that represents a mutable entity should carry `created_at` and `updated_at` columns.

`updated_at` must be updated by the database, not trusted to the application:

```sql
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();
```

Application code can forget to set `updated_at`. The trigger cannot.

---

If your team is starting a new product or inheriting a Postgres schema that has accumulated years of these mistakes, Clixo can help you design or refactor it properly. [Start a build](https://clixo.sh/#contact) and we will review your data model.

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)