From: Ainee Kohime
Please advise me.
I'll give it my best shot. There's a lot to change here.

From: Ainee Kohime
I have scanned at 900 pixels per sq inch (lower scanning is too blurry).
A couple things here. First, I assume you mean 900 pixels per inch, not per square inch. 900 per square inch would only be 30 per inch, which is extremely low.
So you know, square inches are not usually the units of measurement for this sort of thing. It's generally just inches. 900 dpi would put 810,000 dots in every square inch.
Second, scanning at 900 dpi is almost always overkill. Most "high quality" prints are done at 300 or 600 dpi (which, if you want to get into square inches, means 90,000 or 360,000). Scanning at anything above that is usually pointless.
The only reason you'd ever need to go higher would be if you're scanning negatives or something, and that's only because negatives are small. Sometimes it's acceptable to scan a photograph at high resolution, if your intent is to reprint it at several times its original size. But be prepared in those cases to do a lot of cleanup work, because when your dot size is that small, you end up picking up extra-image details like the grain of the paper, tiny specs of dust, fibers, etc. All that stuff would then need to be removed digitally before the image would be suitable for reprint.
When scanning for texturing purposes, there's almost never any no need to go that high, ever. The only exception would be if you wanted to make a large texture from scanning something very, very small. For example, if you want to produce a 1024x1024 texture from a scanned penny, then you might want to go as high as about 1365 dpi, since a penny is only 3/4 of an inch across. (3/4 of 1365 is 1024.)
For more normal operations, like making textures from photographs, there's no point in scanning at ultra-high resolution. When I scan a photo, I usually do it at 300 dpi, just to create an archive copy, since that's the resolution I'd be most likely to reprint it at if I ever need to. Then, to make it into a texture, I'll downsize it appropriately by its pixel count, not by its dpi or ppi settings.
As others have already mentioned in this thread dpi (or ppi) is absolutely meaningless for textures. Your video card has no idea what an inch is. All that matters, as far as size goes, is the number of pixels in the image. How many of them might happen to fit into an inch only matters for print. I'd strongly encourage you to forget all about things like inches, centimeters, dpi, ppi, etc., and instead focus just on pixel counts.
From: Ainee Kohime
And yes, I have done this with the Sistine Chapel ceiling for the ballroom, and it comes out just fine as a jpg!
No matter how good or bad it might look being sourced from a JPEG, I can promise you it would look better if it were sourced from any of the other three usable formats. JPEG is lossy; the others are not. By definition, TGA, BMP, and PNG imagery will always be higher quality than JPEG imagery.
From: Ainee Kohime
An example facade is 31 KB in PSD, and changes to 41 KB in tga.
That's not really possible. A 32-bit TGA file has 4 bytes of file size for every pixel of canvas size (32 bits = 4 bytes). In order to be just 41 kilobytes, it would have to have only about 10,000 pixels in it. If the image were square, that would mean it would be just 100x100 pixels.
If you scanned at 900 dpi, then to create a 100x100 image, your source would have had to have been just 1/3 of an inch wide by 1/3 of an inch tall. You're not scanning postage stamps or something, are you?
Did you perhaps mean 41 MB? If my math is right, at 32 bits per pixel, a 900 dpi scan would yield a 41MB TGA file if the source image were roughly 7.25x7.25 inches. That sounds about right for a photograph.
From: Ainee Kohime
The tga file is much to large to upload to SL,
Yes, it is, but not for the reason you seem to be thinking. It's not the file size that's relevant; it's the canvas size. The largest size SL can use is 1024x1024 pixels. By the sound of it, your images are at least 10 times that size.
From: Ainee Kohime
so I progressively shrink it to around 300 or even 150mm in size so that it will be accepted by SL.
Again, forget about things like millimeters. All that matters is the number of pixels.
From: Ainee Kohime
This takes a long time and much logging off and on again.
I can see how it might take a long time to resize such large imagery if your computer isn't the fastest, but I can't imagine why it would require you to log on and off repeatedly. Just size your image appropriately, and upload it, end of story. What am I missing?
From: Ainee Kohime
When I finally do get it small enough to be uploaded, (between 1,500 and 900 KB) it has shrunk its pixel count from 900 psi to a measly 72 psi.
Two things:
First, it's "ppi", pixels per inch, not "psi". I assume by "psi", you meant pixels per square inch, which as I said earlier, is not the way these things are typically measured.
Second, as I've said a few times now, the question of how many pixels fit into a real world inch is completely meaningless for textures. Again, what's important is the number of pixels you have, not how many might or might not happen to fit in an inch if you were to print the thing.
From: Ainee Kohime
This makes it all blurry when I paste it onto the mega prim I use for the facade.
This is why you don't see too many facades made out of just a single prim. There's not generally enough pixels available in a single texture to create enough detail for anything with that much complexity to it.
Additionally, in my observation megaprims have some rendering issues. Textures on them do not seem to render quite as well as they do on regular prims. I'm not sure why, technically, this should be, but it does appear to be the case.
If you put something like a 256x256 on a reguar prim, and zoom in on it so it fills the screen, you'll be viewing the texture at somewhere around 16-25 times its actual size, depending on how big your screen is. In many programs, that would be more than enough for things to start to look terrible, but SL happens to be really, really good at this sort of thing, so that blown up texture ends up looking just great. But now, put that same texture on a large megaprim, and zoom your view out so it's the same apparent size as that regular prim just was a second ago. You'll see that the texture on the megaprim will look considerably more pixelated than it did on the regular prim, even though it's occupying the same amount of pixels on-screen either way. It seems that whatever interpolation scheme SL uses for scaled high quality texture rendering starts to fail when the polygons get too large. Again, I don't know why this should be, but it is.
Another problem is that since you haven't been sizing your textures properly in powers of two, you're dealing with SL's resize-upon-upload process, which isn't that great. When you resize your images properly in your paint program ahead of time, rather than letting the uploader do it for you after the fact, you'll always get better looking results.
From: Ainee Kohime
I don't understand where in the process it plunges in psi count down to something so minimal and unsatisfactory.
Good news, it's not "the process" that's the problem. It's just YOUR process that needs improvement. It's nothing that can't be fixed.
From: Ainee Kohime
I use transparencies very sparingly, as I know they cause lag, and the megaprims are used judiciously too.
Transparency is the least of your problems. The shear size of your textures will cause more lag than any degree of transparency ever could. Remember, the average video card can only precess a few hundred megabytes worth of data at a time. It doesn't take all that many 1024x1024's to make one choke.
The single biggest reason SL runs as slowly as it does is because most people use textures that are way too big. To keep things running smoothly, keep your textures as small as possible, always. In most cases, 256x256 is as big as you need to go. Rarely go as high as 512x512. 1024x1024 should only be used under extenuating circumstances. If any more than 5% of your textures are 1024x1024, you're doing something wrong.
That said, it is true that over-use of transparency will cause problems, so it's good that you're keeping things reasonable in that regard. Now you just need to be equally responsible about your texture sizes, and you'll be all set.
Read the sticky on sizes and file formats, at the top of the forum. It's need-to-know information.