PostgreSQL will not perform well on genome data compared to specialized algorithms like BLAST and its alternatives. For exact search a suffix array should allow fast lookup, but DNA search usually requires approximate matches.
In theory you can support this search with a suffix tree [1] or n-gram index [2], (but you will need n>3 because your alphabet is small.)
I'm not sure what ready-made tools or libraries you might find for this - last time I checked there were a lot more papers than production-quality open source code.
Or you could always just try a fast RE engine like Hyperscan and see if it is fast enough for your use case.
I had some friends who worked on research of data structures specifically for genome sequences. No idea about the details, but they're in the same tune as this paper: https://hub.hku.hk/handle/10722/60628
(the first author was their supervisor, they published a series of similar computational biology papers that you might be interested in, so if you check his other publications you (GP) might find something interesting or a least find an entry point to dig into references and citations that may result in something you'd be interested in -- that said if you find any data structure useful you'll probably have to implement it yourself...)
I don't know what is state of the art these days, but this is probably a good place to start: https://en.wikipedia.org/wiki/BLAST_(biotechnology)#Alternat...