pg_stats.correlation Is Not a Monotonic Index Detector

Some columns naturally move in one direction as new rows are inserted.

Think timestamps, event times, sequence values, or identity columns. These columns are often used in secondary indexes because applications frequently ask for the newest rows first.

For this tip, we’ll use an order_events table with a created_at column.

In PostgreSQL, pg_stats.correlation can be a useful clue about whether a column’s logical order matches the table’s physical heap order.

In YugabyteDB, that same value can surprise you.

You can insert rows in perfect created_at order and still see a correlation near zero.

That does not mean created_at is not monotonic. It means the base table is not physically ordered by created_at.

And in YugabyteDB, that is usually expected.

Important: pg_stats.correlation measures table-row ordering, not secondary-index write behavior. A timestamp index can show low correlation and still be a hotspot risk if new writes keep landing at the newest edge of a range-ordered index.

Create a Demo Table

Let’s create the same logical order_events table in both PostgreSQL and YugabyteDB.

The data will be loaded in increasing created_at order so we can compare how pg_stats.correlation behaves in each database.

In PostgreSQL:

				
					DROP TABLE IF EXISTS order_events;

CREATE TABLE order_events (
  event_id    uuid NOT NULL,
  customer_id uuid NOT NULL,
  status      text NOT NULL,
  created_at  timestamptz NOT NULL,
  amount      numeric(12,2) NOT NULL,
  payload     jsonb,
  PRIMARY KEY (event_id)
);

CREATE INDEX idx_order_events_created_at
ON order_events (created_at DESC);

In YugabyteDB, the primary key uses event_id HASH, which is a good fit for distributing the base table rows:

				
					DROP TABLE IF EXISTS order_events;

CREATE TABLE order_events (
  event_id    uuid NOT NULL,
  customer_id uuid NOT NULL,
  status      text NOT NULL,
  created_at  timestamptz NOT NULL,
  amount      numeric(12,2) NOT NULL,
  payload     jsonb,
  PRIMARY KEY (event_id HASH)
);

CREATE INDEX idx_order_events_created_at
ON order_events (created_at DESC);

The secondary index is the same in both databases:

				
					idx_order_events_created_at -> created_at DESC

The important difference is the base table storage shape:

				
					PostgreSQL -> PRIMARY KEY (event_id)
YugabyteDB -> PRIMARY KEY (event_id HASH)

Load Sample Data

Run the same insert in both PostgreSQL and YugabyteDB.

This loads 250,000 rows where created_at increases by one second for each generated row.

