Splitting a database table into multiple segments for performance or manageability.

learn more… | top users | synonyms (1)

3
votes
0answers
87 views

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color ...
3
votes
0answers
678 views

Slow inserts/updates into partitioned table with lot of partitions

I'm designing the partitioning for our new database, but we are facing performance issues in inserting or updating the partitioned table. For simplicity suppose we have one table T, which have an ...
3
votes
0answers
335 views

Fast restore of dropped partitions

I want to test the restore of erroneously dropped partitions of a subpartitioned table. This restore would consist of three steps: restore the database (or at least the necessary tablespaces) to the ...
3
votes
0answers
548 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
2
votes
0answers
34 views

Is there impact on query execution as more partitions are added to a table?

We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical ...
2
votes
0answers
38 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
2
votes
0answers
78 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders ...
2
votes
0answers
249 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
2
votes
0answers
129 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
2
votes
0answers
92 views

MySQL Partioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
1
vote
0answers
10 views

Can a BRIN index be used instead of Table Partitioning in Postgres?

Given an append only table with a GUID and a timestamp (and a bunch of other columns) which can grow by ~50Mio entries / year. I want to keep the number of indices low and just use a normal (B-Tree) ...
1
vote
0answers
40 views

How can I partition records vertically across machines?

I recently asked two questions: (1) how to store and connect partial records in two or more machines, with the record halves correctly associated, and (2) how to determine the increase in time/space ...
1
vote
0answers
44 views

indexes and partition switching - how it works

We use partition switches to quickly load data, switching entire tables. We load an empty Table A with INSERTs and then switch it with empty Table B. These tables have identical indexes. What I ...
1
vote
0answers
32 views

Table clustered-index and partitioning in innoDB

I have a table with 45 columns, 3 of which are relevant: table Transactions(tr_id : int primary key, client : int, tr_date : datetime); The inserts to the table are done client-by-client The ...
1
vote
0answers
21 views

In Postgres, is it possible for me to have a table partitioning constraint based on data type?

Say I have the following schema for my Postgres database: CREATE TABLE sources ( source TEXT NOT NULL, PRIMARY KEY (source) ); CREATE TABLE ipaddrs ( addr INET ) INHERITS (source): CREATE ...
1
vote
0answers
57 views

PostgreSQL doesn't use index on inherited tables

I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS: an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about ...
1
vote
0answers
40 views

Restoring SQL Server 2012 Enterprise to Web Edition not possible due to full text partitioning

I have an SQL Server 2012 Enterprise database I’m trying to restore to SQL Server 2012 Web Edition instance. I’ve used sys.dm_db_persisted_sku_features to find features to remove before a ...
1
vote
0answers
37 views

Can I Implement a Sliding Window for a Table Spread Across Two Partition Schemes on the Same Partition Function?

I have a table with a PK based on a bigint identity and will have a clustered index on a datetime2 column. I've created a partition function based on calendar quarter and was planning on two partition ...
1
vote
0answers
67 views

Explain partitions

What does the possible keys, reference and rows indicate in explain partitions? I have used partitions on the table and I executing the explain partitions with a query: select ...
1
vote
0answers
14 views

Delete datafile from parttioned table

I'm used SQL Server 2014. My big table partitioned by date column and each partition has own datafile. I want delete oldest partition data and data file too. How to do it?
1
vote
0answers
554 views

Postgresql: Partition table by hour

I have a system that inserts several million records over the course of 24 hours. Users don't care about the data that's older than 24 hours, and typically query data over a small time frame (5 to 60 ...
1
vote
0answers
51 views

Using the partition function in the Where clause

I've been asked to test out performance of a database (a test system) after implementing horizontal partitioning. The database is on SQL Server 2008 r2. I'm inheriting this from someone else and ...
1
vote
0answers
82 views

Does partitioning help if you don't query on the partition value?

Say I have an orders.Order table that is partitioned on CreatedWhen (a datetime column set to GetDate() when the row is created). The table has over 10 million rows and the partition is quarterly ...
1
vote
0answers
54 views

Best ratio for files and file groups for a highly partitioned table

I have a number of tables with exactly 1000 partitions. Each table can be up to 200G with 100M rows and cannot be compressed. Data in partitions distributed quite equally. The SQL Server has 8 ...
1
vote
0answers
109 views

MySQL table partition strange behavior (slow query suddenly)

(I asked this question in "stack overflow" and was recommended to ask in here). (MySQL version: 5.6.15) I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a ...
1
vote
0answers
53 views

SQL Server partitioning of many-to-many tables - triggers

Many to many tables usually have only foreign keys to related tables, like EmployeeId PositionId Is it good idea to add column only for partitioning and set its value with INSTEAD OF INSERT/UPDATE ...
1
vote
0answers
165 views

Is it best practice to include partition keys within foreign keys when referencing partitioned tables?

The large tables in our database are partitioned by an id in order to separate data and speed up IO. The table of the data is partitioned by the partition key, but the primary key is only on an ...
1
vote
0answers
343 views

SQL Server : ALTER TABLE tablename SWITCH PARTITION 2 to tempempty

Have a sliding partition where I need to remove the trailing partition when they expire. I create an empty table that matches the existing table. When I create the empty table, I specify the file ...
1
vote
0answers
878 views

Incorrect information in .frm file after a power outage?

CentOS release 6.3 (Final) x86_64 mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1 SHOW SLAVE STATUS\G: Slave_IO_Running: Yes Slave_SQL_Running: No ...
1
vote
0answers
25 views

How do I configure MySQL to store specific entire InnoDB tables on a different drive in Windows ( Server )?

After skimming the MySQL ( 5.5 ) docs on Partitioning, that seems to deal with breaking parts of the same table off onto other drives. I'm struggling to find information on how I pinpoint an ...
1
vote
0answers
372 views

When to split a large table

I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc). However, in my use-case, every single day, the table gets 2160000 new rows. It's also crucial that ...
1
vote
0answers
119 views

