Achieving High-Performance Transactions with PostgreSQL Extension Using C Functions

Question

My goal is to achieve the maximum performance available for copying a block of data from a database into a C function, which will be processed and returned as a result of the request.

I am new to PostgreSQL, and now I am exploring possible ways to move data. In particular, I'm looking for nuances or keywords related to PostgreSQL to quickly move big data.

Note: My ultimate goal is speed, so I’m ready to accept the answers beyond the exact question that I posed while it gets great results. For example, I came across the keyword COPY (PostgreSQL only), which quickly moves data from tables to files; and vice versa. I try to stay away from processing, which is external to the database, but if it provides a performance improvement that does not allow us to understand the obvious drawback of external processing, then so be it.

Answer 1

It looks like you probably want to use the server-side programming interface (SPI) to implement the stored procedure as the C language function works inside the postgreSQL backg end.

Use SPI_connect to configure SPI.

Now SPI_prepare_cursor request, then SPI_cursor_open it. SPI_cursor_fetch strings and SPI_cursor_close when done. Note that SPI_cursor_fetch allows you to select line lots.

SPI_finish to perform a cleanup.

You can return rows of results to tuplestore as they are created, avoiding the need to create the entire table in memory. See Examples in any of the set-returning functions in the PostgreSQL source code. You can also look at the helper function SPI_returntuple .

See also: C language functions and SQL extension .

If maximum speed is of interest, your client may want to use the libpq binary protocol via libpqtypes so that it receives the data generated by your server-side SPI procedure with minimal overhead.