0

I am using to store information about event ticket sales.

I have two tables: event_sale and event_sale_seat:

  • 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))

The event_sale table is 200M records. The event_sale_seat table is 1.5bn records.

Every time 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 event_sale_seat table.

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_seat does 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 event_sale.created_at to event_sale_seat?
  • how will the latter join example be performed when the table is partitioning by created_at?

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();
  • Someone with more reputation should create a timescale tag and add it to this question. – Gajus Jan 8 at 11:09
  • By now you can use time-series-database, I've added it to the question. – McNets Jan 8 at 19:56
  • As there is not much information within the event_sale table, I would merge the two tables together creating a single de-normalized table that contains all the data. Then you would have a date within your event_sale_seat table. – Mr.Brownstone Jan 8 at 20:16
  • @Mr.Brownstone Thats not the actual schema. Just the key components for the illustration purposes. – Gajus Jan 8 at 20:45
  • That's fair enough - but, is there anything stopping you from including the timestamp field in the event_sale_seat table? – Mr.Brownstone Jan 8 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.