CODEWITHSUNDEEP
database-performancegoogle-cloud-spanner
Jason H
Jason H

Reputation: 511

Do logical or physical deletes perform better in Spanner?

Based on the example below, which of the following deletion strategies is recommended in Spanner?

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
  BirthDate  DATE,
  Status     STRING(1024)
) PRIMARY KEY(SingerId);

From my understanding of the way Spanner works, logically deleted records will need to be shifted to the end of the scan so that the relevant data is read first and it's not needlessly scanning through deleted records.

Physically deleted records would cause Spanner to have to re-index or split the data.

I'm not sure which is preferred, or if my understanding of data modification in Spanner is truly correct.

Upvotes: 2

Views: 349

Answers (1)

Mangu
Mangu

Reputation: 3325

Going with Physical deletion is preferred over logical, since you'll have less date to scan in the end, and you can try to avoid full scans in this way, as this is more time consuming.

As for the splits, it's true that with less splits, the reads are faster, but they are created when you add more rows, so, I would go with Physical.

Upvotes: 1

Related Questions