Simple Infinite Feed Pagination using Postgres

Here is how i structure infinite feed pagination with super simple implementation.

The key is to let clients maintain a session_id throughout their scroll session, and store the items you look at in a special table.

Database Setup

First, we need to set up a database table to manage feed sessions. This table will store session IDs and item IDs, allowing us to track which items have been seen in a session.

CREATE TABLE feed_session
(
    session_id UUID NOT NULL DEFAULT uuid_generate_v4(),
    item_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (session_id, item_id)
) WITH (ttl_expire_after = '1 day', ttl_job_cron = '@daily');

This SQL snippet creates a feed_session table with a time-to-live (TTL) of one day, ensuring that session data is automatically cleaned up daily.

Leveraging feed sessions to get unique items

In my example here, I will use TypeORM to fetch some data. The concept is simple.

1. Start a transaction
2. Lock the feed_session row to prevent concurrent modifications
3. Fetch the data
4. Save the data to the feed_session table
5. Return the data

const result = await this.dataSource.transaction(async (manager) => {
  await manager.query(
    `
    SELECT * FROM feed_session
    WHERE session_id = $1
    FOR UPDATE
  `,
    [sessionId],
  );

  const queryRes = await manager.query(
    `
  WITH seen_items AS (
    SELECT item_id
    FROM feed_session
    WHERE session_id = $1
  ),

  -- add your query here
  SELECT id
  FROM foo
  LEFT JOIN seen_items ON foo.id = seen_items.item_id
  WHERE seen_items.item_id IS NULL                    -- filter out seen items
`,
    [
      sessionId, // use this to filter
    ],
  );

  // Save session items to avoid seeing same groups in different sessions
  const feedSessions = queryRes.map(
    (row) =>
      new FeedSession({
        sessionId,
        itemId: row.id,
      }),
  );

  await manager.save(feedSessions);

  return result;
});

Conclusion

Easy peasy.

Only downside is your functions will become a bit more bloaty.

But usually an application only have so many infinite feeds.