Normalized Tables
For1:N and M:N relationships, start by creating a ParadeDB index on each table rather than flattening them into one wide table. Include only the columns needed for search, filtering, sorting, joins, or aggregates (updating unindexed columns can still use Postgres Heap-Only Tuple updates without touching the ParadeDB index):
posts.title rewrites one entry in posts_idx rather than every comment belonging to that post. Foreign keys, constraints, and row-level security policies also remain on their source tables.
At query time, ParadeDB pushes the join, filters, and Top K ordering into the indexes:
posts.id and comments.post_id) with partition_by lets workers join matching key ranges locally without exchanging rows across processes.
When to Denormalize
Keeping data in a single table is still preferable in a few cases:- Multi-valued attributes owned by one entity: When values do not have their own lifecycle or foreign-key references, store them on the parent row using text arrays (such as
tags TEXT[]) or JSONB (such asmetadata JSONB) instead of a separate join table. - Queries outside join pushdown or rank-join limits: Score-ordered rank joins support
INNERand single-sidedSEMIjoins up toLIMIT 1000(see Score-Ordered Joins). Read-heavy workloads that rely on deep pagination or outer joins ordered by combined relevance may run faster against a denormalized table orMATERIALIZED VIEW.
If keeping columns on a single table exceeds Postgres’s 32-column index limit,
pack related fields into a named composite type. See Indexing 32+
Columns.
How Schema Shape Affects BM25 Scoring
Within a single ParadeDB index, a multi-clause BM25 query such astitle ||| 'keyboard' OR body ||| 'keyboard' scores each clause against its own field and sums the matching clause scores. Clauses that do not match contribute 0.0.
Across joined tables, adding pdb.score calls (pdb.score(p.id) + pdb.score(c.id)) combines clauses the same way. Positive scalar multipliers weight one table relative to another, such as 2.0 * pdb.score(p.id) + pdb.score(c.id), matching how ::pdb.boost(...) scales individual clauses within one table.
Whether normalizing fields across tables changes the underlying BM25 numbers comes down to which corpus statistics are per field versus per index:
- Per-field statistics: Term frequency, field token length, total tokens in the field, document frequency (how many rows contain the term in that field), and BM25
k1/bparameters are all tracked independently for each field. A row has no combined cross-field length. - Per-index statistic: The total number of rows in the index is tracked at the table level. BM25 uses this row count for inverse document frequency (IDF) and as the denominator for a field’s average length (rows where a field is
NULLhave token length0and still count toward total rows).
1:1tables with matching row counts produce the same scores as a single table. When every row inusershas a corresponding row inuser_profiles, both indexes have the same row count and the same per-field statistics.pdb.score(u.id) + pdb.score(up.user_id)yields the same BM25 score as indexing all of those fields on one table (aside from minorfloat4rounding or unvacuumed dead rows; see Score Refresh).- Optional
1:0..1tables shift field averages on the sparser table. Ifuser_profilesomits rows for users without a profile instead of storingNULLfields,user_profileshas a smaller total row count thanusers. On its fields, a smaller row count raises average field length and lowers IDF compared to a single table that storesNULLvalues. 1:Nnormalized tables score each entity against its own population. In a join betweenpostsandcomments, post fields use the row count ofpostsand comment fields use the row count ofcomments. Flatteningpostsonto everycommentsrow in a single index would instead duplicate each post’s tokens once per comment, skewing post document frequencies and average field lengths toward heavily commented posts.