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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.