intarray

What intarray does

intarray treats integer arrays as sets and provides operators for the kind of operations you would normally need to write procedural code or subqueries to accomplish: intersecting two arrays, computing their union, removing elements, and checking containment — all as single-expression operators.

Beyond operators, it adds a query_int data type that lets you write boolean expressions against array contents. A query like '1&(2|3)&!4'::query_int matches arrays that contain 1, contain either 2 or 3, and do not contain 4. This query language is indexable — GiST and GIN indexes on integer arrays can accelerate @@ queries.

The extension also provides three index operator classes: gist__int_ops and gist__intbig_ops for GiST, and gin__int_ops for GIN. These support overlap (&&), containment (@>), and boolean query (@@) operators.

When to use intarray

• Tag systems — store tags as integer arrays of tag IDs rather than junction tables, then query for articles matching complex tag combinations
• Permission and role bitmasks — check whether a user's role array contains the required roles using containment operators
• Many-to-many relationships stored as arrays — when the "many" side is small and read-heavy, integer arrays with intarray indexes can be faster than join tables
• Set arithmetic in queries — intersection, union, and difference on arrays without writing subqueries or procedural logic
• Boolean array search — the query_int type allows complex AND/OR/NOT queries on array contents, backed by index support

Installation and setup

intarray is a contrib module that ships with PostgreSQL — no extra packages to install, no shared library preloading, no restart. A single CREATE EXTENSION and you are in business. I appreciate an extension that does not make an event of its own arrival.

-- intarray is a contrib module — no extra packages needed
CREATE EXTENSION intarray;

-- Verify it's installed
SELECT * FROM pg_extension WHERE extname = 'intarray';

Operators

intarray overloads several operators for integer arrays and adds a few of its own. The & operator computes the intersection of two arrays, | computes the union, and - computes the difference. The @> and <@ operators test containment, and icount() returns the number of elements. All the set theory you sat through in university, available as single-character operators.

-- Sample data: articles with tag IDs stored as integer arrays
CREATE TABLE articles (
  id serial PRIMARY KEY,
  title text,
  tag_ids integer[]
);

INSERT INTO articles (title, tag_ids) VALUES
  ('Intro to SQL',     '{1,2,3}'),
  ('Advanced Postgres', '{2,3,4,5}'),
  ('Index Tuning',      '{1,4,6}'),
  ('Data Modeling',     '{3,5,7,8}');

-- Intersection: elements common to both arrays
SELECT icount('{1,2,3}'::int[] & '{2,3,4}'::int[]);  -- 2 (elements 2,3)

-- Union: merge two arrays
SELECT '{1,2,3}'::int[] | '{3,4,5}'::int[];  -- {1,2,3,4,5}

-- Difference: elements in first but not second
SELECT '{1,2,3,4}'::int[] - '{2,4}'::int[];  -- {1,3}

-- Containment: does the left array contain all elements of the right?
SELECT '{1,2,3,4}'::int[] @> '{2,4}'::int[];  -- true

-- Element count
SELECT icount('{1,2,3,4,5}'::int[]);  -- 5

GiST and GIN indexes

intarray provides three index operator classes. gist__int_ops stores arrays as compressed ranges and works well for small-to-medium arrays. gist__intbig_ops uses bitmap signatures and scales better for columns with many distinct values. gin__int_ops provides a GIN-based alternative that is often fastest for pure containment and overlap queries.

-- GiST index for small-to-medium arrays (default operator class)
CREATE INDEX idx_articles_tags_gist
  ON articles USING GIST (tag_ids gist__int_ops);

-- GiST index for columns with many distinct values (bitmap signature)
CREATE INDEX idx_articles_tags_gist_big
  ON articles USING GIST (tag_ids gist__intbig_ops);

-- GIN index (alternative — often faster for containment queries)
CREATE INDEX idx_articles_tags_gin
  ON articles USING GIN (tag_ids gin__int_ops);

-- All three index types support &&, @>, and @@ operators

Note that standard PostgreSQL GIN indexes on integer[] columns (without intarray) already support the &&, @>, and <@ operators. The intarray-specific operator classes add support for the @@ (query_int) operator and, in the case of GiST, offer a different performance profile with lossy compression.

Boolean queries with query_int

The query_int data type allows you to express complex boolean conditions on array contents in a single expression. Use & for AND, | for OR, ! for NOT, and parentheses for grouping.

-- query_int: boolean queries on array contents
-- Find articles tagged with both 1 AND 3
SELECT * FROM articles
WHERE tag_ids @@ '1&3'::query_int;

-- Find articles tagged with 2 OR 5
SELECT * FROM articles
WHERE tag_ids @@ '2|5'::query_int;

-- Find articles tagged with 1 AND (4 OR 5), but NOT 7
SELECT * FROM articles
WHERE tag_ids @@ '1&(4|5)&!7'::query_int;

These queries are indexable — if a GiST or GIN index using an intarray operator class exists on the column, the @@ operator will use it. Boolean logic and index support in a single expression. Tidy.

Utility functions

intarray includes several utility functions for manipulating integer arrays.

-- sort(), sort_asc(), sort_desc() — sort array elements
SELECT sort('{3,1,4,1,5,9}'::int[]);       -- {1,1,3,4,5,9}
SELECT sort_desc('{3,1,4,1,5,9}'::int[]);  -- {9,5,4,3,1,1}

-- uniq() — remove consecutive duplicates (sort first)
SELECT uniq(sort('{3,1,4,1,5,1}'::int[])); -- {1,3,4,5}

-- idx() — find position of an element (0 if not found)
SELECT idx('{10,20,30,40}'::int[], 30);     -- 3

-- subarray(array, start, length) — extract a slice
SELECT subarray('{10,20,30,40,50}'::int[], 2, 3); -- {20,30,40}

-- intset() — convert a single integer to a one-element array
SELECT intset(42);  -- {42}

Cloud availability

How Gold Lapel relates

I should note that Gold Lapel treats integer array columns and their indexes no differently from any other workload. Containment checks, overlap tests, query_int boolean expressions — we track their execution patterns and, if an integer array column appears frequently in WHERE clauses without an appropriate index, our recommendations will mention it.

intarray requires no special configuration on our end. It adds operators and index types but does not alter how queries are planned or routed through the proxy. The sort of extension that simply works and asks for nothing in return — I have always found those the easiest to recommend.