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+czqheqsgpw7yka_4rsz_...@mail.gmail.com
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 <[email protected]>
To: Sergei Golubchik <[email protected]>
Cc: [email protected]
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 <[email protected]> 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 • [email protected]
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
