Is theme('pager') designed to work only with dynamic query?
theme('pager') is not designed to work only with dynamic queries. The documentation for page_default_initialize() shows example code where dynamic queries are not used.

  // First find the total number of items and initialize the pager.
  $where = "status = 1";
  $total = mymodule_select("SELECT COUNT(*) FROM data " . $where)-&gt;result();
  $num_per_page = variable_get('mymodule_num_per_page', 10);
  $page = pager_default_initialize($total, $num_per_page);

  // Next, retrieve and display the items for the current page.
  $offset = $num_per_page * $page;
  $result = mymodule_select("SELECT * FROM data " . $where . " LIMIT %d, %d", $offset, $num_per_page)-&gt;fetchAll();
  $output = theme('mymodule_results', array('result' =&gt; $result));

  // Finally, display the pager controls, and return.
  $output .= theme('pager');
  return $output;


The other example given in that documentation page shows code that uses an external service to retrieve information which is shown. Also in that case, dynamic queries are not used. (The external service could not even running Drupal.)

The only difference I can see between that code, and the PagerDefault class is that PagerDefault uses a unique ID that passes as last argument of page_default_initialize(). 

/**
 * The unique ID of this pager on this page.
 *
 * @var int
 */
protected $element = NULL;




$total_items = $this-&gt;getCountQuery()-&gt;execute()-&gt;fetchField();
$current_page = pager_default_initialize($total_items, $this-&gt;limit, $this-&gt;element);
$this-&gt;range($current_page * $this-&gt;limit, $this-&gt;limit);


The other difference is that, as PagerDefault uses dynamic queries, its query can be altered with hook_query_alter(). In this case, as the added tag is pager, the query can be also altered with hook_query_pager_alter() which is a hook_query_TAG_alter() hook.

  public function __construct(SelectQueryInterface $query, DatabaseConnection $connection) {
    parent::__construct($query, $connection);

    // Add pager tag. Do this here to ensure that it is always added before
    // preExecute() is called.
    $this-&gt;addTag('pager');
  }