Обсуждение: [GENERAL] questions about 2nd index on one column
I have a bit strange question. I am trying to figure out how to avoid table locking while creating an index through Django (1.5.1) in Postgres 9.4.7
Django 1.5.1 does not support concurrent indexing. So my thought is to first create a concurrent index using SQL prompt.
Then try to update django model to add index, which will effectively create 2 indexes on same column.
So my questions are:
If I create a 2nd index on one column,
1. Does postgres scan entire table to create index from scratch or does it simply copy the first index?
2. Does postgres lock the table to create this index?
thanks
On 01/03/2017 11:07 AM, Ravi Kapoor wrote: > > I have a bit strange question. I am trying to figure out how to avoid > table locking while creating an index through Django (1.5.1) in Postgres > 9.4.7 First Django 1.5.x has been past end of life for 2.25 years. Second before it went EOL it was up to 1.5.12. > > Django 1.5.1 does not support concurrent indexing. So my thought is to > first create a concurrent index using SQL prompt. > Then try to update django model to add index, which will effectively > create 2 indexes on same column. Is the index going to be on a single column? How are you going to update the model? > > So my questions are: > > If I create a 2nd index on one column, > 1. Does postgres scan entire table to create index from scratch or does > it simply copy the first index? > 2. Does postgres lock the table to create this index? > > thanks > -- Adrian Klaver adrian.klaver@aklaver.com
On 01/03/2017 11:07 AM, Ravi Kapoor wrote: > > I have a bit strange question. I am trying to figure out how to avoid > table locking while creating an index through Django (1.5.1) in Postgres > 9.4.7 > > Django 1.5.1 does not support concurrent indexing. So my thought is to > first create a concurrent index using SQL prompt. > Then try to update django model to add index, which will effectively > create 2 indexes on same column. I really need to read the entire subject. So in response to my own question, yes it is on one column. > > So my questions are: > > If I create a 2nd index on one column, > 1. Does postgres scan entire table to create index from scratch or does > it simply copy the first index? > 2. Does postgres lock the table to create this index? > > thanks > -- Adrian Klaver adrian.klaver@aklaver.com
On 01/03/2017 11:35 AM, Ravi Kapoor wrote: Please reply to list also. Ccing list. > Yes I am aware of django EOL. However, our company is still using it, we > have a migration plan later this year, however for now, I got to work > with what we have. Still, you are missing 14 patch releases to the 1.5 version. > > Correct, the index will be on one column. > > In Django, the model is described in model.py file, so to update it, I > will simply change following line > votes1 = models.CharField(default='', max_length=200) > to following line > votes1 = models.CharField(db_index=True, default='', max_length=200) > > and run Django migrations. Hmm, from this: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/indexcmds.c;h=fdfa6ca4f5cd3be2db624e1c709ad037dc700b40;hb=c7681b2b9a115eb05048a485480826bc0efa6d3b /* * CheckIndexCompatible * Determine whether an existing index definition is compatible with a * prospective index definition, such that the existing index storage * could become the storage of the new index, avoiding a rebuild. * /* It would seem that the index would not be rebuilt, assuming all conditions are the same. The part that has me questioning is this: * This is tailored to the needs of ALTER TABLE ALTER TYPE, .. Someone who knows the internals better will have to verify this. > > > On Tue, Jan 3, 2017 at 11:26 AM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 01/03/2017 11:07 AM, Ravi Kapoor wrote: > > > I have a bit strange question. I am trying to figure out how to > avoid > table locking while creating an index through Django (1.5.1) in > Postgres > 9.4.7 > > Django 1.5.1 does not support concurrent indexing. So my thought > is to > first create a concurrent index using SQL prompt. > Then try to update django model to add index, which will effectively > create 2 indexes on same column. > > > I really need to read the entire subject. So in response to my own > question, yes it is on one column. > > > > So my questions are: > > If I create a 2nd index on one column, > 1. Does postgres scan entire table to create index from scratch > or does > it simply copy the first index? > 2. Does postgres lock the table to create this index? > > thanks > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
apologies, my bad.
> It would seem that the index would not be rebuilt, assuming all conditions are the same.
Thanks for finding this. This is enough info for me to spend a day experimenting. I did not want to waste a day if we knew upfront that it wont work. But looks like it will be worth the time.
regards
On Tue, Jan 3, 2017 at 12:09 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 01/03/2017 11:35 AM, Ravi Kapoor wrote:
Please reply to list also.
Ccing list.
> Yes I am aware of django EOL. However, our company is still using it, we
> have a migration plan later this year, however for now, I got to work
> with what we have.
Still, you are missing 14 patch releases to the 1.5 version.
>
> Correct, the index will be on one column.
>
> In Django, the model is described in model.py file, so to update it, I
> will simply change following line
> votes1 = models.CharField(default='', max_length=200)
> to following line
> votes1 = models.CharField(db_index=True, default='', max_length=200)
>
> and run Django migrations.
Hmm, from this:
https://git.postgresql.org/gitweb/?p=postgresql.git;a= blob;f=src/backend/commands/ indexcmds.c;h= fdfa6ca4f5cd3be2db624e1c709ad0 37dc700b40;hb= c7681b2b9a115eb05048a485480826 bc0efa6d3b
/*
* CheckIndexCompatible
* Determine whether an existing index definition is compatible with a
* prospective index definition, such that the existing index storage
* could become the storage of the new index, avoiding a rebuild.
*
/*
It would seem that the index would not be rebuilt, assuming all conditions are the same.
The part that has me questioning is this:
* This is tailored to the needs of ALTER TABLE ALTER TYPE, ..
Someone who knows the internals better will have to verify this.
>
>
> On Tue, Jan 3, 2017 at 11:26 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 01/03/2017 11:07 AM, Ravi Kapoor wrote:
>
>
> I have a bit strange question. I am trying to figure out how to
> avoid
> table locking while creating an index through Django (1.5.1) in
> Postgres
> 9.4.7
>
> Django 1.5.1 does not support concurrent indexing. So my thought
> is to
> first create a concurrent index using SQL prompt.
> Then try to update django model to add index, which will effectively
> create 2 indexes on same column.
>
>
> I really need to read the entire subject. So in response to my own
> question, yes it is on one column.
>
>
>
> So my questions are:
>
> If I create a 2nd index on one column,
> 1. Does postgres scan entire table to create index from scratch
> or does
> it simply copy the first index?
> 2. Does postgres lock the table to create this index?
>
> thanks
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com