Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
Do you plan to partition both tables? –  dezso Oct 30 '13 at 11:07
    
Yes I do. I want partition even more than that (log,...) but these are the main tables. –  Jakub Riedl Oct 30 '13 at 13:59
    
What MySQL version and what PostgreSQL version?? MySQL has native partitioning vs tweaked Partitioning postgresql.org/docs/9.3/static/ddl-partitioning.html in PostgreSQL.. But PostgreSQL has an better query optimizer and query planner.. –  Raymond Nijland Oct 30 '13 at 22:10
    
And can you give more requirements like write / read ratio and quess of the record numbers? –  Raymond Nijland Oct 30 '13 at 22:11
    
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 –  Jakub Riedl Oct 31 '13 at 8:14
add comment

1 Answer

Ok, PostgreSQL does not directly support foreign keys and partitions. There are several ways to resolve this but they require some understanding of underlying db implementation concepts to make perform well.

The first is to do your referential integrity enforcement manually through triggers. You can write constraint triggers which can be deferred (if needed) that check the existence of foreign key information. Note this is subject to ALTER TABLE DISABLE TRIGGER ALL issues but it should generally work.

The second is to have a separate table which stores keys and is maintained through a combination of triggers and bulk data operations. This can help a great deal.

So it is possible to enforce things but it requires a bit more work.

I don't know enough about MySQL's table partitioning capabilities to comment on it though.

share|improve this answer
1  
"does not directly support foreign keys and partitions" - if you define the FK constraint for each child table this seems to be working pretty well. Did you mean that with "not directly"? - that you need to create the FK for each partition? –  a_horse_with_no_name Dec 10 '13 at 7:14
1  
@a_horse_with_no_name, I was actually meaning the other way. Yes, you can create a foreign key on each partition, but there is no way to have a foreign key against a set of partitions without either coding the constraint triggers yourself or using a key management table. –  Chris Travers Dec 10 '13 at 7:49
    
I think this answer is not correct. Postgres supports partitioning and foreign keys. I have used both multiple times. Where have you acquired this information? a_horse_with_no_name is 100% correct –  Alexandros Mar 10 at 4:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.