The event_id also embeds the generated sequence number in the last 12 digits. We’ll use that later to prove that created_at increases with the insert sequence.

				
					INSERT INTO order_events (
  event_id,
  customer_id,
  status,
  created_at,
  amount,
  payload
)
SELECT
  ('00000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid AS event_id,

  ('11111111-1111-1111-1111-' || lpad((g % 10000)::text, 12, '0'))::uuid AS customer_id,

  CASE
    WHEN g % 100 < 70 THEN 'processed'
    WHEN g % 100 < 90 THEN 'pending'
    WHEN g % 100 < 98 THEN 'failed'
    ELSE 'cancelled'
  END AS status,

  '2026-01-01 00:00:00+00'::timestamptz + (g || ' seconds')::interval AS created_at,

  round((10 + random() * 500)::numeric, 2) AS amount,

  jsonb_build_object(
    'source', 'demo',
    'sequence', g,
    'region',
      CASE
        WHEN g % 3 = 0 THEN 'east'
        WHEN g % 3 = 1 THEN 'central'
        ELSE 'west'
      END
  ) AS payload
FROM generate_series(1, 250000) AS g;

Collect statistics:

				
					ANALYZE order_events;

Tip: The query against pg_stats reads catalog statistics. It does not scan the table. However, those statistics must already exist and be reasonably current. Run ANALYZE after loading representative data.

The Postgres Result

In PostgreSQL, the order_events table uses a normal heap.

The data was inserted in increasing timestamp order.

Checking the statistics for created_at:

				
					SELECT
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename  = 'order_events'
  AND attname    = 'created_at';

Sample output:

				
					.schemaname |  tablename   |  attname   | n_distinct | correlation
------------+--------------+------------+------------+-------------
 public     | order_events | created_at |         -1 |           1

In this fresh PostgreSQL table, that result is expected.

The rows were inserted in generated order, and created_at increased with each row. Because PostgreSQL stores table rows in a heap, the physical row order in this demo lines up closely with the logical timestamp order.

That is why pg_stats.correlation reports 1.

The n_distinct = -1 value means the number of distinct created_at values scales with the row count. But the main point for this section is correlation = 1.

The YugabyteDB Result

In YugabyteDB, the table uses a hash-sharded primary key:

				
					PRIMARY KEY (event_id HASH)

The secondary index is range-ordered by time:

				
					CREATE INDEX idx_order_events_created_at
ON order_events (created_at DESC);

After loading the same style of data and running ANALYZE, YugabyteDB reports the statistics as:

				
					.schemaname |  tablename   |  attname   | n_distinct | correlation
------------+--------------+------------+------------+--------------
 public     | order_events | created_at |         -1 | -0.001746058

If you mistake correlation for monotonicity, that result looks like the timestamp is not increasing.

But that is not what the value means.

The base table is distributed by the hash of event_id. The table’s physical row order is not based on created_at, so the correlation between physical row order and timestamp order is close to zero.

That is normal in YugabyteDB.

Verify the Insert Pattern Is Monotonic

In this demo, the insert sequence was encoded into the last 12 digits of event_id.

That lets us prove the timestamp increased with the generated sequence:

				
					SELECT
  corr(
    extract(epoch FROM created_at),
    right(event_id::text, 12)::double precision
  ) AS created_at_vs_insert_sequence
FROM order_events;

Output:

				
					.created_at_vs_insert_sequence
-------------------------------
                             1

So the data was generated in increasing created_at order, but pg_stats.correlation still showed a value near zero.

The low correlation value is not telling us that created_at was random. It is telling us that the base table’s physical row order is not organized by created_at.

Demo note: The corr() query works here only because the demo encoded the sequence into event_id. Do not use this as a general production detector unless your schema has a reliable insert sequence, event sequence, or source ordering column.

The Misleading Signal

The wrong conclusion would be:

pg_stats.correlation is near zero, so created_at is not increasing.

The better conclusion is:

● pg_stats.correlation is near zero because the base table is not physically ordered by created_at.
● The range-ordered secondary index may still receive monotonic writes.

The primary key is hash-sharded, while the secondary index is range-ordered by timestamp.

Those are very different shapes.

Key point: The question is not whether created_at is correlated with the base table’s physical row order. The better question is whether new writes keep landing at the newest edge of idx_order_events_created_at.

How to Inspect the Index

A normal index will not appear as a table in pg_stats:

				
					SELECT
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename  = 'idx_order_events_created_at';

Output:

				
					.schemaname | tablename | attname | n_distinct | correlation
------------+-----------+---------+------------+-------------
(0 rows)

That is expected.

To inspect the index definition, use pg_indexes:

				
					SELECT
  schemaname,
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE indexname = 'idx_order_events_created_at';

To inspect index usage, use pg_stat_user_indexes:

				
					SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_order_events_created_at';

To inspect estimated index rows, use pg_class:

				
					SELECT
  relname AS index_name,
  reltuples AS estimated_rows
FROM pg_class
WHERE relname = 'idx_order_events_created_at';

A Better Audit Question

Instead of asking:

Is pg_stats.correlation close to 1?

Ask:

● Is the leading index key range-ordered?
● Is it a timestamp, date, sequence, identity, or increasing application value?
● Do new writes arrive in that same order?
● Is the workload write-heavy?
● Are tablet metrics showing uneven write distribution?

That is a better monotonic-index risk check.

Final Takeaway

pg_stats.correlation can behave very differently in PostgreSQL and YugabyteDB.

In PostgreSQL, sequential heap inserts can make a timestamp column show:

				
					correlation = 1

In YugabyteDB, the same data pattern can show:

				
					correlation near 0

That does not mean the timestamp values were not increasing.

For YugabyteDB, monotonic secondary-index risk is better detected from:

● index key shape
● ASC/DESC range ordering
● timestamp/date/sequence/identity behavior
● actual insert pattern
● tablet-level write distribution

Do not let a low pg_stats.correlation value talk you out of reviewing a range-ordered timestamp index.

Have Fun!