[Maria-developers] Partial indexes?
Dear Maria, :p Thanks for all your hard work! Any plans to implement partial indexes? (https://www.postgresql.org/docs/10/static/indexes-partial.html) I hope this is the right place to suggest new features. Please redirect me if not. -- Jared Beck • (607) 216-5373 • jared@jaredbeck.com
Hi, Jared! On Dec 27, Jared Beck wrote:
Dear Maria,
:p
Thanks for all your hard work!
Any plans to implement partial indexes? (https://www.postgresql.org/docs/10/static/indexes-partial.html)
No such plans at the moment. What do you you need them for?
I hope this is the right place to suggest new features. Please redirect me if not.
To suggest a new feature, you can create a task at jira.mariadb.org (presuming such a task doesn't exist yet). But a mailing list is a better place if you want to start a discussion. If, for example, you'd like to show valid use cases for partial indexes or explain why it's an important feature to have :) Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Hi Jared. I read your pg link in detail a couple of times and this could be interesting for MySQL users,especially those implementing large time-series databases and some multi-tenant scenarios. Can you write up the jira as requested and I'll comment more there? (Note that example 11.3 can be accomplished in both pg and MySQL usingNULLs in a unique index, but the pg partial index allows the original data valuesto be used without a NULL work column, so might be slightly more convenient/obvious.) Thanks, James Briggs. -- Cassandra/MySQL DBA. Available in San Jose area or remote. cass_top: https://github.com/jamesbriggs/cassandra-top From: Sergei Golubchik <serg@mariadb.org> To: Jared Beck <jared@jaredbeck.com> Cc: maria-developers@lists.launchpad.net Sent: Wednesday, December 27, 2017 12:30 PM Subject: Re: [Maria-developers] Partial indexes? Hi, Jared! On Dec 27, Jared Beck wrote:
Dear Maria,
:p
Thanks for all your hard work!
Any plans to implement partial indexes? (https://www.postgresql.org/docs/10/static/indexes-partial.html)
No such plans at the moment. What do you you need them for?
I hope this is the right place to suggest new features. Please redirect me if not.
To suggest a new feature, you can create a task at jira.mariadb.org (presuming such a task doesn't exist yet). But a mailing list is a better place if you want to start a discussion. If, for example, you'd like to show valid use cases for partial indexes or explain why it's an important feature to have :) Regards, Sergei Chief Architect MariaDB and security@mariadb.org _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
On Wed, Dec 27, 2017 at 3:30 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Any plans to implement partial indexes? (https://www.postgresql.org/docs/10/static/indexes-partial.html)
No such plans at the moment. What do you you need them for?
I have used unique partial indexes to enforce business rules like "Active users must have a unique phone number, but there may be any number of inactive users with the same phone number" ``` create unique index ix_active_phones on users (phone) where active = true ; ``` -- Jared Beck • (607) 216-5373 • jared@jaredbeck.com
Yes, that's another good example of 11.3 where you caneither use a pg partial index, or a pg/mysql unique nullable column with active=true or null. Thanks, James Briggs. -- Cassandra/MySQL DBA. Available in San Jose area or remote. cass_top: https://github.com/jamesbriggs/cassandra-top From: Jared Beck <jared@jaredbeck.com> To: Sergei Golubchik <serg@mariadb.org> Cc: maria-developers@lists.launchpad.net Sent: Wednesday, December 27, 2017 4:52 PM Subject: Re: [Maria-developers] Partial indexes? On Wed, Dec 27, 2017 at 3:30 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Any plans to implement partial indexes? (https://www.postgresql.org/docs/10/static/indexes-partial.html)
No such plans at the moment. What do you you need them for?
I have used unique partial indexes to enforce business rules like "Active users must have a unique phone number, but there may be any number of inactive users with the same phone number" ``` create unique index ix_active_phones on users (phone) where active = true ; ``` -- Jared Beck • (607) 216-5373 • jared@jaredbeck.com _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Hi, Jared! On Dec 27, Jared Beck wrote:
On Wed, Dec 27, 2017 at 3:30 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Any plans to implement partial indexes? (https://www.postgresql.org/docs/10/static/indexes-partial.html)
No such plans at the moment. What do you you need them for?
I have used unique partial indexes to enforce business rules like "Active users must have a unique phone number, but there may be any number of inactive users with the same phone number"
``` create unique index ix_active_phones on users (phone) where active = true ;
That's a good use case. It's basically a "partial constraint", not partial index. In MariaDB (and in SQL standard) the way to achieve that would be to use a nullable column for active, and mark inactive users with NULL. Then you can create a unique index on (phone,active). Regards, Sergei Chief Architect MariaDB and security@mariadb.org
On Thu, Dec 28, 2017 at 9:34 AM, Sergei Golubchik <serg@mariadb.org> wrote:
In MariaDB (and in SQL standard) the way to achieve that would be to use a nullable column for active, and mark inactive users with NULL. Then you can create a unique index on (phone,active).
That is a neat trick, thanks James and Sergei! -- Jared Beck • (607) 216-5373 • jared@jaredbeck.com
Hi Jared and Sergei. I've been doing some work with Postgresql, and have learned more abouthow partial indexes are used. It seems partial indexes are a general "design pattern" they use to: 1) have smaller indexes by including less rows2) reduce write-amplification performance issuesas Postgresql likes to update all table indexes on each update (see #1) PG Patch: Write Amplification Reduction Method (WARM) https://www.postgresql.org/message-id/CABOikdMNy6yowA+wTGK9RVd8iw+CzqHeQSGpW... 3) accomplish various "tricks" similar to the null unique index we discussed:https://www.postgresql.org/docs/current/static/indexes-partial.html Indexing an ip range within an access_log table is an interesting use case above. I might use partial indexes for OLAP applications (drop indexes, load data, add partial index.)And potentially smaller indexes is nice while using relatively small SSDs. Thanks, James. From: Jared Beck <jared@jaredbeck.com> To: Sergei Golubchik <serg@mariadb.org> Cc: maria-developers@lists.launchpad.net Sent: Thursday, December 28, 2017 8:37 AM Subject: Re: [Maria-developers] Partial indexes? On Thu, Dec 28, 2017 at 9:34 AM, Sergei Golubchik <serg@mariadb.org> wrote:
In MariaDB (and in SQL standard) the way to achieve that would be to use a nullable column for active, and mark inactive users with NULL. Then you can create a unique index on (phone,active).
That is a neat trick, thanks James and Sergei! -- Jared Beck • (607) 216-5373 • jared@jaredbeck.com _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
participants (3)
-
James Briggs -
Jared Beck -
Sergei Golubchik