Optimizing Analytical Functions for Big Data: Indexing and Partitioning

When you have a ton of data (like those endless deadline messages in corporate chats), your SELECT and processing queries start to slow down. Here are the main reasons why:

1. No indexes. When PostgreSQL has to scan your whole table to run a query (that’s called a "Seq Scan" — sequential scan), your query can take way longer.
2. Inefficient SQL queries. If your queries aren’t built with optimization in mind, even with indexes you might hit production headaches. For example, forgot to use key conditions in WHERE? Get ready for a long wait.
3. Huge amounts of data in one table. Like when you try to analyze sales for all years at once, even indexes might not save you.

But don’t worry, we’ve got two proven ways to deal with this: Indexing and Partitioning.

Using Indexes to Speed Up Queries

Here’s a simple example of creating an index:

CREATE INDEX idx_sales_date ON sales(transaction_date);

• Here, idx_sales_date is the name of the index (you can call it whatever you want, but it’s better to use something meaningful).
• ON sales(transaction_date) — tells which table and column the index is for.

This index is especially useful if you often filter queries by the transaction_date field.

Here’s a query that’ll benefit from this index:

SELECT *
FROM sales
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

Indexing Composite Keys

If your queries often use a combo of several fields, like region and product_id, consider creating a composite index:

CREATE INDEX idx_sales_region_product ON sales(region, product_id);

Now queries like this will run way faster:

SELECT *
FROM sales
WHERE region = 'North America' AND product_id = 42;

Using Unique Indexes

Unique indexes not only speed up lookups, but also guarantee uniqueness of values in a column. For example:

CREATE UNIQUE INDEX idx_unique_customer_email ON customers(email);

Now you can’t accidentally add two customers with the same email.

Indexing for Analytical Functions

Some data analysis functions, like SUM, COUNT, or AVG, can use an index to count values faster. Here’s an example:

CREATE INDEX idx_sales_amount ON sales(amount);

Query:

SELECT SUM(amount)
FROM sales 
WHERE transaction_date >= '2023-01-01';

will run faster thanks to the index.

Partitioning Tables for Big Data

Table partitioning is the process of splitting a big table into smaller logical pieces called partitions. For example, you can split the sales table into partitions by year: sales_2021, sales_2022, etc.

Think it’s hard? Actually, PostgreSQL makes it easier than you’d expect.

Types of Partitioning

1. Range Partitioning (Range Partitioning). Data is split based on a range, like by date.
2. List Partitioning (List Partitioning). Data is split based on exact values, like by regions.
3. Hash Partitioning (Hash Partitioning). Uses a hash function to split data (rarely used manually).

Creating a Partitioned Table

Let’s create a sales table partitioned by year.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    amount NUMERIC,
    region TEXT
) PARTITION BY RANGE (transaction_date);

Now let’s create partitions for different years:

CREATE TABLE sales_2021 PARTITION OF sales
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE sales_2022 PARTITION OF sales
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Queries filtering by date will automatically work only with the needed partition. You can easily check this using the EXPLAIN command.

Partitioning Example

Here’s how a query to sum sales for just 2021 would look:

SELECT SUM(amount)
FROM sales
WHERE transaction_date BETWEEN '2021-01-01' AND '2021-12-31';

As you can see, PostgreSQL works only with the needed partition sales_2021, not the whole table.

Example: Optimizing Metric Calculation by Region

Let’s say you want to calculate total sales by region. Without indexes and partitions, this takes forever. First, let’s create an index for the region column:

CREATE INDEX idx_sales_region ON sales(region);

Your query:

SELECT region, SUM(amount)
FROM sales
GROUP BY region;

Now processing is faster thanks to the index.

Example: Partitioning Time-Based Data

For time-based data, like transactions or logs, create partitions by month. For example:

CREATE TABLE sales_monthly PARTITION BY RANGE (transaction_date);

CREATE TABLE sales_jan_2023 PARTITION OF sales_monthly
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

Query:

SELECT SUM(amount)
FROM sales_monthly
WHERE transaction_date >= '2023-01-01' AND transaction_date < '2023-02-01';

will work faster, since PostgreSQL reads only the sales_jan_2023 partition.

Example: Combining Indexing and Partitioning

You can combine indexing and partitioning to get max performance. For example, you can create indexes inside each partition. Here’s an example:

CREATE INDEX idx_sales_amount_jan_2023 ON sales_jan_2023(amount);

How to Avoid Common Mistakes

A lot of performance issues come from using indexes and partitioning the wrong way. For example:

• Having too many indexes can slow down insert operations.
• You should design partitions so they’re evenly filled; partitions that are too small or too big hurt performance.
• Forgetting to analyze performance (EXPLAIN ANALYZE) before rolling out optimizations — that’s like trying to fix a car without looking under the hood.

Always check if your optimizations actually give you a real speed boost, and don’t be afraid to experiment.