Key-set pagination order/filter on timestamp that does not have the same precision

I'm encountering an issue with my Key-set Pagination functionality because the timestamp value I receive is constrained to milliseconds, causing a mismatch with the precision expected by the database. This limitation stems from the inherent lack of precision in the JavaScript Date implementation. Unfortunately, I have no control over this aspect as the value is obtained from an API endpoint. While one workaround involves transmitting a bigint string value to and from the client, I'm interested in exploring alternative solutions to this problem before resorting to potentially time-consuming changes.

Schema (PostgreSQL v15)

CREATE TABLE IF NOT EXISTS public.activities
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),    
    created_at timestamp with time zone DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE INDEX activities_created_at_id ON activities (created_at, id);

---

Ordering

Below is the table's content sorted by created_at asc, id asc

select id, created_at from activities order by created_at asc, id asc;

---

Issue with Significant Digits

The input provided lacks precision compared to what's stored in the database. Due to JavaScript's limitation in supporting precision beyond milliseconds, the following query retrieves all activities:

select id, created_at from activities
where (created_at, id) > ('2024-03-14T07:23:56.474Z', 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at asc, id asc;

---

Using date_trunc to Cap Precision When Querying

While achieving the desired result, I'm hesitant to handle this solely within the query. This approach would necessitate dynamically adjusting the query construction based on the field type, potentially introducing performance concerns:

select id, created_at from activities
where (date_trunc('milliseconds', created_at::timestamptz), id) < (date_trunc('milliseconds', '2024-03-14T07:23:56.474Z'::timestamptz), 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at desc, id desc;

---

Considering these options, I'm open to suggestions and insights from the community to address this pagination challenge effectively.

View on DB Fiddle

If PostgreSQL microsecond resolution just gets in the way, you don't need to use it: it's customisable.

time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.

You can alter the column type in-place, which will simply round the last 3 digits in the field, in all existing and future rows in the table: demo

alter table activities alter column created_at type timestamptz(3);

select id, 
       to_char(created_at,
               'YYYY-MM-DD"T"HH24:MI:SS.USTZ'),
       to_char('2024-03-14T07:23:56.474Z'::timestamptz,
               'YYYY-MM-DD"T"HH24:MI:SS.USTZ') target
from activities
where (created_at, id) 
    > ('2024-03-14T07:23:56.474Z', 'b9b33ca5-2cd1-490b-a6be-d28784f75b2a')
order by created_at asc, id asc;

The lower resolution column can also be generated based on the original one, in case the microseconds are or might at one point become useful elsewhere:

alter table activities 
  add column created_at_mili_precise timestamptz(3) 
    generated always as (created_at::timestamptz(3)) stored;