This is an interesting question, and one I was surprised hadn't been answered here before!
First and foremost, Ψαπφω is wrong, insofar as ψ indicates the sound
/ps/ in Attic Greek. Hephaestion's Handbook on Meters 14.4 has the following example of the twelve-syllable Alcaic line (taken from Loeb 142 p404):
ἰόπλοκ᾿ ἄγνα μελλιχόμειδε Σάπφοι
ióplok' ágna mellichómeide Sápphoi
"violet-haired, holy, sweetly-smiling Sappho"
The syllable right before Sappho's name here has to be short, and if it were followed by a
/ps/ sound, it would have become long "by position".
Interestingly enough, according to Brown (page 61), every manuscript that preserves this fragment writes it differently from Loeb: Alcaeus wrote it Σσαπφοι Ssapphoi. This could be an error, which is why Loeb corrects it, but it's apparently found in every manuscript. And there definitely couldn't be a double
/ss/ sound there, for the same reason there can't be a
/ps/: there can only be a single short consonant in that position.
What could this mean? Well, some loanwords involve a sound that's written σσ but tends to scan short: see the quantities in Παρνασός/Παρνασσός, "Mount Parnassus", for example. In that particular case, it may have come from a Luwian possessive suffix, with a
/ʃ/ ("sh") sound. There's also the puzzling sibilant in Pre-Greek θάλασσα/θάλαθθα/θάλαττα "sea".
Why would a sibilant be written with a psi? Zuntz says that it's a misinterpretation of an entirely separate letter, found in many non-standard Greek alphabets from Asia Minor and used in words like θάλα??α. After the letter vanished from everyday use, later scribes interpreted it as the closest letter they recognized, like how "þe olde" (that is, "the olde") was reinterpreted as "ye olde" in English.
This separate letter might descend from the Linear B 𐀮 se, the way Icelandic added the rune þ to the Latin alphabet, later evolving into ϡ sampi before vanishing. Or it could come from Phoenician 𐤑 ṣade/dialectal Greek Ϻ san. Either way, it could easily have indicated the
/ʃ/ sound that was missing from Attic Greek—and it's known that Sappho wrote digammas in her poems, so it's not much of a stretch to say she used other extra letters too.
And where did this non-Greek sibilant in Sappho's name come from? Some scholars have proposed a Luwian cognate to Hittite šuppi- "ritually pure", while Brown goes farther back, connecting it to Hattian (pre-Hittite) šḫap- "god", as a shortened form of the Hittite name Šapalli "numinous".
(There's also an unrelated name Ψαφων Psaphōn "draughts-player", found only in Doric, which confuses things further. But both Zuntz and Brown agree this is a red herring.)
TL;DR: She likely pronounced her name "Šappho", and wrote it that way, with a letter not in the standard Attic alphabet.