PostgreSQL query to get if a requested interval intersects with existing booked intervals

The title is not clear at all, and that is because it is very hard to explain what I am trying to do and if it's faster to do it, if it's even possible, during the query or on the backend.

First of all, I am using PostgreSQL.

The main problem is having rooms, and they will be booked.
There are two ways to go. Store the available times per room or store the booked times for the room (per day, of course).

When I store availability intervals, finding empty slots is very easy with simple queries.
For example, I have a table that looks like that

CREATE TABLE "Calendar"
(
    "id"                       bigserial PRIMARY KEY,
    "calendar_day"             date    NOT NULL,
    "room_id"                  integer NOT NULL,
    "available_interval_start" time  NOT NULL,
    "available_interval_end"   time  NOT NULL,
    "booked"                   boolean NOT NULL DEFAULT false
);

To find an available interval, I just have to query on day, room_id, and times inside the interval. Easy and fast.
When the order is for multiple rooms and days (since there might be a discount), I can do it quickly inside a transaction. It is also very fast to check if it will pass, by just counting the successful results, in a joined table that has the required queried values.

Something like

select count(*) from "Calendar" as c
join (
    values('2023-06-03'::date,5,'09:00'::time,'09:30'::time),
          ('2023-06-03'::date,5,'11:00'::time,'11:30'::time)
     ) as t (dd,room,st,en) 
       on   c.calendar_day= t.dd 
       and c.room_id=t.room 
       and c.available_interval_start<=t.st
       and c.available_interval_end<=t.st;

Everything is roses. But now we have to consider canceling. After a successful booking, we will have in the database

a) the original interval [a,e] as booked
b) [a,b] and [c,e] as free
c) and [b,c] as booked

In the meantime, those free intervals can be booked and fragmented. Even if they are not, if [b,c] is cancelled, it has to be marked with booked = true, and then, all available intervals for the (day, room) have to be collected and defragmented, if possible, marked booked if required, and new intervals inserted in the database.

A lot of bookkeeping.

Now the second case is I do not store availability intervals but booked intervals.
In this case, there is zero bookkeeping. If an interval is cancelled, it will just be removed from the database (or marked as invalid), and there is no need for defragmentation.

CREATE TABLE "Calendar"
(
    "id"                       bigserial PRIMARY KEY,
    "calendar_day"             date    NOT NULL,
    "room_id"                  integer NOT NULL,
    "booked_interval_start"    time  NOT NULL,
    "booked_interval_end"      time  NOT NULL,
    "booked"                   boolean NOT NULL DEFAULT true
);

The problem I have in this case is querying. I have to get all booked intervals, and for every result, check if the requested interval intersects with a booked interval. And this has to be done for every day, room, and requested interval, which means more time spent during the purchase transaction.

Any ideas on which is the best way to go?

Is there a way to reduce the number of queries in the second case?

Thanks to everyone for any ideas.

Do yourself a big favor and start implementing the timestamp range, the tsrange or tstzrange when the timezone is needed as well. Using a timestamp range you can create constraints, look for overlap, look for available time slots, etc. etc. Once you start integrating them into your workflow, you'll find it hard to believe that you once worked without ranges.

https://www.postgresql.org/docs/current/rangetypes.html