Postgresql – Index or View of all UUIDs in Postgres DB

postgresqluuid

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?

Best Answer

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%' 
  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

Generic case

Say you want to retain the values belonging the max(id) for every hour and for every distinct row3 value (but see the UUID section below). The IDs of these you get with a query like

SELECT max(id)
FROM table1
GROUP BY row3, EXTRACT(hour FROM "time")
;

(This will omit the date from your data, which is what you may want—or not. In the latter case you may want to use date_trunc('hour', "time") instead.)

Then you can use the above query to populate a table just as you did in your question, or delete everything else from table1 like

DELETE FROM table1
WHERE id NOT IN (
    [the query above comes here]
)
;

UUID

There is no max() for UUID. Therefore another solution is needed in your case, like this one:

SELECT DISTINCT first_value(id) OVER w
FROM table1
WINDOW w AS (PARTITION BY row3, EXTRACT(hour FROM "time")
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
;

This one applies a window function, and the grouping is achieved by the PARTITION BY clause and DISTINCT.

(Acknowledgment: thanks for the idea for AndriyM!)

Postgresql – Slow Postgres updates if OTHER columns are indexed

We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten

That's not the case.

In order to support rollback and crash-safety, PostgreSQL must write a new copy of every modified row, rather than modifying the row in-place. Twice, actually, because it must be written to WAL (a sequential log for crash recovery) then to the table.

PostgreSQL's autovacuum then comes along later and marks the old row versions as free space that can be re-used.

See the user manual for more information on concurrency control and MVCC.

the data is not changing size (e.g., rewriting a boolean value)

Irrelevant, because the row must be rewritten anyway.

only one column is being updated, and it is non-indexed
there are indexes on other columns

This only matters insofar as it affects HOT updates, where PostgreSQL can potentially avoid writing new index entries for a row update if no indexed columns are modified and there's enough free space on the same disk page (8k block) to store a new copy of the row.

Why do indexes on columns irrelevant to this query balloon the update time?

In most cases PostgreSQL must add new index entries even if you didn't modify indexed columns, because it has to write a new version of the row to a separate database page.

Setting a FILLFACTOR of 50 or less can help with this, as it encourages PostgreSQl to keep more free space for updates, at the cost of scans having to read and process more data.

Best Answer

Related Solutions

Postgresql – Removing duplicate entries for each hour

Generic case

UUID

Postgresql – Slow Postgres updates if OTHER columns are indexed

Related Question