Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
1,059
questions
0
votes
1
answer
35
views
PostgreSQL query scanning all partitions instead of specific one when filtering on dimention table
I have two tables in PostgreSQL, one large (transactional) with millions of data which is partitioned on column TenantId and one smaller (dimension) which has TenantId and TenantName columns, I have ...
- 170
0
votes
0
answers
13
views
VALIDATE CONSTRAINT of Foreign key pointing to partitioned table for individual partitions instead of everything at once
I have a partitioned table user(id, tenant_id) partitioned on tenant_id that has a hundred partitions (user_1000, user_2000 etc.)
I have a separate existing table events(id, user_id, tenant_id) that I ...
- 269
0
votes
1
answer
42
views
How to get partition scheme for filestream data?
Short version: is there a way to get the partition scheme for the FILESTREAM data used by a clustered index created on a partitioned table?
Longer version:
Say you want to partition a table storing ...
- 93
0
votes
0
answers
33
views
Problem with creating partitions in postgresql
I have partitioned tables in postgresql. With some of them everything is fine and partitions are created. But with some of them there are problems. When I start a transaction to create a partition it ...
0
votes
2
answers
47
views
how to divide a filetable to several filetable on several disk in sql server?
I have a dms database that use filestream. I want to distribute data to several disk based on year. How can I divide this database to several disk in sql server?
I used Partitioning technique but I ...
- 1
0
votes
1
answer
21
views
MySQL table partition on non primay key column
Below is my table structure, the primary key id is a combination of reportedUserId_reporteeUserId. I used it to perform INSERT IGNORE queries, I want to implement a table partition on the scanType ...
- 115
0
votes
0
answers
48
views
How does table partitioning size affect query performance?
In my system, I have a set of Oracle 19c database tables which stored temparary data, which are getting expired. I have a solution where I partitioned these tables with the create timestamp of these ...
- 1
0
votes
0
answers
50
views
RLS Does Not Bind Queries to List Partitioned Partitions
Each table in this database is partitioned with a column named SourceId, list partitioned such that SourceId= 'COKE' or 'PEPSI', etc.
RLS(Row Level Security) is being used such that each user will be '...
- 567
0
votes
1
answer
35
views
Adding FK to a newly attached partition slow
We have a partitioned table from which we attach and detach partitions for archiving purposes. When attempting to add a foreign key on the partitioned table, the process is taking a long time (up to ...
- 23
0
votes
1
answer
112
views
TRUNCATE WITH PARTITION Constantly Fails on 1 Out of 620 Tables
There is a table that seems to have constant updates. There is also a stored procedure that runs periodically and iterates through tables and attempts to truncate a partition of data using the command ...
- 567
0
votes
1
answer
72
views
Error "No catalog entry found for partition ID ..."
When trying to delete disabled index or enable the same disabled index on one heavily utilised table, I am getting an error:
No catalog entry found for partition ID 72057594967228416 in database 6. ...
- 360
1
vote
1
answer
84
views
SQL query to get the names of the partitions which are detached from the parent table in postgres?
I used pg_partman to create and drop partitions from my 2 postgres tables. Since there existed a foreign key relations among the tables, I couldn't drop the tables using partman(does not allow).
...
0
votes
1
answer
67
views
Using ENUM as partition key when RANGE Partitioning PostgreSQL
I am working with PostgreSQL 14. I have a table called Order with a status column:
CREATE TABLE "Order" (
"orderId" BIG SERIAL PRIMARY KEY NOT NULL,
"orderDescription&...
- 25
0
votes
1
answer
74
views
Compressing partitioned tables in Oracle 19c
I am compressing partitioned tables.
before partitioned tables, I tried normal tables with the follwoing steps:
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'USER',
orig_table => '...
1
vote
1
answer
68
views
How to attach partition with primary key
I'm struggling to attach a partition to a table with a primary key.
I have a partitioned table Transactions:
create table "Transactions"
(
id bigserial ...
0
votes
1
answer
121
views
How to move partitions to other pg server?
I have a table with 1b rows and it continues growing. Currently, I use pg_partman for partitioning by month. There are a lot of select queries only for current or previous months. Is it possible to ...
- 103
7
votes
1
answer
267
views
Filter by partition number when the table is partitioned by computed column
I've created 2 identical tables partitioned by an integer column. In one of the tables the column is computed, in the other table the column is not computed.
When I query the table with computed ...
- 1,533
0
votes
1
answer
75
views
Removing partitions from MySQL table
I have table containing 3.2TB data in partition named default_partition, & all writes/read goes to this partition, as we have defined partitions on table like below :
PARTITION BY RANGE (to_days(...
- 101
7
votes
3
answers
1k
views
Azure SQL Managed Instance: 1. excessive initial compile times leading to app timeouts and 2. partition maintenance: long-running, bloats data file
We are having a couple of issues preventing us from going live with an on-prem SQL 2016 Std Ed to Azure SQL Managed Instance Business Critical tier re-platform, and I was wondering if anyone had ...
- 416
0
votes
1
answer
83
views
Optimizing Cassandra Table Design for Queries Involving Two Partition Keys
I’m designing a Cassandra table and need advice on the best schema design to handle my query patterns efficiently. The primary queries are as follows:
90% of the queries involve both account_id and ...
- 131
0
votes
1
answer
310
views
What is the fastest way to insert a large amount of data into a partitioned table in postgresql?
I have a table, natively partitioned by date. The partitions encompass 1 month. I have another very large table (19GB), from which I want to copy data into the partitioned table. I have used ...
0
votes
2
answers
111
views
Performance issue - using try catch to return data to service & how to use partition table with unique index
I'm working on enviorment SQL azure.
I've got a stored procedure that runs from several AKS simultaneously
and insert a message.
So it's a race for who writes the message first and the rest fails.
The ...
- 47
0
votes
1
answer
308
views
Foreign key constraint check for partitioned tables
I am trying to find the best way to manage postgresql partitioned tables.
I have two partitioned tables (one partition per day, based on column created_at existing on both tables), fridge_tagreading ...
- 15
1
vote
0
answers
81
views
Poor Postgres Query Performance with a Partitioned Table - pg_hint_plan does better
Simplified, I have a table with this layout:
CREATE TABLE IF NOT EXISTS public.scans
(
id bigint NOT NULL DEFAULT nextval('new_scans_id_seq'::regclass),
geo geometry(Point) NOT NULL,
...
- 11
0
votes
1
answer
42
views
strange behaviour of the postgresql planner on a partitioned table
I have a problem where I don't understand the behaviour of the Postgresql 14.9 database planner on a partitioning table.
I'm sorry if my explanation is long, but I wanted to give as many details as ...
- 23
0
votes
1
answer
150
views
With postgres table partitioning by list (of varchar), will the varchars be stored physically in each row?
When having a table that gets partitioned by list, using a varchar field (or text field) that contains long strings, will those strings be physically stored in each partition and hence increasing the ...
- 141
0
votes
1
answer
31
views
Search in Archived table Automatically or not in sql server?
I partitioned my main table in sql server which has over 12,000,000 records and archived the records before a certain date. My question is, without changing the queries that were written before ...
0
votes
1
answer
304
views
Moving data to new partitioned table
I have one large table in SQL Server (around 5,5 billion rows) and I intend to create new partitioned table for that data, probably by year, with filegroup for each year for better management. This is ...
- 3
5
votes
1
answer
236
views
DELETE failed with errors: A system assertion check has failed. + Range is missing prefix on partition id column
I have partitioned table with many indexes, all of them are aligned and only one is not.
This is because it controls the uniqueness through more then one partition:
CREATE UNIQUE NONCLUSTERED INDEX [...
- 11.1k
0
votes
1
answer
41
views
SSDT Project Will Not Publish or Generate Preview When Source and Target Partition Function Not Matched
In a SSDT project there is a partition function defined in a file as follows.
CREATE PARTITION FUNCTION [pfSourceID](varchar(50)) AS RANGE RIGHT FOR VALUES (
'',
'COKE',
'DRPEPPER',
'MOUNTAINDEW',
'...
- 567
0
votes
0
answers
27
views
Querying the size of the PostgreSQL partitioning range
I have the below query which shows me the partitioned table names in a PostgreSQL database. But I need to define which table in this list is partitioned with which range?
For example, this query finds ...
1
vote
1
answer
108
views
Can't see table partitions after adding PK
I'm on Azure SQL, adding monthly partitioning to some historical archive tables (creating a 2nd table with partitioning scheme/function and dumping the rows from the original into it). I was using a ...
- 113
0
votes
2
answers
43
views
Need help identifying opportunities for improvement in table config and query design
I am working on a project with a fairly large data set. We require arbitrary aggregates on this dataset, these are generated at request time from a user. Here is a basic description of our current ...
0
votes
1
answer
23
views
Identifying Rows Where Conditions Occur
I am working with Netezza SQL.
I have the following table:
name year var1 var2
John 2001 a b
John 2002 a a
John 2003 a b
Mary 2001 b a
Mary 2002 a b
Mary ...
- 143
1
vote
2
answers
48
views
Table partitioning for data pruning purposes
We are using MySQL database. The total space it can accommodate is 250 GB. It is currently at 225 GB. With this, there had been this initiative within our team to create a daily cron job for daily ...
0
votes
1
answer
50
views
Partition table and partition elimination
I've got a large table of about 300 millones rows. My enviorment is Sql server azure.
There are constant insert to this table And some updates.
The quieres for this table are only for data in the past ...
- 47
0
votes
2
answers
100
views
Storing terabytes of data in Azure SQL Server
I need to develop a Azure SQL server production system capable of storing a user item recommendations based on the user's purchase history, user health profile and item recommendations(around 415 MB). ...
- 103
0
votes
0
answers
52
views
SPLIT RANGE ON A TABLE
I'm trying to create a job that executed automatically the rotation of partitions on a table.
I executed the next script but the ranges are not rotating successfully
DECLARE @PARTITION_FUNCATION_NAME ...
- 37
0
votes
2
answers
99
views
Why has the file group for the partition grown in size after the alter?
A table is partitioned by month/year. They would like to add 1/1/2024 but what the scheme is the correct sequence until the end where it ends like:
[FG_11_2023], [FG_12_2023], {PRIMARY], [FG_01_2024])...
- 9
0
votes
1
answer
224
views
Table Partitioning on Multiple columns different types
I would like to create a partition table in Oracle SQL DEVELOPER, using two columns with different types? I want to stock in the partition table all data where:
creation_date <'2021' and ...
- 3
0
votes
1
answer
156
views
Switching table partition into temporary table
I'm trying to switch a table partition into a temporary table with the following query:
create partition function [PFCT_T](datetime2) as range right for values ()
create partition scheme [PSCH_T] as ...
- 27
0
votes
2
answers
32
views
How do I design a table which has limited key diversity?
I'm in the process of designing a Cassandra database expected to handle messages on several clusters. The data model is primarily structured with a partitioning strategy based on a key locationId ...
- 131
1
vote
0
answers
190
views
Convert table to partition and columnstore index on different key
I have a table with the following characteristics:
200 million rows
A clustered primary key that is an auto-incrementing BIGINT column
An NVARCHAR(MAX) column consisting of raw JSON data, which we ...
- 574
1
vote
2
answers
139
views
MySQL 8- Add a Default Partition to a Table Partitioned on a Field Value
I have a table partitioned by values of a KPI field. There are some kpi with a large amount of rows some with small numbers. Partitioning by KPI name (string) keeps query performance good.
The data ...
- 13
0
votes
0
answers
63
views
Insert data in postgres partition
I have table:
CREATE TABLE full_data (
id int4 NOT NULL,
name varchar NOT NULL,
deleted bool NOT NULL
)
PARTITION BY LIST (deleted);
and its two partitions: active (where deleted = false) ...
- 115
1
vote
1
answer
297
views
How do I find missing sub-partitions of an index to make main index complete?
I have a table with many partitions that I wanted to add a new index to. Following the instructions from Postgres I created the new index on the main table using ON ONLY and then proceeded to create ...
- 121
2
votes
1
answer
152
views
Should I use this partitioning/indexing strategy for a table that grows by about a billion rows per year?
I'm a software engineer with about 10 years of experience building Laravel web applications for small businesses. I'm pretty comfortable with database design, but it's not my greatest strength.
One of ...
- 23
0
votes
1
answer
78
views
SQL Server : Partitioned table data type change
We have 40TB size SQL Server 2016 database. we have tables with 100+ billion rows per table which are partitioned. The older partitions are read only and new partitions are Read write. Recently for ...
- 1
0
votes
3
answers
358
views
Partitioning an existing table yearly in SQL Server with huge data
I have a logs table for my application which is almost 3TB data. I want to partition this table yearly.
Below is the scripts for Table, View, and indexes script:
GO
CREATE TABLE [dbo].[AuditLogs](
...
0
votes
1
answer
64
views
What'd be the downside of a uniqueness check within a trigger for a partitioned table?
I had the following table:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence') PARIMARY KEY,
transaction_id VARCHAR(255) DEFAULT NULL NULL,
...
- 179