The Sega CD actually had a fair bit of RAM in it for the time. 512 KiB dedicated to the second 68K, 256KiB that could be swapped between the two processors or split into two 128 KiB banks with each processor getting one of those and 64 KiB of PCM RAM (there's also a little RAM for the CD-ROM drive's buffer and some battery backed up RAM for saves, but those aren't directly useful for game assets).
The library is very much a mixed bag, but I think a large part of that was game developers needing time to figure out what to do with all the extra space. The largest cartridge released during the Genesis/Mega Drive's heyday was only 5 MiB and most were a fair bit smaller than that. CD-ROM gave you two orders of magnitude more storage. The other problem is that Sega of America didn't believe there was much demand for RPGs in the US so while there were quite a lot of them released for the system in Japan, only a few of them made it here.
I think the main thing that did it in was the cost though. CD-ROM drives themselves weren't cheap at the time, all the RAM wasn't cheap and neither was all the extra hardware they stuffed in there. The much more modest CD add-on for the PC Engine/TG-16 sold almost as many units despite the base console being much less popular than the Genesis/Mega Drive and it seems likely that hardware costs were a major factor in that.
The library is very much a mixed bag, but I think a large part of that was game developers needing time to figure out what to do with all the extra space. The largest cartridge released during the Genesis/Mega Drive's heyday was only 5 MiB and most were a fair bit smaller than that. CD-ROM gave you two orders of magnitude more storage. The other problem is that Sega of America didn't believe there was much demand for RPGs in the US so while there were quite a lot of them released for the system in Japan, only a few of them made it here.
I think the main thing that did it in was the cost though. CD-ROM drives themselves weren't cheap at the time, all the RAM wasn't cheap and neither was all the extra hardware they stuffed in there. The much more modest CD add-on for the PC Engine/TG-16 sold almost as many units despite the base console being much less popular than the Genesis/Mega Drive and it seems likely that hardware costs were a major factor in that.