Handling PostgreSQL slow counting on Elixir
Tags: elixir, postgres, phoenixframework, jsonapi
Published: 2018-04-19 18:10:04

---
title: Handling PostgreSQL slow counting on Elixir
published: true
description: Elixir implementation of PostgreSQL estimate counting
tags: elixir, postgresql, phoenix framework, jsonapi
---

---

Some time ago I found an unexpected issue when working on a simple [JSON API](http://jsonapi.org/) project that should provide paginated resources. 

Most JSON API friendly libraries perform a record count in order to provide the pagination links on each response, especially for the **last page link**. But under some conditions a simple count query can be **very** slow:
```sql
SELECT COUNT(*) FROM table;
```
Specifically, in this case, this query was taking over 6sec when performed on a table with over 2.7M records.


---

## Understanding the issue
This count query is high time consuming due to [PostgreSQL's MVCC implementation](http://momjian.us/main/writings/pgsql/mvcc.pdf). The MVCC (Multiversion Concurrency Control) is a method commonly used to provide data consistency when multiple processes are accessing the same data. This is usually done by taking snapshots of the data, so when concurrent transactions are working on the same data, each one will read a "version" of a given database object based on the transaction time.

In order to count the table rows, PostgreSQL needs to ensure that they exist. But given the multiple snapshots/versions of each record, PostgreSQL is unable to summarize the whole table directly. So instead PostgreSQL reads each row, performing a **sequential scan.!**


## How to fix this?
There are different approaches to fix this issue, including a [trigger-based mechanism](http://www.varlena.com/GeneralBits/120.php). In my case, it was acceptable to use an estimated count of the rows, and luckily, [PostgreSQL provides exactly that via **pg_class reltuples**](https://www.postgresql.org/docs/current/static/catalog-pg-class.html):
```sql
SELECT reltuples::BIGINT AS estimate FROM pg_class WHERE relname=<table_name>
```
### Wait.. What is reltuples?
*[It is the] Number of rows in the table. This is only an estimate used by the planner.* - [PostgreSQL: Documentation: pg_class](https://www.postgresql.org/docs/current/static/catalog-pg-class.html)

The accuracy of this estimate depends on how frequently the planner updates pg_class reltuples:

*It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.* - [PostgreSQL: Documentation: pg_class](https://www.postgresql.org/docs/current/static/catalog-pg-class.html)

You are probably not executing CREATE and INDEX that often, but don't worry, ANALYZE will also update this value. VACUUM and ANALYZE are automatically executed by the [autovacuum daemon](https://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM) which is enabled by default.

Notice this solution is based on the **total** estimate record count, so it won't work for cases when you need to filter query results.


---

## OK, let's write some Elixir code!
In our implementation, we were using [scrivener_ecto](https://github.com/drewolson/scrivener_ecto) which is a very nice package that helps you to add pagination to your Ecto queries.

Recently we added the [total_entries](https://github.com/drewolson/scrivener_ecto/pull/42) option to `scrivener_ecto` so any custom logic can be used to calculate the total_entries and fix similar situations.

This is the function that handles the pagination based on a given `conn` and page parameters. We pass the query to `Scrivener.paginate` with a config struct containing the `total_entries` option:

```elixir
@spec handle_pagination(conn :: Plug.Conn.t(), page :: number | atom, page_size :: number | atom, query :: module) ::
        Plug.Conn.t()
defp handle_pagination(conn, page, page_size, query) do
  config = %Scrivener.Config{
    module: Repo,
    page_number: page,
    page_size: page_size,
    options: [total_entries: total_entries(query)]
  }

  query |> Scrivener.paginate(config)
end
```


Notice the call to `total_entries` on the previous function. We get the estimated value by executing SQL code to retrieve `reltuples` directly:

```elixir
# Retrieves the estimated record count
@spec total_entries(query :: module) :: number
defp total_entries(query) do
  table_name = table_name(query)

  {:ok, result} =
    Ecto.Adapters.SQL.query(Repo, "SELECT reltuples::BIGINT AS estimate FROM pg_class WHERE relname=$1", [table_name])

  result.rows |> List.first() |> List.first()
end
```

We have a missing piece on this puzzle, given that we don’t know the table name to query *pg_class’ reltuples*. To fix this we read the `__schema__(:source)` from the query:


```elixir
# Determines the table name based on a given query
@spec table_name(query :: module) :: module
defp table_name(query) do
  module_from_query(query).__schema__(:source)
end

# Retrieves the model module from a given query
@spec module_from_query(module :: module) :: module
defp module_from_query(%{from: {_table, module}}), do: module
defp module_from_query(module), do: module
```

---

## Conclusions
In certain cases, when it’s possible to use relatively accurate values, we can take advantage of PostgreSQL estimated count capabilities. This plus the very open elixir community that allows us to add new options to existing packages makes an almost effortless transition from an unacceptable issue to a **very** efficient endpoint: taking less than 0.5 ms in my computer with half a million records.

Suggestions and comments are more than welcome!