Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [partitioning]

Splitting a database table into multiple segments for performance or manageability.

Filter by
Sorted by
Tagged with
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 ...
user avatar
  • 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 (...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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....
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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' ...
user avatar
  • 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 ...
user avatar
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 (...
user avatar
  • 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 ...
user avatar
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 (...
user avatar
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. ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
-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. ...
user avatar
  • 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....
user avatar
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 ...
user avatar
-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 ...
user avatar
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) ( ...
user avatar
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 [...
user avatar
  • 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 ...
user avatar
  • 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 )...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
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" ...
user avatar
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(...
user avatar
  • 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,...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar

1
2 3 4 5
19