Aggregates

ParadeDB can execute aggregate work using the columnar portion of the ParadeDB index, which can significantly accelerate performance compared to vanilla Postgres. There are two aggregate surfaces:

Native SQL aggregates for normal Postgres queries: COUNT, SUM, AVG, MIN, MAX, STDDEV, VARIANCE, BOOL_AND, BOOL_OR, ARRAY_AGG, STRING_AGG, GROUP BY, HAVING, and per-aggregate FILTER.
pdb.agg JSON aggregates for Elasticsearch-compatible bucket and metric aggregations, including facets, histograms, percentiles, cardinality, and top hits.

Native SQL Aggregates

Use standard SQL aggregate functions when you want ordinary Postgres-shaped results. ParadeDB can push these aggregates into the index when the query contains a ParadeDB predicate and the aggregate fields are in the ParadeDB index.

SELECT
  COUNT(*) AS count,
  AVG(rating) AS avg_rating,
  MIN(created_at) AS oldest_item,
  MAX(created_at) AS newest_item
FROM mock_items
WHERE description ||| 'running shoes';

Grouped aggregates use normal GROUP BY syntax:

SELECT rating, COUNT(*) AS count
FROM mock_items
WHERE description ||| 'running shoes'
GROUP BY rating
ORDER BY rating
LIMIT 5;

If a query does not otherwise need a full-text predicate, add pdb.all() to make the query eligible for aggregate pushdown:

SELECT COUNT(*)
FROM mock_items
WHERE id @@@ pdb.all();

See Limitations for the full list of supported aggregate shapes and fallback cases.

JSON Aggregates

The pdb.agg function accepts an Elasticsearch-compatible JSON aggregate query string. This is useful when you want bucketed or faceted responses in Elasticsearch-compatible JSON. For example, the following query counts the total number of results for a search query:

SELECT pdb.agg('{"value_count": {"field": "id"}}')
FROM mock_items
WHERE category === 'electronics';

import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    agg: search.agg({ value_count: { field: "id" } }),
  })
  .from(mockItems)
  .where(search.term(mockItems.category, "electronics"));

from paradedb import Agg, ParadeDB, Term

MockItem.objects.filter(
    category=ParadeDB(Term('electronics'))
).aggregate(agg=Agg('{"value_count": {"field": "id"}}'))

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(pdb.agg(facets.value_count(field="id")))
    .select_from(MockItem)
    .where(search.term(MockItem.category, "electronics"))
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:category)
        .term("electronics")
        .facets_agg(agg: ParadeDB::Aggregations.value_count(:id))

await dbContext
    .MockItems.Where(item => EF.Functions.Term(item.Category, "electronics"))
    .Select(item => EF.Functions.Agg(new { value_count = new { field = "id" } }))
    .ToListAsync();

Expected Response

      agg
----------------
 {"value": 5.0}
(1 row)

This query counts the number of results for every distinct group:

SELECT rating, pdb.agg('{"value_count": {"field": "id"}}')
FROM mock_items
WHERE category === 'electronics'
GROUP BY rating
ORDER BY rating
LIMIT 5;

import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    rating: mockItems.rating,
    agg: search.agg({ value_count: { field: "id" } }),
  })
  .from(mockItems)
  .where(search.term(mockItems.category, "electronics"))
  .groupBy(mockItems.rating)
  .orderBy(mockItems.rating)
  .limit(5);

from paradedb import Agg, ParadeDB, Term

MockItem.objects.filter(
    category=ParadeDB(Term('electronics'))
).values('rating').annotate(
    agg=Agg('{"value_count": {"field": "id"}}')
).order_by('rating')[:5]

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(MockItem.rating, pdb.agg(facets.value_count(field="id")).label("agg"))
    .where(search.term(MockItem.category, "electronics"))
    .group_by(MockItem.rating)
    .order_by(MockItem.rating)
    .limit(5)
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:category)
        .term("electronics")
        .aggregate_by(
          :rating,
          agg: ParadeDB::Aggregations.value_count(:id)
        )
        .order(:rating)
        .limit(5)

await dbContext
    .MockItems.Where(item => EF.Functions.Term(item.Category, "electronics"))
    .GroupBy(item => item.Rating)
    .Select(group => new
    {
        Rating = group.Key,
        Agg = EF.Functions.Agg(new { value_count = new { field = "id" } })
    })
    .OrderBy(result => result.Rating)
    .Take(5)
    .ToListAsync();

Expected Response

 rating |      agg
--------+----------------
      3 | {"value": 1.0}
      4 | {"value": 3.0}
      5 | {"value": 1.0}
(3 rows)

Multiple Aggregations

To compute multiple aggregations at once, simply include multiple pdb.agg functions in the target list:

SELECT
  pdb.agg('{"avg": {"field": "rating"}}') AS avg_rating,
  pdb.agg('{"value_count": {"field": "id"}}') AS count
FROM mock_items
WHERE category === 'electronics';

import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    avgRating: search.agg({ avg: { field: "rating" } }),
    count: search.agg({ value_count: { field: "id" } }),
  })
  .from(mockItems)
  .where(search.term(mockItems.category, "electronics"));

from paradedb import Agg, ParadeDB, Term

