ParadeDB

https://www.paradedb.com/docs
Admin
ParadeDB is a PostgreSQL extension that adds full-text search and BM25 indexing capabilities with an...
Tokens:247,465
Snippets:2,140
Trust Score:9.2
Update:4 days ago
Show doc for...
Tokens:
Raw
### Install Docker on Ubuntu

Source: https://www.paradedb.com/docs/operate/deploy/cloud-platforms/digitalocean

Use the official Docker installation script to set up Docker on the Droplet.

```bash
curl -fsSL https://get.docker.com | sh
```

--------------------------------

### Install ParadeDB with Curlbash

Source: https://www.paradedb.com/docs/start/install

Runs the installation script to start ParadeDB in Docker with persistent storage and an active psql session.

```bash
curl -fsSL https://paradedb.com/install.sh | sh
```

--------------------------------

### Install SQLAlchemy and Alembic

Source: https://www.paradedb.com/docs/start/configure-your-environment

Set up a virtual environment and install the necessary packages for SQLAlchemy integration.

```bash
python3 -m venv .venv
source .venv/bin/activate
pip install sqlalchemy "psycopg[binary]" alembic sqlalchemy-paradedb==0.12.0
```

--------------------------------

### Example migration for users_service

Source: https://www.paradedb.com/docs/operate/deploy/logical-replication/multi-database

Concrete example of migrating the users and profiles tables to a users_service schema.

```sql
BEGIN;

-- Create new schema
CREATE SCHEMA IF NOT EXISTS users_service;

-- Move tables
ALTER TABLE public.users SET SCHEMA users_service;
ALTER TABLE public.profiles SET SCHEMA users_service;

-- Create backwards-compatible views
CREATE OR REPLACE VIEW public.users AS SELECT * FROM users_service.users;
CREATE OR REPLACE VIEW public.profiles AS SELECT * FROM users_service.profiles;

COMMIT;
```

--------------------------------

### Install Prometheus Stack

Source: https://www.paradedb.com/docs/operate/deploy/self-hosted/kubernetes

Install the Prometheus stack for monitoring support.

```bash
helm repo add prometheus-community https://prometheus-community.github.io/helm-charts
helm upgrade --atomic --install prometheus-community \
--create-namespace \
--namespace prometheus-community \
--values https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/main/docs/src/samples/monitoring/kube-stack-config.yaml \
prometheus-community/kube-prometheus-stack
```

--------------------------------

### Facet query with Rails

Source: https://www.paradedb.com/docs/start/run-queries

Rails example using MockItem.search with match_any and with_agg to get results and aggregates.

```ruby
relation = MockItem.search(:description)
                   .match_any("running shoes")
                   .with_agg(agg: ParadeDB::Aggregations.value_count(:id))
                   .order(:rating)
                   .select(:description, :rating, :category)
                   .limit(5)

rows = relation.to_a
facets = relation.aggregates

```

--------------------------------

### Install ParadeDB

Source: https://www.paradedb.com/docs/operate/deploy/cloud-platforms/digitalocean

Install ParadeDB using the provided installation script. The tag parameter pins the version; check Docker Hub for available tags.

```bash
curl -fsSL "https://paradedb.com/install.sh?tag=0.26.2-pg18" | sh
```

--------------------------------

### Min aggregation examples

Source: https://www.paradedb.com/docs/reference/aggregates/metrics/minmax

Examples of performing a min aggregation across various supported frameworks.

```SQL
SELECT pdb.agg('{"min": {"field": "rating"}}') FROM mock_items
WHERE id @@@ pdb.all();
```

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

await db
  .select({
    agg: search.agg({ min: { field: "rating" } }),
  })
  .from(mockItems)
  .where(search.all(mockItems.id));
```

```Python
from paradedb import Agg, All, ParadeDB

MockItem.objects.filter(
    id=ParadeDB(All())
).aggregate(agg=Agg('{"min": {"field": "rating"}}'))
```

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

stmt = (
    select(pdb.agg(facets.min(field="rating")))
    .select_from(MockItem)
    .where(search.all(MockItem.id))
)

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

```Ruby
MockItem.search(:id)
        .match_all
        .facets_agg(agg: ParadeDB::Aggregations.min(:rating))
```

```C#
await dbContext
    .MockItems.Where(item => EF.Functions.All(item.Id))
    .Select(item => EF.Functions.Agg(new { min = new { field = "rating" } }))
    .ToListAsync();
