CODEWITHSUNDEEP
postgresqlscalabilitylibpq
user2369060
user2369060

Reputation: 871

Efficient row reading with libpq (postgresql)

This is a scalability related question.

We want to read some rows from a table and, after processing some of them, stop the query. The stop criteria is data dependent (we do not know in advance how many or what rows are we interested in).

This is scalability sensitive when the number of rows of the table grows far beyond the number of rows we really are interested in.

If we use the standard PQExec, all rows are returned and we are forced to consume them (we have to call PQGetResult until it returns null). So this does not scale.

We are now trying "row by row" reading.

We first used PQsendQuery and PQsetSingleRowMode. However, we still have to call PQGetResult until it returns null.

Our last approach is PQsendQuery, PQsetSingleRowMode and when we are done we cancel the query as follows

  void CloseRowByRow() {
    PGcancel *c = PQgetCancel(conn);
    char errbuf[256];
    PQcancel(c, errbuf, 256);
    PQfreeCancel(c);
    while (res) {
      PQclear(res);
      res = PQgetResult(conn);
    }
  }

This produces some performance benefits but we are wondering if this is the best we can do.

So here comes the question: Is there any other way?

Upvotes: 1

Views: 445

Answers (1)

AdamKG
AdamKG

Reputation: 14081

Use DECLARE and FETCH to define & read from a server-side cursor, this is exactly what they are meant for. You would use standard APIs, FETCH will just let you retrieve the results in batches of a controlled size. See the examples in the docs for more details.

Upvotes: 1

Related Questions