FAQ Database Discussion Community


How to Mimic Postgres Foreign Keys into a Partitioned Table

postgresql,foreign-keys,postgresql-9.3,database-partitioning
I have a partitioned table (call it A) with a serial primary key that is referenced by another table (call it B). I know that I can't actually create a foreign key from one to the other (since I don't know from which partition the data is actually stored), so...

Partition pruning based on check constraint not working as expected

sql,postgresql,postgresql-9.3,database-partitioning,postgresql-performance
Why is the table "events_201504" included in the query plan below? Based on my query and the check constraint on that table I would expect the query planner to be able to prune it entirely: database=# \d events_201504 Table "public.events_201504" Column | Type | Modifiers ---------------+-----------------------------+--------------------------------------------------------------- id | bigint |...

Do I have to drop the primary key or reload the table data for partitioning in MySQL InnoDB?

mysql,innodb,database-performance,database-partitioning,pruning
I have to partition a few tables by date range. Do I have to drop the primary or reload the complete data to be able to partition these tables? Some of the tables do contain a lot more than 50 million rows. alter table temp_table_test1 partition by range (unix_timestamp(created_at)) (...

Partitioning a related set of Oracle tables by day when they don't all have Time information

oracle,database-partitioning
I have a set of tables that look similar to this: Time_Table (relatively small): Time (TIMESTAMP) timeId (NUMBER) Data... (NUMBER) Table2 (large, about 30 rows per time_table row): timeId (NUMBER) table2Id (NUMBER) Data... (NUMBER) Table3 (very large, around 10 rows per table2 row, currently 1.4 billion rows after a couple...

Alter table switch statement failed due to check constraints

sql,sql-server-2008-r2,database-partitioning
I have a scenario where i load the flat file into 3 staging tables and switch to partitioned table. When i try to switch from staging to partition table, am getting the following error. Could you help? Here are the file group names PRIMARY FG1 FG2 FG3 FG4 and here...

PostgreSQL 9.3 Partitions Not Working

postgresql,database-partitioning
I have a table, e, with range partitions setup daily. The main table and partition tables have been defined like so. CREATE TABLE e ( toi TIMESTAMP WITH TIME ZONE WITH ( OIDS=TRUE ); CREATE TABLE e_20150501 CONSTRAINT e_20150501_toi_chk CHECK (toi >= ‘2015-05-01’::DATE AND toi < ‘2015- 05-02’::DATE) INHERITS (e)...

Many tables or one big table with horizontal partitioning

mysql,database-partitioning
I'm design database for new project. I need to create a custom multi-chat system but I have a question: many tables (one for one chat) or one big table with horizontal partitioning ? My database is mysql and the chat are isolated each other. I ask this because the project...

Oracle Partition by 2 columns Interval

oracle,database-partitioning
Im trying to find on google my situation but no one talk about this situation. i have a table thats is gonna be partitionized with 2 columns. for 2 columns partitions can anyone show an example for the interval? In this case i have only one. For this example how...

SQLite database partitioning

database,sqlite,sqlite3,database-partitioning
I want to create sqlite partition structure, I created 3 databases, I attached those 3 databases to another database which will be parent with ATTACH DATABASE '1.db' as 1 ATTACH DATABASE '2.db' as 2 ATTACH DATABASE '3.db' as 3 after i .quit the parent database the database attachments detach, How...