pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedreindexing db
17+ messages / 4 participants
[nested] [flat]
* reindexing db
@ 2020-12-08 00:45 Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 17+ messages in thread
From: Scott Ribe @ 2020-12-08 00:45 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Does reindexdb --concurrently skip over indexes for exclusion constraints, or reindex them separately without the concurrently option? The docs for reindexdb simply point one to the reindex docs for the caveats of concurrently, where that restriction is mentioned.
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 01:00 David G. Johnston <david.g.johnston@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2020-12-08 01:00 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Dec 7, 2020 at 5:45 PM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> Does reindexdb --concurrently skip over indexes for exclusion constraints,
> or reindex them separately without the concurrently option? The docs for
> reindexdb simply point one to the reindex docs for the caveats of
> concurrently, where that restriction is mentioned.
>
>
The REINDEX page says:
"Furthermore, indexes for exclusion constraints cannot be reindexed
concurrently. If such an index is named directly in this command, an error
is raised. If a table or database with exclusion constraint indexes is
reindexed concurrently, those indexes will be skipped. (It is possible to
reindex such indexes without the CONCURRENTLY option.)"
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 02:07 Scott Ribe <scott_ribe@elevated-dev.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Scott Ribe @ 2020-12-08 02:07 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Dec 7, 2020, at 6:00 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Mon, Dec 7, 2020 at 5:45 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> Does reindexdb --concurrently skip over indexes for exclusion constraints, or reindex them separately without the concurrently option? The docs for reindexdb simply point one to the reindex docs for the caveats of concurrently, where that restriction is mentioned.
>
>
> The REINDEX page says:
>
> "Furthermore, indexes for exclusion constraints cannot be reindexed concurrently. If such an index is named directly in this command, an error is raised. If a table or database with exclusion constraint indexes is reindexed concurrently, those indexes will be skipped. (It is possible to reindex such indexes without the CONCURRENTLY option.)"
That is the sentence I was referring to, but it does not answer my question about what reindexdb does--whether it simply calls REINDEX DATABASE ..., or has logic to deal with this.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 02:27 David G. Johnston <david.g.johnston@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2020-12-08 02:27 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Monday, December 7, 2020, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> > On Dec 7, 2020, at 6:00 PM, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
> >
> > On Mon, Dec 7, 2020 at 5:45 PM Scott Ribe <scott_ribe@elevated-dev.com>
> wrote:
> > Does reindexdb --concurrently skip over indexes for exclusion
> constraints, or reindex them separately without the concurrently option?
> The docs for reindexdb simply point one to the reindex docs for the caveats
> of concurrently, where that restriction is mentioned.
> >
> >
> > The REINDEX page says:
> >
> > "Furthermore, indexes for exclusion constraints cannot be reindexed
> concurrently. If such an index is named directly in this command, an error
> is raised. If a table or database with exclusion constraint indexes is
> reindexed concurrently, those indexes will be skipped. (It is possible to
> reindex such indexes without the CONCURRENTLY option.)"
>
> That is the sentence I was referring to, but it does not answer my
> question about what reindexdb does--whether it simply calls REINDEX
> DATABASE ..., or has logic to deal with this.
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 02:33 Scott Ribe <scott_ribe@elevated-dev.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Scott Ribe @ 2020-12-08 02:33 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Dec 7, 2020, at 7:27 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> reindexdb is a wrapper around the SQL command REINDEX. There is no effective difference between reindexing databases via this utility and via other methods for accessing the server.
LOL, so I guess if you'd been here you might have been able to hear the whooshing sound as that went right by me...
thanks
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 05:42 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 17+ messages in thread
From: Ron @ 2020-12-08 05:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 12/7/20 6:45 PM, Scott Ribe wrote:
> Does reindexdb ...
Changing subjects, but the thought of reindexing every index, whether it
needs it or not, in one command, seems like a crime against database
administration.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 06:36 Paul Förster <paul.foerster@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Paul Förster @ 2020-12-08 06:36 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Ron,
> On 08. Dec, 2020, at 06:42, Ron <ronljohnsonjr@gmail.com> wrote:
>
> Changing subjects, but the thought of reindexing every index, whether it needs it or not, in one command, seems like a crime against database administration.
hmm, yes, I agree to *some* point. But also think about possible changes in glibc and the collation problems corrupting indexes that come with it. So, you do a reindexdb deliberately to protect yourself against index corruption because PostgreSQL can't know by itself that it needs such protection because it doesn't know of any changes in glibc.
So, it's not a crime against database administration but rather a means of defense for a database administrator against an external crime attacking the database.
Cheers,
Paul
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 06:59 Ron <ronljohnsonjr@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Ron @ 2020-12-08 06:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 12/8/20 12:36 AM, Paul Förster wrote:
> Hi Ron,
>
>> On 08. Dec, 2020, at 06:42, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> Changing subjects, but the thought of reindexing every index, whether it needs it or not, in one command, seems like a crime against database administration.
> hmm, yes, I agree to *some* point. But also think about possible changes in glibc and the collation problems corrupting indexes that come with it. So, you do a reindexdb deliberately to protect yourself against index corruption because PostgreSQL can't know by itself that it needs such protection because it doesn't know of any changes in glibc.
>
> So, it's not a crime against database administration but rather a means of defense for a database administrator against an external crime attacking the database.
:) I understand that sometimes all tables *need* to be reindexed.
Probably a habit from my VAX/VMS days, but I prefer to enumerate every
table, not only for tracking but so that if the command fails 90% of the way
through, I can skip over the the completed portions.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 07:21 Paul Förster <paul.foerster@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Paul Förster @ 2020-12-08 07:21 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Ron,
> On 08. Dec, 2020, at 07:59, Ron <ronljohnsonjr@gmail.com> wrote:
>
> Probably a habit from my VAX/VMS days, but I prefer to enumerate every table, not only for tracking but so that if the command fails 90% of the way through, I can skip over the the completed portions.
doing this IMHO defeats the purpose of concurrency because you'd have to issue reindex statements sequentially. Also, but I definitely can't say for sure, reindexdb should skip and report indexes which it has a problem with and then continue to do the remaining indexes. But as I said, I may be wrong with this.
Cheers,
Paul
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 07:42 Ron <ronljohnsonjr@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ron @ 2020-12-08 07:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 12/8/20 1:21 AM, Paul Förster wrote:
> Hi Ron,
>
>> On 08. Dec, 2020, at 07:59, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> Probably a habit from my VAX/VMS days, but I prefer to enumerate every table, not only for tracking but so that if the command fails 90% of the way through, I can skip over the the completed portions.
> doing this IMHO defeats the purpose of concurrency because you'd have to issue reindex statements sequentially. Also, but I definitely can't say for sure, reindexdb should skip and report indexes which it has a problem with and then continue to do the remaining indexes. But as I said, I may be wrong with this.
The CONCURRENTLY clause is about rebuilding *an index* online instead of
blocking all other access to the table, not about reindexing multiple tables
in parallel.
Thus, it's perfectly valid to write something like
DB=test
while read line;
do
psql $DB "REINDEX TABLE $line CONCURRENTLY;"
done < list_of_tables.txt
You might also be able to play with GNU Parallel and concurrently run
multiple "REINDEX TABLE $line CONCURRENTLY;" statements.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 07:46 Paul Förster <paul.foerster@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Paul Förster @ 2020-12-08 07:46 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Ron,
> On 08. Dec, 2020, at 08:42, Ron <ronljohnsonjr@gmail.com> wrote:
>
> DB=test
> while read line;
> do
> psql $DB "REINDEX TABLE $line CONCURRENTLY;"
> done < list_of_tables.txt
yes, you can do that of course. In this case, I would add an "&" after the psql and probably a "wait" at the end. This should get the job done in parallel. :-)
That of course depends on how fast the machine is and how much load it can take. :-)
Cheers,
Paul
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 08:32 Ron <ronljohnsonjr@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ron @ 2020-12-08 08:32 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 12/8/20 1:46 AM, Paul Förster wrote:
> Hi Ron,
>
>> On 08. Dec, 2020, at 08:42, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> DB=test
>> while read line;
>> do
>> psql $DB "REINDEX TABLE $line CONCURRENTLY;"
>> done < list_of_tables.txt
> yes, you can do that of course. In this case, I would add an "&" after the psql and probably a "wait" at the end. This should get the job done in parallel. :-)
>
> That of course depends on how fast the machine is and how much load it can take. :-)
The purpose of GNU Parallels is to not flood a system. Adding the "--jobs="
feature to reindexdb (along with enumerating tables using "--tables") would
serve the same purpose.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 10:01 Paul Förster <paul.foerster@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Paul Förster @ 2020-12-08 10:01 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Ron,
> On 08. Dec, 2020, at 09:32, Ron <ronljohnsonjr@gmail.com> wrote:
>
> The purpose of GNU Parallels is to not flood a system. Adding the "--jobs=" feature to reindexdb (along with enumerating tables using "--tables") would serve the same purpose.
I forgot about the -j option, sorry, my bad.
Cheers,
Paul
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 13:32 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Paul Förster <paul.foerster@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Scott Ribe @ 2020-12-08 13:32 UTC (permalink / raw)
To: Paul Förster <paul.foerster@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> On Dec 7, 2020, at 11:36 PM, Paul Förster <paul.foerster@gmail.com> wrote:
>
> hmm, yes, I agree to *some* point. But also think about possible changes in glibc and the collation problems corrupting indexes that come with it. So, you do a reindexdb deliberately to protect yourself against index corruption because PostgreSQL can't know by itself that it needs such protection because it doesn't know of any changes in glibc.
This is exactly why I'm doing it.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 14:26 Ron <ronljohnsonjr@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Ron @ 2020-12-08 14:26 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 12/8/20 4:01 AM, Paul Förster wrote:
> Hi Ron,
>
>> On 08. Dec, 2020, at 09:32, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> The purpose of GNU Parallels is to not flood a system. Adding the "--jobs=" feature to reindexdb (along with enumerating tables using "--tables") would serve the same purpose.
> I forgot about the -j option, sorry, my bad.
The "--jobs" option does not exist (in v12, at least.) *Adding* that feature
to reindex would serve the same purpose as GNU Parallels.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 14:28 Paul Förster <paul.foerster@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Paul Förster @ 2020-12-08 14:28 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Ron,
> On 08. Dec, 2020, at 15:26, Ron <ronljohnsonjr@gmail.com> wrote:
>
> The "--jobs" option does not exist (in v12, at least.) Adding that feature to reindex would serve the same purpose as GNU Parallels.
right. 13 has it.
Cheers,
Paul
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: reindexing db
@ 2020-12-08 14:29 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Scott Ribe @ 2020-12-08 14:29 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Dec 8, 2020, at 7:26 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> The "--jobs" option does not exist (in v12, at least.) Adding that feature to reindex would serve the same purpose as GNU Parallels.
It does exist in PG 13--I did not realize it was new, having never needed to do this before.
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2020-12-08 14:29 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-08 00:45 reindexing db Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-08 01:00 ` David G. Johnston <david.g.johnston@gmail.com>
2020-12-08 02:07 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-08 02:27 ` David G. Johnston <david.g.johnston@gmail.com>
2020-12-08 02:33 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-08 05:42 ` Ron <ronljohnsonjr@gmail.com>
2020-12-08 06:36 ` Paul Förster <paul.foerster@gmail.com>
2020-12-08 06:59 ` Ron <ronljohnsonjr@gmail.com>
2020-12-08 07:21 ` Paul Förster <paul.foerster@gmail.com>
2020-12-08 07:42 ` Ron <ronljohnsonjr@gmail.com>
2020-12-08 07:46 ` Paul Förster <paul.foerster@gmail.com>
2020-12-08 08:32 ` Ron <ronljohnsonjr@gmail.com>
2020-12-08 10:01 ` Paul Förster <paul.foerster@gmail.com>
2020-12-08 14:26 ` Ron <ronljohnsonjr@gmail.com>
2020-12-08 14:28 ` Paul Förster <paul.foerster@gmail.com>
2020-12-08 14:29 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-12-08 13:32 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox