0

I have an events style table with enough data to be a problem, but nothing insane. I do need a bit of help the most appropriate index or query pattern for cursor based pagination, since I am finding myself a bit confused. I say "cursor style" here because all this is done in the API, and not actually PostgreSQL server side cursors.

The table is straightforward, but the id is not user visible (only uuid).

CREATE TABLE events (
    id              serial       primary key,
    uuid            uuid         not null default uuid_generate_v1(),
    location_id     int          not null,
    event_time      timestamp    not null,
    payload         jsonb        not null,
    ...
)

Queries against this table always use the following 3 critiera (rarely more). Due to the duplicates in the event_time field I have added the id to guarantee the order. The query itself has many joins for data, but not for filtering/ordering in this case.

WHERE
        location_id  = :building-id
    AND event_time  >= '2022-01-01...'::timestamptz
    AND event_time  <= '2022-12-31...'::timestamptz

I am working with an ORM so some of what is generated is outside of my control, and what is below reflects that.

I have come up with the following indexes and queries and want to ensure that I don't accidentally create some limit offset style issue as the cursor advances further through the records:

CREATE INDEX ix_loc_event_id ON events (
    location_id asc, 
    event_time desc, 
    id desc
)
SELECT * FROM events 
WHERE
        location_id  = 1
    AND event_time  >= {min date} 
    AND event_time  <= {max date}
    AND event_time  <  {cursor position} -- i hope the optimizer collapses these 2!
ORDER BY
    event_time DESC, id DESC

The second idea is to use only the ID as the ordering, since that is a guaranteed order, and also as the cursor position. This has some advantages, but I uncertain how this will affect the indexes since the event_time will always be part of the query.

WHERE
        location_id  = 1
    AND event_time  >= {min date} 
    AND event_time  <= {max date}
    AND id          <  {cursor position, decreasing}
ORDER BY
    id DESC
5
  • That should work well with keyset pagination. Jun 20, 2023 at 6:33
  • @LaurenzAlbe That is the keyword I couldn't remember, thanks! Any suggestions about the ordering issue?
    – Andrew
    Jun 20, 2023 at 9:10
  • Yo should sort like you want the query result to be sorted. Also, there is no reason to use DESC in the index definition in this case. Jun 20, 2023 at 10:26
  • "use only the ID as the ordering" That's a game changer. You'll have to define which sort order you need, first. (Typically, you'll want to sort by the timestamp.) Also, you are mixing timestamp and timestamptz, which is a recipe for confusing errors. Clarify your question in that regard, also. Jun 20, 2023 at 12:23
  • @ErwinBrandstetter This is just example code, no issues. ... is also not a valid part of a timestamp. Can you please clarify what you want clarified, your comment is confusing. It seems like you figured out the point there, but missed the question about the indexes to achieve it given that the
    – Andrew
    Jun 21, 2023 at 3:11

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.