Questions tagged [partitioning]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
4 views

Fast long-to-wide reshaping in PostgreSQL

I am trying to change a really long table (1955451890 rows -- 278 Gb on the DB) to a wide format using crosstab from the tablefunc extension. Following several examples from SO (i.e. Crosstab with ...
0
votes
1answer
161 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
582 views

How do partitions handle new data?

I have a database with nearly 100M rows of stock data. Pulling the data for the last 7 days to generate a chart takes a couple seconds. My thought is to create partitions that represent the normal ...
1
vote
1answer
505 views

Any workarounds for lack of primary key on partitioned tables?

I am currently looking at implementing declarative partitioning with PostgreSQL 10. However, one sticking point is that we need to have a foreign key between two such partitioned tables, which is not ...
2
votes
2answers
91 views

Truncate partitioned table with values from partitioned column?

I have partitioned a table on a column with varchar datatype. The documentation on truncate table with partitioning uses the partitionids: Truncate table parttable with (partitions (1,2,5)) I only ...
1
vote
2answers
113 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
1answer
32 views

Check partition function if list of value exists, if not iterate through list and add new partition value to the partition function?

I have an azure sql db with a partitioned table with a partition function on varchar datatype. In an Etl process I will get a list of partiton values as a parameter that i will the need to: check ...
0
votes
3answers
817 views

Switching a partition on a table with one to many relation

There is two main tables in database (SQL Server 2014) table1: ---------------------------------------------- id name datetime image table2: -------------------------------- ...
0
votes
1answer
356 views

Get partition column for a table in Greenplum

How can I find what the partitioning scheme is for a table? Is it in information_schema or in the pg_class related tables?
3
votes
2answers
1k views

Difference between clustered index and partitioning column

As I understand partitioning, the column that you partition on is how things are divided into partitions. But does the partitioning column have anything to do with order on the disk? (Aside from ...
0
votes
0answers
17 views

Postgres partition search in reverse order of partitions

Our production system is on postgres 9, with a partition table. In one of the select queries, we cannot pass the partition key for the select, but we want the data from the latest partition, is it ...
0
votes
2answers
282 views

Does mysql horizontal partitioning create new ibd files for each partition?

To my best knowledge, each table created in mysql creates an ibd file as a pointer to your table. Any alteration made to this table happens on this ibd file. Now if Table1 of size 1GB, with its ibd ...
0
votes
1answer
18 views

Table Partition Function Empty Partitions

I plan to partition my table monthly along a date column, and have read at few places, that it is recommended (in fact a best practice) to keep empty partitions at both "end" of the range. https://...
0
votes
2answers
126 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 ...
-1
votes
1answer
20 views

Postgresql: LIKE and partition by fields of the initial table

I have an already existing table which I want to partition, looking at the documentation I see that this cannot happen, so I re-created the table and try to partition that: CREATE TABLE tableB (LIKE ...
1
vote
1answer
90 views

Partitioning in PostgreSQL 10

Currently we are using pg_partman for partitioning our tables in PostgreSQL 9.4. We are planning on upgrading to PostgreSQL 10. I read that PostgreSQL 10 has better support for partitioning. While I ...
0
votes
1answer
328 views

MySQL - Move data between partitions aka re-partition

I have a mysql table whose partitions look as below p2015h1 - Contains data where date < 2015-07-01 (Has data from 2016-06-01. Hence only month worth of data) p2015h2 - Contains data where date &...
0
votes
1answer
21 views

Insert into partitioned tables with select, handle conflicts

I need to insert into partitioned tables using a select, how do I handle conflicts? Postgres complains this: [0A000] ERROR: ON CONFLICT clause is not supported with partitioned tables My query looks ...
0
votes
1answer
118 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
249 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
21 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
68 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 ...
1
vote
1answer
493 views

drop table causes ORA-08103 with another one

In my process I have 2 tables. target table, load table. The target table holds the current data, and a lot of queries are fired against it. When new data coming we use a FULL LOAD and the goal ...
0
votes
1answer
484 views

When range partitioning, should I drop primary UUID-like key?

I'm aware of the limitation "every unique key on the table must use every column in the table's partitioning expression". So given that my primary key is a UUID, it's going to require a change. ...
0
votes
3answers
56 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
2
votes
1answer
1k views

Table partition in aws MYSQL RDS master slave setup

we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this ...
7
votes
4answers
527 views

Multiple files per partition/filegroup?

I am designing a partitioning based solution for a high performance large SQL Server 2016 database. Some of the data will be coming in with hundreds of millions of records per day. During the day we ...
0
votes
1answer
46 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
1answer
1k views

USER_TAB_PARTITIONS Query Runs Incredibly Slow

I seem to be seeing incredibly slow (5-10 seconds) to run a query against USER_TAB_PARTITIONS (Oracle) intermittently. When I do an explain plan on : SELECT * FROM USER_TAB_PARTITIONS WHERE ...
0
votes
1answer
67 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), -- ...
1
vote
1answer
438 views

MariaDB: Querying a timestamp partition

I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
0
votes
1answer
25 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 (...
1
vote
1answer
998 views

What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on. We are using partitioning for this and ...
5
votes
1answer
494 views

MySQL Partitions with Partition limit

I have a partitioned table on the basis of the Primary key having 1.5 Billion rows in it. But when I check the .ibd files only 80 ibd show in GB's whereas 120ibd shows 1MB file. I would like to know ...
0
votes
1answer
210 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. ...
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
20 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
36 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
vote
2answers
302 views

Mysql Range partition

I have the next table structure: CREATE TABLE `sat_msgPos` ( `idOwnApp` mediumint(11) NOT NULL DEFAULT '0', `idMsg` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `idVeh` mediumint(11) NOT NULL ...
1
vote
2answers
47 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 ...
-1
votes
1answer
78 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 ...
0
votes
0answers
37 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 ...
6
votes
2answers
1k views

Round robin vs Demand Parallelism partitioning

This is related to having a common key driver on top of the query with a nested loops, and the parallelism for the rows coming out of driver being either demand type or round robin type. I would have ...
0
votes
1answer
509 views

Create Range Partition on existing large MySQL table

I have inherited a database that contains a large table (relative to the rest of the database - 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 ...
0
votes
1answer
168 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 ...
1
vote
1answer
61 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 ...
3
votes
1answer
106 views

SQL Server 2012 - How to Remove Partition on Publisher but not Subscriber?

Is it possible to setup partitioning on a SQL Server 2012 publisher table, so that older data can be removed without affecting the same table on a subscriber table? Table exists on Database A (SQL ...
0
votes
1answer
84 views

Unable to view partition table in a schema

I am using oracle 11g enterprise edition, I have created a schema as "TESTSCHEMA" and in it i have created a partition table as "sales_test". The query for the table, create table sales_test (...
0
votes
1answer
141 views

Partitioned mariadb table files(the .ibd ones) placed outside of data directory return back to data directory itself after altering the table in use

While Partitioning large tables in Mariadb we want some partitions to be placed in another storage device with different speed and capacity. That is our requirement. For that we are trying to perform ...

1 2 3 4 5 16