Reducing round-trips to the database

[kylepl]

Hi,

In my use-case, the round-trip time to my database is a bit high, and while I'm separately working on moving my application closer to my database, I'm trying to reduce the number of round trips.

I have a few specific cases:

Transactions

I'm using pqxx::transaction, and have the style of:

pqxx::transaction txn();
txn.exec_params1 (...) // Execute some statements
txn.exec_params0 (...) // Execute some other statements
txn.commit();

Two related questions:

• My observation is just creating the txn requires a round-trip. I assume that's because it is executing the BEGIN;. I'm wondering if it could actually be done lazily, and pre-pended to the first statement (even optionally, a lazy-txn if you will).
• Similarly, commit requires a round-trip (not surprising). I'm wondering if the user could opt-in to commits bundled with the last statement, like txn.exec_params1_and_commit (...), to reduce a RTT.

While these don't seem too complicated to support to me, just curious if I'm missing it - or if my needs are different than others.

Multiple inserts.

Sometimes I have multiple dependent inserts, like:

• INSERT INTO table_a (row_a) VALUES($1) RETURNING id;
• And a second subsequent insert that references it INSERT INTO table_b (table_a_id) VALUES($1)

It seems unfortunate to have another round-trip just to use a dependent value. I realize this may be more of a "postgres would need to support this" - just confirming.

Thanks!

Kyle

[tt4g]

You should use PL/pgSQL.
You should be able to register operations as functions and call them from libpq.

See: https://stackoverflow.com/questions/32350187/how-to-call-a-pg-function-with-libpq-and-get-the-param-value

[jtv]

Consider using the pqxx::pipeline class as well.

[kylepl]

Thanks for the responses.

In my specific case, I happen to be using CockroachDB, which does not seem to support PL/pgSQL for functions, just SQL.

I have realized that for Multiple (Dependent) Inserts, Common Table Expressions using WITH just solves that problem nicely - only one round trip for two inserts:

WITH first_insert (INSERT INTO table_a (row_a) VALUES($1) RETURNING id)
INSERT INTO table_b (table_a_id)
SELECT id FROM first_insert

@jtv : Any thoughts on transaction support for combining the 'BEGIN' and 'END' with the first-and-last statement?

e.g. imagine if you want to run two SELECT statements in parallel, you could do:

pqxx::on_demand_transaction txn;
pqxx::pipeline pl(txn);
txn.insert("SELECT * FROM foo");
txn.insert("SELECT * FROM bar");
pl.complete_and_commit_transaction();

which instead of doing 4 round trips with:

• BEGIN;
• SELECT * FROM foo;
• SELECT * from bar;
• COMMIT;

it would actually just do 2:

• BEGIN; SELECT * FROM foo;
• SELECT * from bar; COMMIT;

[jtv]

About the combining of BEGIN & COMMIT with run-of-the-mill statements: I think you could build a pipeline on top of a pqxx::nontransaction instead of a regular transaction, and then insert the BEGIN and COMMIT yourself. (Or leave them out altogether, if that suits your purpose better.)

It does all get a bit messy and if anything fails, you'd probably be best off ditching the entire connection.

I should say that pqxx::pipeline is very old code now, and I've been meaning to try and re-implement it on top of a very similar feature that has since been added to libpq.

[kylepl]

Sounds good - I can experiment with things on my side, and see if there is something useful to share later.

Appreciate the input from everyone!