PostgreSQL Partial Indexes package

[Mattias Linnap]

Hi django-developers,

I have written a package that implements PostgreSQL and SQLite partial
indexes on top of the new class-based
indexes: https://github.com/mattiaslinnap/django-partial-index
The most common use case is partial unique constraints, but I have a few
projects where non-unique partial indexes have turned out useful as well.

I have a few questions on how to continue with this:

1. Right now the "where condition" expression is provided as a string, and
has to be different for PostgreSQL and SQLite in some common cases (for
example boolean literals). Is there a good abstraction for SQL expressions
somewhere in Django internals that I could use instead, something similar
to Q-expressions perhaps? In particular, to add validate_unique() support
to ModelForms, I would need to be able to extract all fields that are
mentioned in the where condition.
2. I've seen mentions of a "check constraints" support being in development
(https://github.com/django/django/pull/7615). Will that include partial
unique constraints, or is just for the per-column checks?
3. If separate, then it would be nice to one day get partial indexes merged
into the contrib.postgres package. Do you have any suggestions on what
needs to happen before that - more test coverage, more contributors, more
users, or similar?

Best,

Mattias

[Tim Allen]

I would love to see partial indexes supported. Great work! As far as
databases with Django support:

- PostgreSQL supports partial indexes
- SQLite supports partial indexes
- SQL Server supports them, called "filtered indexes"
- Oracle: Sort of supports
them: https://blog.jooq.org/2017/01/18/how-to-emulate-partial-indexes-in-oracle/
- MySQL/Maria/Drizzle: no support, AFAIK.

Would we want to build partial indexes in for all databases, with the
caveat that they would be ignored on MySQL and perhaps Oracle? In those
cases, would be default to a full index?

Regards,

Tim

Post by Mattias Linnap
Hi django-developers,
I have written a package that implements PostgreSQL and SQLite partial
https://github.com/mattiaslinnap/django-partial-index
The most common use case is partial unique constraints, but I have a few
projects where non-unique partial indexes have turned out useful as well.
1. Right now the "where condition" expression is provided as a string, and
has to be different for PostgreSQL and SQLite in some common cases (for
example boolean literals). Is there a good abstraction for SQL expressions
somewhere in Django internals that I could use instead, something similar
to Q-expressions perhaps? In particular, to add validate_unique() support
to ModelForms, I would need to be able to extract all fields that are
mentioned in the where condition.
2. I've seen mentions of a "check constraints" support being in
development (https://github.com/django/django/pull/7615). Will that
include partial unique constraints, or is just for the per-column checks?
3. If separate, then it would be nice to one day get partial indexes
merged into the contrib.postgres package. Do you have any suggestions on
what needs to happen before that - more test coverage, more contributors,
more users, or similar?
Best,
Mattias

[Adam Johnson]

It looks to me that the same trick on Oracle can be used on MySQL/MariaDB -
indexing a generated column. There's a comment on
https://mariadb.com/kb/en/library/generated-columns/ to that effect.

P.S. Drizzle is long dead, the site isn't even up any more :)

Post by Tim Allen
I would love to see partial indexes supported. Great work! As far as
- PostgreSQL supports partial indexes
- SQLite supports partial indexes
- SQL Server supports them, called "filtered indexes"
- Oracle: Sort of supports them: https://blog.jooq.org/
2017/01/18/how-to-emulate-partial-indexes-in-oracle/
- MySQL/Maria/Drizzle: no support, AFAIK.
Would we want to build partial indexes in for all databases, with the
caveat that they would be ignored on MySQL and perhaps Oracle? In those
cases, would be default to a full index?
Regards,
Tim

Post by Mattias Linnap
Hi django-developers,
I have written a package that implements PostgreSQL and SQLite partial
indexes on top of the new class-based indexes: https://github.com/ma
ttiaslinnap/django-partial-index
The most common use case is partial unique constraints, but I have a few
projects where non-unique partial indexes have turned out useful as well.
1. Right now the "where condition" expression is provided as a string,
and has to be different for PostgreSQL and SQLite in some common cases (for
example boolean literals). Is there a good abstraction for SQL expressions
somewhere in Django internals that I could use instead, something similar
to Q-expressions perhaps? In particular, to add validate_unique() support
to ModelForms, I would need to be able to extract all fields that are
mentioned in the where condition.
2. I've seen mentions of a "check constraints" support being in
development (https://github.com/django/django/pull/7615). Will that
include partial unique constraints, or is just for the per-column checks?
3. If separate, then it would be nice to one day get partial indexes
merged into the contrib.postgres package. Do you have any suggestions on
what needs to happen before that - more test coverage, more contributors,
more users, or similar?
Best,
Mattias

--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/
msgid/django-developers/df13ff66-8033-4f91-b49a-
081bfa28db00%40googlegroups.com
<https://groups.google.com/d/msgid/django-developers/df13ff66-8033-4f91-b49a-081bfa28db00%40googlegroups.com?utm_medium=email&utm_source=footer>
.
For more options, visit https://groups.google.com/d/optout.

--
Adam

[Ashley Waite]

I did a similar thing (but only tested in postgres) a while back, using Q's
/ filter clause to generate the where clause.

Perhaps with our powers combined we're close to a generalised
implementation?

https://github.com/ashleywaite/django-more/blob/master/django_more/indexes.py

Post by Mattias Linnap
Hi django-developers,
I have written a package that implements PostgreSQL and SQLite partial
https://github.com/mattiaslinnap/django-partial-index
The most common use case is partial unique constraints, but I have a few
projects where non-unique partial indexes have turned out useful as well.
1. Right now the "where condition" expression is provided as a string, and
has to be different for PostgreSQL and SQLite in some common cases (for
example boolean literals). Is there a good abstraction for SQL expressions
somewhere in Django internals that I could use instead, something similar
to Q-expressions perhaps? In particular, to add validate_unique() support
to ModelForms, I would need to be able to extract all fields that are
mentioned in the where condition.
2. I've seen mentions of a "check constraints" support being in
development (https://github.com/django/django/pull/7615). Will that
include partial unique constraints, or is just for the per-column checks?
3. If separate, then it would be nice to one day get partial indexes
merged into the contrib.postgres package. Do you have any suggestions on
what needs to happen before that - more test coverage, more contributors,
more users, or similar?
Best,
Mattias

[Sergey Fursov]

Hi everyone,

In our company, we also use the ability to define our own indexes, but with
the current implementation of base Index class, we basically need to copy
several methods entirely from it.
What do we want is implement UNIQUE indexes with the UPPER function wrapper
around field names [1].
This provides two benefits to us:
1) case-insensitive uniqueness of the field or the fields combination
2) quick filtering using iexact lookup, which uses UPPER(<field>) LIKE
<string> statement in SQL (at least for the Postgres)

I think, adding more flexibility in current indexes build logic would help
to implement and further support custom indexes implementations (like
django-partial-index <https://github.com/mattiaslinnap/django-partial-index>)
if we are not going to merge them in Django core or contrib.

[1] https://gist.github.com/GeyseR/363b4b9d2d1cd8c91d51ebfe9369a7ac

Post by Ashley Waite
I did a similar thing (but only tested in postgres) a while back, using
Q's / filter clause to generate the where clause.
Perhaps with our powers combined we're close to a generalised
implementation?
https://github.com/ashleywaite/django-more/blob/master/django_more/indexes.py

Post by Mattias Linnap
Hi django-developers,
I have written a package that implements PostgreSQL and SQLite partial
https://github.com/mattiaslinnap/django-partial-index
The most common use case is partial unique constraints, but I have a few
projects where non-unique partial indexes have turned out useful as well.
1. Right now the "where condition" expression is provided as a string,
and has to be different for PostgreSQL and SQLite in some common cases (for
example boolean literals). Is there a good abstraction for SQL expressions
somewhere in Django internals that I could use instead, something similar
to Q-expressions perhaps? In particular, to add validate_unique() support
to ModelForms, I would need to be able to extract all fields that are
mentioned in the where condition.
2. I've seen mentions of a "check constraints" support being in
development (https://github.com/django/django/pull/7615). Will that
include partial unique constraints, or is just for the per-column checks?
3. If separate, then it would be nice to one day get partial indexes
merged into the contrib.postgres package. Do you have any suggestions on
what needs to happen before that - more test coverage, more contributors,
more users, or similar?
Best,
Mattias

--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/84f4431a-cdab-43c7-b96b-de4d2eb9805b%40googlegroups.com
<https://groups.google.com/d/msgid/django-developers/84f4431a-cdab-43c7-b96b-de4d2eb9805b%40googlegroups.com?utm_medium=email&utm_source=footer>
.
For more options, visit https://groups.google.com/d/optout.