Examples of Operator Classes

Case-Insensitive Text Search

Imagine you are building a production app where users search for names, and case-sensitivity isn’t important. PostgreSQL’s default btree operator class for text columns is case-sensitive, meaning it will treat John and john as different values.

To make searches case-insensitive, you can use the text_pattern_ops operator class:

CREATE INDEX idx_username_ci ON users (username text_pattern_ops);

This index will speed up case-insensitive searches, like:

SELECT * FROM users WHERE username = 'john';

Without an optimized operator class, each search might require extra processing to handle case sensitivity, which can slow down response times—especially when dealing with millions of rows.

Optimizing Geometric Data Queries

In location-based applications (like maps or delivery services), geometric data types are common. Suppose you have a POINT column for store locations and need to find all stores within a specific radius of a user’s location. Using an operator class like point_ops optimizes distance-based comparisons.

CREATE INDEX idx_store_location ON stores (location point_ops);

With point_ops, PostgreSQL can handle distance calculations efficiently, ensuring location-based queries run smoothly. This is crucial when response time directly impacts the user experience.

Efficient Indexing for Custom Data Types

Creating a custom operator class in PostgreSQL requires a bit of groundwork, including defining operators that work with the custom data type. The following example is a simplified way to show how an operator class could be used for an encrypted_text type. This example assumes a basic encryption method (like hashing) that allows comparisons without decryption. In practice, you’d likely need to use cryptographic libraries and functions that PostgreSQL can call.

Step 1: Define the Custom Data Type

Let’s assume we have an encrypted_text type that represents hashed strings.

CREATE DOMAIN encrypted_text AS bytea;

Here, we are using bytea (binary data type) to store encrypted or hashed data.

Step 2: Create Comparison Functions for Encrypted Data

You would need a function to compare two encrypted values. Let’s say that for hashed values, equality (=) is the only operator we need.

CREATE FUNCTION compare_encrypted_text(a encrypted_text, b encrypted_text)

RETURNS boolean AS $$

BEGIN

    RETURN a = b;  -- This is simplified; in reality, you might involve hash comparisons

END;

$$ LANGUAGE plpgsql IMMUTABLE;

Step 3: Create the Operator Using the Comparison Function

We now define an operator that PostgreSQL can use in indexing and query comparisons.

CREATE OPERATOR = (

    LEFTARG = encrypted_text,

    RIGHTARG = encrypted_text,

    PROCEDURE = compare_encrypted_text

);

Step 4: Define the Operator Class

Now we can define the operator class for our encrypted_text type, telling PostgreSQL that it should use this class in indexes for encrypted_text columns.

CREATE OPERATOR CLASS encrypted_text_ops

DEFAULT FOR TYPE encrypted_text USING btree AS

    OPERATOR 1 =;

In this operator class:

• DEFAULT FOR TYPE encrypted_text specifies that this operator class is the default for encrypted_text columns.
• USING btree means this class works with B-tree indexes.
• OPERATOR 1 = tells PostgreSQL to use our equality operator (=) for comparisons in this index.

Step 5: Create an Index Using the Custom Operator Class

Now we can create an index using encrypted_text_ops.

CREATE INDEX idx_encrypted_text ON sensitive_data (hashed_value encrypted_text_ops);

This index now allows for efficient, indexed searches on encrypted_text fields without needing to decrypt the data. Queries can now use the index for comparisons, which saves processing power and helps secure sensitive information.