django-postgres-partition 0.1.4
Partition support for django, based on django-postgres-extra
Requires Python: >=3.11
Keywords: django, postgres, postgresql, partitioning, partition, database, psqlextra

# Django Postgres Partition

Fork of https://github.com/SectorLabs/django-postgres-extra with partition support only.

Status: Unstable, only change is renamed module.

`pip install django-postgres-partition`

## Features

* **PostgreSQL 11.x declarative table partitioning**

    * Supports range and list partitioning

## Prerequisites

* PostgreSQL 13+
* Django 5.2+
* Python 3.11+
* psycopg 3.2+

# Usage

`psql_partition.models.PostgresPartitionedModel` adds support for PostgreSQL Declarative Table Partitioning.

The following partitioning methods are available:

-   `PARTITION BY RANGE`
-   `PARTITION BY LIST`
-   `PARTITION BY HASH`
    

## Creating partitioned tables

Partitioned tables are declared like regular Django models with a special base class and two extra options to set the partitioning method and key. Once declared, they behave like regular Django models.

### Declaring the model

Inherit your model from `psql_partition.models.PostgresPartitionedModel` and declare a child class named `PartitioningMeta`. On the meta class, specify the partitioning method and key.

-   Use `psql_partition.types.PostgresPartitioningMethod.RANGE` to `PARTITION BY RANGE`
-   Use `psql_partition.types.PostgresPartitioningMethod.LIST` to `PARTITION BY LIST`
-   Use `psql_partition.types.PostgresPartitioningMethod.HASH` to `PARTITION BY HASH`
    

```python
from django.db import models

from psql_partition.types import PostgresPartitioningMethod
from psql_partition.models import PostgresPartitionedModel

class MyModel(PostgresPartitionedModel):
    class PartitioningMeta:
        method = PostgresPartitioningMethod.RANGE
        key = ["timestamp"]

    name = models.TextField()
    timestamp = models.DateTimeField()
```

### Generating a migration

Run the following command to automatically generate a migration:

```
python manage.py pgmakemigrations

```

This will generate a migration that creates the partitioned table with a default partition.

> **Warning:**
> 
> Always use `python manage.py pgmakemigrations` for partitioned models.
> 
> The model must be created by the `psql_partition.backend.migrations.operations.PostgresCreatePartitionedModel` operation.
> 
> Do not use the standard `python manage.py makemigrations` command for partitioned models. Django will issue a standard `django.db.migrations.operations.CreateModel` operation. Doing this will not create a partitioned table and all subsequent operations will fail.

## Automatically managing partitions

The `python manage.py pgpartition` command can help you automatically create new partitions ahead of time and delete old ones for time-based partitioning.

You can run this command manually as needed, schedule to run it periodically or run it every time you release a new version of your app.

> **Warning:**
> 
> We DO NOT recommend that you set up this command to automatically delete partitions without manual review.
> 
> Specify `--skip-delete` to not delete partitions automatically. Run the command manually periodically without the `--yes` flag to review partitions to be deleted.

### Command-line options

**Long flag**

**Short flag**

**Default**

**Description**

`--yes`

`-y`

`False`

Specifies yes to all questions. You will NOT be asked for confirmation before partition deletion.

`--using`

`-u`

`'default'`

Optional name of the database connection to use.

`--skip-create`

  

`False`

Whether to skip creating partitions.

`--skip-delete`

  

`False`

Whether to skip deleting partitions.

### Configuration

In order to use the command, you have to declare an instance of `psql_partition.partitioning.PostgresPartitioningManager` and set `PSQLEXTRA_PARTITIONING_MANAGER` to a string with the import path to your instance of `psql_partition.partitioning.PostgresPartitioningManager`.

For example:

```python
# myapp/partitioning.py
from psql_partition.partitioning import PostgresPartitioningManager

manager = PostgresPartitioningManager(...)

# myapp/settings.py
PSQLEXTRA_PARTITIONING_MANAGER = 'myapp.partitioning.manager'
```

#### Time-based partitioning

