Is it possible to stream a query result with PGLite?

[my-lalex]

For memory considerations, I'm searching for a way to stream a query result (onData callback or async iterator) without getting the whole result into memory...

Is there a way to do this in pglite?

[KeepALifeUS]

Streaming Query Results in PGlite

Currently PGlite does not have built-in streaming support, but here are workarounds:

Option 1: Cursor-based pagination

const BATCH_SIZE = 1000;
let offset = 0;

while (true) {
  const { rows } = await db.query(
    `SELECT * FROM large_table LIMIT ${BATCH_SIZE} OFFSET ${offset}`
  );
  
  if (rows.length === 0) break;
  
  for (const row of rows) {
    // Process each row
    await processRow(row);
  }
  
  offset += BATCH_SIZE;
}

Option 2: Keyset pagination (better for large datasets)

let lastId = 0;

while (true) {
  const { rows } = await db.query(
    `SELECT * FROM large_table WHERE id > $1 ORDER BY id LIMIT 1000`,
    [lastId]
  );
  
  if (rows.length === 0) break;
  
  for (const row of rows) {
    await processRow(row);
  }
  
  lastId = rows[rows.length - 1].id;
}

Option 3: Use live queries for reactive updates

import { live } from "@electric-sql/pglite/live";

const { rows, unsubscribe } = await db.live.query(
  `SELECT * FROM table WHERE processed = false LIMIT 100`
);

For true streaming, consider opening a feature request - PostgreSQL supports cursors which could potentially be exposed.

[my-lalex]

Hi @KeepALifeUS ,

Thanks for those possible workarounds, I already thought about this kind of solution, but this could be a huge performance issue on large tables for some specific use cases (ORDER BY on large tables for example)...
And it wouldn't be possible to handle queries already having LIMIT OFFSET (unless you parse SQL and change the values while keeping them in memory... and what about parameterized queries using a parameter for LIMIT/OFFSET ? 🤪)

Actually, there's already a feature request opened on this topic: #833 🤞🤞🤞

[tdrz]

As I said in #833 I think providing a callback that will push each result to the user as it arrives is doable.

[my-lalex]

This would be awesome 👍
You must have read the API/DX consideration I wrote on the FR, but whatever choice you'll make would be a great move 😉