Questions tagged [partitioning]

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

Filter by
Sorted by
Tagged with
0 votes
2 answers
34 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 ...
Kyle Chamberlin's user avatar
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 ...
stats_noob's user avatar
1 vote
2 answers
38 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 ...
Xmus Jackson Flaxon Waxon's user avatar
0 votes
1 answer
40 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 ...
dexon's user avatar
  • 47
0 votes
2 answers
55 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). ...
Ajit Goel's user avatar
  • 103
0 votes
0 answers
37 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 ...
Carolina's user avatar
0 votes
2 answers
86 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])...
Marcel's user avatar
  • 9
0 votes
1 answer
48 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 ...
DS_Tn's user avatar
  • 1
0 votes
1 answer
32 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 ...
Arokh's user avatar
  • 105
0 votes
2 answers
28 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 ...
Chillax's user avatar
  • 131
1 vote
0 answers
81 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 ...
Greg's user avatar
  • 574
1 vote
2 answers
42 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 ...
user22668153's user avatar
0 votes
0 answers
26 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) ...
user122222's user avatar
1 vote
1 answer
79 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 ...
Kenny Loveall's user avatar
2 votes
1 answer
86 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 ...
Ken Schnetz's user avatar
0 votes
1 answer
36 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 ...
TuBha's user avatar
  • 1
0 votes
3 answers
168 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]( ...
Mohd Waseem's user avatar
0 votes
1 answer
59 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, ...
Hasan Can Saral's user avatar
0 votes
1 answer
48 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 ...
Harshita Mishra's user avatar
0 votes
0 answers
200 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 ...
Joe's user avatar
  • 179
1 vote
0 answers
26 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 ...
raphael's user avatar
  • 529
0 votes
1 answer
173 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 ...
Rubén Noriega Mier's user avatar
0 votes
2 answers
128 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 ...
Vid's user avatar
  • 1
0 votes
0 answers
26 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 ...
user45867's user avatar
  • 1,683
2 votes
0 answers
121 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 ...
wheels's user avatar
  • 21
1 vote
1 answer
190 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 | ...
iForests's user avatar
  • 113
0 votes
1 answer
58 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 ...
adam.g's user avatar
  • 335
1 vote
1 answer
232 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 ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
2 answers
72 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 ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
1 answer
37 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-...
Mofarah's user avatar
  • 11
0 votes
1 answer
52 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 ...
Alexander's user avatar
0 votes
1 answer
412 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 ...
sujeet's user avatar
  • 235
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 , ...
Michelle's user avatar
1 vote
1 answer
74 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 ...
Radioleao's user avatar
  • 125
0 votes
2 answers
124 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 ...
Radioleao's user avatar
  • 125
0 votes
2 answers
425 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,...
forke's user avatar
  • 15
0 votes
1 answer
256 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 ) ...
sujeet's user avatar
  • 235
2 votes
2 answers
562 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 ...
EgeronimoL's user avatar
0 votes
4 answers
147 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 ...
pradhan's user avatar
1 vote
1 answer
190 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 ...
mCasamento's user avatar
0 votes
1 answer
676 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 ...
sujeet's user avatar
  • 235
2 votes
1 answer
233 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 ...
Siddharth's user avatar
0 votes
1 answer
266 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)...
Grasba's user avatar
  • 11
0 votes
1 answer
76 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 ...
Kulstad's user avatar
  • 55
0 votes
1 answer
665 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 ...
Siddharth's user avatar
-1 votes
1 answer
965 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 ...
Kevin Meredith's user avatar
0 votes
1 answer
125 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 ...
Danilo Bassi's user avatar
0 votes
1 answer
65 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 ...
Danilo Bassi's user avatar
3 votes
1 answer
88 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 ...
Johnyb's user avatar
  • 131
-1 votes
1 answer
934 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 ...
allendom's user avatar

1
2 3 4 5
21