Modify YSQLDump to generate create index nonconcurrently #19457
Assignees
Labels
2.18 Backport Required
2.20 Backport Required
area/ysql
Yugabyte SQL (YSQL)
jira-originated
kind/enhancement
This is an enhancement of an existing feature
priority/medium
Medium priority issue
Comments
yugabyte-ci
added
area/ysql
OlegLoginov
added a commit
that referenced
this issue
Oct 31, 2023
…ONCURRENTLY Summary: Before the fix the command like `ysql_dump --schema-only --include-yb-metadata` generates indexes as `CREATE INDEX <index-name> ON ...`. After the fix the statement looks like `CREATE INDEX NONCONCURRENTLY <index-name> ON ...`. This allows to prevent start of automated index back-filling in the backup-restore. NOTE: the fix affects only `ysql_dump` run with `--include-yb-metadata` argument. So, it changes the backup pipeline, but it does NOT affect usual user runs of the tool. The fix changes the YSQL function `pg_get_indexdef` implementation. Jira: DB-8259 Test Plan: jenkins-ready PG tests ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpColocatedDB ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpLegacyColocatedDB ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpAllWithYbMetadata ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpWithYbMetadata ybd --java-test org.yb.pgsql.TestYsqlPartitionedBackup#testPartitionedTableWithParentUniqueKey ybd --java-test org.yb.pgsql.TestYbBackup#testTablegroup Reviewers: mihnea, tverona, yguan Reviewed By: yguan Subscribers: yql Differential Revision: https://phorge.dev.yugabyte.com/D29672
OlegLoginov
added a commit
that referenced
this issue
Nov 2, 2023
…eate index NONCONCURRENTLY Summary: Original commit: f22eda8 / D29672 GH link: f22eda8 Before the fix the command like `ysql_dump --schema-only --include-yb-metadata` generates indexes as `CREATE INDEX <index-name> ON ...`. After the fix the statement looks like `CREATE INDEX NONCONCURRENTLY <index-name> ON ...`. This allows to prevent start of automated index back-filling in the backup-restore. NOTE: the fix affects only `ysql_dump` run with `--include-yb-metadata` argument. So, it changes the backup pipeline, but it does NOT affect usual user runs of the tool. The fix changes the YSQL function `pg_get_indexdef` implementation. Jira: DB-8259 Test Plan: jenkins-ready PG tests ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpColocatedDB ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpLegacyColocatedDB ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpAllWithYbMetadata ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpWithYbMetadata ybd --java-test org.yb.pgsql.TestYsqlPartitionedBackup#testPartitionedTableWithParentUniqueKey ybd --java-test org.yb.pgsql.TestYbBackup#testTablegroup Reviewers: mihnea, tverona, yguan Reviewed By: yguan Subscribers: yql Tags: #jenkins-ready Differential Revision: https://phorge.dev.yugabyte.com/D29853
OlegLoginov
added a commit
that referenced
this issue
Nov 2, 2023
…eate index NONCONCURRENTLY Summary: Original commit: f22eda8 / D29672 GH link: f22eda8 Before the fix the command like `ysql_dump --schema-only --include-yb-metadata` generates indexes as `CREATE INDEX <index-name> ON ...`. After the fix the statement looks like `CREATE INDEX NONCONCURRENTLY <index-name> ON ...`. This allows to prevent start of automated index back-filling in the backup-restore. NOTE: the fix affects only `ysql_dump` run with `--include-yb-metadata` argument. So, it changes the backup pipeline, but it does NOT affect usual user runs of the tool. The fix changes the YSQL function `pg_get_indexdef` implementation. Jira: DB-8259 Test Plan: jenkins-ready PG tests ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpColocatedDB ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpLegacyColocatedDB ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpAllWithYbMetadata ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpWithYbMetadata ybd --java-test org.yb.pgsql.TestYsqlPartitionedBackup#testPartitionedTableWithParentUniqueKey ybd --java-test org.yb.pgsql.TestYbBackup#testTablegroup Reviewers: mihnea, tverona, yguan Reviewed By: yguan Subscribers: yql Tags: #jenkins-ready Differential Revision: https://phorge.dev.yugabyte.com/D29852
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-8259
Create Index starts a backfill process resulting in exchange of several RPCs even if the table is empty. This adds 3-4 seconds to the create index statement. For a restore backfill isn’t necessary as the restore populates the index and at the time of index creation, the table is empty.
Proposal is to update ysqldump to generate
create index nonconcurrentlyAn alternative proposal (which might be better) is to introduce a GUC variable to achieve the above and set it once at the beginning of ysql_dump.
The text was updated successfully, but these errors were encountered: