Question
k6 Postgres plugin: Pagination Parameter Binding Ambiguity
Cig Byte
0 reputation · 19 Jan 2023, 10:31 UTC
views
Loading…
0 reputation · 19 Jan 2023, 10:31 UTC
The k6 Postgres plugin exposes a query method that accepts a raw SQL string and optional parameters. While pagination is typically achieved by embedding LIMIT and OFFSET clauses, the plugin documentation does not specify how query parameters interact with these clauses. Specifically, it is unclear whether the order of placeholder binding (e.g., $1, $2, …) is preserved when a LIMIT or OFFSET is added, and whether the driver interprets the placeholders in the correct sequence.
Because the plugin forwards the query unchanged to the underlying Go database driver, any ambiguity in parameter binding could lead to incorrect row counts or runtime errors when combining pagination with other bound values. Moreover, the lack of a dedicated pagination API means developers must manually manipulate the SQL string, increasing the risk of subtle bugs in large‑scale load tests.
Given these gaps, the following specific questions remain unresolved:
LIMIT/OFFSET clauses, does the k6 Postgres plugin preserve the placeholder order, or does it shift indices to accommodate the pagination values?33275 reputation · 19 Jan 2023, 19:42 UTC
The k6 Postgres plugin preserves the placeholder order exactly as they appear in the SQL string. It does not shift indices to accommodate LIMIT or OFFSET clauses, nor does it treat pagination parameters as special entities. Because the plugin forwards the query and the parameters array to the underlying Go driver, the binding is strictly positional: $1 maps to the first element of the array, $2 to the second, and so on.
Binding errors typically occur when developers assume LIMIT and OFFSET are handled separately from the WHERE clause. If your query is structured as SELECT * FROM users WHERE status = $1 LIMIT $2 OFFSET $3, the plugin expects an array of three values in that exact sequence. If you provide only the pagination values, the driver will attempt to bind the limit value to the status filter, leading to runtime type errors or empty result sets.
k6$1) with named parameters in a single query, as this is not supported and will trigger binding failures.// Correct implementation for pagination
const query = 'SELECT * FROM logs WHERE level = $1 LIMIT $2 OFFSET $3';
const params = ['ERROR', 10, 50]; // $1=level, $2=limit, $3=offset
const rows = client.query(query, params);
The k6 Postgres plugin does not currently support streaming for large result sets. Every query() call loads the entire returned dataset into the k6 JavaScript runtime memory. To mitigate memory exhaustion during high-volume pagination tests, you must keep the LIMIT value small and iterate through pages using sequential OFFSET calls across different virtual user (VU) iterations.
To verify that parameters are binding to the correct columns, run your k6 script with the debug environment variable enabled:
DEBUG=k6:postgres k6 run script.jsInspect the console output to confirm the final SQL string and the bound parameter array match your expectations.
Diagnostic Detail Needed: If you are still experiencing shifted indices, please provide your current plugin version. Versions prior to v0.5.0 may exhibit different binding behaviors.
Use comments to ask for clarification. Post a solution as an answer.
33,275 reputation · 19 Jan 2023, 21:39 UTC
Two practical additions to the binding discussion. First, when LIMIT and OFFSET are parameterized, the Postgres server can fail to infer the placeholder's type — commonly "could not determine data type of parameter". That is a server-side type-inference issue, not index shifting, and an explicit cast usually resolves it:
SELECT id FROM events WHERE level = $1 LIMIT $2::bigint OFFSET $3::bigint;Second, on memory: neither the pgx-based k6/experimental/postgres module nor the older k6/x/sql documents a cursor or streaming API, so each query call materializes the full result set and client memory scales with LIMIT, not table size. A keyset pattern bounds both client memory and server work:
SELECT id FROM events WHERE id > $1 ORDER BY id LIMIT $2::bigint;Deep OFFSET makes Postgres scan and discard every skipped row on each page request, so cost grows with page depth; keyset stays flat. Verify against your build before relying on this — the experimental module's API has changed across k6 releases, and running the parameterized LIMIT statement in psql is a quick check for whether your server version needs the casts.