Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
2
votes
1answer
25 views
MySQL when using WHERE BETWEEN, query hits all partitions
This is probably just me misunderstanding how MySQL partitions work, but I have a table defined with:
`ID` int(11) NOT NULL,
`target_id` int(11) NOT NULL,
`created_at` datetime NOT NULL,
...
0
votes
0answers
24 views
Should I split my database by schema or do a table partition?
I have a PostgreSQL database whose data comes from 800 different locations and right now consists of 4 billion rows in total (all locations combined). All this data is arranged in 3 tables (approx 1....
0
votes
0answers
24 views
Add more partitions to partitioned table [duplicate]
How would I create more partitions to an existing partitioned table? At the moment, there's partitions set for every day for the remainder of 2018, and now I need to add more partitions for everyday ...
1
vote
1answer
14 views
Moving mysql database to new partition with different partition “type”
Sorry, Possibly a dumb question, but something which needs clarification.
Currently mysql is stored on sda which is around 1TB. Now our DB has outgrown that, almost !!. So trying to create new ...
0
votes
1answer
20 views
Postgres 11 declarative partitioning by relative time
I'm working on a system that uses PostgreSQL 11 as it's main data store, and I'm looking at how to most efficiently query a large table of events (up to 120 million rows, so not 'big data' big, but ...
0
votes
0answers
10 views
Optimum partitioning strategy for cassandra
Please bear with me for slightly long problem description.
I am a newbie to Cassandra world and I am trying to migrate my current product from oracle based data layer to Cassandra.
In order to ...
0
votes
0answers
21 views
Partitioned table does not return number of rows inserted successfully
Recently we partitioned out postgres tables and when we run the insert query to this partitioned table, postgres does not return number of rows inserted. Is this an expected behavior of postgres, if ...
8
votes
1answer
1k views
TOP(1) BY GROUP of very huge (100,000,000+) table
Setup
I have a huge table of ~115,382,254 rows.
The table is relatively simple and logs application process operations.
CREATE TABLE [data].[OperationData](
[SourceDeciveID] [bigint] NOT NULL,
...
1
vote
1answer
23 views
Does dropping a partition and running update indexes stop DML execution on the whole table?
I am looking to drop a partition from a table in the database.
I wanted to know if I run ALTER TABLE DROP PARTITION UPDATE INDEX, would there be any blocking of DML statement executions on the other ...
2
votes
1answer
53 views
Moving the data between partitions in a table
There is one table with partition, where partition function and scheme is defined like below:
CREATE PARTITION FUNCTION DateRangePF (CHAR(8))
AS RANGE RIGHT FOR VALUES ('20180101','20190101')
...
1
vote
1answer
30 views
Adding a CHECK constraint doesn't improve performance of ATTACH PARTITION on postgres table
I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote....
Before running the ATTACH PARTITION command, it ...
1
vote
2answers
65 views
How to identify the column used to partition a table from the Postgres system catalogs
Given a table created as so...
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (...
0
votes
0answers
17 views
Designing a table to receive snapshot data in daily windows in PostgreSQL
I want to store a large snapshot in a table. This snapshot contains data from today and the next few days related to specific regions. This snapshot is created every day and the old data should be ...
0
votes
2answers
34 views
With partitioned table, How can I use hints to group each partition separately
Suppose I have the following table
data(partitioned_key_index, some_dummy_measure)
Assume that partitions are of an equal large size. with Oracle 11g.
The end result should be like this
...
1
vote
0answers
11 views
Filegroup per Table partition - remove filegroup and re-add filegroup
I've seaching all over the web for a solution, if it is even possible... I'm not a savvy in Partitioned tables in different filegroups...
Our software is supported by SQL 2008 R2 SP2 to SQL 2017 for ...
1
vote
0answers
22 views
How to VACUUM FULL a single partition without stalling the parent?
I have a table called TABLE_A that's partitioned by month. Each partition is named according to it's month, TABLE_A_201810, TABLE_A_201811 etc
I've noticed that if I attempt to VACUUM an old ...
0
votes
1answer
22 views
partition in ndbcluster
My environment will use 2-3 cluster nodes. The db schema at first was designed without knowing the limitation/rules with NDBCLUSTER on (BIG mistake! I know). Some things I notice and want to ...
0
votes
1answer
31 views
Partitioning an existing table into multiple filegroups [duplicate]
I am working on a SQL Server 2016 SP2 Standard edition server, with an in-house application that is 24/7 used in multiple locations across multiple countries. The current application has one database ...
0
votes
0answers
17 views
Archiving data on partitioned tables with non-aligned index
Scenario:
Managing a production OLTP database that has a rapidly expanding (in size and row count) partitioned table.
In order to manage this growth I'm looking to archive older data to a separate ...
0
votes
0answers
23 views
Partition table - Zabbix (Postgres)
I want to partition some Zabbix tables, namely History_and History_uint, but it has several million records and hundreds of GB. How can I pass the data from the original tables to the partitioned ones ...
2
votes
1answer
61 views
Is database partitioning only created to get a better performance? and why should one partition a table, and a database? [closed]
I was asked if SQL Server can handle huge databases ( for them a huge database is 100GB). I know it can. I worked with 4tB databases already. But I would like to ask, thinking about really huge ...
1
vote
1answer
55 views
Index rebuild at partition level on a table
We have functionality in SQL Server to rebuild indexes at partition level using below command
ALTER INDEX IX_name ON table REBUILD PARTITION=68
In non-partitioned table we disable the indexes, load ...
6
votes
1answer
172 views
Rebuilding indexes for a partitioned table having 300 partitions
Scenario
Partitioned table is empty and I am loading data for 1 partition which has 180k rows. I disabled the indexes and loaded the data and rebuilt the indexes after data is loaded.
Issue
While ...
1
vote
2answers
24 views
How to identify the ranges over which a postgres table was partitioned?
If I create a table and partitions like this...
CREATE TABLE tab1 (a int, b int) PARTITION BY RANGE(a);
CREATE TABLE tab1_p1 PARTITION OF tab1 FOR VALUES FROM (0) TO (100);
CREATE TABLE tab1_p2 ...
1
vote
2answers
56 views
SQL To Fetch Partition Boundaries and Filegroups
For a given partition scheme name, I need to retrieve the set of
Boundary Value
FileGroup Name
This must work without the existence of any tables participating in the scheme.
i.e. joins via sys....
0
votes
1answer
29 views
Declarative partitioning with Postgres for integer column
I was reading this article:
https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1
the article lifts information directly from here:
https://www.postgresql.org/docs/...
2
votes
0answers
26 views
missing ibd files
I'm running mysql 5.6. For reasons entirely my own fault, I've got some missing ibd that make up partitions for several tables.
When I try to mysql I get the following messages for the missing ...
0
votes
1answer
34 views
Switching out of one scheme into another
I have a table named dbo.messages. We are expecting a lot of data in this table. To keep things to a reasonable level we are looking at a weekly partitioning on that table. What I would like to do ...
0
votes
1answer
46 views
Optimizing SQL query with two table joins and over 40 million rows in partitioned table
I have three tables, lets say their names are:
table1 (has total over 40 million rows, but is partitioned with Declarative partition https://www.postgresql.org/docs/10/static/ddl-partitioning.html ...
4
votes
1answer
84 views
How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?
I have a data warehouse comprised of four clustered columnstore index tables (CCI) and nine rowstore tables. These tables are used only for analytics and the CCI data is inserted from staging tables ...
1
vote
1answer
35 views
Select data with the latest table partition that actually holds data
Version: SQL Server 2016
So, I need to be able to select the data within the most recent table partition. I can do this by using the below script but the latest partition doesn't contain any data. ...
0
votes
0answers
21 views
Sharding at SQL Server level [duplicate]
I would like to get some advice about how to implement sharding at SQL Server Level (on-premise), basically, the architecture is something as shown below:
Stand Alone.
Writer: SSIS, Linked Servers
...
0
votes
1answer
45 views
SQL server - Re. Filegroup / partitioning
Assuming VLDB is split into multiple file groups, each having its own file and on a separate disk.
Now let's say there is a disk crash bringing down one of the file groups - then will the other parts ...
0
votes
1answer
31 views
Counting using window function with partition over an empty table
If i have an empty table (created but without rows in it), and i would like to get the number 0 from the below query, how i could achieve that?
select count(*) over (partition by field_name)
from ...
0
votes
1answer
54 views
Select unique records from multiple records based on max date
I have table like below
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct ...
1
vote
0answers
45 views
Handling backups of a large table (>1 TB) in Postgres?
I have a 1TB table (X) that is a pain to backup.
The table X contains historical log data that is not often updated after creation. We usually only access a single row at a time, so performance is ...
1
vote
1answer
70 views
How do I add a partition to an existing table in mariadb / mysql?
I have the following table.
I want to add partitions too.
CREATE TABLE `app_log_Test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`dateCreated` datetime NOT NULL,
`host` varchar(512) COLLATE ...
1
vote
0answers
42 views
MySQL 5.7 issue with 16TB disk
(Repost from StackOverflow)
I have a MySQL 5.7 running on a Windows Server 2016 VM with two disks from a NAS: C:\ for system and D:\ for data (empty at start). I try to create a partitioned table:
...
2
votes
1answer
48 views
Question on best practice - keep empty partitions at both ends of your table?
I was reading ALTER PARTITION FUNCTION best practices which says -
Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) ...
0
votes
1answer
17 views
Create Range Partition on existing large MySQL table
I have inherited a database that contains a large table (relative to the rest of the database - 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 ...
0
votes
1answer
34 views
Internal error code, kksgaItLvl_Setup [closed]
A simple query fails with the error. Has anyone faced this issue or has any solutions?
SELECT MAX(some_value)
FROM some_table
WHERE some_id = 62328188199
AND some_name = '...
1
vote
1answer
42 views
Log shipping from SQL Server 2014 Enterprise db to SQL Server 2017 Standard with partitioning
I wish to implement log shipping from my current primary which is a SQL Server 2014 Enterprise database to a SQL Server 2017 Standard edition database. I also wish to implement table partitioning on ...
0
votes
0answers
19 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 ...
0
votes
0answers
82 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 ...
0
votes
3answers
54 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
40 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
12 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
53 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
33 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,...
3
votes
0answers
39 views
Variable column size overflow when merging partition [closed]
Supporting a product that out of the box does ETL to an OLTP DB every night for that day's activity, and every day is a new partition. Well, over x number of years this gets kind of messy. So in the ...
