Check Collation Semantics Before Accepting a PostgreSQL Migration

Separate changed text comparisons, dependent-index evidence and version-metadata refresh when reviewing a PostgreSQL source and an RDS target.

Matching rows do not establish that a PostgreSQL migration preserves text comparisons. Before accepting the target, identify the effective collation of each critical operation, inspect the stored objects that depend on it, and compare independently expected application behavior against supplied source and target observations. Keep three decisions separate: whether the new behavior is acceptable, whether affected objects were rebuilt and retested, and whether recorded version metadata can be refreshed.

This article uses PostgreSQL 17 documentation and fictional source and RDS target aliases. No database was contacted, no SQL was executed, and no real patch, provider version or query result was observed. The companion fixture checks a small supplied evidence packet. It cannot compare strings as PostgreSQL would, discover affected indexes, authenticate an export or authorize a repair. An actual source and target remain unverified until their owners collect the required evidence.

The reader is a database or application engineer reviewing a migration with text-sensitive search, ordering, pagination or identity rules. The useful output is one completed compatibility record for one operation and its declared dependencies. Broad cutover sequencing belongs to the migration compatibility and recovery paper; this article resolves the narrower comparison question before that decision.

1. Establish whether a collation change is in scope

AWS documents an independent default collation library in modern RDS for PostgreSQL releases to reduce changes associated with glibc updates. That protection prevents a useful article from becoming an unsupported warning that every RDS upgrade changes text ordering. Read the current RDS collation guidance, then identify the actual database default and any explicit collation used by the operation. Do not extend a default-library statement to an arbitrary ICU or user-defined collation.

A source outside RDS, a deliberately changed collation, or a different expression can still create a compatibility question. Write down what is changing before prescribing maintenance. A move from one environment to another does not prove changed ordering, and a warning does not prove that a particular index is already corrupt. Both require investigation of the selected dependency and observable behavior. Conversely, unchanged row values cannot rule out a changed comparison.

Separate the platform discussion from application policy. A customer catalogue may require language-aware display ordering, while a machine identifier requires a deliberately byte-sensitive rule. Neither contract should be guessed from the database's default name. Ask the application owner which operation must preserve behavior, whether an intentional change is permitted, and which consumers rely on the old result. Include background exports and cursor consumers, not only the visible page.

2. Identify the effective operation, not only its column

In PostgreSQL 17, collation is associated with expressions as well as columns. An explicit clause can change the comparison used by a query. The collation concepts also distinguish deterministic comparisons, which break otherwise equal comparisons bytewise, from nondeterministic comparisons that can equate different byte representations. Do not claim that every library-version change makes previously distinct identifiers equal. Record the actual deterministic setting and operation.

Preserve the query or expression revision, ordering direction, null treatment, operator and stable tie-breaker. For a paginated catalogue, record both the comparison key and the unique record key used to settle ties. A changed first page could otherwise come from an incomplete ordering clause, a different dataset or a concurrent write rather than collation. The evidence needs to distinguish those explanations before attributing a cause.

Index and query evidence should refer to the same operation. A simple column inventory can miss a computed key or a partial index predicate. PostgreSQL's index catalogue provides per-key collation references and separate expression/predicate fields. Use those definitions to design an authorized read-only inventory; do not treat one table column's label as the complete dependency of every index and query on that table.

3. Resolve database defaults and inventory exclusions

The collation catalogue distinguishes the database-default provider from libc, ICU and builtin providers. A default marker is a reference to resolve, not a complete provider description. The database catalogue carries its own locale-provider, encoding, locale and recorded-version fields. Keep database-level and named-collation evidence separate. Record schema-qualified names and environment identity; local object OIDs are not portable business identities across a migration.

For version evidence, PostgreSQL documents separate actual-version functions for a named collation and a database default. A missing value needs an explained treatment. It must not be rewritten into an invented version or silently counted as equal. This article's deliberately narrow fixture holds null version fields instead of modeling every legitimate unversioned provider. That is a fixture exclusion, not a claim that all null versions indicate a broken database.

The dependency inventory needs an explicit completeness argument. Dependency tracking does not discover every reference hidden inside a string-defined function body. The dependency catalogue also omits dependency entries for pinned objects. Therefore a query joining only explicit collation dependencies cannot establish that every default-dependent object or application statement has been covered. Supplement it with the selected index/expression definitions, database defaults and application query inventory. State unresolved consumers instead of reporting a misleading empty list.

4. Keep rebuild evidence and catalog refresh separate

