Memory foam, can be an excellent choice for pressure relief. It is only suitable however for use as a comfort layer in a mattress and is used over other support layers, preferably high quality such as HR polyfoam, innersprings, and latex. It has some unusual qualities which leads to both its strengths and its weaknesses.
Interestingly enough, memory foam shares some of the qualities of waterbeds and airbeds in that it partly flows away from pressure rather than compresses underneath it. This and its ability to respond to pressure in only the area of the pressure and its ability to absorb energy give memory foam some genuine advantages. What has often been forgotten though in the massive advertising and misinformation that surrounds it is that it shares most of these advantages with other materials which accomplish the same thing in different ways. Many of these "informed comparisons" are never made because of the amount of misinformation that exists in the industry in general. It also has some drawbacks that are also unique and it tends to be a love it or hate it type of material.
When you lie on a memory foam mattress, it will soften with heat and pressure and become semi solid. It is also less open celled than other foams so the air inside the foam takes more time to both move away from pressure and also to come back "on the rebound". This transformation from elastic behavior to viscous (liquid) behavior and back again in combination with the more closed cell structure of the foam results in a slower response to changing pressure and shape and is why it is often called "slow response foam".
Because it is able to "flow" around pressure, it forms itself very exactly around your body profile, it does a very good job at relieving pressure as part or all of a comfort layer. This is its main strength. It is considered by many to be the best at pressure relief however while there is no disputing that it is very good, whether it is better than some other materials such as latex or buckling column gel is open to question and most of these claims are not supported by real evidence but simply repeated over and over again until they are believed. They are all very close in this regard. It is also a "dead" feeling foam and absorbs energy that is applied to it so it has little to no resilience as opposed to latex, polyurethane, innersprings, or even many natural fibers and so is not nearly as springy or lively. This too is attractive to some who do not move around a lot or change positions as often throughout the night. It is also very good at isolating movement between sleeping partners because of its ability to respond to pressure in a very local area. This too it shares with latex and to a lesser degree some higher quality polyfoams. Finally it is attractive to those who like sleeping "in" their mattress rather than "on" their mattress as it forms a deeper pressure relieving cradle than other foams or materials.
While memory foam has real strengths in the area of pressure relief and movement isolation, it also has some drawbacks because of its very different qualities and structure. It tends to sleep hot for those who are sensitive to this and even the newer generation more breathable memory foams are not as breathable or open celled as other types of foam. Because it absorbs energy, has little resilience and is a poorly supportive material and relies on the layers underneath to provide support and spinal alignment. This may also mean that with thicker layers there is little support for the lumbar area which does not come in firm contact with the support layers and will not be held up well by a foam that "melts" under pressure. It can also present problems for those who prefer some "assistance" in their movements on a mattress as it responds very slowly to new and changing positions and some have described it as a little like "sleeping in sand". Finally it is difficult to control how far you sink into a memory foam layer as it may soften over the course of the night and what started out as correct spinal alignment could turn into a "hammock" position and a backache by morning. A final drawback of memory foam, and this is especially true of memory foam made by unknown manufacturers with unknown certifications, is that it can smell very bad for significant periods of time and in some cases this smell and the outgassing that produces it can create some health concerns.
If you choose to go in the direction of memory foam, I would recommend that you use the minimum thickness that is suitable for your needs and preferences. I would also consider combining it with other types of material in your comfort layer, which can be very comfortable for some, so you can take advantage of its strengths without being as affected by its weaknesses. Finally make sure that you are choosing a high quality memory foam made by a reputable manufacturer that meets 3rd party testing standards and publishes that they have done so. There are many of these who produce high quality memory foam for a very reasonable price and it is not necessary to purchase either overpriced memory foam to get good quality or to purchase questionable qualities of memory foam which have not been subjected to testing.
Memory foam does a great job as far as its strengths but some important trade-offs are required in order to take advantage of these strengths. The popularity of memory foam has been driven to such a large degree by "advertising copy" and misleading information that it is difficult to get accurate comparative information about it anywhere on the web as the same stories are repeated endlessly. For example ... it was never even used in the space program because of its drawbacks and yet is is almost always connected to NASA to create the impression that it was used by astronauts who are subject to high G forces. It is true that NASA was the source of the original attempts at producing a formula that worked however they did not continue its development. It is also difficult for consumers to compare it to materials that offer similar benefits as it is so often compared to materials or firmer versions of materials that do not have the same pressure relieving properties that many are unaware that there are alternatives available that have some of these same strengths without some of the drawbacks.
In any case, memory foam or a combination of memory foam with other pressure relieving materials can be very comfortable. Just make sure you do a little homework so you are not subject to the misinformation that exists and end up paying a lot of money without knowing exactly what you are purchasing.