Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[ysql] Speed up YSQL inserts by skipping lookup of keys being inserted - essentially providing upsert semantics. #11269

Closed
rthallamko3 opened this issue Jan 28, 2022 · 0 comments
Closed

[ysql] Speed up YSQL inserts by skipping lookup of keys being inserted - essentially providing upsert semantics. #11269

rthallamko3 opened this issue Jan 28, 2022 · 0 comments
Assignees
@sushantrmishra @paullee-yb
Labels
area/docdb YugabyteDB core features area/ysql Yugabyte SQL (YSQL)

Comments

@rthallamko3
Copy link
Contributor

Description

During bulk load (for example inserts by Copy command), skip lookup of the key being inserted, to speed up the inserts. This is similar to the upsert mode that is supported for YCQL.
@rthallamko3 rthallamko3 added the area/docdb YugabyteDB core features label Jan 28, 2022
@sushantrmishra sushantrmishra self-assigned this Feb 8, 2022
@sushantrmishra sushantrmishra added the area/ysql Yugabyte SQL (YSQL) label Feb 8, 2022
@rthallamko3 rthallamko3 changed the title [DocDB] Speed up YSQL inserts by skipping lookup of keys being inserted. [ysql] Speed up YSQL inserts by skipping lookup of keys being inserted - essentially providing upsert semantics. Mar 15, 2022
@ymahajan ymahajan mentioned this issue Mar 15, 2022
Open
paullee-yb added a commit that referenced this issue Apr 22, 2022
@paullee-yb
[#11269] YSQL: Add session variable to enable upsert mode for inserts
c6b90bb 
Summary:
Speed up YSQL bulk loads by introducing the `yb_enable_upsert_mode` session variable. When set to true, inserts skip the lookup of any keys being inserted, thereby, enabling faster inserts.
The default value for this flag is false.
To enable the flag:
```
SET yb_enable_upsert_mode=true
```
To disable the flag:
```
SET yb_enable_upsert_mode=false
```

Test Plan:
ybd --java-test 'org.yb.pgsql.TestBatchCopyFrom'

We expect the following behaviors when bulk loading via COPY command:
1) Indexes should be updated just like a normal INSERT
2) Foreign key integrity should be maintained just like a normal INSERT
3) Disable batching if the table contains non-FK trigger as per the following [change](7c428da).

Reviewers: ena, lnguyen, smishra

Reviewed By: lnguyen, smishra

Subscribers: dmitry, mihnea, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D16241
paullee-yb added a commit that referenced this issue Apr 22, 2022
@paullee-yb
[BACKPORT 2.13.1][#11269] YSQL: Add session variable to enable upsert…
985231d 
… mode for inserts

Summary:
Speed up YSQL bulk loads by introducing the `yb_enable_upsert_mode` session variable. When set to true, inserts skip the lookup of any keys being inserted, thereby, enabling faster inserts.
The default value for this flag is false.
To enable the flag:
```
SET yb_enable_upsert_mode=true
```
To disable the flag:
```
SET yb_enable_upsert_mode=false
```

Test Plan:
ybd --java-test 'org.yb.pgsql.TestBatchCopyFrom'

We expect the following behaviors when bulk loading via COPY command:
1) Indexes should be updated just like a normal INSERT
2) Foreign key integrity should be maintained just like a normal INSERT
3) Disable batching if the table contains non-FK trigger as per the following [change](7c428da).

Original commit: D16241 / c6b90bb

Reviewers: ena, lnguyen, smishra

Reviewed By: smishra

Subscribers: yql, mihnea, dmitry

Differential Revision: https://phabricator.dev.yugabyte.com/D16656
paullee-yb added a commit that referenced this issue Apr 22, 2022
@paullee-yb
[BACKPORT 2.12][#11269] YSQL: Add session variable to enable upsert m…
9ad58d8 
…ode for inserts

Summary:
Speed up YSQL bulk loads by introducing the `yb_enable_upsert_mode` session variable. When set to true, inserts skip the lookup of any keys being inserted, thereby, enabling faster inserts.
The default value for this flag is false.
To enable the flag:
```
SET yb_enable_upsert_mode=true
```
To disable the flag:
```
SET yb_enable_upsert_mode=false
```

Test Plan:
ybd --java-test 'org.yb.pgsql.TestBatchCopyFrom'

We expect the following behaviors when bulk loading via COPY command:
1) Indexes should be updated just like a normal INSERT
2) Foreign key integrity should be maintained just like a normal INSERT
3) Disable batching if the table contains non-FK trigger as per the following [change](7c428da).

Original commit: D16241 / c6b90bb

Reviewers: ena, lnguyen, smishra

Reviewed By: smishra

Subscribers: yql, mihnea, dmitry

Differential Revision: https://phabricator.dev.yugabyte.com/D16655
@FranckPachot FranckPachot mentioned this issue Jul 8, 2022
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@sushantrmishra sushantrmishra

@paullee-yb paullee-yb

Labels
area/docdb YugabyteDB core features area/ysql Yugabyte SQL (YSQL)
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@sushantrmishra @rthallamko3 @amitanandaiyer @paullee-yb