```python
from dateutil.relativedelta import relativedelta

from psql_partition.partitioning import (
    PostgresPartitioningManager,
    PostgresCurrentTimePartitioningStrategy,
    PostgresTimePartitionSize,
    partition_by_current_time,
)
from psql_partition.partitioning.config import PostgresPartitioningConfig

manager = PostgresPartitioningManager([
    # 3 partitions ahead, each partition is one month
    # delete partitions older than 6 months
    # partitions will be named `[table_name]_[year]_[3-letter month name]`.
    PostgresPartitioningConfig(
        model=MyPartitionedModel,
        strategy=PostgresCurrentTimePartitioningStrategy(
            size=PostgresTimePartitionSize(months=1),
            count=3,
            max_age=relativedelta(months=6),
        ),
    ),
    # 6 partitions ahead, each partition is two weeks
    # delete partitions older than 8 months
    # partitions will be named `[table_name]_[year]_week_[week number]`.
    PostgresPartitioningConfig(
        model=MyPartitionedModel,
        strategy=PostgresCurrentTimePartitioningStrategy(
            size=PostgresTimePartitionSize(weeks=2),
            count=6,
            max_age=relativedelta(months=8),
        ),
    ),
    # 12 partitions ahead, each partition is 5 days
    # old partitions are never deleted, `max_age` is not set
    # partitions will be named `[table_name]_[year]_[month]_[month day number]`.
    PostgresPartitioningConfig(
        model=MyPartitionedModel,
        strategy=PostgresCurrentTimePartitioningStrategy(
            size=PostgresTimePartitionSize(days=5),
            count=12,
        ),
    ),
])

```

##### Changing a time partitioning strategy

When switching partitioning strategies, you might encounter the problem that partitions for part of a particular range already exist.

In order to combat this, you can use the `psql_partition.partitioning.PostgresTimePartitioningStrategy` and specify the `start_datetime` parameter. As a result, no partitions will be created before the given date/time.

#### Custom strategy

You can create a custom partitioning strategy by implementing the `psql_partition.partitioning.PostgresPartitioningStrategy` interface.

You can look at `psql_partition.partitioning.PostgresCurrentTimePartitioningStrategy` as an example.

## Manually managing partitions

If you are using list or hash partitioning, you most likely have a fixed amount of partitions that can be created up front using migrations or using the schema editor.

### Using migration operations

#### Adding a range partition

Use the `psql_partition.backend.migrations.operations.PostgresAddRangePartition` operation to add a new range partition. Only use this operation when your partitioned model uses `psql_partition.types.PostgresPartitioningMethod.RANGE`.

```python
from django.db import migrations, models

from psql_partition.backend.migrations.operations import PostgresAddRangePartition

class Migration(migrations.Migration):
    operations = [
        PostgresAddRangePartition(
           model_name="mypartitionedmodel",
           name="pt1",
           from_values="2019-01-01",
           to_values="2019-02-01",
        ),
    ]

```

#### Adding a list partition

Use the `psql_partition.backend.migrations.operations.PostgresAddListPartition` operation to add a new list partition. Only use this operation when your partitioned model uses `psql_partition.types.PostgresPartitioningMethod.LIST`.

```python
from django.db import migrations, models

from psql_partition.backend.migrations.operations import PostgresAddListPartition

class Migration(migrations.Migration):
    operations = [
        PostgresAddListPartition(
           model_name="mypartitionedmodel",
           name="pt1",
           values=["car", "boat"],
        ),
    ]

```

#### Adding a hash partition

Use the `psql_partition.backend.migrations.operations.PostgresAddHashPartition` operation to add a new list partition. Only use this operation when your partitioned model uses `psql_partition.types.PostgresPartitioningMethod.HASH`.

```python
from django.db import migrations, models

from psql_partition.backend.migrations.operations import PostgresAddHashPartition

class Migration(migrations.Migration):
    operations = [
        PostgresAddHashPartition(
           model_name="mypartitionedmodel",
           name="pt1",
           modulus=3,
           remainder=1,
        ),
    ]

```

#### Adding a default partition

Use the `psql_partition.backend.migrations.operations.PostgresAddDefaultPartition` operation to add a new list partition.

Note that you can only have one default partition per partitioned table/model. An error will be thrown if you try to create a second default partition.

If you used `python manage.py pgmakemigrations` to generate a migration for your newly created partitioned model, you do not need this operation. This operation is added automatically when you create a new partitioned model.

```python
from django.db import migrations, models

from psql_partition.backend.migrations.operations import PostgresAddDefaultPartition

class Migration(migrations.Migration):
    operations = [
        PostgresAddDefaultPartition(
           model_name="mypartitionedmodel",
           name="default",
        ),
    ]

```

