What is partition in PSQL?

The `PARTITION BY` clause in PostgreSQL is used with window functions or for table partitioning. In the context of window functions, it defines a set of rows, or a "window," over which the function operates. When used for table partitioning, it defines how the table is divided into partitions based on the column values.

Partition By with Window Functions

In window functions, `PARTITION BY` allows you to partition a result set into subsets over which the window function is applied, without collapsing the rows into a single output row like `GROUP BY` does. Each partition is processed separately by the window function.

Here's a basic example using `PARTITION BY` with a window function:

SELECT

  department,

  salary,

  avg(salary) OVER (PARTITION BY department) as avg_department_salary

FROM

  employees;

In this query, we calculate the average salary (avg_department_salary) for each department without collapsing the result set into one row per department. Each row in the output will show the employee's salary and the average salary for that employee's department.

Partition By for Table Partitioning

When defining a partitioned table, `PARTITION BY` is used to specify how the table should be divided into partitions. PostgreSQL supports range, list, and hash partitioning.

Here's an example of creating a partitioned table using range partitioning:

CREATE TABLE measurement (

    city_id         int not null,

    logdate         date not null,

    peaktemp        int,

    unitsales       int

) PARTITION BY RANGE (logdate);

After creating the partitioned table, you would define individual partitions. For example:

CREATE TABLE measurement_y2020m01 PARTITION OF measurement

    FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');

This command creates a partition for January 2020. You would add similar tables for other months or ranges.

In summary, `PARTITION BY` is versatile in PostgreSQL, serving crucial roles in both window functions for analytical queries and in defining how tables are partitioned for performance optimization.

Fell free to checkout latest database jobs in kerala