One day, a simple API query started taking minutes:

SELECT option_code
FROM organization_option_codes
WHERE organization_id = $1;

This query only returned a small list of options. It should have been fast.

But pg_stat_activity showed many requests in this state:

state           = active
wait_event_type = Lock
wait_event      = relation

The requests were not slow because PostgreSQL was taking a long time to find the rows. They were waiting for permission to read the relation.

The blocker was:

REFRESH MATERIALIZED VIEW organization_option_codes;

What happened?

The names in this post are generic, but the setup was straightforward:

  • an API read from a materialized view;
  • a background job refreshed that view;
  • the refresh used PostgreSQL’s default mode;
  • the refresh took a long time.

A materialized view stores the result of a query. When PostgreSQL refreshes it, it runs the underlying query again and replaces the stored result.

The default refresh takes an ACCESS EXCLUSIVE lock on the materialized view. A normal SELECT takes an ACCESS SHARE lock. These locks conflict, so reads must wait until the refresh finishes.

The production sequence looked like this:

refresh starts
  -> materialized view is locked
  -> API requests try to read it
  -> API requests wait
  -> more requests pile up
  -> database connections and web workers are consumed

This was not a deadlock. Nothing was waiting in a cycle. Many readers were simply waiting behind one long-running operation. This is often called a lock convoy.

Why did the database become unhealthy?

The refresh itself was doing a lot of work. It was reading the source data from disk:

wait_event_type = IO
wait_event      = DataFileRead

Other expensive queries were running at the same time, so they competed for the same database resources and made the refresh slower.

While that was happening, API requests continued to arrive and wait for the view. The waiting requests used database connections and application workers. Eventually, the database reported very high CPU usage and the application became unhealthy.

The lock was not necessarily using all the CPU by itself. The incident was the result of several things happening together:

  • a long-running refresh;
  • IO pressure from rebuilding the view;
  • user requests waiting for the view;
  • other expensive queries competing for resources.

How we found the blocker

During an incident, this is a useful first check:

SELECT
  pid,
  clock_timestamp() - query_start AS running_for,
  wait_event_type,
  wait_event,
  left(query, 200) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

Look for two things:

  • requests with wait_event_type = 'Lock';
  • a long-running query that is operating on the same table or materialized view.

PostgreSQL’s monitoring documentation explains these wait events, and pg_locks can be used when more detailed lock information is needed.

The safer option: concurrent refreshes

PostgreSQL supports a refresh mode that does not block normal reads:

REFRESH MATERIALIZED VIEW CONCURRENTLY organization_option_codes;

With CONCURRENTLY, users can continue reading the existing data while PostgreSQL builds the new version.

There are a few requirements:

  • the materialized view must already contain data;
  • it must have a qualifying unique index;
  • the index must cover every row and use actual columns, not expressions or a partial WHERE clause;
  • only one refresh can run for a materialized view at a time.

For example, if an organization cannot have the same option code twice:

CREATE UNIQUE INDEX CONCURRENTLY
  index_option_codes_on_organization_and_code
ON organization_option_codes (organization_id, option_code);

Only add this index if that combination is truly unique in the data.

In Rails, the refresh should make the mode explicit. The exact helper depends on the library, but the intent should look like this:

DatabaseViews.refresh(
  :organization_option_codes,
  concurrently: true
)

The important thing is to check the SQL generated by the helper. A method that sounds like a refresh may still be using the blocking default.

Other safeguards

Concurrent refreshes reduce reader blocking, but refresh jobs still need limits:

  • Set a statement_timeout so a refresh cannot run forever.
  • Set a lock_timeout so it fails quickly if it cannot get its initial lock.
  • Prevent two refresh jobs from running at the same time.
  • Log how long each refresh takes.
  • Alert on long refreshes, blocked requests, and connection-pool pressure.
  • Run large reporting queries outside the busiest refresh window when possible.

Timeouts do not make a query faster. They limit how much damage a slow query can cause.

Do we need a materialized view at all?

Materialized views are useful for expensive queries where slightly stale data is acceptable. But they may be unnecessary for small lookup lists.

For simple options or dropdown data, consider:

  • a normal lookup table;
  • an indexed query on the source tables;
  • an application cache;
  • a separately refreshed table.

The best choice depends on the size of the data and how fresh it needs to be. The important question is:

What happens to the user-facing request while this data is being rebuilt?

The lesson

A materialized view is not just a cached table. Its refresh strategy affects production availability.

Before using one behind an API endpoint, check:

  1. Can it be refreshed concurrently?
  2. Does it have the unique index required for that?
  3. What happens if the refresh is slow or fails?

For user-facing lookup data, slightly stale data is usually better than an unavailable endpoint.

Further reading