I am creating a db schema for a home exchange site. Host can set his home as exposed for exchange on certain dates. Guest must be able to view available dates. If exposed date is free (not reserved by anyone), we consider it available.

The question is how to store available dates in order to efficiently find related homes.

First, I created a table exposed_days, containing home_id, particular date, reserved (boolean) and guest_id. E.g, host exposes his home from Feb 10 to Feb 20 - 10 records are created, one for each date. Then, if a guest reserves, say, from 10 to 15 - we mark these days as reserved.

home_id |date      | reserved | guest_id
1       |2020-02-10|t         |10
1       |2020-02-11|t         |10
1       |2020-02-15|t         |10
1       |2020-02-16|f         |NULL
1       |2020-02-17|f         |NULL
1       |2020-02-20|f         |NULL

Now we can select available days and show available homes for each day:

SELECT rd.date, ARRAY_AGG(homes.title) FROM homes
  JOIN exposed_days ed ON ed.home_id = homes.id AND NOT ed.reserved
GROUP BY rd.date
ORDER BY rd.date

However, my colleague said storing days is stupid. He suggested using date ranges. I created two tables


home_id |daterange  
1       | [2020-02-10,2020-02-20]


home_id |daterange              |guest_id
1       |[2020-02-10,2020-02-15]|10

But I can’t figure out how to make an efficient request.

My best try is

SELECT h.id, generate_series(lower(daterange), upper(daterange), '1 day'::interval) 
  FROM exposed_dates
  JOIN homes h ON exposed_dates.home_id = h.id
SELECT h.id, generate_series(lower(daterange), upper(daterange), '1 day'::interval) 
  FROM reserved_dates
  JOIN homes h ON reserved_dates.home_id = h.id

I tested the request for 10000 homes, ~10000 exposed date ranges (10 days each) and ~5000 reserved date ranges and it took about 500ms, which is too slow

explain log:

"SetOp Except  (cost=3961202.85..4111337.85 rows=10009000 width=20) (actual time=383.094..454.354 rows=50045 loops=1)"
"  ->  Sort  (cost=3961202.85..4011247.85 rows=20018000 width=20) (actual time=383.091..421.361 rows=190171 loops=1)"
"        Sort Key: "*SELECT* 1".id, "*SELECT* 1".generate_series"
"        Sort Method: external merge  Disk: 5560kB"
"        ->  Append  (cost=319.20..301963.97 rows=20018000 width=20) (actual time=10.119..201.710 rows=190171 loops=1)"
"              ->  Subquery Scan on "*SELECT* 1"  (cost=319.20..150981.98 rows=10009000 width=20) (actual time=10.118..115.715 rows=120108 loops=1)"
"                    ->  Hash Join  (cost=319.20..50891.98 rows=10009000 width=16) (actual time=10.117..93.285 rows=120108 loops=1)"
"                          Hash Cond: (h.id = exposed_dates.home_id)"
"                          ->  Seq Scan on homes h  (cost=0.00..315.09 rows=10009 width=8) (actual time=0.011..4.523 rows=10009 loops=1)"
"                          ->  Hash  (cost=194.09..194.09 rows=10009 width=22) (actual time=9.687..9.687 rows=10009 loops=1)"
"                                Buckets: 16384  Batches: 1  Memory Usage: 676kB"
"                                ->  Seq Scan on exposed_dates  (cost=0.00..194.09 rows=10009 width=22) (actual time=0.014..3.981 rows=10009 loops=1)"
"              ->  Subquery Scan on "*SELECT* 2"  (cost=319.20..150981.98 rows=10009000 width=20) (actual time=3.257..63.583 rows=70063 loops=1)"
"                    ->  Hash Join  (cost=319.20..50891.98 rows=10009000 width=16) (actual time=3.257..51.251 rows=70063 loops=1)"
"                          Hash Cond: (h_1.id = reserved_dates.home_id)"
"                          ->  Seq Scan on homes h_1  (cost=0.00..315.09 rows=10009 width=8) (actual time=0.009..2.618 rows=10009 loops=1)"
"                          ->  Hash  (cost=194.09..194.09 rows=10009 width=22) (actual time=3.173..3.174 rows=10009 loops=1)"
"                                Buckets: 16384  Batches: 1  Memory Usage: 676kB"
"                                ->  Seq Scan on reserved_dates  (cost=0.00..194.09 rows=10009 width=22) (actual time=0.010..1.442 rows=10009 loops=1)"
"Planning time: 0.929 ms"
"Execution time: 466.946 ms"

Maybe indexes or a trickier query will help solve the problem? or is it better to stick with a 'naive' decision with exposed_days for this case?

  • Storing ranges only makes sense if exposed and reserved days are guaranteed to always be continuous. E.g. when you are not allowed to "withdraw" a single day that's not the start or end day. If you have ranges in both tables you could try to join them on the ranges directly using the - (difference) operator to find the free days
    – user1822
    Feb 10, 2020 at 6:00
  • @a_horse_with_no_name dates are likely to be continuous. I assume the average exposed range will be about 20 days. Calculation of the difference did not work due to postgres error result of range difference would not be contiguous Feb 10, 2020 at 10:31


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.