PostgreSQL's ALTER COLLATION notes explain why a changed definition can invalidate assumptions in stored objects. They distinguish rebuilding dependent objects from refreshing the recorded version. Refresh makes the catalog record current; it does not verify that the rebuild was completed correctly. A quiet log after refresh cannot replace object-level readback and application retests.

The database owner should retain the original version evidence before any authorized maintenance. For each declared affected object, record its definition revision, why it is included, the chosen maintenance method, actual receipt and post-maintenance checks. An operator who refreshes metadata first has changed one diagnostic observation without establishing that the underlying dependency is safe. Preserve that history and hold missing evidence rather than reconstructing a reassuring old screenshot.

Rebuilding under the new rules can still leave an application behavior that the product owner rejects. For example, a supplied target order might be internally consistent while changing which item a saved pagination cursor returns. The database and application decisions need different owners. One reviews the selected object's maintenance evidence; the other accepts or rejects the operation's resulting behavior. Neither grants production write authority.

There is no production repair command here. REINDEX documentation describes operational restrictions, locks and additional work for concurrent rebuilding. Choose a method only after reviewing exact object types, managed permissions, resource budgets and recovery. A physical read-only blue/green evaluation surface has different possibilities from a writable isolated clone; consult the RDS blue/green admissibility paper rather than assuming maintenance can be run anywhere.

5. Work through three fictional review packets

The example operation is catalogue-page-r1, over three inert records item-a, item-b and item-c. The exact text values and real locale sort order are intentionally absent: the example evaluates supplied ordered identities, not a fabricated ICU result. The application owner independently stipulates the accepted order item-a, item-b, item-c and equality result false for the text comparison between item-a and item-b. Source and target observations must each identify that same dataset, operation and profile revision.

Packet A supplies the accepted source result but a target order item-b, item-a, item-c. All three records still exist. The target observation may be correct under its configured rules, but the stated application contract is not met. The fixture returns HOLD with a target-semantics reason. It does not diagnose an index or recommend copying the rows again. The next investigation compares query/profile evidence and, if needed, separately authorized database observations that distinguish stale access structure from intended new comparison behavior.

Packet B supplies matching operation results and matched current version labels, but declares one affected target index without its rebuild receipt. The target record says its catalog version was refreshed. The fixture still returns HOLD for missing dependency evidence. The refreshed flag does not supply a receipt or a retest. These fictional version labels are opaque tokens, not examples of real glibc or ICU releases.

Packet C supplies matching operation results, complete declared inventory and a rebuilt/retested affected index, while recorded and actual version tokens differ and the refresh status is pending. Its supplied dependency evidence covers the actual-version signature. The fixture returns MODEL_REVIEW_ONLY with a separate metadata-pending note. A remaining warning does not invalidate the existence of those supplied records, nor does this result authorize clearing it. The database owner must review the metadata transition separately under the real change procedure.

A: same records, changed target order

Model disposition: HOLD: target-semantics

Missing decision or evidence: Application contract and cause investigation

B: refreshed catalog, missing rebuild receipt

Model disposition: HOLD: object-evidence

Missing decision or evidence: Affected-object receipt and bound retest

C: rebuilt and retested, refresh pending

Model disposition: MODEL_REVIEW_ONLY; metadata pending

Missing decision or evidence: Separate metadata/change authority and actual environment verification

6. Complete one record before expanding the population

The following filled record is a synthetic review specification. Its aliases are supplied labels, not authenticated resources. The real target patch, provider and outputs have not been observed. Replace each example field through an authorized evidence collection before using the format for a migration decision. Keep sensitive customer values outside general tickets; retain references to controlled evidence instead.

Record and owner

packet-c-r1; application-owner-a and database-owner-a, fictional roles

Environments

source-alias-a and rds-target-alias-b; actual patch identities unverified

Operation and dataset

catalogue-page-r1; dataset-three-r1; three stable item IDs

Effective profile

profile-source-r1 and profile-target-r1; explicit fictional ICU locale fixture-locale; UTF8; deterministic true

Version evidence

Target stored token v-before; actual token v-after; source tokens v-source; no real library release represented

Expected behavior

Ordered IDs item-a, item-b, item-c; selected pair equality false

Observed projections

obs-source-r1 and obs-target-r1 supply that same order and equality, bound to their respective profile revisions

Declared dependency

index-alias-page; definition-r1; explicitly affected in this synthetic packet

Maintenance and retest

