[Python] postgresql sqlalchemy performance. Query maximum number or incremental table

I have a table for which I want to increment it by 1. For this simple task I can query the highest value and add 1 or keep track of a coupling table to query from.

I was wondering what would be the best solution from a performance perspective.

Let's say we have this solution for the max value + 1

# get maximum group_id and add 1 to it
query_max_broup = select(func.max(Broup.group_id))
max_group_id_results = await db.execute(query_max_broup)
# This always returns a tuple with one element
max_group_id_result = max_group_id_results.one()
max_group_id = max_group_id_result[0]
if not max_group_id:
max_group_id = 0
group_id = max_group_id + 1


Or this table solution

CREATE TABLE group_sequences
(
group_id integer PRIMARY KEY REFERENCES group(id),
max_group_id integer NOT NULL DEFAULT 1,
);

Continue reading...