Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
1,018
questions
1
vote
2
answers
25
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
17
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) ...
- 105
0
votes
1
answer
13
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 ...
- 111
2
votes
1
answer
77
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
30
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
137
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
58
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,
...
- 169
0
votes
0
answers
29
views
Performance impact when querying without partition key on a partitioned table
Assuming I partition a table of large size (say 6TB) by month. We are serving very high traffic of around 300 RPS.
Sample schema
Create table mydata(
id int not null,
date_created not null,
uuid_val ...
0
votes
0
answers
85
views
Migrate existing Postgres table with foreign key references to declarative partitioning
I want to apply declarative partitioning to a Postgres table referenced by many other tables with foreign key constraints.
As far as I can tell, Postgres provides limited support for this. The entire ...
- 179
1
vote
0
answers
15
views
PostgreSQL - Table partitioned on date doesn't prune partitions when querying with a daterange
Our tables are partitioned by month on the dt date column
CREATE TABLE IF NOT EXISTS ta
(
link_dir text COLLATE pg_catalog."default" NOT NULL,
dt date NOT NULL,
tod time without ...
- 529
0
votes
1
answer
92
views
Analyze only one partition in PostgreSQL
I have a metrics table partitioned by year and month (metrics_y2023m01, metrics_y2023m02, etc). The analyze metrics command updates the statistics of the table and each partition individually, but the ...
0
votes
2
answers
68
views
Partitioning a Table Vs Partitioning a clustered index
If I have a Table in SQL server, with Time series data for every business date (~100k rows a day)
and only the last 45 days are frequently updated/deleted/inserted, it would be better to partition ...
- 1
0
votes
0
answers
25
views
Design for massive dimensional table to improve query time? Can it be partitioned horizontally?
I've never had this issue before -- but inheriting a big data project.
There is a dimensional table "account" or "user" that contains every dimensions known to mankind. Or let's ...
- 1,651
2
votes
0
answers
97
views
Efficient Partitioning Strategies for PostgreSQL Table with KSUID and High Volume
I'm working with a PostgreSQL table containing terabytes of data, and it grows by millions of rows weekly. Each row is identified by a KSUID, and my primary read patterns are:
Retrieve a row by its ...
- 21
1
vote
1
answer
117
views
Postgres: deadlock when ATTACH PARTITION to a table with a lots of INSERTs
I have a partitioned table user_stay which has many INSERTs and UPDATEs.
db=> \d user_stay;
Partitioned table "public.user_stay"
Column | ...
- 113
0
votes
1
answer
41
views
Can I do transaction for dynamic sql which contains DISABLE change tracking / TRUNCATE / PARTITION SWITCH
I would like to create transaction which will :
DISABLE change tracking IF exists.
TRUNCATE partition for table.
SWITCH PARTITION from different table to main table.
ENABLE change tracking IF was ...
- 295
0
votes
1
answer
124
views
Unable to SWITCH IN a partitioned table
I have created two tables one is OrdersLatest and another is OrdersMarch. I want to switch data from the partitioned table to the staging table(OrdersMarch) on the same filegroup and vice versa.
It ...
0
votes
2
answers
54
views
SQL Server database table was partition but table property showing partitioned false
Here orders table was partitioned but at the order table property, it was not partitioned. What is the reason for that? Because when I am trying to switch partition 2 from the old table to another ...
0
votes
1
answer
34
views
PostgreSQL select a record that not pass the condition
I have partitioned a table named Order into two partitions, Order_open and Order_close.
I run following query on Order table to retrieve the related record ("orderID"='e641d634-5c6c-4c75-...
- 11
0
votes
1
answer
41
views
Partitioning using column from different table
I have a table ActionValues with the following columns:
AccountID
Action
ActionValue
This table is related with a second table called Actions that has the following columns:
AccountID
Action
...
0
votes
1
answer
230
views
PostgreSQL Partitioning and Indexing - Optimal WHERE Clause for Partition Pruning
I have a PostgreSQL table named orders, which I have partitioned based on the user_idcolumn using LIST partitioning using,
PARITION BY LIST lower(right(user_id, 2))
The table has columns customer_id ...
- 213
0
votes
0
answers
40
views
How to have another column showing first date from partiton by clause?
select distinct ea.FIN
, dem.patient_name
, dem.dob
, age_in_years(e.beg_effective_dt_tm::date, dem.dob) as Age_at_Encounter
, cv.display as Facility
, cv2.display as Nurse_unit
, cv3.display as Loc
, ...
- 1
1
vote
1
answer
72
views
MS SQL which best data configuration for huge online datas?
I have a database on SQL SERVER, which has a mix of operational data and huge amount of storage data:
Nr 15 table for configuration infos;
Nr 3 tables with huge amount of data for every month:
Data ...
- 125
0
votes
2
answers
99
views
MS SQL full backup by partition, can be done without log?
The matter is that I really don't need t-tlog and i wish to avoid it. I'have looked in many topics but I haven't seek out the answer I need (the topic of MSSQL backup is very tricky to understand well ...
- 125
0
votes
2
answers
205
views
MariaDB partitioning table and query performance
I am looking for a way to optimize large table with 8M+ rows.
Table struct looks like follow:
id INT PRIMARY AUTOINCREMENT
removed TINYINT(1)
status TINYINT(1)
~10 other data fields
There is also (id,...
- 15
0
votes
1
answer
150
views
PostgreSQL query scanning all partitions instead of specific one
I created my table as below:
CREATE TABLE IF NOT EXISTS public.customers (
user_id varchar(255) NOT NULL,
item_name varchar(255) NOT NULL,
score int4 NULL,
is_marked bool NULL
) ...
- 213
2
votes
2
answers
464
views
How can I prevent race conditions in Cassandra?
Lets say two users simultaneously want to access the same partition of a Cassandra table:
Suppose we have a Cassandra table called "user_data" that stores user data. The table has a ...
- 21
0
votes
4
answers
124
views
Choosing the index partition type for OLTP application
The ORDER_ITEMS table holds the data related to the orders.
Use case: OLTP. The rows will be added and the historical data(more than 3 years old) will be deleted periodically. Partition/Index ...
- 1
1
vote
1
answer
148
views
SQL Server Encryption at rest for some rows of a database (SQL 2019 Std)
I'm looking for a solution to encrypt a portion of a database at rest. The database contains data from different companies that should be encrypted with separate keys.
So far, I've considered that TDE ...
- 121
0
votes
1
answer
438
views
Partitioning strategy and optimal number of partitions for varying data formats (string, numeric, and UUID) in a large table
I'm working on designing a partitioning strategy for a large table in my database, and I'm considering using hash-based partitioning. I came across a recommendation to have the number of partitions ...
- 213
2
votes
1
answer
207
views
PostgreSQL partitioned table data model
I have created partition by range based on row_created_tmst for PostgreSQL table. Since partition keys needs to be part of the primary key in PostgreSQL, row_created_tmst is included in the primary ...
- 27
-1
votes
1
answer
193
views
How should I handle index maintenance on partitioned tables in Azure SQL DB?
let's start saying that i have no experience with partitioned tables and i'm not quite sure how to handle index maintenance (periodically Rebuild / Reorganize, if needed, to reduce index fragmentation)...
- 1
0
votes
1
answer
52
views
MSSQL2019 - Manage files and filegroups in sliding window partitioning
I am just getting into partitioning, and have found quite a few good blogs on how to set up and manage sliding window partitions (the ones I've chosen to follow are at SQL Shack and MSSQLTips). Both ...
- 55
0
votes
1
answer
460
views
Create PostgreSQL partitions automatically
I need help to understand if partitions for PostgreSQL table can be created automatically using triggers.
For e.g., to create daily partition based on range partition and then sub-partitions of that ...
- 27
-1
votes
1
answer
583
views
Partitioning with Primary Key
https://www.postgresql.org/docs/14/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW notes the following table:
CREATE TABLE measurement (
city_id int not null,
logdate date not ...
- 495
0
votes
1
answer
97
views
Cant perform partitioning after Foreign Keys were dropped
I'm trying to partitionate a table that has almost 1TB in size in MySQL, I already dropped al my FK using this command for all the FK:
ALTER TABLE mytable DROP FOREIGN KEY my_fk;
but when I try to ...
0
votes
1
answer
62
views
What strategy should I use for my gigant MYSQL Tables?
I'm working in a system that is using MySQL as primary DB.
One of the biggest tables is almost 1TB in size, others are around 100-300 GBs.
Queries started to be really slow.
I search some info, and I ...
3
votes
1
answer
68
views
How do distributed databases follow unique constraints?
Lets say i have an application where user can register, and the username has to be unqiue value.
Now lets say i have N partitions and for each partition i have M replicas with multiple leaders.
Now i ...
- 131
-1
votes
1
answer
729
views
How to use count to to get the max value in SQL partition
I have a table including branches and items sold. I would like to check which item has the highest quantity sold in the store.
I have a table with hundreds of rows like this structure and need to use ...
- 1
0
votes
1
answer
48
views
Is creating a new table better than partitioning for my use case?
Problem: i have new data that is being generated monthly. When this new data becomes available, my existing rows in my table becomes stale and I have to delete all of them.
I am considering two ...
- 119
3
votes
0
answers
173
views
What should be the column order , when creating indexes on a Partitioned Table
Partitioned Table Question.
When creating indexes on a Partition table ( Fact Table) , we always include the column that is the basis for the partitioning in the index. This column is also ALWAYS used ...
- 41
0
votes
1
answer
35
views
Partition Function has got out of Synch
we have a large database that has a number of partitioned tables
The tables are partitioned by an EventKey column that is int datatype, with each unique value being allocate it's own partition.
For ...
- 41
4
votes
1
answer
205
views
Unable to restore database with datetime2(0) in Partition Function
I have a SQL-Server database with large table that partition by a datetime2(2) column.
Some (old) filegroups marked as READ_ONLY.
Periodically I make the backup with READ_WRITE_FILEGROUPS option.
I ...
- 43
1
vote
1
answer
327
views
Need a PL/SQL code to drop many partition in 1 single SQL statements
I have a requirement where we need to drop historical partitions which are not dropped .
Requirement is dropping multiple partitions in one step instead of dropping one by one.
like
ALTER TABLE sales ...
- 13
0
votes
1
answer
478
views
MySQL Select query for a calculated column that uses previous row value for current row
I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated.
Table has 3 columns - Employee, Month, Salary. ...
- 1
1
vote
1
answer
106
views
Will partitions speed up data ingestion for Postgresql?
Using Postgresql version 15.1
I'm making a table of blockchain transactions (1 row per transaction). In total, it will have ~2 billion rows. Once the table reached ~50 million rows, the data ingestion ...
- 13
0
votes
1
answer
185
views
How can I finish a "RANGE type" partition in PostgreSQL?
I have a 7 TB table in a PostgreSQL 12.3 database. I want to create a "RANGE type" partition on this column. The other thing to note is that this year column is a text field rather than an ...
0
votes
1
answer
99
views
How do I improve the performance of this particular delete function? Is table Partitioning the answer?
I am taking over a database that is... not great. When this DB was originally built, it was built without too much understanding about the volumes of data that were going to be involved. The initial ...
0
votes
1
answer
42
views
Is it advantageous to split parts of large time-based tables and store in separate data files? [closed]
On Microsoft SQL Server, I have large (multi-gigabyte) time-based tables for which I want to periodically age out and archive older data (say at the end of each year drop everything 5 years or older).
...
David Posner
2
votes
2
answers
1k
views
How defragment compressed partitioned table in oracle?
I have partitioned table by day with compressed query high option for partitions in Oracle 11.2 database.
Delete by key and direct insert was used to populate a data for the table. So the table is ...
- 33