Questions tagged [partitioning]

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

Filter by
Sorted by
Tagged with
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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/...
user avatar
  • 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, ...
user avatar
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'...
user avatar
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 ...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 (...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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....
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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' ...
user avatar
  • 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 ...
user avatar
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 (...
user avatar
  • 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 ...
user avatar
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 (...
user avatar
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. ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
-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. ...
user avatar
  • 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....
user avatar
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 ...
user avatar
-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 ...
user avatar
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) ( ...
user avatar
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 [...
user avatar
  • 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 ...
user avatar
  • 111

1
2 3 4 5
20