topn

Overview

Latest PGDG RPM/catalog version is 2.7.1; EL8/EL9 x86_64, Pigsty DEB, and Pigsty source remain on 2.7.0.

Version

Build

You can build the DEB packages for topn using pig build:

pig build pkg topn         # build DEB packages

Install

You can install topn directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install topn;          # Install for current active PG version

pig ext install -y topn -v 18  # PG 18
pig ext install -y topn -v 17  # PG 17
pig ext install -y topn -v 16  # PG 16
pig ext install -y topn -v 15  # PG 15
pig ext install -y topn -v 14  # PG 14

dnf install -y topn_18       # PG 18
dnf install -y topn_17       # PG 17
dnf install -y topn_16       # PG 16
dnf install -y topn_15       # PG 15
dnf install -y topn_14       # PG 14

apt install -y postgresql-18-topn   # PG 18
apt install -y postgresql-17-topn   # PG 17
apt install -y postgresql-16-topn   # PG 16
apt install -y postgresql-15-topn   # PG 15
apt install -y postgresql-14-topn   # PG 14

Create Extension:

CREATE EXTENSION topn;

Usage

topn: top-N values approximation for PostgreSQL

Provides approximate top-N value tracking using an approximation algorithm that keeps a predefined number of frequent items and counters. Supports materialization, incremental updates, and merging across time intervals.

CREATE EXTENSION topn;

Data Type

Uses JSONB to store the frequent items and their frequencies.

Aggregates

Functions

Configuration

• topn.number_of_counters – number of counters to track (default: 1000)

Examples

-- Materialize top products by date
CREATE TABLE popular_products (
  review_date date UNIQUE,
  agg_data jsonb
);

INSERT INTO popular_products
SELECT review_date, topn_add_agg(product_id)
FROM customer_reviews GROUP BY review_date;

-- Get the top-1 product for each day
SELECT review_date, (topn(agg_data, 1)).*
FROM popular_products ORDER BY review_date;

-- Top 10 across a time range (merging daily summaries)
SELECT (topn(topn_union_agg(agg_data), 10)).*
FROM popular_products
WHERE review_date >= '2000-01-01' AND review_date < '2000-02-01'
ORDER BY 2 DESC;