2 Incorporated comments
source | link

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which partitioning fits better our needs.

Our case

The IS is backend for selling tickets. We sell thousands of tickets daily so the table scales quite fast and seat reservations scale even faster.

+--------------+            +-------------------+
| tickets      |    1:N     | seat_reservations |
+--------------+            +-------------------+

We want to use date partitioning for both of these tables and some others but this is the core.

We need to force preserve db integrity via foreign keys or some similar solution. I know that InnoDB doesn't support FK with partitioning.

The questions

  • Does PostgreSQL support FK and Partitioning? Probably for rules based partitioning.
  • Is another way how to force preserve DB integrity?
  • Which SQL server do you recomend? (based on partitioning)

I plan to partition both tables. I want partition even more than that (log,...) but these are the main tables.

We are just building the system so we want to use the newest versions; MySQL 5.6 or PostgreSQL 9.3. The r/w ratio will be about 3:1 (r:w), and the line count will be 700k growing about 100k per month so we have to be able to have more than 7m lines.

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which partitioning fits better our needs.

Our case

The IS is backend for selling tickets. We sell thousands of tickets daily so the table scales quite fast and seat reservations scale even faster.

+--------------+            +-------------------+
| tickets      |    1:N     | seat_reservations |
+--------------+            +-------------------+

We want to use date partitioning for both of these tables and some others but this is the core.

We need to force preserve db integrity via foreign keys or some similar solution. I know that InnoDB doesn't support FK with partitioning.

The questions

  • Does PostgreSQL support FK and Partitioning? Probably for rules based partitioning.
  • Is another way how to force preserve DB integrity?
  • Which SQL server do you recomend? (based on partitioning)

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which partitioning fits better our needs.

Our case

The IS is backend for selling tickets. We sell thousands of tickets daily so the table scales quite fast and seat reservations scale even faster.

+--------------+            +-------------------+
| tickets      |    1:N     | seat_reservations |
+--------------+            +-------------------+

We want to use date partitioning for both of these tables and some others but this is the core.

We need to force preserve db integrity via foreign keys or some similar solution. I know that InnoDB doesn't support FK with partitioning.

The questions

  • Does PostgreSQL support FK and Partitioning? Probably for rules based partitioning.
  • Is another way how to force preserve DB integrity?
  • Which SQL server do you recomend? (based on partitioning)

I plan to partition both tables. I want partition even more than that (log,...) but these are the main tables.

We are just building the system so we want to use the newest versions; MySQL 5.6 or PostgreSQL 9.3. The r/w ratio will be about 3:1 (r:w), and the line count will be 700k growing about 100k per month so we have to be able to have more than 7m lines.

1
source | link

Partitioning MySQL vs PostgreSQL

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which partitioning fits better our needs.

Our case

The IS is backend for selling tickets. We sell thousands of tickets daily so the table scales quite fast and seat reservations scale even faster.

+--------------+            +-------------------+
| tickets      |    1:N     | seat_reservations |
+--------------+            +-------------------+

We want to use date partitioning for both of these tables and some others but this is the core.

We need to force preserve db integrity via foreign keys or some similar solution. I know that InnoDB doesn't support FK with partitioning.

The questions

  • Does PostgreSQL support FK and Partitioning? Probably for rules based partitioning.
  • Is another way how to force preserve DB integrity?
  • Which SQL server do you recomend? (based on partitioning)