I love this question.

There are several concepts of resolution and several aspects that affect each one. I will be playing with different names, sorry for that, ppi, lpi, pixel density, etc, but you simply focus on the number.

These variables are

1. Distance to the viewer/physical size.

2. Density of the display media.

3. Interaction with the image.

3. Interaction. On a computer you can interact with the image. If you use google maps with the satelite view, you are zooming because you do want to see details on a specific zone. The original image would be... realy, realy, realy, realy hughe, but what you want to see is a portion of it.

2. Leaving this interaction (zooming) aside, The resolution is caped to the resolution the media is using phisically.

On a monitor could have a resolution of 1920x1080px and if you put a bigger image this will not matter. On a print medium this maximum resolution is a bit harder to find.

But notice that i mentioned a generic monitor, regardless of the physical size of it.

When we introduce a physical size we use a new unit, unit per inch.

On an offset print (magazine) this resolution is normally 150 lpi. On a 23 diagonal inch monitor this unit density is arround 100 pxpi and on a 6 diagonal inch cellphone is about 350pxpi

1. But the most important thing here is what will the physical distance to the viewer this device will be from the user.

A 23 inch monitor at 20" will have more or less the same size to the viewer than a 6" cellphone at 4". (this are rough numbers... Im not using math to calculate it)

But if I want for example a really big print, and I want to see a lot of detail, for example a city map, where I want to see the streets and buildings I need more information, a bigger file.

But if the total image is going to be viewed at once this simplyfies things.

My rule of thumb is to consider the maximum dimension of the image (for example the base of a monitor, 20") as the distance from the viewer (20") to simplyfy things.

This means that an image used for a magazine, of 30cm at 300ppi can be used as a street artboard of 3 mt viewed at 3 mt with 30ppi.

30 cm at 300ppi are 3,543px on this long side.

This image is not that big, yes, a 24Mpx is fine to be used in most scenarios. a 24 Mpx image has 6,000px wide.

---

The original Retina Claim was to use a 300ppi device at 10 inches would match the maximum resolution of the average human vision. Some claim that it is more like 450ppi.

Another important issue is, do the viewer really care of this small resolutions?

A 300ppi vs a 600ppi device at 10 inches sounds like a great improvement... in reality, no, most people will not see a real practical difference between the two.

One has a micro tiny dot, the other has a half micro tiny dot.

This includes what important is the image to the viewer, does it contains small text or important details or not? etc.

I will edit this answer in a few months because I am preparing some practical investigations and surveys about this.

---

Commercial decisions.

Notice some patterns. Normally 4k resolution is used for big screens. It has sense, you want to stick your nose in the monitor to have an "environmental" experience.

And small phoens are pushing the resolution because you, with for example, googles where you insert the phone (and stick your nose, literally), you are using magnifying glasses for the same reason.

This images will be bigg because, again the interaction with it. Turning your head to see more of it, panoramic, or zooming in.