I am using postgresql to store information about event ticket sales.
I have two tables:
event_sale (id integer, created_at timestamp with time zone)
event_sale_seat (id integer, event_sale_id integer REFERENCES event_sale(id), event_seat_id integer REFERENCES event_seat(id))
event_sale table is 200M records.
event_sale_seat table is 1.5bn records.
event_sale record is created, the corresponding
event_sale_seat records are created as well, i.e. the latter table identifies which seats were sold.
The most common operation is getting all the
event_sale_seat records for some
event_sale criteria, e.g.
select es1.id event_sale_id, ess1.event_seat_id from event_sale es1 inner join event_sale_seat ess1 on ess1.event_sale_id = es1.id where es1.created_at BETWEEN now() - interval '1 month' and now();
As both tables are growing really large, I want to partition them, starting with
I am considering of using https://www.timescale.com/.
My understanding that to partition a time-series database, I should have a column with a time value.
As I want to convert
event_sale_seat to a hypertable and this table does not have a column carrying a time value, my questions are:
event_sale_seatdoes not have a column with meaningful time value. What column should be used for partitioning?
- suppose that I must/ should have a time column, would I just copy
- how will the latter join example be performed when the table is partitioning by
Regarding the last question, would this be how the join is performed?
select es1.id event_sale_id, ess1.event_seat_id from event_sale es1 inner join event_sale_seat ess1 on ess1.created_at = es1.created_at and ess1.event_sale_id = es1.id where es1.created_at BETWEEN now() - interval '1 month' and now();