I want to have a table column which will be filled with wildcard-type DNS entries, like


such that I can make fast queries against actual reverse DNS hostnames, matching foobar.example.org to the first one above and 12-34-56-78.routers.popularisp.net to the second, for instance.

What is the best way to achieve this in a PostgreSQL index?


as dns domains are parsed from the end, it's useful to also store them that way - e.g:
For bar.foo.co.uk you'll keep uk.co.foo.Bar.

It makes both indexing and prefix searches simple and fast. In the example above you'll search for uk.co.foo.% - a simple search for regular index.

Note: it's the same way many directory (ldap,dns, etc. ) searches work.

| improve this answer | |
  • So something like SELECT […] FROM domains WHERE '12-34-56-78.routers.popularisp.net' LIKE hostname (assuming domains is the table and hostname is the column I’m trying to create)? – dpk Oct 16 '16 at 9:31
  • Hi, you'll need to reverse the query string as well, so: where 'net.popularisp.routers.12-34-56-78' like hostname (not the best example as this has no wildcard...) – cohenjo Oct 16 '16 at 10:21
  • Okay, it doesn't work because pattern LIKE col_name doesn't work with indexes apparently … – dpk Oct 16 '16 at 13:13
  • Take a look at pg_trgm and gist indexes - they give more flexibility to work with patterns... – cohenjo Oct 16 '16 at 13:33

After some experimentation I found the best way to go was to just index the wildcard forms of names, generate wildcards in the application, and do queries like

FROM domain 
WHERE hostname IN ('12-34-56-78.routers.popularisp.net', '*.routers.popularisp.net', '*.popularisp.net', '*.net')

since the numbers of levels of hierarchy are low.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.