Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
950
questions
0
votes
0
answers
26
views
Is there any option to have change tracking partition table?
We have one partitioned table which has a lot of changes during week. This are stats for table:
table_rows
table_size_mb
ct_rows
ct_size_mb
175 857 125
18 762
2 610 849 197
467 859
Is there any ...
- 307
0
votes
0
answers
24
views
Foreign Key to Partitioned Table
I have the following basic schema for an ever growing huge transaction table. In order to query speedup & phase out stale data, I need to partition by time & country.
CREATE TABLE transaction (...
- 103
-1
votes
0
answers
48
views
Could not find an entry for table or index with partition ID in SQL
How to fix this error? Many links mentioned upgrading the SQL Server and I have already updated my SQL Server 2014 to SP3, but I'm still getting the error.
Also, I am not able to drop a table having ...
-1
votes
0
answers
31
views
Is a status column an effective column for partitioning?
We have a table that we would like to partition horizontally. Our goal for partitioning is to reduce maintenance time by only performing maintenance on the smaller set of newer data. Also, we would ...
- 11
0
votes
2
answers
20
views
Postgresql partitioning queries benefits
I am trying to apply partitioning process to some large tables on my Postgresql database, I have read the documentation and many articles about that and could not found an answer to this question.
If ...
- 1
1
vote
2
answers
51
views
Get postgres partitioned table structure by a select
I'm looking for some SIMPLE SQL-based command to show me the partitioned table structure in postgres.
Something like \d <table>.
All I find so far is by doing pgdump.
db=> \d ...
- 383
2
votes
1
answer
31
views
Postgres partition pruning with text prefix matching
I have a table where the natural identifier is a text like this: AB-C123456E-F, which is my primary key.
The table is approaching 200 million rows, and so to get better performance on the unique ...
- 21
0
votes
0
answers
16
views
Maximum number of partition function objects
What is the maximum number of partition scheme objects, and partition function objects?
Note, this is not the partition number per table (which is 15 000), but just how much such schema objects can be ...
- 266
0
votes
1
answer
112
views
Azure SQL - Hyperscale service tier - are table partitions supported
In the Azure SQL DB, running in the Hyperscale service tier, are table level partitions supported? It does not seem to be called out in the known limitations page.
While trying to create a filegroup ...
1
vote
1
answer
60
views
Not all check constraints used when ATTACH PARTITION in PostgreSQL
I have a table verbatim which is partitioned by an integer column dataset_key which also is part of a compound primary key:
\d+ verbatim_default
Partitioned table "public....
2
votes
1
answer
24
views
How to gauge performance of switching to PostgreSQL table partitioning?
I have a table in a PostgreSQL database containing about 100m records that's mostly read-only with a few monthly writes to update new data.
It has over 300 columns, and is heavily indexed for specific ...
- 1,038
0
votes
0
answers
267
views
Primary keys in Postgresql partitioned tables
I have a Rails app with a Postgres 11 database. There's a table (callees) that needs to be partitioned. Currently, it has an auto-increment column (id) as the primary key. The table is referenced from ...
- 101
0
votes
1
answer
37
views
Oracle - Big Table Archiving
Version: Oracle 12cR2 EE
The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2
Table size: 95GB
Table row count: 20M
Table partitioned? No
Indexes on table: there ...
- 29
0
votes
0
answers
18
views
Change the control column of pg_partman
I have a postgres table, and I am partitioning it with pg_partman extension. I am using column A for partitioning the table, but now, I want to change it to column B.
Does partman provides any utility ...
- 101
0
votes
1
answer
41
views
Deadlock on same table when creating partition in PostgreSQL
PostgreSQL 14.1. I have a partitioned table liked this:
create table dataset_session
(
dataset_id integer not null references dataset (id)
session_id integer not null,
external_id uuid not ...
- 165
0
votes
1
answer
114
views
PostgreSQL partitions consuming huge amounts of memory
I have a decent sized (~10M row) table that recently has started slowing down quite a bit. As a solution to this, we decided to partition this table into 1,000 tables, partitioned by a hash of the ...
- 103
0
votes
0
answers
59
views
Is there performance penalty for many partition ranges? [duplicate]
The following partition defines the monthly ranges for one year ahead:
CREATE PARTITION FUNCTION PF_Monthly(date)
AS RANGE RIGHT FOR VALUES (
'2022-01-01'
, '2022-02-01'
, '2022-03-01'
...
- 266
4
votes
1
answer
40
views
PostgreSQL – Can queries that require all partitions be blocked or rejected?
We are planning to partition a dataset of ~500million rows using list/range partitioning in PostgreSQL 13.4 on RDS. Our read access patterns should only require a very small number of partitions to be ...
- 141
0
votes
1
answer
53
views
Oracle date or timestamp PARTITION columns
Is there a query I can issue to tell if a PARTITION column is a date or timestamp.
For example this should retun timestamp
CREATE TABLE ts (
num_val NUMBER(6),
ts_val TIMESTAMP)
PARTITION BY RANGE (...
- 103
0
votes
1
answer
47
views
Table partitioning on an existing table and using different filegroups
I have an existing table on the primary filegroup, which I want to partition. The partition key is on year, which is a calculated column. I want to partition the table in such a way that in the end ...
- 445
1
vote
1
answer
164
views
How to utilize partition pruning with subqueries or joins?
I have a partition table...
CREATE TABLE erco.rtprices
(
scedtime timestamp with time zone NOT NULL,
node_id integer NOT NULL,
lmp numeric(12,6),
CONSTRAINT rtprices_pkey PRIMARY KEY (...
- 601
0
votes
2
answers
65
views
How to resolve INSERT contention in Azure SQL Database?
I'm working with an Azure SQL Database that has a very hot logging table. Any time a row in any other table is inserted, updated, or deleted, the web application writes a row to this logging table. ...
- 2,008
0
votes
1
answer
43
views
Partitioning strategy for table with two commonly used columns
So, I'm still kind of new to postgresql so sorry in advance if this might be a silly question.
I have this table (let's call it some_table) that I'm trying to partition in a logical way based on how ...
0
votes
0
answers
5
views
paritition exchange without validation mysql 8
I have a table in production facing about 1M inserts per day,partitioned on a date column.
This table is queried as well but the inserts are important for application.
Have a small script that runs ...
0
votes
1
answer
75
views
MSSQL Large table rolling using partitions
I have a large DB (6TB) consisting of 3 tables: “messages”, “messagesDetails” , “unprocessedMessages”.
Here are simple descriptions of them:
TABLE [messages](
[id] [bigint] IDENTITY(1,1) NOT ...
0
votes
2
answers
105
views
How to partition large 200M PostgreSQL table for analytical use
I have ~1.5TB of json data, 200 million records that I need to import into a PostgreSQL database and would like some advice on the recommended way to partition the table. The target schema is fairly ...
- 111
1
vote
2
answers
879
views
Create partitioned table from existing table
Given the following table:
create table widget (
id serial primary key,
description text,
created_at timestamptz not null default current_timestamp
);
I try to create a ...
- 113
0
votes
0
answers
40
views
Alter an existing partition in a table
The table definition of existing table is:
CREATE TABLE [TEST].[TEST1]
(
[ProductId] [nvarchar](256) NULL,
[Date] [date] NULL,
)
WITH
(
DISTRIBUTION = HASH ( [ProductId] ),
CLUSTERED ...
Sharad R. Telkar
0
votes
1
answer
41
views
help finding out the cause of this error in mysql partitioned table
I have a table wallet_transactions which grows very quickly. So, i have partition the table .
CREATE TABLE `wallet_transaction` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`code` bigint(20) DEFAULT ...
- 99
0
votes
2
answers
49
views
What is the best way in terms of non blocking table for creating NONCLUSTERED INDEX for partitioned tables?
We are using SQL Server 2016 and have 3 partitioned tables (every table around 300-500 milion of records). For every table would like to create NONCLUSTERED PARTITIONED INDEX. What is the best way to ...
- 45
0
votes
0
answers
45
views
Oracle - Is it possible to change the parallel degree only at partition or subpartition level?
Is it possible to change the parallel degree just in a specified partition or subpartition? I didn't find anything about it and I'd like to confirm it. Also, does this make sense?
For example, for the ...
- 508
0
votes
0
answers
46
views
Removing unused files and filegroups from partitioned table
I have a partitioned table that has about 50 old, empty partitions in it that need to be removed. Each partition has its own file and filegroup. I merged the partitions, then wrote the below Cursor ...
- 203
-2
votes
1
answer
95
views
What is the best way to order Partitioned table?
What is the most efficient way to get ordered data from a partitioned table?
I have this query:
SELECT TOP (4000)
*
FROM dbo.MS
ORDER BY MANDT
, MBLNR
, MJAHR
, ZEILE
Table is partitioned by MBLNR.
...
- 45
-2
votes
1
answer
283
views
How to create partition function on two columns like integer and datetime
Can a table be partitioned in two ways?
For example, both with date and with the unit
if unit=1 and date > 2021-09-01 save rows in file1.ndf
if unit=1 and date > 2021-10-01 save rows in file2....
2
votes
3
answers
188
views
Large database partitioning and backups of only updated data (partial backups)
I'm setting up a large SQL Server database (2-4tb), but only a small percentage of it will be updated. I'd like to partition by year so that my backups can be small. HOWEVER, some of the old data may ...
-1
votes
0
answers
10
views
Large database partitioning and backups of only updated data (partial backups) [duplicate]
I'm setting up a large database (2-4tb), but only a small percentage of it will be updated. I'd like to partition by year so that my backups can be small. HOWEVER, some of the old data may change on ...
0
votes
1
answer
26
views
Index not used in a partitioned Oracle table
I have a table partitioned by list
create table ICT_ENCOURS_BRUT
(
code_exercice VARCHAR2(4),
CODE_PRODUIT VARCHAR2(2),
.......
)
partition by list (CODE_EXERCICE)
(
...
0
votes
0
answers
44
views
When do parameters not exclude tables from a partitioned view
It seems that I have some instances where passing a parameter to a partitioned view on the partitioned column still queries all the tables. But now there's more to it...
I have a four table [...
- 123
1
vote
1
answer
38
views
Partition by hash on two levels
I'm trying to partition a large table by a generated ID(unfortunately it's a mongoDB, so the first few bytes are not evenly distributed)
I would like to shard it (using a foreign data wrapper) on this ...
- 111
0
votes
2
answers
98
views
What's the best way performance-wise to SELECT from a PARTITIONed table in MySQL: explicit partition selection, pruning with a WHERE clause, or both?
Say you have:
CREATE TABLE demo (
amount ,
year ,
cycle ,
otherStuff ,
PRIMARY KEY ( id , year , cycle )
) ENGINE = INNODB
PARTITION BY RANGE ( year )
SUBPARTITION BY KEY ( cycle )...
- 21
0
votes
1
answer
30
views
Downside of adding partitions for the future?
I'm considering moving some of our large log-table to range partitioning for ease of archiving them. They are rarely used for queries, other than support and related stuff. Say my oldest data is from ...
- 21.1k
0
votes
1
answer
42
views
Doubts about table partitions
I am studying table partitions and I still have some things that I am not clear about.
I created a partition function that organizes the data for the last three months.
CREATE PARTITION FUNCTION ...
- 7
0
votes
1
answer
97
views
How to index when partitioning for start and stop time columns in PostgreSql 12
I have a log table that takes inserts at a rate of 5k-10k rows per second and number of records grow upwards of 20 Million every 24 hrs. Here is the table structure:
CREATE TABLE IF NOT EXISTS public....
- 1
0
votes
2
answers
198
views
Postgresql 12 Custom Hash Function
I have a large table that I would like to partition. The field I want to partition against is a string that contains a coded value, but appears as an integer stored as a string. The "number" ...
0
votes
0
answers
62
views
A strange index that is always marked pg_index.indisvalid=false . Why indexes with ON ONLY option are always rebuilt in invalid state?
I'm looking in database (postresql 11.10) and see an index on a partitioned table that marked as invalid:
SELECT
indrelid::regclass, indexrelid::regclass, indisvalid, ddlx_create_index(...
- 203
1
vote
1
answer
91
views
Expdp partitioned table, Impdp Into non-partitioned table same structure
I have a partitioned table X in my production database.
I want to expdp it from production & impdp the same table X with data to my test database.
As I have a standard license in the test database,...
- 135
0
votes
1
answer
82
views
Postgresql: 'upserting' into two tables (vertical partitioning) using the same id with a unique constraint
I have two tables, one containing all the hot columns and one the static ones. The static table has an unique constraint. When the conflict on the unique constraint triggers only the hot columns in ...
- 3
1
vote
3
answers
280
views
Partitioning and existing Indexes
I have a table that consumes almost 1.2 TB of space and has lots of historical data which are mostly irrelevant. Since performing bulk DELETE is going to cause a lot of heavy-lifting, I'm trying to ...
- 113
4
votes
1
answer
308
views
Add partitioning to unpartitioned table with minimal downtime
We have tried the below approach on monthly basis.
The table size is around 2TB, 30 million rows, containing 50 columns so we started with renaming the old table and partitioning the new table in a ...
- 41
0
votes
1
answer
33
views
When should I Switch In from Stage Table to a SQL OLTP Partitioned Table
I have an OLTP Database with a big table (30M records a day / 400TPS). I want to implement Partitioning & Sliding Window on that table with Monthly Partitions plus a temp Stage-Table.
The time to ...
- 9