Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
788
questions
0
votes
1answer
17 views
Table Partitioning Using Inheritance and Triggers
I am trying to do table partitioning in PostgreSQL using inheritance and triggers but the problem that have occured to me is that the data does not move from the parent table to the children tables I ...
2
votes
2answers
58 views
Handling Big Data
I have a MySQL database that will have 2000 new rows inserted / second. These rows indicate the value of a sensor at that particular moment.
The plan is to get this data into graphical ...
0
votes
3answers
55 views
Indexing only for most recent entries in PostgreSQL
Consider the following table:
|-----------------------------------------------------|
| raffle |
|----|---------|----------|-----|---------------------|
| ...
-1
votes
0answers
21 views
How to specify partitions in range partitioning by multi column while creation?
I am using MySQL Server, PostgreSQL and PostgreSQL XL in three different systems for my tests
I am using multi column partitioning, and I want to specify which node my partition goes to.
How to ...
0
votes
1answer
56 views
Syntax Error in Create Table Statement
I'm using Oracle 12c, I want to create a table with partition by year and subpartition by month like this.
create table par.Log_his
(
ID NUMBER(20) not null,
Year number(4,0), -- ...
0
votes
1answer
24 views
Can I grant access to a MySQL user for a single partition of a table?
Is there a way, in MySQL, to grant access to a single partition of a table to a specific user? I see no way in the doc for that, but maybe I'm missing it...
Here's a test case:
CREATE TABLE testing (...
0
votes
1answer
27 views
Partition table behavior for Postgres
I have created a partitioned table with 1 partition. below is the script for the same:
CREATE TABLE public.ms (
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
a int8 NOT NULL,
b int4 NOT NULL,
c ...
0
votes
0answers
13 views
Trigger on update of any columns to copy the update to another able
I am partitioning an existing table in production called app_users, to do this, I have created a partition table app_users_partition with it's partitions along with a trigger in app_users which copies ...
1
vote
0answers
19 views
SQL Server Partition. How would splitting range with Guid work?
I have a very large table in my database, and it has a GUID column called Group_Id. This Group_Id has a pretty even split of data, and it would be beneficial to partition the table by this column.
...
0
votes
0answers
34 views
Oracle - partition table by 3 columns
I have, in Oracle database, fact table based upon which reports are generated. Table contains sales data - large volume.
Users generating reports are usually using grouping by (sale_day, customer, ...)...
-1
votes
1answer
56 views
Postgres 12 Scalability using table Partitioning and Foreign Data Wrappers
I have looked through the archives and I cant find any discussions around the following topic.
I have a fairly in depth question that I would appreciate some guidance with.
Current environment
...
1
vote
2answers
46 views
Index on a partitioned key
we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the ...
0
votes
0answers
34 views
Range vs List partitioning for YYYYMM year month field Oracle 12c
I have a table which holds monthly data differentiated by a column year month (YYYYMM, eg 201911).
We have been planning to partition the table by month however we are unable to decide between Range ...
0
votes
1answer
18 views
postgres partition data record assignment
It is possible in PostgreSQL 11 to display the partition table for each row in the table? Well, it's clear that the assignment of partitions is always known by the declarative definition but is there ...
1
vote
1answer
51 views
Triggers on partitioned tables on Postgres 11.5
I asked a question about History table design for deletions in PG 11.5, and received a suggestion to partition the table. This is an excellent idea as the table may become huge, and the information ...
2
votes
1answer
69 views
Increasing join performance on very large table
I have the following query in Postgres database
SELECT
(rel."external-institute" || '#' || rel."external-institute-code")::varchar(50)
FROM public."td_documents" d
JOIN ...
-1
votes
2answers
116 views
Mysql Partitioning Query
i have created partitions on pricing table. below is the alter statement.
ALTER TABLE `price_tbl`
PARTITION BY HASH(man_code)
PARTITIONS 87;
one partition consists of 435510 records. total records ...
6
votes
2answers
133 views
Change collation on partition column (SQL Server 2017)
I'm trying to change collation on column:
ALTER TABLE [dbo].[yourdad] ALTER COLUMN [yourmam] varchar(36) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL;
and it gives message like this:
Msg 5074, ...
0
votes
0answers
42 views
Postgres - Partition pruning and query performance
I created a fact table with range partition on a date column and when a SQL statement is executed like this:
Select *
from my_table
Where dt_col between to_date('01/01/2017','mm/dd/yyyy')
...
0
votes
1answer
86 views
Mariadb/MyISAM: Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an
We have a db with around 150 tables in, of which about 30 or so are partitioned as such :
ALTER TABLE `myTable`
REORGANIZE PARTITION PFUTURE INTO (PARTITION P201910 VALUES LESS THAN ('2019-11-01'),
...
0
votes
1answer
24 views
MySQL - InnoDB - Partitioning a table with uniqueness
Following a previous post I posted: date-based-partitioning
This is my solution for partitioning a table with primary key.
I wanted to add partitioning by months and delete old record.
CREATE TABLE ...
0
votes
1answer
18 views
Why does this partitioning helper view give an 'Input string was not in a correct format' error?
A database I'm investigating has defined in it a view admin.View_PartitionRanges, which is heavily based on the view PartitionRanges to be found in this MSDN blogpost from 2009. The view definition ...
0
votes
0answers
33 views
Rebuild clustered index online on specific partition
Can I rebuild clustered index ONLINE (MS SQL 2017 Developer Edition) for specific partition which is on partitioned table with different filegroups? Selected partition is on read_write filegroup, but ...
1
vote
0answers
32 views
MSSQL - Lock escalation on partitioned indexed view
I'm using partitioned tables where I set LOCK_ESCALATION to AUTO to prevent lock escalation to table level, but keep it on partition level.
On the top of that table I have indexed view which is ...
1
vote
0answers
40 views
Changing clustered key on a big table
I have a big fact table in a datawarehouse (40 Gb worth of data in dev, 120gb in production ) which was created in a very classic way
CREATE TABLE BigFactTable
( BigFactTableId bigint IDENTITY ...
1
vote
1answer
138 views
Postgres 11 declarative partitioning - using large number of partitions
We are running a system using Postgres 11 and we implemented partitioning with ID which is most convenient to us but which produces many partitions (currently over 1000 with expected total of 10000 ...
0
votes
0answers
18 views
How to rename parent table and child tables in partition tables postgres 9.6
I am trying to rename parent table in partitions. I created 45 child tables on monthly wise manner. I can alter the name of parent table but I don't know how to alter the 45 child table references.
...
0
votes
0answers
23 views
Unable create partition by trigger
1) I have table
CREATE TABLE tor
(
events_date timestamp without time zone NOT NULL,
message text
CONSTRAINT tor_pkey PRIMARY KEY (events_date, message)
)PARTITION BY RANGE (events_date);
2) ...
-4
votes
1answer
70 views
Monthly to Daily Partitions in Oracle
I have a table with monthly partitions on it. I was asked to change those monthly partitions to daily partitions. For that, can I do even if the data exists ? What prerequisites do I need to take care....
-1
votes
1answer
136 views
Query performance question
Using Demo from here to reproduce my issue with changes in table structure as below and modifying demo partition function to datetime
CREATE TABLE [dbo].[DemoPartitionedTable](
[DemoID] [int] ...
0
votes
1answer
22 views
Postgres - how to investigate a dependency before DROPing a field
I want to drop a field from a partitioned table, POSTGRES claims there are dependencies and proposes using CASCADE. How can I check that nothing important will be deleted before risking DROP with ...
1
vote
1answer
34 views
How to find out if an index is aligned
I'm struggling to find any confirmation within sql server or sql management studio that my index is in fact aligned with my partition.
I'm creating a primary key constraint on a partitioned table:
...
0
votes
1answer
75 views
Understanding partition elimination
I am trying to understand the below query being run from our developers on read only database which just runs forever.
Table here is partitioned table on partition column trickey per day.
Query is ...
1
vote
1answer
95 views
Cannot choose Storage option on a table in SSMS for standard edition
I am looking into partitioning of tables for SQL Server on two different machines. One machine runs Microsoft SQL Server (RTM) 2016 Developer Edition, and the other is running Microsoft SQL Server ...
0
votes
1answer
94 views
Changing Column Data Type with FKs
I want to change my column data type from "Datetime" to "Datetime2(7)". If I try to do it with the GUI SQL Server 2014 asks to save all FKs table dependencies and then I get a lock timeout because ...
1
vote
1answer
99 views
syntax error at or near “current_timestamp” while creating partition table
I am trying to create a partition for the last 30 days. Yet when I run this code:
create table Test_Older30day partition of Test
for values from (current_timestamp + interval'-30 day') to (...
0
votes
0answers
53 views
Archiving a huge SQL Server table with change data capture and foreign key constraints on multiple columns
We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints.
We decided on the archival since the ...
0
votes
1answer
146 views
Deadlock on partitioned table
we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing.
Our setup is like this: ...
0
votes
0answers
65 views
How Insert data to child table in different schema using trigger function and auto updates for these child tables
I am trying to insert data to the child tables from different schema i.e parent table and trigger function were in schema 1 and child tables were in schema 2. It throws me an error while I am doing ...
2
votes
2answers
45 views
composite clustered index with partitioning
I have a table with hundreds of millions of rows. Data is regularly retrieved via various queries and inserted.
There is a clustered index on
LookupID (int)
TypeID (int)
Created (datetime)
(These ...
4
votes
1answer
181 views
SQL Server query scanning more partitions than expected
So my team has the following select statement in one of our stored procedures
SELECT
ai.Name
,dc.Component
,SUM(dc.Value) Value
FROM
Warm.DailyCosts dc
JOIN Warm.AccountInfo ai
...
0
votes
1answer
155 views
Postgresql - how to list indexes on a table that is partitioned?
This command works for non-partitioned tables:
select * from pg_indexes
But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition.
...
1
vote
1answer
89 views
Unable to alter partition table to set logged
I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command.
Afterwards, I ran an alter table set logged ...
0
votes
0answers
52 views
Partitions for table that hold only ids in PostgreSQL 9.6
I am working in query tuning and decided to go for partitioning my tables. There are 3 tables in the base query and only one table makes the query slower. I tried to create partitions but that table ...
0
votes
2answers
196 views
How to hide the partition child tables in PostgreSQL?
I am creating partition tables in postgreSQL. And the partition is on date column so its nearly high in number to show in schema. I want to show only my parent table for other users.
I tried many ...
10
votes
2answers
325 views
How to Prevent Partitioned Columnstore Deadlocks on SELECT
I have three Clustered Columnstore Index (CCI) tables in SQL Server 2016. All of these CCIs are in the same partitioning scheme, based on Tenant ID. Lately, and inconsistently, I am getting deadlocks ...
1
vote
1answer
76 views
Performance Recommendations for Large Volume of Data
Oracle Table:
Fields: 55
Roughly 10 are Used for Querying
Single Record Length: 512 bytes
Records: 3-5 million records per day
Period of Time: 13 months to be available, > 13 months to be purged ...
1
vote
3answers
47 views
How to remove files and filesgroup from dis that have data in it?
I have created files and filegroup under partitioning process. These secondary files have data in them. I want to remove these files as I have deleted the table from the database but I am finding it ...
0
votes
1answer
17 views
DynamoDB: How to model data that's shared and queried by all users?
I am very intrigued by DynamoDB, and it works incredibly well when I model the data for my main use case for my application. That being said, there is one specific use case that I can't wrap my head ...
0
votes
1answer
53 views
Table Partitioning and Performance
I have to migrate one large table and need to do partitioning of the same.
I would like to know whether i do Partitioning first and migrate the data second or I do migrate the data first and ...
