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