MySQL Partitions

I've done a lot of work with MySQL but generally on a much smaller scale than this, so please excuse my ignorance. We've got an InnoDB table which is currently storing ~3.5 million rows, which I know ...
1
vote
0answers
304 views

How to make Greenplum 4.2.3 only scan the intended partition?

When I uses unnest() in a View, and uses that View in a select statement, Greenplum seems to fail in only searching for the intended partition and search through all the partition of the main table ...
1
vote
0answers
245 views

MySQL Partitioning, Reorganize Max Value partition (Known Bug / Feature)

I have written a crone job which reorganizes last max value partition every week into 2 new partitions , one for the previous week , and one for the future data (new max value partition which ...
1
vote
0answers
67 views

Partition guides and possible solutions?

We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be ...
0
votes
0answers
16 views

Pg_partman giving problem in partman.check_name_length function

I am using Pg_partman inpostgres to partition table using id. I am using the Partition Per 10 ID Values Starting With Empty Table Example given in the below link ...
0
votes
0answers
14 views

Parent Table not returning how much row has been affected

I have on table test and i have participate in multiple table testa,testb.......testz. I have inserted triggered before insert for data is gone in child tables. When i insert in data in parent ...
0
votes
0answers
27 views

Query slow on 5 Mil fact table

I have a fact table with currently 5 mil rec. The record count will go up as ETL runs on daily basis. The below reporting dashboard query is taking 1.50 sec to execute. There are many number of ...
0
votes
0answers
50 views

oracle : Effect of inserting new rows when row movement is enabled

I have a table which is partitioned by key. I want to move few records (which I require later) to another partition so that I can drop this partition and get some free space. I am enabling row ...
0
votes
0answers
24 views

How to Efficiently Handle Queries On a Table Subset

I have a MySQL database where one table in the database contains a datetime field and the other fields contain numeric data. One application frequently queries the server using a WHERE clause on one ...
0
votes
0answers
40 views

How to drop partitioned index in SQL Server 2005

I am unable to drop partitioned index due to partition scheme and partition function existence. Table has a Primary Key, a Clustered Index and non clustered indexes. I am running the following ...
0
votes
0answers
17 views

The most-recent-record problem with partitioned tables

Today's variant on the most-recent-record problem involves a table partitioned on the timestamp field relevant to the most-recent-record selection, and efficiently joining the result of the ...
0
votes
0answers
47 views

Why dropping TokuDB partition so slow

I have defined a simple time series table partitioned by UNIX timestamp (into 32 days). I then loaded ~17K entries, which were distributed among 8 partitions. There were at most 2880 rows in any ...
0
votes
0answers
25 views

Mysql partition - How to do list partitioning of a table that contains unique column?

I am doing mysql list partitioning. my table data is as below ---------------------------------------- id | unique_token | city | student_name | ---------------------------------------- 1 | xyz ...
0
votes
0answers
48 views

Oracle - compressed subpartitions in uncompressed table

I am working with Oracle 11.2. I have table which on subpartition level has in DBA_Tab_subPartitions column COMPRESSION set to ENABLED for all subpartitions. But on partition level in ...
0
votes
0answers
26 views

Broken partition function/schemes

The dilemma SQL Server 2008 Enterprise 2 partition schemes using the same partition function partition function splits on date column, monthly basis maintenance script to split partition function ...
0
votes
0answers
45 views

Insert strategy for partitioned table

Is there a separate strategy for inserting / loading data into a table that is partitioned? We are using TVPs inside a single proc to load multiple tables into a SQL Server DB and I did not change the ...
0
votes
0answers
43 views

Scheduled Partition one time by day

I am trying to schedule partitioning so that it runs only at 11:00pm. For example, I have the following table: Table 1 Name status John 0 Thomas 1 Dimitri 1 Yohann 0 At ...
0
votes
0answers
33 views

SQL Server: can reduce data file with partition table?

I had a database with one table called Saler stored on 'C:\'. Primary MDF file is very big (100G and about 200 million rows) and my disc is nearly full. Now I added a new file group 'Secondary' and a ...
0
votes
0answers
9 views

Designing Users table on DistributedDB

I am using SQL Server 2012 SP2 Express with PDB Wrapper 1.0.4 on 7x Partition nodes + Common server. Currently my users table is shared on common but we see high network latency. We left users table ...