Stack Overflow
Stack Overflow

Questions tagged [database-partitioning]

Database partitioning is the act of splitting a database into separate parts, usually for manageability, performance or availability reasons.

Database partitioning is done in one of two ways:

  1. vertically - reducing the number of columns in tables while increasing the number of tables

  2. horizontally (also called sharding) – splitting rows up into multiple tables based on key values. An example would be moving all the rows for each geographic region (such as a country) into their own tables.

Related questions:

Related tags:

1096 questions
11
votes
3 answers

Ruby on Rails / ActiveRecord and Table Partitions

I've read the documentation and been able to find anything to assist me so far. I've also read a large number of blog articles, but nothing seems to answer my question directly: Is it possible to manage table partitions in an InnoDB table with…
David Hoelzer
  • 15,862
  • 4
  • 48
  • 67
11
votes
8 answers

Storing changes on entities: Is MySQL the proper solution?

i want to store changes that i do on my "entity" table. This should be like a log. Currently it is implemented with this table in MySQL: CREATE TABLE `entitychange` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `entity_id` int(10) unsigned…
Stefan
  • 2,028
  • 2
  • 36
  • 53
10
votes
2 answers

SQL Server Partitioning - Unique Index Error

I have a table that is partitioned by TRANSACTION_DATE_TIME. Table has a column: ID. I want to create a unique index for ID on partition scheme as: CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_ON_PS_DATETIME] ON [CRD].[TRANSACTION] ( [ID] ASC ) ON…
Ahmet Altun
  • 3,910
  • 9
  • 39
  • 64
10
votes
2 answers

Indexing the partition key in Azure Cosmos DB

Suppose I've the following data in my container: { "id": "1DBF704E-1623-4844-DC86-EFA729A5C048", "firstName": "Wylie", "lastName": "Ramsey", "country": "AZ", "city": "Tucson" } Where I use the field "id" as the item id, and the…
galbru
  • 422
  • 1
  • 5
  • 15
10
votes
1 answer

How to sort horizontal partitioned data

I have a telco billing software system. In it there are daily logs of users' calls. The logs are horizontally partitioned by date (month). Each partition is stored in a separate database and may be spread over multiple instances. In the UI the user…
Gaurava Agarwal
  • 974
  • 1
  • 9
  • 32
10
votes
2 answers

How to partition a MySQL table based on char column?

Is it possible to partition based on char column? After reviewing the MySQL 5.1 documentation it appears that only integer types can be used. Is this correct? Or can I use some function to convert the char into an integer? The char field in…
John M
  • 14,338
  • 29
  • 91
  • 143
10
votes
4 answers

Dynamic table partitioning in Oracle

I'm in the process of building a database storage for my app consisting on a single table with a huge data volume (hundreds of millions of records). I'm planning on having an index on the date field, since I'll be doing a batch recovery of all the…
Eduardo Z.
  • 633
  • 3
  • 10
  • 32
9
votes
2 answers

What is a good size (# of rows) to partition a table to really benefit?

I.E. if we have got a table with 4 million rows. Which has got a STATUS field that can assume the following value: TO_WORK, BLOCKED or WORKED_CORRECTLY. Would you partition on a field which will change just one time (most of times from to_work to…
Revious
  • 7,816
  • 31
  • 98
  • 147
8
votes
1 answer

Postgres long-running transaction holding lock on parent partitioned table

TL;DR: we have long-running imports which seem to hold locks on the parent partitioned table even though nothing is directly referencing the parent table. Background In our system, we have inventories and inventory_items. Inventories tend to have…
Alexander Wallace Matchneer
  • 3,289
  • 1
  • 23
  • 26
8
votes
4 answers

Partition Hive table by existing field?

Can I partition a Hive table upon insert by an existing field? I have a 10 GB file with a date field and an hour of day field. Can I load this file into a table, then insert-overwrite into another partitioned table that uses those fields as a…
batman
  • 1,447
  • 5
  • 16
  • 27
8
votes
2 answers

How to do a trigger that modifies a record in a partitioned Postgres table?

I have a table with a trigger that updates a "modified" timestamp whenever the record changes. I did it with a BEFORE trigger: CREATE OR REPLACE FUNCTION update_modified() RETURNS trigger AS $$ BEGIN NEW.modified = now(); RETURN…
ccleve
  • 15,239
  • 27
  • 91
  • 157
8
votes
2 answers

Is it safe to use postgres table partitioning with overlapping constraints?

Postgres documentation says that conditions in table partitions should not overlap Ensure that the constraints guarantee that there is no overlap between the key values permitted in different partitions. but I don't understand why, because exact…
Gruzilkin
  • 1,276
  • 1
  • 11
  • 8
8
votes
2 answers

When to use horizontal partitioning and when to use database sharding?

I'm reading this article on Wikipedia: http://en.wikipedia.org/wiki/Shard_(database_architecture) trying to find the major difference between these 2 techniques. Here is what I found: Horizontal partitioning splits one or more tables by row,…
Dio Phung
  • 5,944
  • 5
  • 37
  • 55
8
votes
5 answers

how to find partition of record oracle

I have a table and there is a partition on it. There are 16 hash partition which is starting from SUBSCRIBER_01 .. etc Table name: SUBSCRIBER Partition Column: CUSTOMER_ID (VARCHAR2 10) Database : 11g How can I find partition of a record? Like…
Mehmet
  • 2,256
  • 9
  • 33
  • 47
7
votes
3 answers

Is there any way to make Hibernate use literal values rather than bind variables?

In Oracle I have a partitioned table. The partitions are of different sizes and have different data distribution. I would like to have hibernate issue SQL statements that includes a literal value for the partition key column rather than a bind…
WW.
  • 23,793
  • 13
  • 94
  • 121
1 2
3
…
73 74