How to implement expiration in PostgreSQL?

Hi,

I guess this question has been asked a million times, but all solutions I can find online don’t really work well for my case. I’ll list them here and hope someone can shed some light.

My use case is to implement joining clubs that require entrance fee:

1. Each clubs only allows maximum number of members.
2. When a person decides to join a club, the seat is reserved for a limited amount of time. If that person fails to pay within that period, the seat will be open again

I want to write a query that can quickly list all clubs that still have open seats and #2 is where I want expiration to happen.

The solutions I find so far:

1. Exclude closed clubs in queries and periodically delete expired members

I can’t come up with a query that can accomplish this in an efficient way.

WITH seated_member AS (
SELECT
club_id,
count(member_id) AS num_seated_member
FROM member
WHERE paid OR join_time > now() - ‘1h’::interval
GROUP BY club_id
),
open_member AS (
SELECT
club_id,
max_num_member - coalesce(num_seated_member, 0) AS num_open_member
FROM club LEFT JOIN seated_member USING(club_id)
)
SELECT club_id AS open_club
FROM open_member
WHERE num_open_member > 0

This requires going through all seated members, which can potentially be large and takes a long time.

I can of course add an num_open_member column to the club table and index it, but the problem becomes how to automatically update it when a member expires, which take us back to square one.

All following solutions assume I add this column and seek to find a way to update it automatically.

2. Run a cron job

This won’t work because the number is updated only after the cron job is run, which only happens at intervals.

3. Update the column before running any related queries

This requires I execute DELETE and UPDATE queries before all seat related queries. It’s hard to manage and seems to slow down all such queries.

4. pg_cron

My environment wouldn’t allow me to install 3rd-party extensions, but even if I could, it seems pg_cron run cron jobs sequentially. I’m not sure it works well when I need to add a cron job for each newly joined member.

—

I’m not aware any other solutions. But the problem seems banal, and I believe it has been solved for a long time. Would really appreciate it if someone could at least point me in the right direction.

Regards,
Glen