Categories
CMS Development Drupal

PostgreSQL unbuffered query

I am producing large query results that can exceed a sizable PHP memory limit (128 Mb). I’ve been trying to address this and came across unbuffered queries. Unfortunately, these only seem to be supported by MYSQL while I’m using PostgreSQL. There does seem to be a similar process for PostgreSQL involving cursors, but I’m unsure how to implement them within the Drupal framework using db_query(). Demo implementation with plain PHP can be found here.

My alternative solution would be to call a query multiple times with restrictions to LIMIT and OFFSET. Query performance is reasonably good, so this may be a viable solution as well. With this method I have yet to figure out how to divide queries into chunks by memory size. Instead, I would have to use a fixed row range. This could be a problematic approach given rows can significantly differ in size.

Leave a Reply

Your email address will not be published. Required fields are marked *