Questions tagged [partitioning]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
949 views

mongodb - monthly archival of data

Coming from a RDBMS background, this is what I have done in the past. Create a table with partitions based on a date field. There are typically three tables (partitions) - current (M0), previous ...
1
vote
1answer
1k views

Is Postgres sharding available on Aurora (AWS)

I need to shard and/or partition my largeish Postgres db tables. The Postgres partitioning functionality seems crazy heavyweight (in terms of DDL). I see talk from <=2015 about pg_shard, but am ...
1
vote
3answers
322 views

Partial local index on partitioned Oracle table

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and ...
1
vote
1answer
130 views

MySQL Partitioning based on addition

I cannot find a good keyword to search for this kind of question, so here you go: Suppose i have this table: CREATE TABLE test ( tr_uuid BINARY(16) NOT NULL, Customer VARCHAR(10) NOT NULL, Data ...
0
votes
1answer
35 views

Non matching attribute 'INSTANT COLUMN(s)' between partition and table

I have a program that regularly exchanges a partition with a table. MyTable is a partitioned table, and MyTableTemplate is an empty copy of this table, with the same structure but no partitioning. ...
1
vote
2answers
302 views

Mysql Range partition

I have the next table structure: CREATE TABLE `sat_msgPos` ( `idOwnApp` mediumint(11) NOT NULL DEFAULT '0', `idMsg` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `idVeh` mediumint(11) NOT NULL ...
0
votes
4answers
357 views

filter out the records with same id having different values in a column

Can anyone help me to solution for below issue. create table [order1] (order_id int, CartTransaction_Id int, Status_Id int); insert into [order1] values (357488,87214,1), (357489,87214,8), (357490,...
-4
votes
2answers
1k views

Can data size be less than index size in sql database?

Can the indexes on a SQL Server table consume more disk space than the table itself? I have gone through several articles and studied this and a few expert recommended table partitioning, for ...
1
vote
1answer
427 views

How to deal with unbounded growth in cassandra shards

Let's say we have a website like youtube where we have a comment session per e video. Users want to be able to comment and to see the comments made by other users by comment creation time. This is ...
1
vote
2answers
135 views

Optionally exclude certain partitions using OLA's scripts

I have been using OLA's scripts for a long time and love them. I have a very large database - 20+ TB with hundreds of partitioned tables. I purge old data by dropping (merging the old partitions)...
1
vote
0answers
98 views

Partitioning Table: SQL Server [duplicate]

I created 12 partitions from July 2017 through July 2018 and didn't leave an empty partition at the end. Hence, am having a hard time adding new partitions using the split function. I even tried ...
3
votes
3answers
2k views

Can I speed up a large MySQL/MariaDB join through partitioning or merging?

I'm trying to run the following join query as part of a more complex one on MariaDB 10.1.26. select distinct project_commits.project_id, date_format(created_at, '%x%v1') as week_commit ...
2
votes
3answers
182 views

Size of Partitions in Partitioned Tables

According to the following link, it is advisable to use hash partitioning when, "The sizes of range partitions would differ quite substantially or would be difficult to balance manually." This implies ...
2
votes
1answer
523 views

What are the downsides of using PARTITION for multi-tenant access?

We have an RDS Postgres database which keeps individual store data in a common schema with a field called store_id to distinguish between stores. Let's say we have 1MB per store and 10k stores so we'...
4
votes
2answers
995 views

Move data from a table to another without losing data

I have a log table which is currently holding millions of records. I want to enable partitioning on that table, so what I did for now is: Created a partition function and a partition scheme. Created ...
1
vote
1answer
257 views

How to move a Daily Partition to a Monthly Partitioned Table

I would like to switch out a partition from a daily partitioned table and merge it into monthly partitioned table. I am unsure as to how to go about it. Here's what I have so far. I have two tables,...
1
vote
1answer
505 views

Any workarounds for lack of primary key on partitioned tables?

I am currently looking at implementing declarative partitioning with PostgreSQL 10. However, one sticking point is that we need to have a foreign key between two such partitioned tables, which is not ...
1
vote
1answer
91 views

Partitioning in PostgreSQL 10

Currently we are using pg_partman for partitioning our tables in PostgreSQL 9.4. We are planning on upgrading to PostgreSQL 10. I read that PostgreSQL 10 has better support for partitioning. While I ...
0
votes
0answers
70 views

Create partitioned with move partitioned data from original table

I have big size of database almost 500GB and i just wanted to retain 1 years of data from some large tables and move others data to a different location, So I am trying this by creating partitions on ...
3
votes
1answer
2k views

Mongo CP, Cassandra AP?

I have read lot of articles on net but still confused why Mongo CP, Cassandra AP, RDBMS CA ? Will explain my understanding and query along with that . Mongo Consider a scenario where I have ...
0
votes
1answer
831 views

Rename column in a partitioned table

I want to rename a column in a partitioned table. Will oracle do this for all partitions? Is there anything special for this change?
2
votes
1answer
337 views

Create table partitions on different filegroups, the first boundary on a Memory Optimised filegroup

99% of all our transactions are against the latest week's data, a WEEK_SELECTOR column describing which week is being described. Partitioning our billion+ row table by week would be beneficial, more ...
4
votes
2answers
179 views

Partition elimination on order by

I have a partitioned table and problems with partition elimination. Setup My table if quite simple and looks like this: CREATE TABLE [data].[OperationData]( [DataLoggerID] [bigint] NOT NULL, ...
5
votes
3answers
283 views

switching from normal list partitioning to reference partitioning in oracle 11g

I have a main parent table and multiple child tables which have normal partitioning in it, wherein the partition column is repeated in all child tables. I want to switch to reference partition(oracle ...
0
votes
3answers
218 views

Question on MySQL Partitioning

Using version 5.5 (on which I'm stuck), I have a table I want to partition, here is the DDL: CREATE TABLE `emp` ( `id` int(11) DEFAULT NULL, `date1` bigint(20) DEFAULT NULL, `gen_year` int(11) ...
0
votes
1answer
94 views

sql server partitioned view wrong execution plan on update a temporary table

i need your help because we have a performance issues. I would like to update a temporary table that contains a join to the partitioned view. Schema: create schema SCHEMA1 go create schema SCHEMA2 ...
2
votes
1answer
454 views

Splitting Existing Partitions in SQL 2014

I have a SQL Server table that contains over 20 billion rows, with 13 billion rows in the last partition. The table is currently partitioned 6 ways by a "Time" field: 2011 2012 2013 2014 2015 2016/...
0
votes
1answer
267 views

Database design / database tuning for market basket analysis on ownership of products

My goal I want to record data for a market basket analysis (https://en.wikipedia.org/wiki/Affinity_analysis) on computer software and query this this data frequently. Data description One user may ...
2
votes
2answers
7k views

Delete data from a specific partition in MS SQL 2012

I would like to delete data from a specific partition using the partition ID. I got queries to truncate data from specific partition for SQL 2016 but did not find any query for lower versions. I ...
0
votes
1answer
52 views

Find continuous records with equal values characters [closed]

I need your kind support to resolve this issue. I have a table contains similar to the below data : |AccountNo|remarks | |Q32571 |Actual Read| |Q32571 |Locked | |Q32571 |Actual Read| |...
2
votes
1answer
275 views

Dealing with out-of-range values on a PARTITION BY RANGE

I have a table for storing events which is partitioned on a timestamptz column by month using PARTITION BY RANGE. There are 5 partitions at the moment, containing a span of a month each, starting ...
-1
votes
1answer
344 views

Fragmentation in InnoDB tables: dump all data together reduced size, but fragmentation got worse [closed]

I have a question about tables and fragmentation in MySQL My starting point is this question: How find and fix fragmented MySQL tables I have a table with 1 million of rows and 128MB of space. This ...
0
votes
1answer
53 views

How to find the time taken to execute a script

As a part of partitioning table,1M records were inserted into a table using insert statement and I forgot to capture the time taken to insert. Is there any way to find out the time taken to execute ...
1
vote
2answers
66 views

Will table partitions help me?

I have 10 tables. dbo.Table2008 dbo.Table2009 ... dbo.Table2018 Each table holds approximately 500,000,000 records which are 20 columns wide (if that matters). And each table has a clustered ...
3
votes
1answer
958 views

Table Partitioning and updates to partition subset

I am partitioning a large table (2 Billion records ) on an integer say AssetID that has 70,000 unique values, due to partitioning limitation of 15,000 I will create a partition on say 10,000 values ...
0
votes
1answer
72 views

MySQL Queries to get only runtime/duration, not fetched results

We are running few benchmark queries against our test RDS instance for checking efficiency of partitioning, before adapting it to production, where there is no partitions presently. We have few ...
0
votes
1answer
67 views

data fetch with multiple filters in a paginated way

We have a very huge table with around 10 partitions in our system. The table data has multiple references in other tables. The database administrator has advised us to avoid performing any join query ...
0
votes
1answer
123 views

Vertical Partition MySQL table

I am reviewing a case, where I have 200+ columns mostly varchar(100). The columns are from several external data sources like CRM / Demographics data etc. I cannot hold them in one MySQL table with ...
1
vote
1answer
584 views

Table Partition Switch Problem SQL Server

I have Tables Schema: CREATE TABLE [dbo].[t1]( [id] [bigint] IDENTITY(1,1) NOT NULL, [id_file] [bigint] NOT NULL, [id_campo] [bigint] NOT NULL, [valor] [nvarchar](255) NULL, [id_doc] [bigint] NULL, ...
1
vote
1answer
18 views

How to determine that the maximum size of the database file is exceeded, and switch the data insertion to the second server?

First I`m sorry for my bad English. My problem is I have one virtual server with PHP backend and one server with one single-table MySQL database. I don't want the file size of the database to exceed ...
4
votes
0answers
96 views

Moving a very large (40 TB) DB from a 2008 server to a 2016 server [duplicate]

The Situation: Our 2008 MSSQL server is old and rapidly running out of drive space. We have a new 2016 server ready, we just need to move a 40 TB database from the old server to the new one. The DB, ...
4
votes
1answer
935 views

Composite Primary Key on partitioned tables, and Foreign Keys

We have a database where several tables are partitioned. The primary key (PK for brevity) for these tables is of the form transaction_date, id Where transaction_date is the partitioning key, and id ...
2
votes
1answer
124 views

Unable to Query Very Large Table

I have a table defined as: CREATE TABLE [dbo].[DataTable]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Data] [varbinary](max) NULL, [Preview] [varbinary](max) NULL, CONSTRAINT [PKDataTable] ...
2
votes
1answer
467 views

Partitioning increases CPU usage in mariadb

I'm using mariadb 10.2, and I had a table with a structure similar to the one below, currently with 15gb, where I insert a few thousand records per minute all the time, each batch for a specific ...
3
votes
1answer
871 views

Creating additional unique keys on a partitioned table

I am utilizing partition schemes in order to logically isolate data based on a TenantId column. TenantId is the partition column and clustering index across all tables that require tenant isolation. I ...
-1
votes
1answer
353 views

Reduced copy of zabbix database

I've a relatively big postgresql database that tooks a lot of space in a server. The database stores a zabbix system used to monitor data that I've to safely keep, so history tables are quite big. My ...
1
vote
1answer
1k views

What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on. We are using partitioning for this and ...
3
votes
1answer
330 views

How to eliminate tables in a partitioned view

I can't get my query that joins a regular table to a partitioned view to eliminate tables that don't meet the predicates on the partitioning column. In particular, I'm interested in the case where I ...
1
vote
2answers
1k views

SQL Partition Function without actual values [duplicate]

I would like to partition a column by fiscalyear. How Do I create a partition function without harding coding values? This is what I currently have below. create table dbo.CustomerAccount ( ...
1
vote
1answer
91 views

Refactoring a very large database table and having join over millions of records

I have a fairly big postgresql jobs table of (more than 60 attributes) and one critical query contains joins with this table of million records. and I cannot reduce its size(paritioning by range which ...

1 2 3 4 5 16