#### Deleting a default partition

Use the `psql_partition.backend.migrations.operations.PostgresDeleteDefaultPartition` operation to delete an existing default partition.

> **Warning:**
> 
> Deleting the default partition and leaving your model without a default partition can be dangerous. Rows that do not fit in any other partition will fail to be inserted.

```python
from django.db import migrations, models

from psql_partition.backend.migrations.operations import PostgresDeleteDefaultPartition

class Migration(migrations.Migration):
    operations = [
        PostgresDeleteDefaultPartition(
           model_name="mypartitionedmodel",
           name="pt1",
        ),
    ]

```

#### Deleting a range partition

Use the `psql_partition.backend.migrations.operations.PostgresDeleteRangePartition` operation to delete an existing range partition. Only use this operation when your partitioned model uses `psql_partition.types.PostgresPartitioningMethod.RANGE`.

```python
from django.db import migrations, models

from psql_partition.backend.migrations.operations import PostgresDeleteRangePartition

class Migration(migrations.Migration):
    operations = [
        PostgresDeleteRangePartition(
           model_name="mypartitionedmodel",
           name="pt1",
        ),
    ]

```

#### Deleting a list partition

Use the `psql_partition.backend.migrations.operations.PostgresDeleteListPartition` operation to delete an existing range partition. Only use this operation when your partitioned model uses `psql_partition.types.PostgresPartitioningMethod.LIST`.

```python
from django.db import migrations, models

from psql_partition.backend.migrations.operations import PostgresDeleteListPartition

class Migration(migrations.Migration):
    operations = [
        PostgresDeleteListPartition(
           model_name="mypartitionedmodel",
           name="pt1",
        ),
    ]

```

#### Deleting a hash partition

Use the `psql_partition.backend.migrations.operations.PostgresDeleteHashPartition` operation to delete an existing range partition. Only use this operation when your partitioned model uses `psql_partition.types.PostgresPartitioningMethod.HASH`.

```python
from django.db import migrations, models

from psql_partition.backend.migrations.operations import PostgresDeleteHashPartition

class Migration(migrations.Migration):
    operations = [
        PostgresDeleteHashPartition(
           model_name="mypartitionedmodel",
           name="pt1",
        ),
    ]

```

### Using the schema editor

Use the `psql_partition.backend.PostgresSchemaEditor` to manage partitions directly in a more imperative fashion. The schema editor is used by the migration operations described above.

#### Adding a range partition

```python
from django.db import connection

connection.schema_editor().add_range_partition(
    model=MyPartitionedModel,
    name="pt1",
    from_values="2019-01-01",
    to_values="2019-02-01",
)

```

#### Adding a list partition

```python
from django.db import connection

connection.schema_editor().add_list_partition(
    model=MyPartitionedModel,
    name="pt1",
    values=["car", "boat"],
)

```

#### Adding a hash partition

```python
from django.db import connection

connection.schema_editor().add_hash_partition(
    model=MyPartitionedModel,
    name="pt1",
    modulus=3,
    remainder=1,
)

```

#### Adding a default partition

```python
from django.db import connection

connection.schema_editor().add_default_partition(
    model=MyPartitionedModel,
    name="default",
)

```

#### Deleting a partition

```python
from django.db import connection

connection.schema_editor().delete_partition(
    model=MyPartitionedModel,
    name="default",
)

```


# Original readme

<h1 align="center">
  <img width="400" src="https://i.imgur.com/79S6OVM.png" alt="django-postgres-extra">
</h1>
  
