php postgres from query to fetching rows in theory

Question

I would like to know what precisely is happening since php script runs the query to the moment when database returns data and php script starts to fetch them.

In theory, I have a Postgre database with huge table, talking about 10/20 million records with 20+ columns and I have a php script which is requesting all that rows from database to be placed in, lets say, some file.

My knowledge is:

• php script runs the pg_query command with sql query
• through postgres php driver/extension query is passed to database
• database does the work and return results to driver
• php postgres driver stores results in server RAM memory and returns resource id to php script which is reference to results
• pg_fetch_row reads records from RAM memory

I am trying to accomplish best possible optimization of php code which is doing described work above.

The questions are:

• Is my knowledge correct? If something is missing or I am not right about some step, please correct me.
• How can I know how much RAM memory is allocated to database result? This can be important if my server doesn't have enough memory.
• I know I can fetch single row, then store it into file and repeat these 2 steps as long there are rows, so I can minimize memory required for php script, but how can I influence the memory used by resource?

Thanks in advance.

Answer 1

PostgreSQL does (by default) return all rows in one go. This has the advantage of freeing resources on the server side at the cost of possibly large result sets at the client.

The alternative is to use a cursor which can return a single row at a time. Some drivers support this directly (maybe PDO?) or you can use DECLARE and FETCH

The other thing to be aware of is PostgreSQL's COPY command which can dump a table or query to a file directly (assuming you don't need much processing/formatting). Check if your datbase library offers direct access.