Deleting Relationships does not reduce Postgres DB size

[moritzploss-k]

What platforms are affected?

linux

What architectures are affected?

amd64

What SpiceDB version are you using?

v1.30.1

Steps to Reproduce

Thanks for making SpiceDB, it's great!

I'm using SpiceDB with a Postgres data store. Here is my (relevant) environment config:

  SPICEDB_DATASTORE_ENGINE: "postgres"
  SPICEDB_DATASTORE_REVISION_QUANTIZATION_MAX_STALENESS_PERCENT: "0"
  SPICEDB_DATASTORE_REVISION_QUANTIZATION_INTERVAL: "0.1s"
  SPICEDB_DATASTORE_GC_MAX_OPERATION_TIME: "5m"
  SPICEDB_DATASTORE_GC_WINDOW: "1m"

My schema is similar to the following, and I have a large number of content-owner relationships in my database:

definition user {}

definition content {
    relation owner: user
}

I'm trying to (slowly) delete all content-owner relationships in my database, using this command:

request :=  &v1.DeleteRelationshipsRequest{
    RelationshipFilter: &v1.RelationshipFilter{
        ResourceType: "content",
        OptionalRelation: "owner",
        OptionalSubjectFilter: &v1.SubjectFilter{
            SubjectType: "user",
        },
    },
    OptionalLimit: 100,
    OptionalAllowPartialDeletions: true,
}

client.PermissionsServiceClient.DeleteRelationships(context.Background(), request)

Using a LookupResourcesRequest, I can see that my relationships are successfully deleted. From the SpiceDB logs, I can also see that they are successfully garbage collected later:

{
  "time": "2024-04-22T14:18:42Z",
  "message": "datastore garbage collection completed successfully",
  "level": "info",
  "highestTxID": "COR5",
  "duration": 31.863053,
  "nowTime": "2024-04-22T14:18:42Z",
  "collected": {
    "namespaces": 0,
    "relationships": 200,
    "transactions": 2
  }
}

I would have expected that, after deleting many content-owner relationships, disk space is freed up eventually, but instead I see an increase in disk usage.

Expected Result

I would have expected that the database size starts to decrease after the relationships have been garbage collected.

Actual Result

I see an increase in Postgres database size every time I send a DeleteRelationshipsRequest, and the disk space is never freed up, even after garbage collection.

[josephschorr]

@moritzploss-k By default, relationships and transactions are not removed for 24 hours, so you should only start to see a decrease after that period of time

[moritzploss-k]

Thanks @josephschorr. I don't see any changes even after 7+ days unfortunately (except for the initial increase after sending the DeleteRelationshipsRequest).

[josephschorr]

@moritzploss-k Make sure the GC is completing and the rows are actually being removed. It should be fairly easy to check.

[moritzploss-k]

@josephschorr Are there any settings I should adjust to ensure that? As I mentioned, according to the logs, garbage collection succeeds successfully and regularly.

[josephschorr]

@moritzploss-k Nope. If the rows are being deleted, then that's all that matters. You might need to call VACUUM, but Postgres should be doing that for you

[moritzploss-k]

ok, thanks! I will double-check that we're running VACUUM.

[josephschorr]

@moritzploss-k Feel free to reopen if you find it still is not working

[moritzploss-k]

thanks @josephschorr for your help! for future reference: we were running VACUUM, but one needs to run VACUUM (FULL) for disk space to be reclaimed, and reading the Postgres docs I realise that what I described above seems to be the expected behaviour.