Questions tagged [partitioning]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
212 views

Can I grant access to a MySQL user for a single partition of a table?

Is there a way, in MySQL, to grant access to a single partition of a table to a specific user? I see no way in the doc for that, but maybe I'm missing it... Here's a test case: CREATE TABLE testing (...
0 votes
1 answer
171 views

Thousands foreign keys referenced to one table and other postgres scaling philosophy

I'm inspecting one legacy and quite big database which is growing faster nowadays than before. Following query select count(*) from information_schema.tables where table_schema = 'public' returns a ...
0 votes
1 answer
361 views

Automatically partition a table in mysql (pymysql) everyday for new data

Im still figuring out partitioning in SQL. Is it possible to automatically partition a table everyday for new data? For example, on Monday, I have one partition. On Tuesday, automatically create a ...
2 votes
3 answers
23k views

conversion of high_value to date datatype

I need to comparte the high_value which is of LONG datatype to sysdate which is of DATE datatype. I need to fetch the partition names of all the partitions which is 90 days old. The code is some what ...
0 votes
1 answer
1k views

Create Range Partition on existing large MySQL table

I have inherited a database that contains a large table (relative to the rest of the database - 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 ...
0 votes
1 answer
218 views

Partitioned mariadb table files(the .ibd ones) placed outside of data directory return back to data directory itself after altering the table in use

While Partitioning large tables in Mariadb we want some partitions to be placed in another storage device with different speed and capacity. That is our requirement. For that we are trying to perform ...
0 votes
1 answer
20 views

Partitioning vs Archiving Data for a specific use case

I'm not going to ask the difference between partitioning and archiving as there are countless of information out there on the net discussing them, and I have read the details of partitioning as ...
0 votes
1 answer
161 views

Postgres natively partitioned tables - adding extra child columns

With Postgres native partitioning, can you add extra columns to a partition of the base table? Like: CREATE TABLE item ( tenant_id bigint NOT NULL, item_id bigint NOT NULL, kind text NOT ...
0 votes
0 answers
22 views

Improving performance with a common low-cardinality field

I have a database with multiple tables. Every table has a field named sourcename, which indicates basically which customer the data came from. There are only about ten possible values for this field ...
0 votes
2 answers
2k views

Is there a way to search the earliest or latest postgres partition for table?

BACKGROUND I am working on a web application that has date pickers for searching. Those searches query a partitioned table. So far, so good. However, based on the data I'm querying, we don't have a ...
4 votes
2 answers
104 views

Optimise query for fetching data across multiple days in a partitioned table

I'm trying to optimise a query to retrieve records across multiple days on a partitioned table (say: entries). The table is partitioned on a timestamp column (let's say: created_at) and for each day a ...
0 votes
1 answer
1k views

How do partitions handle new data?

I have a database with nearly 100M rows of stock data. Pulling the data for the last 7 days to generate a chart takes a couple seconds. My thought is to create partitions that represent the normal ...
1 vote
1 answer
394 views

MySQL Partitioning based on addition

I cannot find a good keyword to search for this kind of question, so here you go: Suppose i have this table: CREATE TABLE test ( tr_uuid BINARY(16) NOT NULL, Customer VARCHAR(10) NOT NULL, Data ...
2 votes
1 answer
2k 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
1 answer
818 views

Any workarounds for lack of primary key on partitioned tables?

I am currently looking at implementing declarative partitioning with PostgreSQL 10. However, one sticking point is that we need to have a foreign key between two such partitioned tables, which is not ...
6 votes
2 answers
10k views

Partitioning a Table Referenced by a Foreign Key

I have one table: CREATE TABLE [dbo].[entry] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [EntryDate] [datetime] NOT NULL, [createddate] [datetime] NOT NULL, CONSTRAINT [PK_Entry_ID] ...
0 votes
2 answers
22 views

Is it possible to create a MySQL partition and then create a subpartition by another column?

I read the docs about subpartitioning, but I think it doesn't cover what I'm trying to achieve. https://dev.mysql.com/doc/refman/5.7/en/partitioning-subpartitions.html We have this table: CREATE TABLE ...
1 vote
0 answers
43 views

Multiple databases vs partitioning in PostgreSQL

Consider following database schema for PostgreSQL (I'm using 13.x.x) Users table - just place to store users and do authentication Projects - projects which are created by specific user Driver/Event/...
1 vote
1 answer
482 views

How to select rows with a particular rank per partition?

I want to pick the row with the highest value in a column based on partitioning on another column. The problem is to pick the highest-earning employees from each department, click here for the ...
0 votes
1 answer
18 views

MySQL partition failed

I do partition with 6.595.057 rows in MySQL with the Galera cluster. When I tried partition in server without galera, partition successfull. But when I tried partition in server with galera cluster, ...
4 votes
1 answer
689 views

postgres automatic table list partitioning

How to handle new partitions in a smart automatic manner in PostgreSQL ? I want to create a PostgreSQL table List partition on a certain column. For example: CREATE TABLE orders ( id ...
6 votes
2 answers
685 views

Trigger on partitioned table and performance

I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about it, so I've decided I'll put it here. So, in a nutshell, I'm facing terrible ...
1 vote
0 answers
31 views

Trigger on partitioned table and performance [duplicate]

I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn'...
0 votes
1 answer
498 views

Moving mysql database to new partition with different partition "type"

Sorry, Possibly a dumb question, but something which needs clarification. Currently mysql is stored on sda which is around 1TB. Now our DB has outgrown that, almost !!. So trying to create new ...
0 votes
1 answer
60 views

Partition scan order in Postgres

I have created a natively partitioned table in Postgres and when trying to query this, Postgres is scanning the partitions in ascending order. The partition key is a timestamp column. For my use case, ...
0 votes
1 answer
601 views

MySQL - Move data between partitions aka re-partition

I have a mysql table whose partitions look as below p2015h1 - Contains data where date < 2015-07-01 (Has data from 2016-06-01. Hence only month worth of data) p2015h2 - Contains data where date &...
2 votes
2 answers
2k views

Table partition in aws MYSQL RDS master slave setup

we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this ...
0 votes
1 answer
257 views

Creating and dropping partition in PostgreSQL "on the fly"?

In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative ...
0 votes
1 answer
56 views

Is reorganizing a partition effectively the same as copying data from one table to another?

If I have a maxvalue partition as such: PARTITION p0 VALUES LESS THAN MAXVALUE and then reorganize the partition so that it has two partitions - one for older data and one for current data: ALTER ...
2 votes
1 answer
42 views

MySQL Query slows down on concurrent requests to different subpartitions of a partitioned table

We have partitioned a large table (with over 100 million records) into multiple partitions and subpartitions in our Serverless Aurora to query different subpartitions in parallel. But we find that ...
0 votes
1 answer
41 views

Optimal procedure for partitioning with auto-increment PK as boundary value

I'm working with an application that deals mainly with large sets of related cost and planning data. This data gets stored across maybe 20 different tables in various models. Each set of data is ...
1 vote
2 answers
374 views

Get postgres partitioned table structure by a select

I'm looking for some SIMPLE SQL-based command to show me the partitioned table structure in postgres. Something like \d <table>. All I find so far is by doing pgdump. db=> \d ...
0 votes
2 answers
278 views

Postgresql 12 Custom Hash Function

I have a large table that I would like to partition. The field I want to partition against is a string that contains a coded value, but appears as an integer stored as a string. The "number" ...
0 votes
1 answer
1k views

Mariadb/MyISAM: Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an

We have a db with around 150 tables in, of which about 30 or so are partitioned as such : ALTER TABLE `myTable` REORGANIZE PARTITION PFUTURE INTO (PARTITION P201910 VALUES LESS THAN ('2019-11-01'), ...
0 votes
1 answer
2k views

Partitioning with AUTO_INCREMENT primary key

I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). The table in question uses an AUTO_INCREMENT column as a primary key as this table ...
0 votes
1 answer
260 views

Dropping PostgreSQL partition is blocked as long as a client is reading data from any partition

I have a simple partitioned table in PostgreSQL 14, like this: create table entity ( dataset_id integer not null references dataset (id), ... ) partition by list (dataset_id); create table ...
0 votes
0 answers
36 views

Is there any option to have change tracking partition table?

We have one partitioned table which has a lot of changes during week. This are stats for table: table_rows table_size_mb ct_rows ct_size_mb 175 857 125 18 762 2 610 849 197 467 859 Is there any ...
1 vote
3 answers
603 views

Partitioning and existing Indexes

I have a table that consumes almost 1.2 TB of space and has lots of historical data which are mostly irrelevant. Since performing bulk DELETE is going to cause a lot of heavy-lifting, I'm trying to ...
0 votes
0 answers
257 views

Foreign Key to Partitioned Table

I have the following basic schema for an ever growing huge transaction table. In order to query speedup & phase out stale data, I need to partition by time & country. CREATE TABLE transaction (...
11 votes
1 answer
6k views

MySQL Partitioning: 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 ...
0 votes
1 answer
171 views

How to add/create default partition for a table in mysql

I searched a lot - but couldn't find. How to create default partition for a table in mysql 5.7. Thanks a lot!
1 vote
1 answer
257 views

Unable create partition by trigger

1) I have table CREATE TABLE tor ( events_date timestamp without time zone NOT NULL, message text CONSTRAINT tor_pkey PRIMARY KEY (events_date, message) )PARTITION BY RANGE (events_date); 2) ...
2 votes
1 answer
579 views

MariaDB: Querying a timestamp partition

I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
2 votes
1 answer
242 views

Partitioning and the InnoDB Buffer Pool

I am trying to maintain a database that contains a table with 40 billion rows (7.2 terabytes) on a server using InnoDB as a storage engine and MariaDB with MySQL 5.5. When my database reaches about 2....
1 vote
1 answer
693 views

Getting insight into partitions of a table in Azure Synapse (similar to SQL Server)

I'm using Dedicated SQL-Pools AKA Azure Synapse (which is different from serverless/on-demand Synapse that comes with Azure Synapse Analytics). And According to Azure: While the syntax of ...
0 votes
2 answers
142 views

Reclaiming space from half filled huge FGs having Partitioned and LOB Data in it

We have a database with multiple filegroups. Out of which, there are 4 filegroups(Non-Primary) which has most of the data. These filegroups have partitioned tables into it. It also contains LOB Data ...
0 votes
2 answers
109 views

Postgresql partitioning queries benefits

I am trying to apply partitioning process to some large tables on my Postgresql database, I have read the documentation and many articles about that and could not found an answer to this question. If ...
0 votes
1 answer
51 views

Oracle - Big Table Archiving

Version: Oracle 12cR2 EE The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2 Table size: 95GB Table row count: 20M Table partitioned? No Indexes on table: there ...
2 votes
1 answer
82 views

Postgres partition pruning with text prefix matching

I have a table where the natural identifier is a text like this: AB-C123456E-F, which is my primary key. The table is approaching 200 million rows, and so to get better performance on the unique ...
0 votes
0 answers
17 views

Maximum number of partition function objects

What is the maximum number of partition scheme objects, and partition function objects? Note, this is not the partition number per table (which is 15 000), but just how much such schema objects can be ...

1
2 3 4 5
20