Плюсы модуля:
бОльшая скорость работы (в случаях, когда идёт много запросов к относительно простым динамическим страницам -- существенно большая. однако, для CGI-варианта существуют решения, позволяющие добиться сравнимой скорости, решения со своими заморочками, впрочем);

меньшее, пожалуй, потребление памяти;

работают php_value и т.п. в .htaccess;

некоторые мелкие радости, вроде того, что pconnect к БД работает только если php установлен модулем.
Минусы модуля:

Все скрипты исполняются от одного UID, что означает серьёзную дыру в защите. С этим, конечно, как-то борятся, но все способы являются в той или иной степени подпорками. Именно из-за этой особенности модуля, в описании установки большинства фришных (и не только) скриптов, взаимодействующих с файлами, указывается создавать директории с правами 777 и прочие радости какера.

Плюсы CGI:

Возможность настроить систему так, чтобы скрипты каждого пользователя исполнялись от его же UID, и с его персональным php.ini, что позволяет, правильно расставив права доступа, получить гораздо более защищённый хостинг. Также удобно, что файлы, создаваемые скриптом, имеют того же, владельца, что и смотрит на них по FTP, и их можно спокойно редактировать, удалять и т.п.

Настроив FastCGI, можно получить сравнимую с модулем скорость работы.
Минусы CGI:

В стандартной установке скорость работы скриптов ниже, а FastCGI бывали проблемы со стабильностью;

Директивы php_value и т.п. в .htaccess не работают, причём не работают с руганью. На самом деле, при наличии собственного php.ini это, как правило, не проблема, однако же не очень искушённого пользователя это может поставить в тупик.

Список не претендует на полноту, возможно, я что-то и забыл, так что дополняйте)

Ну, общий результат таков: для индивидуального сайта (во всех вариантах, от домашней странички на рабочей машине, до выделенного сервера для одного сильно загруженного сайта) больше подходит модуль, для виртуального хостинга -- CGI, желательно через FastCGI, если удастся добиться стабильности. (Насчёт стабильности поясню: в своё время были проблемы со стабильностью 1.3.27 апача с FastCGI на Debian, кажется Woody. Админил систему не я, поэтому мои данные не полны, проблемы были такого плана, что в системе неограниченно росло количество процессов, отработавшие просто не прибивались. Правда, учитывая, что в этой версии были впоследствии найдены проблемы со схожим описанием... Но без FastCGI проблема не проявлялась.)
А насчёт потребления памяти я, пожалуй, не совсем ясно выразился: либо модуль работает быстрее, либо потребляет памяти меньше, чем альтернативное решение для CGI.