2

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

*.example.org
*.popularisp.net

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?

Daphne Preston-Kendal
  • 131
  • 4

2 Answers2

1

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.

cohenjo
  • 1,198
  • 7
  • 6
1

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

SELECT * 
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.

Daphne Preston-Kendal
  • 131
  • 4