Index or View of all UUIDs in Postgres DB?

Question

I have a Postgres database with about 35 tables of varying sizes. Every table has an auto incrementing integer primary key, as well as a column of native Postgres uuid data type recording Version 4 UUID values.

The application code logs UUIDs, but...alas...doesn't always log the object type or table.

While we're working on cleaning that up, we sometimes just want to figure out what row a UUID refers to.

Is there a reasonable strategy to create a view, index, or even full on table to efficiently find a row given a UUID, if every row in the database has a UUID? Or am I chasing an antipattern and should solve it at the application level?

Answer 1

Disclaimer: I think the whole idea doesn't really make sense. But anyway here is one way to get a uniform "view" on all UUIDs in the database.

You can create a function that goes through all columns that are defined as UUID and then returns the values for those columns. You can optionally pass a UUID value that should be searched for:

Something like:

create or replace function find_uuids(p_id uuid default null)
 returns table(schema_name text, table_name text, column_name text, uid uuid)
as
$$
declare
 l_row record;
 l_sql text;
begin
 for l_row in select n.nspname, tb.relname, col.attname
 from pg_attribute col
 join pg_type ty on ty.oid = col.atttypid
 join pg_class tb on tb.oid = col.attrelid
 join pg_namespace n on n.oid = tb.relnamespace
 where col.attnum > 0
 and not col.attisdropped
 and ty.typname = 'uuid' 
 and n.nspname not in ('pg_catalog', 'information_schema')
 and n.nspname not like 'temp%'
 and tb.reltype = 'r'
 loop
 l_sql := format('select %L as schema_name, 
 %L as table_name, 
 %L as column_name, 
 %I as uid 
 from %I.%I', l_row.nspname, l_row.relname, l_row.attname, l_row.attname, l_row.nspname, l_row.relname);
 if p_id is not null then 
 l_sql := l_sql || format(' where %I = %L', l_row.attname, p_id);
 end if;
 return query execute l_sql;
 end loop;
end;
$$
language plpgsql;

Then you can use:

select *
from find_uuids('19d2efcc-45c1-4371-addc-45028a054b85');

to find the tables where that UUID value appears, or

select *
from find_uuids();

to see everything.

The above returns something like this:

schema_name | table_name | column_name | uid 
------------+------------+-------------+-------------------------------------
public | uuid_test | some_id | 666ce0a8-11c9-11e0-b5e2-3b49c86ffc2c
public | uuid_test | some_id | 7fffda0c-11c9-11e0-967c-a300aec7eb54
public | some_table | id | 3aa04664-863c-43e6-9890-1087db021fd8

But this is going to be really slow

Answer 2

I have a very similar issue as you. Depending a little bit how your data looks you could simply make a view with multiple union statements, and query from this:

create view uuid_view as 
select 'table1' as "Table", public_id from table1 
union select 'table2', public_id from table2
union select 'table3', public_id from table3
union select 'table4', public_id from table4;

And then:

select * from uuid_view where public_id='...';

I guess you'll run into troubles if your tables are huge, but this is easy to test. You might also be able to exclude tables that are never (or seldom) found in the logs?

Answer 3

I'm not sure the point of this question. If you need to find something yes it's a "reasonable strategy" to index it, UUID or not.

It is kind of weird to have both a UUID an "auto increment integer primary key". Usually, you go serial → bigserial → uuid. Or jump to UUID when you need sharding.