Re: Will PQsetSingleRowMode get me results faster?
On Mon, Jan 6, 2025 at 12:21 PM Stijn Sanders wrote: > I've been using LibPQ to get data from PostgreSQL instances with great > success. > I'm using PQsendQuery and PQgetResult, but noticed there's also > PQsetSingleRowMode. > The documentation is clearly stating it only benefits a limited set of > scenario's, but I'm saddened that it can't help to get the first resulte of a > (longer running) query faster. I haven't used single-row-mode myself, but I can tell you that using a cursor instead of a statement allows you to reduce your time-to-first-row. But at the expense of overall performance, i.e. in my (limited) testing, getting the whole result-set in one go is faster than getting the same rows via a cursor, even in varying chunk sizes. Another alternative is to use COPY, which gets your the rows as they come, but then you're on your own to "decode" the row and its select-clause. FWIW. --DD
Re: Will PQsetSingleRowMode get me results faster?
Stijn Sanders wrote: > From what I notice using LibPQ, it appears a query needs to complete > before resulting data is being transferred to the client. Please > correct me if I'm wrong. No, the query does not need to complete. If you run something like "select * from bigtable" in single-row or chunked mode, you should see that the first results are typically available immediately to the application, while the query is far from finished. But it depends on the query plan. A lot of queries cannot produce any result until the final stage of their execution. For these, you can't expect any difference in how fast the first results are available client-side. Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
Re: Pipeline Mode vs Single Row Mode / Chunked Rows Mode
Daniel Frey wrote: > I tried to understand the interaction between Pipeline Mode and > Single Row Mode / Chunked Rows Mode. It seems that it works > sometimes, but I don't get the feeling that it was deliberately > designed to work consistently It's supposed to work, and there are regression tests in src/test/modules/libpq_pipeline/libpq_pipeline.c [1] that exercise the single row mode and PQpipelineSync(). You might want to compare your code workflow with how it's done in that file, and maybe submit a bug report with your test case if the conclusion is that your code should not error out. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/modules/libpq_pipeline/libpq_pipeline.c Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
Re: Will PQsetSingleRowMode get me results faster?
"Daniel Verite" writes: > Stijn Sanders wrote: >> From what I notice using LibPQ, it appears a query needs to complete >> before resulting data is being transferred to the client. Please >> correct me if I'm wrong. > No, the query does not need to complete. If you run something like > "select * from bigtable" in single-row or chunked mode, you should > see that the first results are typically available immediately to the > application, while the query is far from finished. > But it depends on the query plan. A lot of queries cannot produce any > result until the final stage of their execution. For these, you can't > expect any difference in how fast the first results are available > client-side. Right. But there's yet another moving part here: when creating a plan for a cursor query, the planner will give some preference (not infinite preference, but some) to plans that are expected to produce their first result row sooner. For example it might pick an indexscan plan on a suitably-ordered index over a seqscan-and-sort plan, even if the indexscan is estimated to take more time to run to completion. So in principle, you might get best results by defining your query with DECLARE CURSOR and then using PQsetSingleRowMode on the FETCH. But it'd really depend on the particular query whether this gives any benefit. regards, tom lane
Re: Questions about the Debian Package version of pgAdmin
On 1/5/25 16:27, Nick wrote: I sent this to the PgAdmin list but didn't get any replies. Not sure how many people are on that list, but since these questions are general enough, I figure someone on the main list will know: I'm using the apt repository version for Debian 12 (Bookworm). I'm also using Ansible to manage the server, and I have some questions about the pgAdmin Debian package version. 1. If we're using the pgAdmin DEB repo, will `apt-update` always update pgAdmin to the latest major version? ie. Will we be doing an upgrade every four weeks? From here: https://www.pgadmin.org/download/pgadmin-4-apt/ Platform Architecture First supported release Last supported Debian 12 (Bookworm) x86_64 7.0 - So yes it will continue to be updated as long as Debian 12 is current. I use Ubuntu 22.04 and pgAdmin regularly updates/upgrades. 2. Should `sudo /usr/pgadmin4/bin/setup-web.sh` be run every time `apt- update` runs, or only once? I have never run it after the first download. 3. Since the package is also installing Apache for us and configuring it, is there a safe way for us to modify the Apache config without breaking updates? I have never gone that deep into it as I rarely use pgAdmin. We'd like to enable TLS for Apache, but in a way that doesn't break anything each time the setup playbook runs and updates PgAdmin. Thanks, Nick -- Adrian Klaver adrian.kla...@aklaver.com
Query related to Logical Replication using test_decoding and unchanged-toast-datum
Hi, I am currently using PostgreSQL version 16 and the test_decoding plugin to perform logical replication (using replication slots). I have a simple table with integer column and JSON column. When a non-JSON column is updated, the value "unchanged-toast-datum" for the JSON column is obtained. This shows that the value recorded in a TOAST table for a JSON column value has not changed and is as expected. could you please tell me whether PostgreSQL will truly log these values to WAL or not ? If not, what will be entered into WAL for the JSON column if it remains unchanged? Please let me know. Thank you very much. Regards, Kiran
Re: Query related to Logical Replication using test_decoding and unchanged-toast-datum
On Mon, Jan 6, 2025 at 7:21 AM Kiran K V wrote: > could you please tell me whether PostgreSQL will truly log these values to > WAL or not ? If not, what will be entered into WAL for the JSON column if > it remains unchanged? > > Not quite sure what you are looking for but: https://www.postgresql.org/docs/current/storage-toast.html#STORAGE-TOAST-ONDISK "A pointer datum representing an out-of-line on-disk TOASTed value therefore needs to store the OID of the TOAST table in which to look and the OID of the specific value (its chunk_id). For convenience, pointer datums also store the logical datum size (original uncompressed data length), physical stored size (different if compression was applied), and the compression method used, if any. Allowing for the varlena header bytes, the total size of an on-disk TOAST pointer datum is therefore 18 bytes regardless of the actual size of the represented value." So the WAL of the main table data will contain 18bytes of data for that column. If the toast data really hasn't changed during the period in question the WAL will not contain any data for the toast relation since no changes to it were made. David J.
Will PQsetSingleRowMode get me results faster?
I've been using LibPQ to get data from PostgreSQL instances with great success. I'm using PQsendQuery and PQgetResult, but noticed there's also PQsetSingleRowMode. The documentation is clearly stating it only benefits a limited set of scenario's, but I'm saddened that it can't help to get the first resulte of a (longer running) query faster. There's a different database solution I won't name here that has a thing they call 'firehose mode' that in fact does this: their equivalent of PQntuples actually returns -1 in this mode, and you're expected to use their equivalent of PQgetResult to get record per record ** as it is rolling in from the server while the query is still running **. >From what I notice using LibPQ, it appears a query needs to complete before resulting data is being transferred to the client. Please correct me if I'm wrong. Please point me in the correct direction if I'm missing something and I need to look elsewhere. (I just now notice there's a PQsetChunkedRowsMode now —nice! — but I suspect the above still holds.) Should I attempt to use LIMIT and OFFSET to limit the running time of queries to get results faster? This will still interrupt the query and add overhead of starting and stopping each query, even using PQexecPrepared, I guess... Greetings Stijn