```

--------------------------------

### Install Rails dependencies

Source: https://www.paradedb.com/docs/start/configure-your-environment

Install the newly added gem.

```bash
bundle install
```

--------------------------------

### Initialize Django project for ParadeDB

Source: https://www.paradedb.com/docs/start/configure-your-environment

Set up a virtual environment and install Django dependencies.

```bash
python3 -m venv .venv
source .venv/bin/activate
pip install django "psycopg[binary]" django-paradedb==0.14.0
python3 -m django startproject myproject .
python3 manage.py startapp myapp
```

--------------------------------

### Setup Distributed Search with Citus

Source: https://www.paradedb.com/docs/reference/sharding/citus

Initializes the Citus and pg_search extensions, creates a distributed table, builds a ParadeDB index, and performs a distributed search. Note that pg_search requires the pgvector extension to be installed.

```sql
CREATE EXTENSION citus;
CREATE EXTENSION pg_search CASCADE;

-- Create a table with a distribution key
CREATE TABLE articles (
    id SERIAL,
    author_id INT NOT NULL,
    title TEXT,
    body TEXT,
    PRIMARY KEY (author_id, id)  -- Must include distribution column
);

-- Distribute the table across shards
SELECT create_distributed_table('articles', 'author_id');

-- Create a ParadeDB index on the distributed table
CREATE INDEX articles_search_idx ON articles
USING paradedb (id, title, body);

-- Insert some data
INSERT INTO articles (author_id, title, body) VALUES
    (1, 'PostgreSQL Performance', 'Optimizing PostgreSQL queries for large datasets'),
    (1, 'Distributed Databases', 'Understanding sharding and replication strategies'),
    (2, 'Full-Text Search', 'Building search engines with PostgreSQL');

-- Search across shards
SELECT id, title FROM articles
WHERE body ||| 'PostgreSQL distributed'
ORDER BY id;

-- Results:
--  id |         title
-- ----+------------------------
--   1 | PostgreSQL Performance
--   3 | Full-Text Search
```

--------------------------------

### Install ParadeDB on Ubuntu 26.04

Source: https://www.paradedb.com/docs/operate/deploy/self-hosted/extension

Download and install the ParadeDB package for Ubuntu 26.04 (Resolute) using apt-get.

```bash
curl -L "https://github.com/paradedb/paradedb/releases/download/v0.26.2/postgresql-18-pg-search_0.26.2-1PARADEDB-resolute_amd64.deb" -o /tmp/pg_search.deb
sudo apt-get install -y /tmp/*.deb
```

--------------------------------

### Install ParadeDB on RHEL 9

Source: https://www.paradedb.com/docs/operate/deploy/self-hosted/extension

Download and install the ParadeDB package for RHEL 9 using dnf.

```bash
curl -L "https://github.com/paradedb/paradedb/releases/download/v0.26.2/pg_search_18-0.26.2-1PARADEDB.el9.x86_64.rpm" -o /tmp/pg_search.rpm
sudo dnf install -y /tmp/*.rpm
```

--------------------------------

### Install ParadeDB on RHEL 10

Source: https://www.paradedb.com/docs/operate/deploy/self-hosted/extension

Download and install the ParadeDB package for RHEL 10 using dnf.

```bash
curl -L "https://github.com/paradedb/paradedb/releases/download/v0.26.2/pg_search_18-0.26.2-1PARADEDB.el10.x86_64.rpm" -o /tmp/pg_search.rpm
sudo dnf install -y /tmp/*.rpm
```

--------------------------------

### Faceted Query Output Example

Source: https://www.paradedb.com/docs/reference/aggregates/facets

Example output format for a faceted query.

```text
 id |         description         | rating |      agg
----+-----------------------------+--------+----------------
 12 | Innovative wireless earbuds |      5 | {"value": 5.0}
  1 | Ergonomic metal keyboard    |      4 | {"value": 5.0}
  2 | Plastic Keyboard            |      4 | {"value": 5.0}
(3 rows)
```

--------------------------------

### Install ParadeDB agent skill

Source: https://www.paradedb.com/docs/start/ai-agents

Use this command to install the paradedb-skill into your agent's skills directory.

```bash
npx skills add paradedb/agent-skills
```

--------------------------------

### Launch ParadeDB Cluster

Source: https://www.paradedb.com/docs/operate/deploy/self-hosted/kubernetes

Install the ParadeDB Helm chart to launch the cluster.

```bash
helm repo add paradedb https://paradedb.github.io/charts
helm upgrade --atomic --install paradedb \
--namespace paradedb \
--values values.yaml \
--set cluster.monitoring.enabled=true \
paradedb/paradedb
```

--------------------------------

### Install Citus and Configure shared_preload_libraries

Source: https://www.paradedb.com/docs/reference/sharding/citus

Commands to install the Citus extension and update the PostgreSQL configuration to include both citus and pg_search in the correct order.

```bash
# Install Citus first
curl https://install.citusdata.com/community/deb.sh | sudo bash
apt-get install -y postgresql-18-citus-14.1

# Add both extensions to shared_preload_libraries
sed -i "s/^shared_preload_libraries = .*/shared_preload_libraries = 'citus,pg_search'/" /var/lib/postgresql/data/postgresql.conf

