table size reduction disproportionate after row deletions

[Jon-ltr]

I recently deleted a significant number of rows from a table (approximately 24x fewer rows), but the size of the table on disk only decreased by about 9x. This disparity seems unusual, and I’m wondering if there’s something about internal storage or garbage collection I might be missing.

I already tried making the GC run more frequently by running these commands:

ALTER RANGE default CONFIGURE ZONE USING gc.ttlseconds=600; ALTER DATABASE system CONFIGURE ZONE USING gc.ttlseconds=600;

Is there a known reason why the size reduction isn’t proportional to the row reduction?

Environment:

• CockroachDB version 21.2.17
• Server OS: Ubuntu, running cockroach in docker

Jira issue: CRDB-45808

[blathers-crl]

Hi @Jon-ltr, please add branch-* labels to identify which branch(es) this C-bug affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: ALTER DATABASE)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[jeffswenson]

If the table you are cleaning up is a relatively small part of the database, it could be the storage is used by uncompacted levels inside of the pebble LSM. If this is the case, I would expect a large drop in table live bytes immediately following the deletion and the storage used by the table to drop over time.