Paginator for Those Suffering From PostgreSQL Count( ) Speed Issues

Our company has been using PostgreSQL for a very long time and has found it a very solid product for our needs. One thing that we’ve run into is that as our database grows (1mil+ records), there is a major slowdown on page loads due to a PostgreSQL bug.

To resolve this, I had to re-create a new paginator that didn’t rely on the count(*) sql call. I changed it to work similar to how Google performs their queries relying solely on the offset and max to determine which pagination buttons to show.

Here’s my final taglib:

package com.example

import org.springframework.web.servlet.support.RequestContextUtils as RCU

class PagerTagLib {

  /**
   * Creates next/previous links to support pagination for the current controller
   * This is developed to avoid problems with the PostgreSQL count(*) bug.
   * http://sql-info.de/postgresql/postgres-gotchas.html#1_7
   * 
   * <g:pager total="${accountListInstance.size()}" params="${params}" />
   */
  def pager = { attrs ->
      def writer = out
      if (attrs.total == null) {
          throwTagError("Tag [pager] is missing required attribute [total] which is the total showing for the current page")
      }

      def messageSource = grailsAttributes.messageSource
      def locale = RCU.getLocale(request)

    def total = attrs.int('total') ?: 0

      def action = (attrs.action ? attrs.action : (params.action ? params.action : "list"))
      def offset = params.int('offset') ?: 0
      def max = params.int('max')

      if (!offset) offset = (attrs.int('offset') ?: 0)
      if (!max) max = (attrs.int('max') ?: 10)

      def linkParams = [:]
      if (attrs.params) linkParams.putAll(attrs.params)
      linkParams.offset = offset - max
      linkParams.max = max
      if (params.sort) linkParams.sort = params.sort
      if (params.order) linkParams.order = params.order

      def linkTagAttrs = [action:action]
      if (attrs.controller) {
          linkTagAttrs.controller = attrs.controller
      }
      if (attrs.id!=null) {
          linkTagAttrs.id = attrs.id
      }
      linkTagAttrs.params = linkParams

      // determine paging variables
    def isFirstStep = (offset == 0)
    def isLastStep = (total < max)

      // display previous link when not on firststep
      if (!isFirstStep) {
          linkTagAttrs.class = 'prevLink'
          linkParams.offset = offset - max
          writer << link(linkTagAttrs.clone()) {
              (attrs.prev ? attrs.prev : messageSource.getMessage('paginate.prev', null, messageSource.getMessage('default.paginate.prev', null, 'Previous', locale), locale))
          }
      }

      // display next link when not on laststep
      if (!isLastStep) {
          linkTagAttrs.class = 'nextLink'
          linkParams.offset = offset + max
          writer << link(linkTagAttrs.clone()) {
              (attrs.next ? attrs.next : messageSource.getMessage('paginate.next', null, messageSource.getMessage('default.paginate.next', null, 'Next', locale), locale))
          }
      }
  }

}