rainbow-mode isn’t a global minor mode, so it needs to be enabled on a per-buffer basis.
I only use it for CSS, so I have:
(add-hook 'css-mode-hook 'my-css-mode-hook)
(defun my-css-mode-hook ()
(rainbow-mode 1))
If you genuinely want it to be global, everywhere, you can easily define a global minor mode yourself:
(define-globalized-minor-mode my-global-rainbow-mode rainbow-mode
(lambda () (rainbow-mode 1)))
(my-global-rainbow-mode 1)
You can add any arbitrary logic to that (lambda () (rainbow-mode 1)) function (which will be evaluated in every buffer) in order to decide whether or not to actually call (rainbow-mode 1) for a given buffer, so if you’re comfortable with elisp then you can easily extend this approach to cover your specific requirements for the mode in question.
More generally, how do I load any mode/package automatically on startup?
It can vary, but the approaches I’ve shown would suffice for most minor modes: Either you want them enabled whenever MODE is enabled (being some specific other mode name), in which case you can use the MODE-hook variable (which will always be available) as per the css-mode-hook example; or else you want the mode enabled permanently, in which case a global minor mode is a good approach (because you can toggle it on and off globally). Some minor modes are global by default (or provide global variants), but you can create your own if necessary, as per the my-global-rainbow-mode example.
Also be aware that modes can be derived from other modes, in which case all relevant MODE-hook hooks will be run (for details see https://stackoverflow.com/a/19295380/324105). A common use-case is to use prog-mode-hook to enable functionality wanted for all the programming modes which are derived from it (which is most programming modes).
Remember that many (hopefully most) libraries and packages will provide usage instructions. If you can’t find documentation, be sure to try M-x find-library to visit the library file, and then read through the comments at the top. There is often a very informative “Commentary” section, and sometimes this is the primary source of end-user documentation, and explain how to enable its functionality.