receipt-index-r1 and retest-index-r1 refer to target profile-target-r1 actual token v-after

Inventory completeness

Supplied complete for this one-operation fixture only; real database/application coverage unknown

Metadata and model outcome

Refresh pending; MODEL_REVIEW_ONLY with metadata-pending note

Next permitted review

Database owner checks the real inventory and maintenance evidence; application owner reviews semantics; operational authority remains separate

Use this blank record without copying the fictional assertions into an operational packet. An unknown entry remains open. A reviewer must be able to trace each value back to a versioned query, observation or explicit decision rather than trusting a completed-looking form.

Record and owner

Record revision; database and application reviewers; review time

Environments

Source and target identities; exact patch and evidence references; unavailable fields

Operation and dataset

Query/expression revision; input population and cut; stable tie-breaker and null rules

Effective profile

Resolved database default or schema-qualified explicit collation; provider, locale/rules, encoding and determinism

Version evidence

Stored and actual observations; applicable function; missing/unversioned treatment; pre-maintenance record

Expected behavior

Independently accepted ordering/equality cases and owner; intentional differences

Observed projections

Source/target result references bound to dataset, operation, environment and effective profile

Declared dependency

Stored objects and definitions; inclusion rationale; unresolved dependencies and consumers

Maintenance and retest

Authorized method and receipts if applicable; post-change observations bound to current signature

Inventory completeness

Database-default, expression, function and application coverage; exclusions and accountable owner

Metadata and model outcome

Refresh state; supplied mismatch treatment; model findings and non-authorizing limit

Next permitted review

Specific missing evidence; owner; invalidating changes; separate operational decision

7. Use the offline checker inside its declared boundary

Download the offline collation record specimen (ZIP). Reading and this download do not require an email. Extract the five files and run the documented Python tests locally; the package performs no database or network operations.

The dependency-free Python specimen accepts one operation, two versioned profiles, supplied observations and a declared target-object list. Literal opposing cases are in cases.json; their expectations do not come from generating the candidate's answer. It holds malformed or missing fields, changed dataset/operation bindings, duplicate record identities, contradictory observation IDs, missing affected-object evidence, failed retests and unaccepted source or target semantics. A compatible supplied packet produces MODEL_REVIEW_ONLY with authorizesChange: false and databaseExecuted: false.

Its strict profile requires nonempty version tokens and an explicit resolved profile. It does not model unversioned providers, resolve a default marker itself, understand SQL, compare full catalogues or discover omitted objects. Its inventoryComplete flag is a supplied claim, not a discovery result. A fabricated but internally consistent packet can pass the consistency checks. Retain that positive limitation case so a passing output cannot be mistaken for provenance or an independent technical review.

An observation ID must keep the same environment, profile, operation, dataset and result wherever reused. The model checks those observation fields within the supplied packet and joins opaque profile and receipt aliases. It does not retain historical revisions or authenticate the contents behind a reference. Changing the target locale while keeping its profile revision, or changing an object definition while keeping its receipt aliases, can still produce MODEL_REVIEW_ONLY. The two limitation regressions deliberately preserve that non-authorizing result.

Changed evidence needs a new revision, but enforcing immutable references belongs to the external evidence store and its owners. They must bind each profile revision and receipt to retained contents and reject edits under an unchanged identity. The checker cannot determine whether a screenshot or export was tampered with, or establish that an unchanged alias still describes the original evidence. No role names, private endpoint, credential or production dataset is needed in the public teaching specimen.

8. Choose the response from the unresolved behavior

Retaining the intended locale-aware behavior can be appropriate when users depend on its ordering. That choice requires valid dependent objects and accepted application results under the selected target profile. Deliberately choosing a stable byte/code-point comparison can be appropriate for a different machine-key contract, but it changes behavior and may be unsuitable for human language display. Have the owner approve that contract before treating stability as a benefit.

Deferral is reasonable when the team cannot identify the effective collation, lacks an affected-object inventory, or cannot observe the critical consumer on an authorized target surface. Assign the missing query, inventory or isolated rehearsal to a named owner with a review deadline. Repeating a row checksum will not answer a changed-pagination question. Repeating a metadata refresh will not supply a missing rebuild receipt.

Bring one completed operation record, including a deliberately failing comparison, to the database and application reviewers. Ask the database owner to explain the effective profile and dependency coverage; ask the application owner to explain the changed-result treatment. Keep that record as a scoped input to data reconciliation acceptance. Any repair, target admission or write-authority transfer needs separately authorized procedures and current evidence.