Mysql – Partitioning MySQL vs PostgreSQL

MySQLpartitioningpostgresql

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.

Best Answer

Ok, PostgreSQL does not directly support foreign keys and partitions. 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.

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.

Related Solutions

Sql-server – Partitioning in SQL Server 2008

For what you want to do, I would recommend the following (which is pretty much what you were thinking).

1> Create history tables for the historic data you have - keep the schemas as similar as possible. Split up by some logical grouping (such as year/month) based on how they are going to be queried (say you need to report with in month/year as well as all). Do not worry about the table size of the splits unless they are getting into the TB size range (your dbms should handle it) just make sure that they are appropriately indexed for the queries that need to be run. You should consider putting these onto a different disk to the active data if performance is an issue.

2> Create a routine to move data from the active table to the relevant historic table. Run this periodically. As a practice rebuild the indexes on the table that has had the data removed from it, and maybe update the table statistics. Easiest way to do this is to write a sql script.

3> Consider the reporting you want to do. If you want to only have to deal with 1 table when writing queries, create a view that joins the archived tables together. Create indexes on all the tables to suite the view. This way if you want all the data, select from the view. If you want data from a specific year/month, query that table. The view will look something like:

create view view_all_data as select "Jan12" as month,a.* from data_Jan12 a union select "Feb12" as month,b.* from data_Feb12 b ....

I am assuming here that the system is not a highly used transactional system and that you have windows of low usage to run the analysis queries. If you need to maintain high levels of performance, you may like to do the above in a separate database (separate hardware) and port across the new data that you get from backups.

Ny way to use “like” operator during oracle table partitioning

Virtual column partitioning:

create table table_name (
    uri varchar2(20),
    part as (
      case 
        when uri like 'model://%' then 'mod'
        when uri like 'list://%' then 'list'
        else 'def'
      end
    )
)
partition by list (part) (
  partition part_mod values ('mod'),
  partition part_list values ('list'),
  partition part_def values ('def')
);

Seems to work:

insert into table_name (uri) values ('model://abc');
insert into table_name (uri) values ('list://abc');
insert into table_name (uri) values ('test://abc');

select * from table_name partition (part_mod);
select * from table_name partition (part_list);
select * from table_name partition (part_def);

Inserted rows are returned from correct partitions.