PostgreSQL partial indexes for Django
Project description
# django-partial-index
PostgreSQL partial (sometimes also called filtered or conditional) index support for Django.
https://www.postgresql.org/docs/current/static/indexes-partial.html
## Install
`pip install django-partial-index`
Requires Django 1.11 or later.
## Usage
With `unique=True`, this can be used to create unique constraints for a subset of the rows.
For example, to enforce that each user can only have one non-deleted room booking at a time:
```python
from partial_index import PartialIndex
class RoomBooking(models.Model):
user = models.ForeignKey(User)
room = models.ForeignKey(Room)
deleted_at = models.DateTimeField(null=True, blank=True)
class Meta:
# unique_together = [('user', 'room')] - Does not allow multiple deleted rows. Instead use:
indexes = [
PartialIndex(fields=['user', 'room'], unique=True, where='deleted_at IS NULL')
]
```
With unique=False, partial indexes can be used to optimise lookups that return only a small subset of the rows.
For example, on a job queue table with millions of completed, and very few pending jobs, it can be used to
speed up a "find next pending job" query:
```python
from partial_index import PartialIndex
class Job(models.Model):
created_at = models.DateTimeField(auto_now_add=True)
is_complete = models.BooleanField(default=False)
class Meta:
indexes = [
PartialIndex(fields=['created_at'], unique=False, where='is_complete = false')
]
```
Of course, these (unique) indexes could be created by a handwritten [RunSQL migration](https://docs.djangoproject.com/en/1.11/ref/migration-operations/#runsql).
But the constraints are part of the business logic, and best kept close to the model definitions.
## TODOs
* Add tests.
* Test on Python versions older than 3.6.
* Add import and version checks to print nice errors when Django 1.11 is not installed.
* Replace `where='some sql expression'` with [Django's query expressions](https://docs.djangoproject.com/en/1.11/ref/models/expressions/) that are checked for valid syntax and field names.
* Add support for SQLite.
* Eventually make this package obsolete by getting it merged into Django's contrib.postgres module.
PostgreSQL partial (sometimes also called filtered or conditional) index support for Django.
https://www.postgresql.org/docs/current/static/indexes-partial.html
## Install
`pip install django-partial-index`
Requires Django 1.11 or later.
## Usage
With `unique=True`, this can be used to create unique constraints for a subset of the rows.
For example, to enforce that each user can only have one non-deleted room booking at a time:
```python
from partial_index import PartialIndex
class RoomBooking(models.Model):
user = models.ForeignKey(User)
room = models.ForeignKey(Room)
deleted_at = models.DateTimeField(null=True, blank=True)
class Meta:
# unique_together = [('user', 'room')] - Does not allow multiple deleted rows. Instead use:
indexes = [
PartialIndex(fields=['user', 'room'], unique=True, where='deleted_at IS NULL')
]
```
With unique=False, partial indexes can be used to optimise lookups that return only a small subset of the rows.
For example, on a job queue table with millions of completed, and very few pending jobs, it can be used to
speed up a "find next pending job" query:
```python
from partial_index import PartialIndex
class Job(models.Model):
created_at = models.DateTimeField(auto_now_add=True)
is_complete = models.BooleanField(default=False)
class Meta:
indexes = [
PartialIndex(fields=['created_at'], unique=False, where='is_complete = false')
]
```
Of course, these (unique) indexes could be created by a handwritten [RunSQL migration](https://docs.djangoproject.com/en/1.11/ref/migration-operations/#runsql).
But the constraints are part of the business logic, and best kept close to the model definitions.
## TODOs
* Add tests.
* Test on Python versions older than 3.6.
* Add import and version checks to print nice errors when Django 1.11 is not installed.
* Replace `where='some sql expression'` with [Django's query expressions](https://docs.djangoproject.com/en/1.11/ref/models/expressions/) that are checked for valid syntax and field names.
* Add support for SQLite.
* Eventually make this package obsolete by getting it merged into Django's contrib.postgres module.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Close
Hashes for django-partial-index-0.1.0.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 6103cbffd567a867622517a6d042ed7433bada0748a46ace63f625d55249dfed |
|
MD5 | 1f38f15509de9dfac06c619c5c5c5ec9 |
|
BLAKE2b-256 | 27ae5b6b23f2bb7055be4066d321d0dcc0529967919d1e54781b99eb0ec2c147 |
Close
Hashes for django_partial_index-0.1.0-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 075737daed4096b4e4748a7e0638c881424ebc0a237a680fe9825a33dc5a1592 |
|
MD5 | e4f3f10a3f39ff9af8904958488f7e14 |
|
BLAKE2b-256 | 1d4b2c18368dc4b593ac7dc1ce18a04ea22bc2756ca22cf066658151d8ea399e |