Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
750
questions
0
votes
0answers
17 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
34 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
82 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
...
1
vote
1answer
57 views
Quasi-Lateral Partitioning [on hold]
I have a table that stores generic data for a wide variety of purposes that are almost totally independent of one another (created by different users for example). So I'm thinking about adding an ...
0
votes
1answer
15 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
24 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
15 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
42 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 ...
9
votes
2answers
167 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
33 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
28 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
0answers
10 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
37 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 ...
0
votes
0answers
14 views
How to switch MBR to GPT without losing any existed data originally in MBR, AWS Ubuntu EC2
I'm trying to extend my diskspace for my AWS EC2 Ubuntu instance, the partition I've started with is MBR and the maximum it can go is 2TB.
Currently the diskspace is full with important data, I need ...
0
votes
1answer
22 views
Is there a way to search the earliest or latest postgres partition for table?
BACKGROUND
I am working on a web application that has date pickers for searching. Those searches query a partitioned table. So far, so good.
However, based on the data I'm querying, we don't have a ...
0
votes
0answers
17 views
Global nonpartitioned index vs local partitioned on for a partitioned table
We have a select query which is really slow. We are using views in the from clause.
Global nonpartitioned index is used for accessing the rows of a partitioned table.
My doubt is that if Global index ...
1
vote
1answer
20 views
Table not analyzed after partition has been split
On my table I split a subpartition successfully. After that I rebuild the indexes and gather statistics.
ALTER TABLE T_MO_DPI SPLIT SUBPARTITION ACTIVE_RELATIONS VALUES (24) INTO (
SUBPARTITION ...
3
votes
0answers
52 views
SQL Server database partitioning purge job failed with an error
We have a SQL Server Agent job that executes package to purge partition file and filegroup older than 25 month. The job was running fine however it failed this month with following error:
The ...
1
vote
1answer
35 views
Understanding the recreation of index in Table partitioning
I need your help. I have[AdventureWorks2014] database and I wanted to perform partitioning on [Production].[TransactionHistoryArchive] table based on [TransactionDate] column. [Production].[...
0
votes
1answer
15 views
Can I use SELECT statement for values in a list partition? Or do I need to explicitly state them?
So I have a table I would like to partition, and in creating the partitions I am trying to run a statement like this:
partition by list(apple_type)
create table foo.apples_green
partition of foo....
0
votes
0answers
30 views
Do Hive Materialized Views respond to added partitions to source tables?
Hive Materialized Views support partitions,
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
[DISABLE REWRITE]
[COMMENT materialized_view_comment]
[PARTITIONED ON (...
-1
votes
1answer
30 views
Postgres: deadlock for CREATE PARTITION with INSERT
I have a partitioned table named logs with a lot of INSERT transactions.
Every day I run a cron job with a query for CREATE PARTITION on this logs table.
But it seems INSERT statements block the ...
0
votes
1answer
54 views
Truncate Table With Partitions - aligned/ non-aligned indexes
SQL Server 2016 introduced a modification to the TRUNCATE TABLE statement which allows you to truncate a specific partition or set of partitions from a table:
TRUNCATE TABLE < tablename > WITH (...
0
votes
1answer
55 views
Efficient partition pruning with ORDER BY on same column as PARTITION BY RANGE + LIMIT?
I've set up a table in MariaDB (10.4.5, currently RC) with InnoDB using partitioning by a column of which its value is incrementing-only and new data is always inserted at the end. For this case, ...
0
votes
0answers
26 views
Advantage and Disadvantage of Partitioning by some of values of Primary Key
I'm going to implement/deploy a table like this:
CREATE TABLE docref
(
namespace VARCHAR(50),
key VARCHAR(50),
time_order INT,
state SMALLINT,
hash TEXT,
...
3
votes
1answer
45 views
Slow performance on partitioned PostgreSQL table when using now() to filter
I have a PostgreSQL v10 table partitioned on a timestamp field:
CREATE TABLE front.probes (
bucket_start timestamp NOT NULL DEFAULT now(),
-- Extra fields here ...
) PARTITION BY RANGE (...
0
votes
1answer
36 views
Limits of table partitioning (in PostgreSQL)
I have the following use case (PostgreSQL, currently v9 but I'm planning to upgrade to the latest version).
I have a data warehouse application with a central table that has a sizeable number of ...
3
votes
1answer
51 views
Check if partition elimination works
I implemented partitions on one of the tables our DEV/TEST environment, and modified some of the procedures to use the partition key when accessing this table.
How can I verify that partition ...
0
votes
0answers
23 views
ORA-00600 When running ALTER modify PARTITION BY RANGE
I am running this on the table with 2 million records (2012378),
I get the below error.
when i do the same for less no.of rows (41000) there are no errors.
Error starting at line : 51 in command -
...
0
votes
1answer
25 views
Postgresql update a partitioned table, where update affects multiple partitions
If I have a partitioned table partitioned on PKey column, can I update (or merge etc) the table like below (or more complex jonining to another table) which will cause multiple partitions to be ...
6
votes
1answer
284 views
Slide Partition from Rowstore to Columnstore
I have a rowstore table which contains ~10 billion records. The table is partitioned on an INT field and has about 1,400 active partitions. I also have a clustered columnstore table built on the same ...
0
votes
2answers
37 views
What's the maximum number of partition per table I can/should have in MySql 8?
I have around 1T (1,000,000,000) records of products belong to 500,000 accounts. Each product record have the account_id and since I always query within a single account context (there are no cross-...
0
votes
1answer
25 views
PostgreSQL Trigger to override insert on table with inheritance
We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the ...
0
votes
1answer
149 views
How to alter an existing partitioned table to add sub-partitions to it?
This forum has helped me earlier on many occasions and now I need someone to help me with this.
I am looking to convert a pre-existing partitioned (range) table - to sub-partitioned (composite range-...
0
votes
1answer
21 views
If I move my SQLdata file from my F drive, delete the F drive, and rename the new Drive F will I have any file path problems?
I have a windows server that only had 50 GB on the C drive and 4TB on the F drive. I need to exstend the C drive but the only way to do that is by deleting the F drive to reallocate space. If I move ...
1
vote
1answer
57 views
Create a partition for a temporal table based on a period
To improve the performance of my ETL, I decided to use temporal table instead of a slowly changing dimension.
For my Employee dimension, I created a temporal table. Now I want to split data into ...
1
vote
0answers
71 views
PostgreSQL logical replication and partition table
We have a large database and we want a copy of the database in our company for faster access. we are going to achieve this copy by logical replication on PostgreSQL 11.
But we have a big problem.
We ...
0
votes
1answer
73 views
Partitioning with AUTO_INCREMENT primary key
I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). The table in question uses an AUTO_INCREMENT column as a primary key as this table ...
0
votes
1answer
109 views
Why does SQL Server table partitioning stop working? [closed]
For a number of reasons, but mostly the sheer volume of data, we have 5 MS-SQL (2017) sharded servers to break up the overall load on any individual server. Secondly we have a real-time table that is ...
-1
votes
1answer
71 views
Alternatives to MySQL Partitioning, so we can have a Unique Index
I have a DBA vs Developer Issue here, and am wondering if there are any alternatives to partitioning that I can employ here.
I have a table as follows:
CREATE TABLE `tracking` (
`Id` int(10) ...
0
votes
1answer
197 views
PostgreSQL 11: how to partition 1000 tenants
Setup
We have a multi-tenant app that has about 1000 customers more or less. When a customer churns we delete all their data after a period. We have a few tables that are pretty big and we're ...
0
votes
0answers
39 views
How to make a table invisible in PostgreSQL?
I'm using the declarative partitioning feature in PG10, so far so good. However, the partitions are also visible as separate tables and it's a bit visually distracting. For example:
Sales
is the ...
2
votes
1answer
43 views
What's the difference between partitions and many tables?
PostgresSQL 10 and 11 introduced partitioned tables. Quoted from the official site:
"Partitioning refers to splitting what is logically one large table
into smaller physical pieces"
And the ...
0
votes
1answer
29 views
How to PARTITION a table in mysql?
I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of
CREATE TABLE pages
(
page_id int(11) ...
1
vote
1answer
168 views
Index partitioned table to prevent Postgres from doing a sequential scan
I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries.
I have defined a partitioned database based on the first letter of the "identifier" - this is split into ...
0
votes
1answer
23 views
Is there a way to partition a mysql table, by range columns, where the 2 columns in question are of datetime and bigint?
I need to partition a table into 4 partitions, p0 to p3: with p0 having all rows with "id"=0, and p1 having all rows with "id"=1 and "doj" before february, and p2 having "id"=1 and "doj" before march, ...
3
votes
1answer
29 views
Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?
I have a big table (30 millions rows) defined like this:
CREATE TABLE `drawing` (
`id` int unsigned NOT NULL,
`artist` int unsigned NOT NULL,
`imageUrl` text NOT NULL,
`uploadDate` timestamp NOT ...
1
vote
2answers
45 views
Index options and applying partitioning to an index
I have following two questions:
I have a SQL Server index with the following options:
PAD_INDEX = OFF
STATISTICS_NORECOMPUTE = OFF
SORT_IN_TEMPDB = OFF
DROP_EXISTING = OFF
ONLINE = OFF
...
1
vote
0answers
61 views
Time Interval Partitioning in SQL Server
I have a table with time stamp (UTC) and for several reasons, like purging from this table (done at later point of ETL), I am trying to find out the feasibility of partitioning the table by 5 minutes ...
0
votes
0answers
168 views
Create a clustered primary key on partitioned table
I have a table that has been partitioned recently based on a datetime column. The same table had a primary key (clustered) on the ID column. During the partitioning of the table, the primary key is ...
