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
17
votes
1 answer

Optimize Postgres query on timestamp range

I have the following table and indices defined: CREATE TABLE ticket ( wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass), eid bigint, created timestamp with time zone NOT NULL DEFAULT now(), status integer NOT NULL DEFAULT 0, …
user1754724
  • 171
  • 1
  • 1
  • 4
16
votes
4 answers

Azure Cosmos DB partition key - is primary key acceptable?

Our Azure Cosmos DB collection has gotten large enough to require a partition key. In doing some reading about this, I get the impression that the best partition key is one that provides for even distribution and higher cardinality. This article…
Scotty H
  • 6,432
  • 6
  • 41
  • 94
16
votes
1 answer

In Azure Cosmos DB, can we change partition key later on once we decided at the beginning

I am new to Cosmos DB and I noticed that we can set the partition key based on needs to scale effectively through code like this: DocumentCollection myCollection = new DocumentCollection(); myCollection.Id =…
Mike
  • 205
  • 2
  • 10
16
votes
8 answers

How to select rows from partition in MySQL

I made partition my 300MB table and trying to make select query from p0 partition with this command SELECT * FROM employees PARTITION (p0); But I am getting following error ERROR 1064 (42000): You have an error in your SQL syntax; check the manual…
Kad
  • 542
  • 1
  • 5
  • 18
15
votes
2 answers

Dynamic Partitioning + CREATE AS on HIVE

I'm trying to create a new table from another table with CREATE AS and dynamic Partitioning on HiveCLI. I'm learning from Hive official wiki where there is this example: CREATE TABLE T (key int, value string) PARTITIONED BY (ds string, hr int)…
Adriano Foschi
  • 648
  • 1
  • 8
  • 23
14
votes
3 answers

Automatic partitioning by day - PostgreSQL

I would like to do a daily partitions. I know with oracle is something like this. CREATE TABLE "PUBLIC"."TEST" ( "ID" NUMBER(38,0) NOT NULL ENABLE, "SOME_FIELD" VARCHAR2(20 BYTE) NOT NULL ENABLE, "ANOTHER_FIELD" VARCHAR2(36 BYTE) NOT…
R. Pereira
  • 205
  • 1
  • 3
  • 10
14
votes
1 answer

How does sharding handle the joining of related tables?

When I read about sharding, looks like authors don't take into account other tables the sharded table has to be joined to (even though they describe a shard as a "subset of an original database"). However, this is a very common situation and I still…
raiks
  • 1,270
  • 1
  • 15
  • 12
14
votes
2 answers

Oracle Partition - Error ORA14400 - inserted partition key does not map to any partition

I'm trying to insert information in a partition table, but I don't know what I'm doing wrong! Show me this error: ORA-14400: inserted partition key does not map to any partition" The table dba_tab_partitions shows this informations below: 1 …
Alan
  • 151
  • 1
  • 1
  • 7
14
votes
3 answers

Mysql 5.5 Table partition user and friends

I have two tables in my db that have millions of rows now, the selection and insertion is getting slower and slower. I am using spring+hibernate+mysql 5.5 and read about the sharding as well as partitioning the table and like the idea of…
maaz
  • 4,371
  • 2
  • 30
  • 48
13
votes
6 answers

hibernate insert batch with partitioned postgresql

is there a solution for batch insert via hibernate in partitioned postgresql table? currently i'm getting an error like this... ERROR org.hibernate.jdbc.AbstractBatcher - Exception executing batch: org.hibernate.StaleStateException: Batch update…
tropikalista
  • 1,629
  • 3
  • 19
  • 35
13
votes
2 answers

How does one do a SQL select over multiple partitions?

Is there a more efficient way than: select * from transactions partition( partition1 ) union all select * from transactions partition( partition2 ) union all select * from transactions partition( partition3 );
Yusufk
  • 1,055
  • 2
  • 10
  • 15
12
votes
1 answer

Partition by date range PostgreSQL scans all partitions

I have a table partitioned per month (timestamp column). when querying the data, explain shows that all partitions are being queried when I'm constructing a date with date functions, whereas when I use hard coded dates only the targeted partitions…
Elger Mensonides
  • 6,930
  • 6
  • 46
  • 69
12
votes
3 answers

Is there a performance difference in using a GROUP BY with MAX() as the aggregate vs ROW_NUMBER over partition by?

Is there a performance difference between the following 2 queries, and if so, then which one is better?: select q.id, q.name from( select id, name, row_number over (partition by name order by id desc) as row_num from…
Marina
  • 3,222
  • 5
  • 25
  • 35
11
votes
1 answer

How to integrate postgresql 10/11 declarative table partitioning (i.e. PARTITION BY clause) in a Django model?

PostgreSQL 10 introduces declarative table partitioning with the PARTITION BY clause, and I would like to use it to a Django model. In principle all what I would need to do is introduce the PARTITION BY clause at the end of the CREATE TABLE…
toto_tico
  • 17,977
  • 9
  • 97
  • 116
11
votes
8 answers

Mysql improve SELECT speed

I'm currently trying to improve the speed of SELECTS for a MySQL table and would appreciate any suggestions on ways to improve it. We have over 300 million records in the table and the table has the structure tag, date, value. The primary key is a…
allyLogan
  • 403
  • 2
  • 6
  • 13
1
2
3
…
73 74