|  |  |  |
|--------------------|---------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| :white_check_mark: | **Tests** | [![CircleCI](https://circleci.com/gh/SectorLabs/django-postgres-extra/tree/master.svg?style=svg)](https://circleci.com/gh/SectorLabs/django-postgres-extra/tree/master) |
| :memo: | **License** | [![License](https://img.shields.io/:license-mit-blue.svg)](http://doge.mit-license.org) |
| :package: | **PyPi** | [![PyPi](https://badge.fury.io/py/django-postgres-extra.svg)](https://pypi.python.org/pypi/django-postgres-extra) |
| :four_leaf_clover: | **Code coverage** | [![Coverage Status](https://coveralls.io/repos/github/SectorLabs/django-postgres-extra/badge.svg?branch=coveralls)](https://coveralls.io/github/SectorLabs/django-postgres-extra?branch=master) |
| <img src="https://cdn.iconscout.com/icon/free/png-256/django-1-282754.png" width="22px" height="22px" align="center" /> | **Django Versions** | 2.0, 2.1, 2.2, 3.0, 3.1, 3.2, 4.0, 4.1, 4.2, 5.0, 5.1, 5.2 |
| <img src="https://cdn3.iconfinder.com/data/icons/logos-and-brands-adobe/512/267_Python-512.png" width="22px" height="22px" align="center" /> | **Python Versions** | 3.6, 3.7, 3.8, 3.9, 3.10, 3.11, 3.12, 3.13 |
| <img src="https://pbs.twimg.com/profile_images/1152122059/psycopg-100_400x400.png" width="22px" height="22px" align="center" /> | **Psycopg Versions** | 2, 3 |
| :book: | **Documentation** | [Read The Docs](https://django-postgres-extra.readthedocs.io/en/master/) |
| :warning: | **Upgrade** | [Upgrade from v1.x](https://django-postgres-extra.readthedocs.io/en/master/major_releases.html#new-features)
| :checkered_flag: | **Installation** | [Installation Guide](https://django-postgres-extra.readthedocs.io/en/master/installation.html) |
| :fire: | **Features** | [Features & Documentation](https://django-postgres-extra.readthedocs.io/en/master/index.html#features) |
| :droplet: | **Future enhancements** | [Potential features](https://github.com/SectorLabs/django-postgres-extra/issues?q=is%3Aopen+is%3Aissue+label%3Aenhancement) |

`django-postgres-extra` aims to make all of PostgreSQL's awesome features available through the Django ORM. We do this by taking care of all the hassle. As opposed to the many small packages that are available to try to bring a single feature to Django with minimal effort. ``django-postgres-extra`` goes the extra mile, with well tested implementations, seamless migrations and much more.
 
With seamless we mean that any features we add will work truly seamlessly. You should not have to manually modify your migrations to work with fields and objects provided by this package.

---

:warning: **This README is for v2. See the `v1` branch for v1.x.**

---

## Major features

[See the full list](http://django-postgres-extra.readthedocs.io/#features)

* **Conflict handling (atomic upsert)**

    Adds support for PostgreSQL's `ON CONFLICT` syntax for inserts. Supports `DO UPDATE` and `DO NOTHING`. Single statement, atomic and concurrency safe upserts. Supports conditional updates as well.

* **Table partitioning**

    Adds support for PostgreSQL 11.x declarative table partitioning. Integrated into Django migrations. Supports all types of partitioning. Includes a command to automatically create time-based partitions.

* **Views & materialized views**

    Adds support for creating views & materialized views as any other model. Integrated into Django migrations.

* **Locking models & tables**

    Support for explicit table-level locks.

* **Creating/dropping schemas**

    Support for managing PostgreSQL schemas.

* **Truncating tables**

   Support for ``TRUNCATE TABLE`` statements (including cascading).

For Django 3.1 and older:

* **Conditional unique index**
* **Case insensitive index**

For Django 2.2 and older:

* **Unique index**
* **HStore unique and required constraints on specific HStore keys**

## Working with the code
### Prerequisites

* PostgreSQL 14 or newer.
* Django 5.x or newer.
* Python 3.11 or newer.

These are just for local development. CI for code analysis etc runs against these. Tests will pass on all Python, Django and PostgreSQL versions documented. Linting, formatting and type-checking the code might not work on other Python and/or Django versions.

### Getting started

1. Clone the repository:

        λ git clone https://github.com/SectorLabs/django-postgres-extra.git

2. Create a virtual environment:

       λ cd django-postgres-extra
       λ virtualenv env
       λ source env/bin/activate

3. Create a postgres user for use in tests (skip if your default user is a postgres superuser):

       λ createuser --superuser psqlextra --pwprompt
       λ export DATABASE_URL=postgres://psqlextra:<password>@localhost/psqlextra

   Hint: if you're using virtualenvwrapper, you might find it beneficial to put
   the ``export`` line in ``$VIRTUAL_ENV/bin/postactivate`` so that it's always
   available when using this virtualenv.

4. Install the development/test dependencies:

       λ pip install -r requirements-test.txt

5. Run the tests:

       λ poe test

6. Run the benchmarks:

       λ poe benchmark

7. Auto-format code, sort imports and auto-fix linting errors:

       λ poe fix