# Restart Postgres
# Then create extensions in your database
```

--------------------------------

### Install ParadeDB on Ubuntu 24.04

Source: https://www.paradedb.com/docs/operate/deploy/self-hosted/extension

Download and install the ParadeDB package for Ubuntu 24.04 (Noble) using apt-get.

```bash
curl -L "https://github.com/paradedb/paradedb/releases/download/v0.26.2/postgresql-18-pg-search_0.26.2-1PARADEDB-noble_amd64.deb" -o /tmp/pg_search.deb
sudo apt-get install -y /tmp/*.deb
```

--------------------------------

### Install ParadeDB on Debian 13

Source: https://www.paradedb.com/docs/operate/deploy/self-hosted/extension

Download and install the ParadeDB package for Debian 13 (Trixie) using apt-get.

```bash
curl -L "https://github.com/paradedb/paradedb/releases/download/v0.26.2/postgresql-18-pg-search_0.26.2-1PARADEDB-trixie_amd64.deb" -o /tmp/pg_search.deb
sudo apt-get install -y /tmp/*.deb
```

--------------------------------

### Initialize Drizzle project for ParadeDB

Source: https://www.paradedb.com/docs/start/configure-your-environment

Install the necessary dependencies to use Drizzle with ParadeDB.

```bash
npm init -y
npm pkg set type=module
npm install drizzle-orm@1.0.0-rc.4 postgres @paradedb/drizzle-paradedb@0.6.0 tsx
```

--------------------------------

### Example output for listing indexes

Source: https://www.paradedb.com/docs/operate/index-maintenance/verify-index

The expected tabular output when listing indexes.

```text
 schemaname |  tablename  |   indexname   | indexrelid | num_segments | total_docs
------------+-------------+---------------+------------+--------------+------------
 public     | products    | products_idx  |      16421 |            3 |      50000
 public     | documents   | documents_idx |      16435 |            5 |     125000
 app        | articles    | articles_idx  |      16448 |            2 |      10000
```

--------------------------------

### Facet query with SQLAlchemy

Source: https://www.paradedb.com/docs/start/run-queries

SQLAlchemy example using facets.with_rows and search.match_any to combine rows and aggregate.

```python
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, search

base = (
    select(MockItem.description, MockItem.rating, MockItem.category)
    .where(search.match_any(MockItem.description, "running shoes"))
    .order_by(MockItem.rating)
    .limit(5)
)

stmt = facets.with_rows(base, agg=facets.value_count(field="id"), field=MockItem.id)

with Session(engine) as session:
    rows = session.execute(stmt).all()
    facets.extract(rows)

```

--------------------------------

### Initialize .NET project

Source: https://www.paradedb.com/docs/start/configure-your-environment

Create a new .NET console project and install necessary EF Core packages.

```bash
dotnet new console --framework net10.0
dotnet new tool-manifest
dotnet tool install dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package ParadeDB.EntityFrameworkCore --version 0.4.0
```

--------------------------------

### Full Program.cs Implementation

Source: https://www.paradedb.com/docs/start/configure-your-environment

A complete example including the DbContext, model definition, and a query scratchpad for interacting with the mock_items table.

```csharp
using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using NpgsqlTypes;
using ParadeDB.EntityFrameworkCore;
using ParadeDB.EntityFrameworkCore.Extensions;

await using var dbContext = new AppDbContext();

// Replace this with the query you want to run.
var results = await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAny(item.Description, "running shoes") && item.Rating > 2
    )
    .OrderBy(item => item.Rating)
    .Select(item => new { item.Description, item.Rating, item.Category })
    .Take(5)
    .ToListAsync();

PrintResults(results);

static void PrintResults<T>(IReadOnlyList<T> rows)
{
  var properties = typeof(T).GetProperties();

  foreach (var property in properties)
  {
      Console.Write($"{property.Name,-24}");
  }

  Console.WriteLine();
  Console.WriteLine(new string('-', properties.Length * 24));

  foreach (var row in rows)
  {
      foreach (var property in properties)
      {
          var value = property.GetValue(row)?.ToString() ?? "";
          value = value.Length > 21 ? value[..21] + "..." : value;

          Console.Write($"{value,-24}");
      }

      Console.WriteLine();
  }
}

public sealed class AppDbContext : DbContext
{
    public DbSet<MockItem> MockItems => Set<MockItem>();

    protected override void OnConfiguring(DbContextOptionsBuilder options) =>
        options.UseNpgsql("Host=localhost;Port=5432;Database=paradedb;Username=postgres;Password=<PASSWORD>", npgsql => npgsql.UseParadeDb());

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MockItem>(entity =>
        {
            entity.ToTable("mock_items");
            entity.HasKey(item => item.Id);

            entity.Property(item => item.Id).HasColumnName("id");
            entity.Property(item => item.Description).HasColumnName("description");
            entity.Property(item => item.Rating).HasColumnName("rating");
            entity.Property(item => item.Category).HasColumnName("category").HasColumnType("varchar(255)");
            entity.Property(item => item.InStock).HasColumnName("in_stock");
            entity.Property(item => item.Metadata).HasColumnName("metadata").HasColumnType("jsonb");
            entity.Property(item => item.CreatedAt).HasColumnName("created_at").HasColumnType("timestamp");
            entity.Property(item => item.LastUpdatedDate).HasColumnName("last_updated_date").HasColumnType("date");
            entity.Property(item => item.LatestAvailableTime).HasColumnName("latest_available_time").HasColumnType("time");
            entity.Property(item => item.WeightRange).HasColumnName("weight_range").HasColumnType("int4range");
            entity.Property(item => item.Embedding).HasColumnName("embedding").HasColumnType("vector(8)");
        });
    }
}

public sealed class MockItem
{
    public int Id { get; set; }
    public string? Description { get; set; }
    public int? Rating { get; set; }
    public string? Category { get; set; }
    public bool? InStock { get; set; }
    public JsonDocument? Metadata { get; set; }
    public DateTime? CreatedAt { get; set; }
    public DateOnly? LastUpdatedDate { get; set; }
    public TimeOnly? LatestAvailableTime { get; set; }
    public NpgsqlRange<int>? WeightRange { get; set; }
    public float[]? Embedding { get; set; }
}

```

--------------------------------

### SQL: SELECT with regex query

Source: https://www.paradedb.com/docs/reference/full-text/regex

Example of using a regex query in SQL to search for terms starting with 'key'.

```APIDOC
## SQL: SELECT with regex query

### Description
Performs a regex search on the description column to find terms matching the pattern 'key.*'.

### Method
SELECT

### Endpoint
N/A (SQL query)

### Parameters
#### Query Parameters
- **pattern** (string) - Required - The regex pattern to match.

### Request Example
```sql
SELECT description, rating, category
FROM mock_items
WHERE description @@@ pdb.regex('key.*');
```

### Response
#### Success Response (200)
- **description** (string) - The description field.
- **rating** (number) - The rating field.
- **category** (string) - The category field.

#### Response Example
```json
{
  "description": "keyboard",
  "rating": 4.5,
  "category": "electronics"
}
```
```

--------------------------------

### Get First Snippet

Source: https://www.paradedb.com/docs/reference/full-text/highlight

Example of using the snippets function with limit=1 to retrieve only the first snippet from search results.

```APIDOC
## snippets Function with limit=1

### Description
Retrieves only the first snippet from the search results for the matched documents.

### Parameters
- **max_num_chars** (integer) - Required - Maximum number of characters in the snippet.
- **limit** (integer) - Required - Number of snippets to return.

### Example (SQL)
```sql
SELECT id, pdb.snippets(description, max_num_chars => 15, "limit" => 1)
FROM mock_items
WHERE description ||| 'running'
LIMIT 5;
```

### Example (Drizzle)
```javascript
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    id: mockItems.id,
    snippets: search.snippets(mockItems.description, {
      maxNumChars: 15,
      limit: 1,
    }),
  })
  .from(mockItems)
  .where(search.matchAny(mockItems.description, "running"))
  .limit(5);
```

### Example (Django)
```python
from paradedb import MatchAny, ParadeDB, Snippets

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running'))
).annotate(
    snippets=Snippets('description', max_num_chars=15, limit=1)
).values('id', 'snippets')[:5]
```

### Example (SQLAlchemy)
```python
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import pdb, search

stmt = (
    select(MockItem.id, pdb.snippets(MockItem.description, max_num_chars=15, limit=1).label("snippets"))
    .where(search.match_any(MockItem.description, "running"))
    .limit(5)
)

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

### Example (Rails)
```ruby
MockItem.search(:description)
        .match_any("running")
        .with_snippets(:description, max_chars: 15, limit: 1)
        .select(:id)
        .limit(5)
```

### Example (EF Core)
```csharp
var snippetsOptions = new SnippetsOptions { MaxNumChars = 15, Limit = 1 };

await dbContext
    .MockItems.Where(item => EF.Functions.MatchAny(item.Description, "running"))
    .Select(item => new
    {
        item.Id,
        Snippets = EF.Functions.Snippets(item.Description, snippetsOptions)
    })
    .Take(5)
    .ToListAsync();
```
```

--------------------------------

### Create Index with regex_pattern

Source: https://www.paradedb.com/docs/reference/tokenizers/available-tokenizers/regex

Example of creating an index using the regex_pattern tokenizer to capture words starting with the letter 'h'.

```sql
CREATE INDEX search_idx ON mock_items
USING paradedb (id, (description::pdb.regex_pattern('(?i)\bh\w*')));
```

--------------------------------

### Facet query with Django ORM

Source: https://www.paradedb.com/docs/start/run-queries

Django example using ParadeDB MatchAny and facets method to get top results and aggregate.

```python
from paradedb import MatchAny, ParadeDB

(
    MockItem.objects
    .filter(description=ParadeDB(MatchAny('running shoes')))
    .order_by('rating')
    .values('description', 'rating', 'category')[:5]
    .facets(agg='{"value_count": {"field": "id"}}')
)

```

--------------------------------

### Get Second Snippet (Offset)

Source: https://www.paradedb.com/docs/reference/full-text/highlight

Example of using the snippets function with limit=1 and offset=1 to skip the first snippet and retrieve the second one.

```APIDOC
## snippets Function with limit=1 and offset=1

### Description
Retrieves the second snippet from the search results by skipping the first one using the offset parameter.

### Parameters
- **max_num_chars** (integer) - Required - Maximum number of characters in the snippet.
- **limit** (integer) - Required - Number of snippets to return.
- **offset** (integer) - Required - Number of snippets to skip before starting to return results.

### Example (SQL)
```sql
SELECT id, pdb.snippets(description, max_num_chars => 15, "limit" => 1, "offset" => 1)
FROM mock_items
WHERE description ||| 'running'
LIMIT 5;
```

### Example (Drizzle)
```javascript
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    id: mockItems.id,
    snippets: search.snippets(mockItems.description, {
      maxNumChars: 15,
      limit: 1,
      offset: 1,
    }),
  })
  .from(mockItems)
  .where(search.matchAny(mockItems.description, "running"))
  .limit(5);
```

### Example (Django)
```python
from paradedb import MatchAny, ParadeDB, Snippets

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running'))
).annotate(
    snippets=Snippets('description', max_num_chars=15, limit=1, offset=1)
).values('id', 'snippets')[:5]
```

### Example (SQLAlchemy)
```python
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import pdb, search

stmt = (
    select(
        MockItem.id,
        pdb.snippets(MockItem.description, max_num_chars=15, limit=1, offset=1).label("snippets"),
    )
    .where(search.match_any(MockItem.description, "running"))
    .limit(5)
)

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

### Example (Rails)
```ruby
MockItem.search(:description)
        .match_any("running")
        .with_snippets(:description, max_chars: 15, limit: 1, offset: 1)
        .select(:id)
        .limit(5)
```

### Example (EF Core)
```csharp
var snippetsOptions = new SnippetsOptions { MaxNumChars = 15, Limit = 1, Offset = 1 };

await dbContext
    .MockItems.Where(item => EF.Functions.MatchAny(item.Description, "running"))
    .Select(item => new
    {
        item.Id,
        Snippets = EF.Functions.Snippets(item.Description, snippetsOptions)
    })
    .Take(5)
    .ToListAsync();
```
```

--------------------------------

### Example of search_tokenizer usage

Source: https://www.paradedb.com/docs/reference/tokenizers/search-tokenizer

Demonstrates creating a table and index with a search_tokenizer, followed by search queries.

```sql
CREATE TABLE products (
    id serial8 NOT NULL PRIMARY KEY,
    title text
);
INSERT INTO products (title) VALUES
    ('shoes'), ('shirt'), ('shorts'), ('shoelaces'), ('socks');

CREATE INDEX idx_products ON products USING paradedb
    (id, (title::pdb.edge_ngram(1, 10)))
    WITH (search_tokenizer = 'unicode_words');

-- "sho" stays as one token → matches shoes, shorts, shoelaces
SELECT id, title FROM products WHERE title ||| 'sho' ORDER BY id;

-- "s" stays as one token → matches all five titles
SELECT id, title FROM products WHERE title ||| 's' ORDER BY id;
```

--------------------------------

### Initialize Rails project

Source: https://www.paradedb.com/docs/start/configure-your-environment

Create a new Rails application configured for PostgreSQL.

```bash
rails new paradedb -d postgresql
cd paradedb
```

--------------------------------

### Top Hits Aggregation - Rails

Source: https://www.paradedb.com/docs/reference/aggregates/metrics/tophits

Rails example using ParadeDB::Aggregations.top_hits to get top 3 results sorted by created_at for each rating category.

```APIDOC
## Top Hits Aggregation - Rails

### Description
Returns the top documents for each bucket of a terms aggregation using the top_hits aggregation.

### Method
SELECT

### Endpoint
N/A (Rails query)

### Parameters
#### Aggregation Parameters
- **top_hits** (object) - Required - The top_hits aggregation definition.
  - **size** (integer) - Required - Number of top documents to return per bucket.
  - **sort** (array) - Required - Sort criteria for the top documents.
  - **docvalue_fields** (array) - Required - Fields to return in the response.
  - **from** (integer) - Optional - Offset for pagination.

### Request Example
```ruby
MockItem.search(:id)
        .match_all
        .aggregate_by(
          :rating,
          agg: ParadeDB::Aggregations.top_hits(
            size: 3,
            sort: [{ created_at: "desc" }],
            docvalue_fields: %w[id created_at]
          )
        )
```

### Response
#### Success Response
Returns a result set with an `agg` column containing the top hits for each bucket.

#### Response Example
```
agg
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"hits": [{"sort": [10907000251854775808], "docvalue_fields": {"id": [25], "created_at": ["2023-05-09T10:30:15Z"]}}, {"sort": [10906844884854775808], "docvalue_fields": {"id": [26], "created_at": ["2023-05-07T15:20:48Z"]}}, {"sort": [10906666358854775808], "docvalue_fields": {"id": [13], "created_at": ["2023-05-05T13:45:22Z"]}}]}
 {"hits": [{"sort": [10906756363854775808], "docvalue_fields": {"id": [24], "created_at": ["2023-05-06T14:45:27Z"]}}, {"sort": [10906385295854775808], "docvalue_fields": {"id": [28], "created_at": ["2023-05-02T07:40:59Z"]}}, {"sort": [10906236353854775808], "docvalue_fields": {"id": [29], "created_at": ["2023-04-30T14:18:37Z"]}}]}
 {"hits": [{"sort": [10906480573854775808], "docvalue_fields": {"id": [17], "created_at": ["2023-05-03T10:08:57Z"]}}, {"sort": [10906315942854775808], "docvalue_fields": {"id": [20], "created_at": ["2023-05-01T12:25:06Z"]}}, {"sort": [10906218361854775808], "docvalue_fields": {"id": [8], "created_at": ["2023-04-30T09:18:45Z"]}}]}
 {"hits": [{"sort": [10906573359854775808], "docvalue_fields": {"id": [27], "created_at": ["2023-05-04T11:55:23Z"]}}, {"sort": [10905961160854775808], "docvalue_fields": {"id": [15], "created_at": ["2023-04-27T09:52:04Z"]}}, {"sort": [10905202003854775808], "docvalue_fields": {"id": [7], "created_at": ["2023-04-18T14:59:27Z"]}}]}
 {"hits": [{"sort": [10906586188854775808], "docvalue_fields": {"id": [10], "created_at": ["2023-05-04T15:29:12Z"]}}]}
(5 rows)
```

### Notes
- The `sort` value returned is Tantivy's internal sort ID and should be ignored.
- To get actual fields, pass a list of fields to `docvalue_fields`.
- If a text or JSON field is passed to `docvalue_fields`, it must be indexed with the literal or literal normalized tokenizer.
- To specify an offset, use `from`.
```

--------------------------------

### Initialize Alembic

Source: https://www.paradedb.com/docs/start/configure-your-environment

Run the initialization command to create the migrations directory.

```bash
alembic init migrations
```

--------------------------------

### Top Hits Aggregation - SQLAlchemy

Source: https://www.paradedb.com/docs/reference/aggregates/metrics/tophits

SQLAlchemy example using pdb.agg and facets.top_hits to get top 3 results sorted by created_at for each rating category.

```APIDOC
## Top Hits Aggregation - SQLAlchemy

### Description
Returns the top documents for each bucket of a terms aggregation using the top_hits aggregation.

### Method
SELECT

### Endpoint
N/A (SQLAlchemy query)

### Parameters
#### Aggregation Parameters
- **top_hits** (object) - Required - The top_hits aggregation definition.
  - **size** (integer) - Required - Number of top documents to return per bucket.
  - **sort** (array) - Required - Sort criteria for the top documents.
  - **docvalue_fields** (array) - Required - Fields to return in the response.
  - **from** (integer) - Optional - Offset for pagination.

### Request Example
```python
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(
        pdb.agg(
            facets.top_hits(
                size=3,
                sort=[{"created_at": "desc"}],
                docvalue_fields=["id", "created_at"],
            )
        )
    )
    .select_from(MockItem)
    .where(search.all(MockItem.id))
    .group_by(MockItem.rating)
)

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

### Response
#### Success Response
Returns a result set with an `agg` column containing the top hits for each bucket.

#### Response Example
```
agg
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"hits": [{"sort": [10907000251854775808], "docvalue_fields": {"id": [25], "created_at": ["2023-05-09T10:30:15Z"]}}, {"sort": [10906844884854775808], "docvalue_fields": {"id": [26], "created_at": ["2023-05-07T15:20:48Z"]}}, {"sort": [10906666358854775808], "docvalue_fields": {"id": [13], "created_at": ["2023-05-05T13:45:22Z"]}}]}
 {"hits": [{"sort": [10906756363854775808], "docvalue_fields": {"id": [24], "created_at": ["2023-05-06T14:45:27Z"]}}, {"sort": [10906385295854775808], "docvalue_fields": {"id": [28], "created_at": ["2023-05-02T07:40:59Z"]}}, {"sort": [10906236353854775808], "docvalue_fields": {"id": [29], "created_at": ["2023-04-30T14:18:37Z"]}}]}
 {"hits": [{"sort": [10906480573854775808], "docvalue_fields": {"id": [17], "created_at": ["2023-05-03T10:08:57Z"]}}, {"sort": [10906315942854775808], "docvalue_fields": {"id": [20], "created_at": ["2023-05-01T12:25:06Z"]}}, {"sort": [10906218361854775808], "docvalue_fields": {"id": [8], "created_at": ["2023-04-30T09:18:45Z"]}}]}
 {"hits": [{"sort": [10906573359854775808], "docvalue_fields": {"id": [27], "created_at": ["2023-05-04T11:55:23Z"]}}, {"sort": [10905961160854775808], "docvalue_fields": {"id": [15], "created_at": ["2023-04-27T09:52:04Z"]}}, {"sort": [10905202003854775808], "docvalue_fields": {"id": [7], "created_at": ["2023-04-18T14:59:27Z"]}}]}
 {"hits": [{"sort": [10906586188854775808], "docvalue_fields": {"id": [10], "created_at": ["2023-05-04T15:29:12Z"]}}]}
(5 rows)
```

### Notes
- The `sort` value returned is Tantivy's internal sort ID and should be ignored.
- To get actual fields, pass a list of fields to `docvalue_fields`.
- If a text or JSON field is passed to `docvalue_fields`, it must be indexed with the literal or literal normalized tokenizer.
- To specify an offset, use `from`.
```

--------------------------------

### Top Hits Aggregation - SQL

Source: https://www.paradedb.com/docs/reference/aggregates/metrics/tophits

SQL example using pdb.agg with top_hits to get top 3 results sorted by created_at for each rating category.

```APIDOC
## Top Hits Aggregation - SQL

### Description
Returns the top documents for each bucket of a terms aggregation using the top_hits aggregation.

### Method
SELECT

### Endpoint
N/A (SQL query)

### Parameters
#### Aggregation Parameters
- **top_hits** (object) - Required - The top_hits aggregation definition.
  - **size** (integer) - Required - Number of top documents to return per bucket.
  - **sort** (array) - Required - Sort criteria for the top documents.
  - **docvalue_fields** (array) - Required - Fields to return in the response.
  - **from** (integer) - Optional - Offset for pagination.

### Request Example
```sql
SELECT pdb.agg('{"top_hits": {"size": 3, "sort": [{"created_at": "desc"}], "docvalue_fields": ["id", "created_at"]}}')
FROM mock_items
WHERE id @@@ pdb.all()
GROUP BY rating;
```

### Response
#### Success Response
Returns a table with an `agg` column containing the top hits for each bucket.

#### Response Example
```
agg
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"hits": [{"sort": [10907000251854775808], "docvalue_fields": {"id": [25], "created_at": ["2023-05-09T10:30:15Z"]}}, {"sort": [10906844884854775808], "docvalue_fields": {"id": [26], "created_at": ["2023-05-07T15:20:48Z"]}}, {"sort": [10906666358854775808], "docvalue_fields": {"id": [13], "created_at": ["2023-05-05T13:45:22Z"]}}]}
 {"hits": [{"sort": [10906756363854775808], "docvalue_fields": {"id": [24], "created_at": ["2023-05-06T14:45:27Z"]}}, {"sort": [10906385295854775808], "docvalue_fields": {"id": [28], "created_at": ["2023-05-02T07:40:59Z"]}}, {"sort": [10906236353854775808], "docvalue_fields": {"id": [29], "created_at": ["2023-04-30T14:18:37Z"]}}]}
 {"hits": [{"sort": [10906480573854775808], "docvalue_fields": {"id": [17], "created_at": ["2023-05-03T10:08:57Z"]}}, {"sort": [10906315942854775808], "docvalue_fields": {"id": [20], "created_at": ["2023-05-01T12:25:06Z"]}}, {"sort": [10906218361854775808], "docvalue_fields": {"id": [8], "created_at": ["2023-04-30T09:18:45Z"]}}]}
 {"hits": [{"sort": [10906573359854775808], "docvalue_fields": {"id": [27], "created_at": ["2023-05-04T11:55:23Z"]}}, {"sort": [10905961160854775808], "docvalue_fields": {"id": [15], "created_at": ["2023-04-27T09:52:04Z"]}}, {"sort": [10905202003854775808], "docvalue_fields": {"id": [7], "created_at": ["2023-04-18T14:59:27Z"]}}]}
 {"hits": [{"sort": [10906586188854775808], "docvalue_fields": {"id": [10], "created_at": ["2023-05-04T15:29:12Z"]}}]}
(5 rows)
```

### Notes
- The `sort` value returned is Tantivy's internal sort ID and should be ignored.
- To get actual fields, pass a list of fields to `docvalue_fields`.
- If a text or JSON field is passed to `docvalue_fields`, it must be indexed with the literal or literal normalized tokenizer.
- To specify an offset, use `from`.
```

--------------------------------

### Top Hits Aggregation - Django

Source: https://www.paradedb.com/docs/reference/aggregates/metrics/tophits

Django ORM example using Agg with top_hits to get top 3 results sorted by created_at for each rating category.

```APIDOC
## Top Hits Aggregation - Django

### Description
Returns the top documents for each bucket of a terms aggregation using the top_hits aggregation.

### Method
SELECT

### Endpoint
N/A (Django query)

### Parameters
#### Aggregation Parameters
- **top_hits** (object) - Required - The top_hits aggregation definition.
  - **size** (integer) - Required - Number of top documents to return per bucket.
  - **sort** (array) - Required - Sort criteria for the top documents.
  - **docvalue_fields** (array) - Required - Fields to return in the response.
  - **from** (integer) - Optional - Offset for pagination.

### Request Example
```python
from paradedb import Agg, All, ParadeDB

MockItem.objects.filter(
    id=ParadeDB(All())
).values('rating').annotate(
    agg=Agg('{"top_hits": {"size": 3, "sort": [{"created_at": "desc"}], "docvalue_fields": ["id", "created_at"]}}')
).values('agg')
```

### Response
#### Success Response
Returns a queryset with an `agg` field containing the top hits for each bucket.

#### Response Example
```
agg
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"hits": [{"sort": [10907000251854775808], "docvalue_fields": {"id": [25], "created_at": ["2023-05-09T10:30:15Z"]}}, {"sort": [10906844884854775808], "docvalue_fields": {"id": [26], "created_at": ["2023-05-07T15:20:48Z"]}}, {"sort": [10906666358854775808], "docvalue_fields": {"id": [13], "created_at": ["2023-05-05T13:45:22Z"]}}]}
 {"hits": [{"sort": [10906756363854775808], "docvalue_fields": {"id": [24], "created_at": ["2023-05-06T14:45:27Z"]}}, {"sort": [10906385295854775808], "docvalue_fields": {"id": [28], "created_at": ["2023-05-02T07:40:59Z"]}}, {"sort": [10906236353854775808], "docvalue_fields": {"id": [29], "created_at": ["2023-04-30T14:18:37Z"]}}]}
 {"hits": [{"sort": [10906480573854775808], "docvalue_fields": {"id": [17], "created_at": ["2023-05-03T10:08:57Z"]}}, {"sort": [10906315942854775808], "docvalue_fields": {"id": [20], "created_at": ["2023-05-01T12:25:06Z"]}}, {"sort": [10906218361854775808], "docvalue_fields": {"id": [8], "created_at": ["2023-04-30T09:18:45Z"]}}]}
 {"hits": [{"sort": [10906573359854775808], "docvalue_fields": {"id": [27], "created_at": ["2023-05-04T11:55:23Z"]}}, {"sort": [10905961160854775808], "docvalue_fields": {"id": [15], "created_at": ["2023-04-27T09:52:04Z"]}}, {"sort": [10905202003854775808], "docvalue_fields": {"id": [7], "created_at": ["2023-04-18T14:59:27Z"]}}]}
 {"hits": [{"sort": [10906586188854775808], "docvalue_fields": {"id": [10], "created_at": ["2023-05-04T15:29:12Z"]}}]}
(5 rows)
```

### Notes
- The `sort` value returned is Tantivy's internal sort ID and should be ignored.
- To get actual fields, pass a list of fields to `docvalue_fields`.
- If a text or JSON field is passed to `docvalue_fields`, it must be indexed with the literal or literal normalized tokenizer.
- To specify an offset, use `from`.
```

--------------------------------

### Create and Populate Array Table

Source: https://www.paradedb.com/docs/reference/indexing/indexing-arrays

Example SQL to create a table with a text array column and insert sample data.

```sql
CREATE TABLE array_demo (id SERIAL PRIMARY KEY, categories TEXT[]);
INSERT INTO array_demo (categories) VALUES
    ('{"food","groceries and produce"}'),
    ('{"electronics","computers"}'),
    ('{"books","fiction","mystery"}');
```
Tokens:
Raw