3 added the newly created tag
| link
2 edited tags
| link
1
source | link

How to partition a table without time data?

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();