Questions tagged [partitioning]

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

Filter by
Sorted by
Tagged with
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 ...
user22668153's user avatar
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) ...
user122222's user avatar
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 ...
Kenny Loveall's user avatar
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 ...
Ken Schnetz's user avatar
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 ...
TuBha's user avatar
  • 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]( ...
Mohd Waseem's user avatar
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, ...
Hasan Can Saral's user avatar
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 ...
Harshita Mishra's user avatar
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 ...
Joe's user avatar
  • 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 ...
raphael's user avatar
  • 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 ...
Rubén Noriega Mier's user avatar
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 ...
Vid's user avatar
  • 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 ...
user45867's user avatar
  • 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 ...
wheels's user avatar
  • 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 | ...
iForests's user avatar
  • 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 ...
adam.g's user avatar
  • 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 ...
Sheikh Wasiu Al Hasib's user avatar
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 ...
Sheikh Wasiu Al Hasib's user avatar
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-...
Mofarah's user avatar
  • 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 ...
Alexander's user avatar
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 ...
sujeet's user avatar
  • 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 , ...
Michelle's user avatar
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 ...
Radioleao's user avatar
  • 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 ...
Radioleao's user avatar
  • 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,...
forke's user avatar
  • 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 ) ...
sujeet's user avatar
  • 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 ...
EgeronimoL's user avatar
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 ...
pradhan's user avatar
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 ...
mCasamento's user avatar
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 ...
sujeet's user avatar
  • 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 ...
Siddharth's user avatar
-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)...
Grasba's user avatar
  • 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 ...
Kulstad's user avatar
  • 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 ...
Siddharth's user avatar
-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 ...
Kevin Meredith's user avatar
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 ...
Danilo Bassi's user avatar
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 ...
Danilo Bassi's user avatar
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 ...
Johnyb's user avatar
  • 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 ...
allendom's user avatar
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 ...
Tinker's user avatar
  • 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 ...
PeterL's user avatar
  • 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 ...
PeterL's user avatar
  • 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 ...
Oleg's user avatar
  • 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 ...
Dhiren Singh's user avatar
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. ...
Vineeth's user avatar
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 ...
itgav's user avatar
  • 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 ...
RubyRedGrapefruit's user avatar
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 ...
M.Barbara's user avatar
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). ...
user avatar
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 ...
Banan904's user avatar

1
2 3 4 5
21