Delete/Cascade can be slow for larget databases with complex interdependencies

[erikd]

• Linux
• ghc 8.6
• cabal-install 3.2
• PostgreSQL backend

I have a very simple delete cascade function:

deleteCascadeSlotNo :: MonadIO m => Word64 -> ReaderT SqlBackend m Bool
deleteCascadeSlotNo slotNo = do
  keys <- selectKeysList [ BlockSlotNo ==. Just slotNo ] []
  mapM_ deleteCascade keys
  pure $ not (null keys)

It is not enforced by the schema, but I know that slotNo is unique. I have also confirmed (by adding debug) that the list of keys contains at most 1 element. However, the database has 25 tables (with more to come) with a relatively high level of linkage between the tables, in part to ensure proper delete/cascade behavior.

Currently, when deleteCascadeSlotNo is used on the block table (over 5 million entries) it only needs to delete the most recently added row or two. However, for debugging/testing I may want to delete many more rows and here is where we run into problems. For larger delete/cascade operations, the time taken increases dramatically.

Rows to delete	Time taken
1000	0m44s
2000	1m25s
4000	3m04s
8000	7m14s
1600	13m18s

The speed is pretty much linear in the number of row to be deleted, but for large numbers of row (say 1 million) it can still be slow (30 minutes or more).

The slowness seem to be due to the fact the the operation requires a lot of back and forth communication between Haskell/Persistent and PostgreSQL. The delete/cascade operation would be much quicker if more of the work was handed off to Postgres.

[parsonsmatt]

Yeah - in a Reasonable world, we'd have #910 implemented, and then it'd just be mapM_ delete keys.

[parsonsmatt]

Oh, neat, it looks like that's included with 2.11!

[erikd]

Wow, i would almost run a version straight out of git to get that.

[erikd]

Hmm, does not seem to work (yet?).

[kderme]

Hmm, does not seem to work (yet?).

@erikd do you get any compile errors?

[erikd]

No compile errors, and when I generate the schema with added OnDeleteCascade statements, the schema does not change. I was expecting changes in the generated schema to reflect the schema change. Was I wrong on that?

[parsonsmatt]

You would need to be on the git version of persistent and persistent-sqlite and support for that should have been built in to persistent-sqlite.

[erikd]

I am using the current git HEAD version, but my backend is PostgreSQL.

[erikd]

@parsonsmatt Did you complete the PostgreSQL support mentioned in #1056 (review) ?

I have tried adding eg:

  Treasury
    addrId              StakeAddressId
    certIndex           Word16
    amount              Word64
    txId                TxId                OnDeleteCascade
    UniqueTreasury      addrId txId

and I am expecting ON DELETE CASCADE in the generated SQL, but there is none. Even more suspiciously if I change that to OnDeleteCascadeX there is no error message or even warning.

[erikd]

The file persistent-postgresql/Database/Persist/Postgresql.hs seems to correctly call renderFieldCascade.

Digging deeper.

[erikd]

I am looking at the tests for this, persistent-test/src/ForeignKey.hs it seems that I have the syntax wrong.
Changing the above to:

  Treasury
    addrId              StakeAddressId
    certIndex           Word16
    amount              Word64              sqltype=lovelace
    txId                TxId
    Foreign             Tx OnDeleteCascade fktx txId
    UniqueTreasury      addrId txId

but then I get an error about a missing primary key and id I add:

    Primary             id

I get an unknown column error on id, but that is supposed to be the auto-generated primary key.

[erikd]

It seems I need an explicit primary key on the Tx table. How do I get one that works just like the old implicit auto-generated index/primary keys?

[parsonsmatt]

You can write

Tx
  txId    SqlBackendKey sql=id
  Primary txId

as a work-around but that behavior should not be necessary. Are your models defined in a single file or QQ block or split up?

[erikd]

They are written as a single QQ.

I seemingly need an explicit txId so that I can use that as the key on which the delete cascade works.