The exact details of how RimWorld/Unity loads (mod) textures and keeps them in memory are quite complex and beyond the scope of this guide, but for a general understanding: The game loads all mod-textures into memory at startup, builds them into an atlas, and keeps them there for quick access. In fact, some of that load will even be kept in memory multiple times and spread between your VRAM and RAM - or even virtual memory, if the former gets close to 100% utilization.
Using all three layers of memory in conjunction allows you to load more textures than just your RAM could handle, but it's important to understand that each successive layer is considerably slower than the former, which will significantly slow down game initialization. Consistently running high on memory utilization (85%+) can even destabilize the game and in fact the whole system - so ideally you only want to run as many mods as your available RAM can handle, including enough breathing room.
At initialization, RimWorld converts the typically provided PNG files into a native format your GPU can handle - this conversion is optimized for speed, which will cause mod textures to become worse in quality after being loaded. It also has a rather large memory footprint, eating up more RAM than necessary.
Since the 1.6 update, RimWorld can now natively load DDS-files as well as Unity's own assetBundle format. While the latter is mostly relevant for modders, the former is the format textures get converted to when using the optimization process described here. Previous versions of RimWorld needed mods to add that support - most commonly Graphics Settings+ and Performance Fish. Both mods have additional benefits though, so they didn't completely become obsolete yet.
The exact memory load each mod adds through textures is often hard to predict - typically it comes down to the pure 'amount of pixels' from all textures combined. There are a few rules of thumb to keep in mind to get a better feeling for a mod's footprint:
Filesize is a bad indicator:
The file-size of a mod (on disk) poorly represents its load in memory. PNG-files are optimized for low file-size, not counting transparent pixels. DDS-files and RimWorld's internal format more or less count every single pixel, making direct comparisons pretty much impossible.
High-res textures:
Unnecessarily high texture-resolutions are often the main cause for significant overhead. RimWorld's own textures aim for 64x64 pixels per in-game 'square' - mods established a sort-of 128x128 standard for less blurry appearance. This matters for mods using higher resolutions - like a 256x256 weapon texture casually eating up 4x the memory of a 128x128 one, often with close to zero visual benefits.
Texture quantity:
By the same logic, the mere number of textures makes a difference too. Body apparel or armor in RimWorld typically needs 16 different textures for each item, while things like weapons usually just use a single one. Resulting in a mod adding 150 guns potentially having a smaller footprint than a mod adding 10 armors - especially if those armors use higher resolutions like 256x256, where it can easily spiral out of proportion.
Empty space:
The excessive use of empty space (padding) in texture files can artificially bloat a mod's footprint in addition. This is mostly relevant for modders, but if you're creating textures with significant dead space to make something appear smaller in-game, consider using as much of your texture's resolution as possible and adjust the size via the drawSize in your Defs instead.
In addition to the points above, the 1.6 update brought some other changes to how the game handles textures. Most importantly the fact that DDS-textures not of a resolution following "Multiple Of Four" will cause errors now. While this is mostly an issue caused by modders themselves, converting textures using ToDDS/RimSort will automatically re-size them accordingly. This is also the reason RimPy stopped working for texture-conversion - since its converter fails to adjust the resolution, resulting in invalid DDS-files.
Related but slightly less critical is the aspect of textures not following the "Power Of Two"-rule in addition - which causes RimWorld to not generate the full amount of mipmaps for those textures, which can cause graphical glitches. In previous versions of the game it was still recommended to follow those rules when deciding on a resolution, but the game still handled them even if not respected - after the Unity-engine update 1.6 brought with it though, it can cause all sort of issues, so it's now more relevant than ever for modder to follow those two rules and pick proper texture-resolutions (32x32, 64x64, 128x128, 256x256, etc.)
If you're a modder yourself and when in doubt, just consult the community - like in the #mod-development channel over on the Community Discord!