MockItem.objects.filter(
    category=ParadeDB(Term('electronics'))
).aggregate(
    avg_rating=Agg('{"avg": {"field": "rating"}}'),
    count=Agg('{"value_count": {"field": "id"}}'),
)

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(
        pdb.agg(facets.avg(field="rating")).label("avg_rating"),
        pdb.agg(facets.value_count(field="id")).label("count"),
    )
    .select_from(MockItem)
    .where(search.term(MockItem.category, "electronics"))
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:category)
        .term("electronics")
        .facets_agg(
          avg_rating: ParadeDB::Aggregations.avg(:rating),
          count: ParadeDB::Aggregations.value_count(:id)
        )

await dbContext
    .MockItems.Where(item => EF.Functions.Term(item.Category, "electronics"))
    .Select(item => new
    {
        AvgRating = EF.Functions.Agg(new { avg = new { field = "rating" } }),
        Count = EF.Functions.Agg(new { value_count = new { field = "id" } })
    })
    .ToListAsync();

Expected Response

   avg_rating   |     count
----------------+----------------
 {"value": 4.0} | {"value": 5.0}
(1 row)

Performance Optimization

By default, pdb.agg runs transaction visibility checks so that deleted or updated-away rows are not factored into the result set. This behavior is controlled by the visibility argument, which takes one of three modes.

Mode	Behavior
`'transaction'`	Check transaction visibility. Aggregate results match vanilla Postgres. This is the default.
`'raw'`	Skip the checks and aggregate raw index data. Faster, and approximate whenever the index still holds entries for rows your transaction cannot see.
`'threshold'`	Check transaction visibility only when the query’s estimated matching row count is below `paradedb.visibility_threshold`.

If your table is not frequently updated or you can tolerate an approximate result, the performance of aggregate queries can be improved by skipping these visibility checks. To do so, set visibility to 'raw'.

SELECT pdb.agg('{"value_count": {"field": "id"}}', 'raw')
FROM mock_items
WHERE description ||| 'running shoes';

import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    agg: search.agg({ value_count: { field: "id" } }, false),
  })
  .from(mockItems)
  .where(search.matchAny(mockItems.description, "running shoes"));

from paradedb import Agg, MatchAny, ParadeDB

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running shoes'))
).aggregate(
    agg=Agg('{"value_count": {"field": "id"}}', exact=False)
)

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(pdb.agg(facets.value_count(field="id"), approximate=True).label("agg"))
    .where(search.match_any(MockItem.description, "running shoes"))
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:description)
        .match_any("running shoes")
        .facets_agg(exact: false, agg: ParadeDB::Aggregations.value_count(:id))

await dbContext
    .MockItems.Where(item => EF.Functions.MatchAny(item.Description, "running shoes"))
    .Select(item => EF.Functions.Agg(new { value_count = new { field = "id" } }, false))
    .ToListAsync();

Skipping this check can improve query times by 2-4x in some cases, at the expense of correctness.

Thresholded Visibility

An unvacuumed dead tuple skews a small result visibly and a large one barely at all, so the tradeoff above is really a function of how many rows the query matches. visibility => 'threshold' makes that decision per query instead of hardcoding it in your application: visibility checks run when the estimated matching row count is below paradedb.visibility_threshold, and the aggregate reads raw index data otherwise.

SELECT pdb.agg('{"value_count": {"field": "id"}}', 'threshold')
FROM mock_items
WHERE description ||| 'running shoes';

paradedb.visibility_threshold defaults to 10000 and can be set at the system, database, session, or transaction level.

SET paradedb.visibility_threshold = 50000;

The estimate is made for the query as a whole, so a bucketed aggregation such as GROUP BY or terms resolves to one decision for every bucket. A query matching a million rows across fifty thousand buckets aggregates every bucket from raw index data, including the low-cardinality ones.

If a single query contains multiple pdb.agg calls, all of them must use the same visibility setting. Omitting the argument selects 'transaction', so an omitted argument alongside an explicit 'raw' is a conflict, not a default.

The solve_mvcc argument is deprecated in favor of visibility. Passing a boolean is still accepted, where true means 'transaction' and false means 'raw'.

JSON Fields

If metadata is a JSON field with key color, use metadata.color as the field name:

SELECT pdb.agg('{"terms": {"field": "metadata.color"}}')
FROM mock_items
WHERE id @@@ pdb.all();

import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    agg: search.agg({ terms: { field: "metadata.color" } }),
  })
  .from(mockItems)
  .where(search.all(mockItems.id));

from paradedb import Agg, All, ParadeDB

MockItem.objects.filter(
    id=ParadeDB(All())
).aggregate(agg=Agg('{"terms": {"field": "metadata.color"}}'))

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(pdb.agg(facets.terms(field="metadata.color")))
    .select_from(MockItem)
    .where(search.all(MockItem.id))
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:id)
        .match_all
        .facets_agg(agg: ParadeDB::Aggregations.terms("metadata.color"))

await dbContext
    .MockItems.Where(item => EF.Functions.All(item.Id))
    .Select(item => EF.Functions.Agg(new { terms = new { field = "metadata.color" } }))
    .ToListAsync();

If a text or JSON field is used inside pdb.agg, it must use the literal or literal normalized tokenizer.

Indexing

Full-Text / Lexical Search

Vector / Semantic Search

Hybrid Search

Aggregations

JOINs

Cheatsheets

Native SQL Aggregates

JSON Aggregates

Multiple Aggregations

Performance Optimization

Thresholded Visibility

JSON Fields

​Native SQL Aggregates

​JSON Aggregates

​Multiple Aggregations

​Performance Optimization

​Thresholded Visibility

​JSON Fields

Native SQL Aggregates

JSON Aggregates

Multiple Aggregations

Performance Optimization

Thresholded Visibility

JSON Fields