Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →You can store translations in PostgreSQL without adding a column for every language. The practical choices are a locale-keyed jsonb column on the existing row or a separate translation table. Neither changes what an application displays by itself: the app must select a locale and apply explicit fallback rules. To avoid a broad rewrite, add a small compatibility layer at the query or model boundary and keep the rest of the application-facing interface stable.
What PostgreSQL can—and cannot—do for application i18n
PostgreSQL supports character sets, locale-sensitive behavior such as collation, and localized server messages. Those features are different from translating product names, descriptions, or other application content. A collation can affect comparisons and sorting; it does not translate a stored value.
As an Amazon Associate I earn from qualifying purchases.
The schema can hold multiple language versions, but some component still has to decide which one a request needs. That component must define locale negotiation, fallback order, and behavior when no translation exists. A reader’s concern in a public discussion put the schema issue plainly: “I don’t want to add an extra column for each supported language.” One JSONB field or a translation relation addresses that column-growth concern; neither supplies language selection automatically.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Choose where the translations belong
Use JSONB for a modest, relatively stable set of translations that is usually read alongside its parent row. Use a translation relation when each translation needs its own relational constraints, workflow state, or row-level management. There is no universal winner: choose based on how translations are queried, changed, validated, and exposed through the existing application.
#1 Best Overall
Option 1: A locale-keyed JSONB column
ALTER TABLE products ADD COLUMN name_i18n jsonb;
-- Example value:
-- {"en": "Hat", "es": "Sombrero", "fr-CA": "Chapeau"}A JSONB object keeps a product’s translated labels next to the product and avoids a physical column per language. Use standardized locale identifiers and a predictable object shape. Decide whether matching requires an exact tag such as fr-CA, whether it may fall back to a language-only tag such as fr, and what the final fallback is. Do not rely on JSON key order to choose a translation.
- Good fit: locale sets can vary by row, translation payloads are manageable, and common reads fetch the parent record with its localized value.
- Validation: JSONB does not by itself ensure that keys are supported locales or that required translations exist. Enforce those rules with application validation or suitable database constraints.
- Indexing: PostgreSQL supports GIN indexes for documented JSONB containment, key-existence, and JSONPath operators. An index helps only when the query predicates use operators it supports; it is not automatically useful for every lookup of a JSON value.
- Updates: changing JSONB changes the containing row, and PostgreSQL locks the whole row for an update. Keep the document reasonably sized and avoid treating it as a separately mutable translation store with frequent concurrent edits.
Option 2: A translation relation
CREATE TABLE product_translation (
product_id bigint NOT NULL REFERENCES products(id),
locale text NOT NULL,
name text NOT NULL,
PRIMARY KEY (product_id, locale)
);This design gives each product-locale translation its own row. The primary key makes the one-translation-per-product-and-locale rule explicit, and ordinary relational constraints can support validation and workflow needs. Reads typically require a join or a separate lookup, and the application still needs a resolver to choose the locale and fallback.
Rank #2
A relation is often a better fit when you need to audit translation completeness, attach review or publication state, or constrain locale values. It is a schema design option, not a built-in PostgreSQL localization framework.
How the options differ
| Concern | JSONB on the parent row | Translation relation |
|---|---|---|
| Storage shape | One object on the existing row, keyed by locale | One row per parent record and locale |
| Read pattern | Can retrieve the parent and its translations together | Usually requires a join or lookup |
| Constraints and workflow | Locale and completeness rules need deliberate validation | Relational keys and per-translation workflow fields are natural to model |
| Update scope | Updates lock the containing row | Each translation is a separate row |
| Indexing | GIN can support suitable JSONB operators | Relational indexes and constraints can support locale-keyed lookup |
Keep the application interface stable with a resolver
If the application still executes SELECT name FROM products, adding name_i18n does not change the returned value. A database migration alone cannot make existing code display a request-specific language. The narrowest useful change is generally a resolver near the query or model boundary, or an adapter such as a view where the application’s SQL and write behavior make that safe.
For example, a resolver can accept the requested locale and a fallback chain, then choose from JSONB or fetch the corresponding translation row. The exact SQL and interface depend on how the application reads and writes the field. Before using a view for compatibility, confirm how inserts and updates behave and whether the ORM depends on particular table metadata.
A generated column is not a general substitute for that resolver. PostgreSQL generated expressions are limited to immutable expressions over the current row and cannot use subqueries, so they cannot dynamically look up a per-request locale or another translation row. Keep request-specific locale selection in an application-aware layer.
Plan an incremental migration
- Map the field’s use. Find reads and writes in application queries, ORM-generated SQL, background jobs, exports, and cache keys. Identify which component can receive the requested locale.
- Add storage without changing current behavior. Add nullable JSONB storage or create the translation relation. Leave the existing field’s meaning intact while translations are populated.
- Define the resolver contract. Specify exact locale matching, fallback order, and the response when a translation is missing. Decide whether the original-language value is the final fallback.
- Populate and validate. Backfill or enter translations, then measure missing values and validate locale and completeness rules before relying on them.
- Check query plans and index needs. Add JSONB indexes only for predicates that use supported operators; test the actual application queries.
- Roll out in stages. Verify locking and deployment behavior for the exact
ALTER TABLEsubform, table, and PostgreSQL version. PostgreSQL documents different lock levels for different forms;ACCESS EXCLUSIVEis the default unless otherwise stated. - Keep a rollback path. Retain the old read and write path until the application consistently uses the intended translation route.
Keep sorting and search separate from translation storage
Sorting and comparisons
Collation determines locale-sensitive comparison and sort behavior; it does not provide translated content. The PostgreSQL 17 documentation defines a collation as “an SQL schema object that maps an SQL name to locales provided by libraries installed in the operating system.” PostgreSQL can use providers including ICU, when enabled in the build, and libc. ICU behavior can vary with the ICU version, while libc locale behavior can differ across platforms.
ICU collations can be customized for language behavior and insensitive comparisons. Nondeterministic collations can treat byte-distinct strings as equal, but carry performance and operational tradeoffs; PostgreSQL’s documentation notes that pattern matching is unavailable for them. If sort order or uniqueness matters, test representative names, accents, and comparisons against the PostgreSQL and ICU build you deploy.
Full-text search
Full-text search uses its own language configurations and dictionaries. Putting translations in JSONB or choosing a collation does not automatically provide suitable tokenization or stemming for each language. Select and validate configurations for the languages the product actually searches, using representative vocabulary.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
