Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
959
questions
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 ...
- 1
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 ...
- 101
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 ...
- 141
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
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/...
- 111
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, ...
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'...
- 354
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 ...
- 354
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, ...
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 ...
- 121
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
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 ...
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 ...
- 177
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 ...
- 307
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 (...
- 113
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 ...
- 1
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 ...
- 425
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 ...
- 21
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 ...
- 266
0
votes
1
answer
176
views
Azure SQL - Hyperscale service tier - are table partitions supported
In the Azure SQL DB, running in the Hyperscale service tier, are table level partitions supported? It does not seem to be called out in the known limitations page.
While trying to create a filegroup ...
1
vote
1
answer
133
views
Not all check constraints used when ATTACH PARTITION in PostgreSQL
I have a table verbatim which is partitioned by an integer column dataset_key which also is part of a compound primary key:
\d+ verbatim_default
Partitioned table "public....
2
votes
1
answer
46
views
How to gauge performance of switching to PostgreSQL table partitioning?
I have a table in a PostgreSQL database containing about 100m records that's mostly read-only with a few monthly writes to update new data.
It has over 300 columns, and is heavily indexed for specific ...
- 1,058
0
votes
0
answers
679
views
Primary keys in Postgresql partitioned tables
I have a Rails app with a Postgres 11 database. There's a table (callees) that needs to be partitioned. Currently, it has an auto-increment column (id) as the primary key. The table is referenced from ...
- 101
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 ...
- 29
0
votes
0
answers
40
views
Change the control column of pg_partman
I have a postgres table, and I am partitioning it with pg_partman extension. I am using column A for partitioning the table, but now, I want to change it to column B.
Does partman provides any utility ...
- 101
0
votes
1
answer
124
views
Deadlock on same table when creating partition in PostgreSQL
PostgreSQL 14.1. I have a partitioned table liked this:
create table dataset_session
(
dataset_id integer not null references dataset (id)
session_id integer not null,
external_id uuid not ...
- 177
0
votes
1
answer
275
views
PostgreSQL partitions consuming huge amounts of memory
I have a decent sized (~10M row) table that recently has started slowing down quite a bit. As a solution to this, we decided to partition this table into 1,000 tables, partitioned by a hash of the ...
- 103
0
votes
0
answers
59
views
Is there performance penalty for many partition ranges? [duplicate]
The following partition defines the monthly ranges for one year ahead:
CREATE PARTITION FUNCTION PF_Monthly(date)
AS RANGE RIGHT FOR VALUES (
'2022-01-01'
, '2022-02-01'
, '2022-03-01'
...
- 266
4
votes
1
answer
50
views
PostgreSQL – Can queries that require all partitions be blocked or rejected?
We are planning to partition a dataset of ~500million rows using list/range partitioning in PostgreSQL 13.4 on RDS. Our read access patterns should only require a very small number of partitions to be ...
- 141
0
votes
1
answer
67
views
Oracle date or timestamp PARTITION columns
Is there a query I can issue to tell if a PARTITION column is a date or timestamp.
For example this should retun timestamp
CREATE TABLE ts (
num_val NUMBER(6),
ts_val TIMESTAMP)
PARTITION BY RANGE (...
- 103
0
votes
1
answer
80
views
Table partitioning on an existing table and using different filegroups
I have an existing table on the primary filegroup, which I want to partition. The partition key is on year, which is a calculated column. I want to partition the table in such a way that in the end ...
- 457
3
votes
1
answer
349
views
How to utilize partition pruning with subqueries or joins?
I have a partition table...
CREATE TABLE erco.rtprices
(
scedtime timestamp with time zone NOT NULL,
node_id integer NOT NULL,
lmp numeric(12,6),
CONSTRAINT rtprices_pkey PRIMARY KEY (...
- 631
0
votes
2
answers
88
views
How to resolve INSERT contention in Azure SQL Database?
I'm working with an Azure SQL Database that has a very hot logging table. Any time a row in any other table is inserted, updated, or deleted, the web application writes a row to this logging table. ...
- 2,078
0
votes
1
answer
45
views
Partitioning strategy for table with two commonly used columns
So, I'm still kind of new to postgresql so sorry in advance if this might be a silly question.
I have this table (let's call it some_table) that I'm trying to partition in a logical way based on how ...
0
votes
0
answers
7
views
paritition exchange without validation mysql 8
I have a table in production facing about 1M inserts per day,partitioned on a date column.
This table is queried as well but the inserts are important for application.
Have a small script that runs ...
0
votes
1
answer
91
views
MSSQL Large table rolling using partitions
I have a large DB (6TB) consisting of 3 tables: “messages”, “messagesDetails” , “unprocessedMessages”.
Here are simple descriptions of them:
TABLE [messages](
[id] [bigint] IDENTITY(1,1) NOT ...
0
votes
2
answers
180
views
How to partition large 200M PostgreSQL table for analytical use
I have ~1.5TB of json data, 200 million records that I need to import into a PostgreSQL database and would like some advice on the recommended way to partition the table. The target schema is fairly ...
- 111
1
vote
2
answers
2k
views
Create partitioned table from existing table
Given the following table:
create table widget (
id serial primary key,
description text,
created_at timestamptz not null default current_timestamp
);
I try to create a ...
- 113
0
votes
0
answers
45
views
Alter an existing partition in a table
The table definition of existing table is:
CREATE TABLE [TEST].[TEST1]
(
[ProductId] [nvarchar](256) NULL,
[Date] [date] NULL,
)
WITH
(
DISTRIBUTION = HASH ( [ProductId] ),
CLUSTERED ...
Sharad R. Telkar
0
votes
1
answer
54
views
help finding out the cause of this error in mysql partitioned table
I have a table wallet_transactions which grows very quickly. So, i have partition the table .
CREATE TABLE `wallet_transaction` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`code` bigint(20) DEFAULT ...
- 99
0
votes
2
answers
50
views
What is the best way in terms of non blocking table for creating NONCLUSTERED INDEX for partitioned tables?
We are using SQL Server 2016 and have 3 partitioned tables (every table around 300-500 milion of records). For every table would like to create NONCLUSTERED PARTITIONED INDEX. What is the best way to ...
- 45
0
votes
0
answers
69
views
Oracle - Is it possible to change the parallel degree only at partition or subpartition level?
Is it possible to change the parallel degree just in a specified partition or subpartition? I didn't find anything about it and I'd like to confirm it. Also, does this make sense?
For example, for the ...
- 508
0
votes
0
answers
88
views
Removing unused files and filegroups from partitioned table
I have a partitioned table that has about 50 old, empty partitions in it that need to be removed. Each partition has its own file and filegroup. I merged the partitions, then wrote the below Cursor ...
- 203
-2
votes
1
answer
127
views
What is the best way to order Partitioned table?
What is the most efficient way to get ordered data from a partitioned table?
I have this query:
SELECT TOP (4000)
*
FROM dbo.MS
ORDER BY MANDT
, MBLNR
, MJAHR
, ZEILE
Table is partitioned by MBLNR.
...
- 45
-2
votes
1
answer
609
views
How to create partition function on two columns like integer and datetime
Can a table be partitioned in two ways?
For example, both with date and with the unit
if unit=1 and date > 2021-09-01 save rows in file1.ndf
if unit=1 and date > 2021-10-01 save rows in file2....
2
votes
3
answers
227
views
Large database partitioning and backups of only updated data (partial backups)
I'm setting up a large SQL Server database (2-4tb), but only a small percentage of it will be updated. I'd like to partition by year so that my backups can be small. HOWEVER, some of the old data may ...
-1
votes
0
answers
10
views
Large database partitioning and backups of only updated data (partial backups) [duplicate]
I'm setting up a large database (2-4tb), but only a small percentage of it will be updated. I'd like to partition by year so that my backups can be small. HOWEVER, some of the old data may change on ...
0
votes
1
answer
35
views
Index not used in a partitioned Oracle table
I have a table partitioned by list
create table ICT_ENCOURS_BRUT
(
code_exercice VARCHAR2(4),
CODE_PRODUIT VARCHAR2(2),
.......
)
partition by list (CODE_EXERCICE)
(
...
0
votes
0
answers
52
views
When do parameters not exclude tables from a partitioned view
It seems that I have some instances where passing a parameter to a partitioned view on the partitioned column still queries all the tables. But now there's more to it...
I have a four table [...
- 123
1
vote
1
answer
49
views
Partition by hash on two levels
I'm trying to partition a large table by a generated ID(unfortunately it's a mongoDB, so the first few bytes are not evenly distributed)
I would like to shard it (using a foreign data wrapper) on this ...
- 111