Thread: reindexing db
Does reindexdb --concurrently skip over indexes for exclusion constraints, or reindex them separately without the concurrentlyoption? The docs for reindexdb simply point one to the reindex docs for the caveats of concurrently, where thatrestriction is mentioned. -- Scott Ribe scott_ribe@elevated-dev.com https://www.linkedin.com/in/scottribe/
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.
> 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 concurrentlyoption? The docs for reindexdb simply point one to the reindex docs for the caveats of concurrently, where thatrestriction is mentioned. > > > The REINDEX page says: > > "Furthermore, indexes for exclusion constraints cannot be reindexed concurrently. If such an index is named directly inthis command, an error is raised. If a table or database with exclusion constraint indexes is reindexed concurrently, thoseindexes 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 simplycalls REINDEX DATABASE ..., or has logic to deal with this.
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.
From reindexdb:
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.
Use the
CONCURRENTLY option. See REINDEX, where all the caveats of this option are explained in detail.So, if it doesn’t behave identically I’d call that a documentation bug as the docs seem clear that it does.
David J.
> 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 viathis 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
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.
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 acrime against database administration. hmm, yes, I agree to *some* point. But also think about possible changes in glibc and the collation problems corrupting indexesthat come with it. So, you do a reindexdb deliberately to protect yourself against index corruption because PostgreSQLcan'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 anexternal crime attacking the database. Cheers, Paul
On 12/8/20 12:36 AM, Paul Förster wrote:
:) 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.
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.
Angular momentum makes the world go 'round.
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 thecommand 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, butI definitely can't say for sure, reindexdb should skip and report indexes which it has a problem with and then continueto do the remaining indexes. But as I said, I may be wrong with this. Cheers, Paul
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.
Angular momentum makes the world go 'round.
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 shouldget the job done in parallel. :-) That of course depends on how fast the machine is and how much load it can take. :-) Cheers, Paul
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. Thisshould 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.
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 enumeratingtables using "--tables") would serve the same purpose. I forgot about the -j option, sorry, my bad. Cheers, Paul
> 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 corruptingindexes that come with it. So, you do a reindexdb deliberately to protect yourself against index corruption becausePostgreSQL 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.
On 12/8/20 4:01 AM, Paul Förster wrote:
The "--jobs" option does not exist (in v12, at least.) Adding that feature to reindex would serve the same purpose as GNU Parallels.
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.
Angular momentum makes the world go 'round.
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 asGNU Parallels. right. 13 has it. Cheers, Paul
> 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 asGNU Parallels. It does exist in PG 13--I did not realize it was new, having never needed to do this before.