PostgreSQL schema designer

In PostgreSQL, text and varchar(n) have identical storage and performance characteristics — there is no speed penalty for using text. The only difference is that varchar(n) adds a length check constraint. In practice, text is almost always the better choice. Length validation is an application concern that changes over time (email max length was once 254 characters, then RFC 5321 clarified 320), and altering a varchar(n) column to increase its length requires an ACCESS EXCLUSIVE lock on the table prior to PostgreSQL 9.2 and still requires a table rewrite if you decrease the length. If you genuinely need to enforce a maximum length at the database level, prefer text with a CHECK (length(column_name) <= N) constraint — this is easier to modify later because you can drop and recreate the check constraint without rewriting the table. The only exception is if you are interfacing with external systems or standards that require a fixed-length field, in which case char(n) might be appropriate. Never use varchar without a length specifier — it is functionally identical to text but less idiomatic.

Both are valid choices, and the decision depends on your architecture. bigint (8 bytes) is smaller, naturally sequential (which gives excellent B-tree insert performance and cache locality), and human-readable. It is the right default for most single-database applications. UUIDs (16 bytes) are useful when you need globally unique identifiers without coordination — distributed systems, multi-tenant architectures where IDs are exposed in URLs, or when merging data from multiple sources. If you choose UUIDs, strongly prefer UUID v7 (time-ordered, available via the pg_uuidv7 extension or application-side generation) over UUID v4 (random). UUID v4 causes severe B-tree index fragmentation because values are randomly distributed, leading to poor cache utilization and write amplification. UUID v7 is time-sorted, so it preserves insertion order like bigint while still being globally unique. In PostgreSQL 17+, you can also use the built-in uuidv7() function. Never use UUIDs "just in case" — the 2x size increase compared to bigint affects every index, foreign key, and join operation across your entire schema.

JSONB is excellent for semi-structured data that varies per row and is primarily read as a whole document — user preferences, API response caches, product attributes that differ by category, event metadata, and feature flags. It is not a substitute for proper relational modeling. Use relational tables when: you need to query, filter, or join on the data frequently; you need foreign key constraints; the structure is consistent across rows; or you need to update individual fields without rewriting the entire document. A common hybrid approach works well: store stable, queryable fields as regular columns and put variable, read-mostly data in a JSONB column. For example, a products table might have name, price, and category_id as regular columns, and an attributes JSONB column for varying properties like material, dimensions, and certifications. Index JSONB with GIN (column jsonb_path_ops) for containment queries (@>) or create expression indexes on specific keys you filter on frequently. Avoid deeply nested JSONB structures — they are hard to query, hard to validate, and the jsonb_set function for partial updates becomes unwieldy beyond two levels.

Partition a table only when it is large enough that partition pruning provides a meaningful benefit — typically hundreds of millions of rows or when you need efficient bulk deletion (dropping a partition instead of DELETE). PostgreSQL supports three partitioning methods: range, list, and hash. Range partitioning is the most common, used for time-series data (partition by month or quarter) and append-mostly workloads. It enables efficient pruning when queries include a WHERE clause on the partition key, and old data can be detached or dropped without vacuum overhead. List partitioning works for tenant isolation (partition by tenant_id) or categorical data with a small, known set of values. Hash partitioning distributes rows evenly and helps with parallel query execution, but does not support efficient range scans or easy partition management. Choose your partition key based on your most frequent query filter — if 90% of queries filter by created_at, partition by time range. The partition key must be part of the primary key and any unique constraints. Design partitions so that hot queries touch one or two partitions at most. Create partitions ahead of time (automate with pg_partman or a cron job) and monitor for partition bloat. Do not over-partition — hundreds of partitions increase planning time.

Almost always use timestamptz (timestamp with time zone). Despite its name, timestamptz does not store a time zone — it stores a UTC instant. When you insert a value, PostgreSQL converts it from the session time zone to UTC; when you read it, PostgreSQL converts from UTC to the session time zone. This means the same instant is always represented correctly regardless of the client's time zone setting. Plain timestamp (without time zone) stores whatever value you give it with no conversion. This creates subtle bugs: if one application server is configured for UTC and another for US/Eastern, they will insert different UTC instants for the same wall clock time, and your data becomes silently inconsistent. The only valid use case for timestamp without time zone is when you intentionally need a "wall clock" time that should not be converted — for example, storing "the meeting starts at 9:00 AM" as a local time that applies in whatever time zone the viewer is in. For event timestamps, created_at, updated_at, scheduled_at, and any time that represents "when something happened," always use timestamptz. Set your database and application servers to timezone = 'UTC' to avoid surprises.