Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
1
vote
1answer
25 views
Table partitioning appropriate for large app log table (insert heavy)?
I have a log table which receives logs from multiple applications, several inserts per second, approaching ~1M rows per day. The table has a dozen columns (mostly nvarchar), e.g. severity, app name ...
0
votes
1answer
41 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
28 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
27 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
0answers
6 views
Checking Raid configuration works [on hold]
I'm setting up Raid 10 with 4 disks (sdb1,sdc1,sdd1,sde1) configuration into centos 10 and I have created a file to test when creating on it it will use the raid technology . How I can verify if it ...
0
votes
1answer
24 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
27 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
16 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
0answers
22 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
36 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
...
0
votes
0answers
43 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
51 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 ...
1
vote
2answers
46 views
What is the impact on the Cost-Based Optimizer (CBO) when implementing ISCSI SAN solid-state drives
A Microsoft Technet article recommends creating a secondary file group as the default file group (see reference below). The secondary file group should have a number of files, say four, that are each ...
3
votes
2answers
140 views
Table partition existing table where partition key is not part of the primary key
Main objective: Add partitioning to table to make deletion of old orders non-blocking/quicker (and also understand partitioning)
I have an existing table Order, like this:
CREATE TABLE Order (
...
2
votes
1answer
20 views
Is it possible to add a spatial index to a partition with Postgres 10?
I am creating declarative partitions like:
CREATE TABLE mydata.my_features (
id bigint,
my_feature1 character(1),
my_feature2 integer,
...
my_geom geometry
) PARTITION BY ...
0
votes
1answer
29 views
Questions on database table and index partitioning in SQL Server
I have developed Transact-SQL script to partition the table. To apply the partition, I am creating one non-clustered index for my partition key column and associating it to partition scheme. I have ...
6
votes
2answers
319 views
Datetime conversion fails when filtered index is placed on partition column
I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.
The index's filter ...
0
votes
0answers
22 views
How to effectively, and efficiently and the simplest way to partition and move data from one db to another?
We have a large database with client data, with many entities, but we dont allow connections to the production db (mysql) due to security. Only option remaining is to move some data to development db (...
0
votes
1answer
39 views
Oracle partitioning scenario with primary key absolutely required
Is there any partitioning scenario in which the partitioned table is absolutely required to have primary key?
0
votes
1answer
28 views
Would querying from a partitioned table be faster than from multiple tables with the same table definition?
I have a table that contains about 50 million rows that I have partitioned by 5 million rows per partition. I have manually partitioned it into Table_1, Table_2, Table_3, etc., not using MySql's built-...
1
vote
1answer
30 views
Suboptimal query plan when updating partitioned table
Background
I have a simple CTE used to update a declaratively-partitioned table.
The subquery runs quickly (1.7sec per EXPLAIN ANALYZE) and returns 3,769 records (CTE y in the query below).
The ...
0
votes
1answer
49 views
SQL Server Partition Switch
I have a partitioned table in SQL server 2017.
I want to switch partition number 2 of this table the the staging table which is created using the same partition scheme as the original table.
I'm ...
1
vote
0answers
51 views
Problem with partitioning non-clustered indexes
I have a database with partition function defined. Over this partition function there are 2 schemes defined, one for Data files and one for Indexes files.
Data and index files are divided quaterly (...
0
votes
1answer
11 views
Oracle 11g, Will droping partition reuse free space on bigfile tablespace
I have four range partitioned tables which belongs to bigfile tablespace. Also local indexes on that tables belongs to other bigfile tablespace.
My partitioning range is one day and I have stored ...
0
votes
0answers
17 views
Adding constraints to child tables in pg_partman
I'm trying to build a logging table and using pg_partman partitioning it on a daily level. This part is setup.
The issue is I need a unique constraint on the fk_id and timestamp. The process to fill ...
0
votes
0answers
36 views
Postgres 10 partition pruning through Extended Query protocol
From what I can tell, in postgres 10, parition pruning through declaritive partitioning only works if the partition key value is included in the query explicitly
SELECT *
FROM partitioned_table
WHERE ...
2
votes
1answer
52 views
How can I get the last items by date desc then by id desc?
I have a employee course table which holds the current record for each of my employees who have some courses that they have taken. Then I have a history that keeps a static version of the save data ...
0
votes
0answers
28 views
Where each partition has only one value in the constraint column, can I avoid filtering?
I have partitioned a table in postgres 9.6 by date. Every date within a parition is identical.
When I select data, a relatively expensive Seq Scan filter operation happens against every row in the ...
0
votes
1answer
40 views
sliding window partitioning on date field
I need to apply Sliding Window Partitioning on a table that contains 1B of records.
CREATE TABLE table_1(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[log_date] datetime NULL,
[field1] [bigint] ...
0
votes
0answers
31 views
Partitioning Postgres table that have references to and queried without date range filters
As I understand partitioning is useful only for fact tables that isn't referenced by other tables and when you issue range select queries in case if you also have retention policy.
Spring Batch ...
2
votes
1answer
35 views
Network Partitioning
My current understanding of the network-partitioning scenario, also mentioned in the CAP theorem, is that it's a scenario where the clients are not able to connect to some DB nodes even though they ...
7
votes
2answers
127 views
Wrong estimate on a query on partitioned tables
I wonder why SQL Server makes wrong estimations in such a simple case. There is a scenario.
CREATE PARTITION FUNCTION PF_Test (int) AS RANGE RIGHT
FOR VALUES (20140801, 20140802, 20140803)
CREATE ...
0
votes
1answer
18 views
mysql 5.5 repartition
With MySQL 5.5, I want to change the partitioning scheme of a very large InnoDB table from "hash" to "range". Can I do it while the table is accepting DML? If not, what is the best-practice work-...
0
votes
0answers
35 views
how to backup, remove and then restore a filegroup of a partitioned table
Continuation of the question "Archiving SQL Server data on disk to be pottentially restored".
I'm still looking for a way to archive data per month which can easily be restored...
I'm in the ...
0
votes
2answers
39 views
Is there a way to automatically update a view based on daily created tables?
Let's say I have a table that is created daily:
20181227
20181226
20181225
...
There is view that is like:
CREATE OR REPLACE VIEW someview
AS
SELECT * FROM 20181227
UNION
SELECT * FROM 20181226
...
4
votes
1answer
156 views
PostgreSQL How to DEFAULT Partitioned Identity Column?
PostgreSQL 11
What is the best way to generate default values for identity columns on partition tables.
E.g
CREATE TABLE data.log
(
id BIGINT GENERATED ALWAYS AS IDENTITY
...
0
votes
0answers
18 views
Mariadb / mysql table partitionning column usage
I read mariadb documention :https://mariadb.com/kb/en/library/partition-maintenance/
And it sayed :
The partition field should not be the field first in any key
But in mysql documentation :https:...
2
votes
1answer
73 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,
...
1
vote
1answer
32 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
96 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 ...
8
votes
1answer
2k 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
29 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
193 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
90 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
241 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
2answers
42 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
18 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
45 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
30 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
80 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 ...
