1,110 questions
3
votes
2
answers
197
views
Postgres move partition to archive table
I have a main prod table which I am converting to partitions and am taking a deep dive into Postgres (v14) partitioning on an isolated test system.
On the test system I have two schemas a production ...
- 141
0
votes
1
answer
103
views
Partitioned Table - Query filtering on partition field
I have a large table which I want to move to a partitioned model. I created the partitioned table, same fields as the original and partioning by a particular timestamp field (by range). I then ...
- 141
0
votes
1
answer
117
views
How to use references to partitioned table in PostgreSQL?
I research declarative partitioning and prepared such query for partitioned table:
create table _my_message
(
id uuid not null,
chat_id uuid constraint _my_message_chat_fkey references _chat,
...
...
- 2,010
0
votes
3
answers
98
views
Oracle Partition in For Loop Select
I need to fetch all rows in a specific partition of a table and print the values to a file.
CREATE OR REPLACE PROCEDURE
WriteRecordToFile
(
mypartition IN VARCHAR2,
myfilename IN VARCHAR2,
...
- 33
0
votes
0
answers
32
views
Jakarta @Version sql update version error
All entity with @version
During saving, have next error
could not execute statement [Not found 3 param.] [update cons_data.e_fin_form set version=? where id=? and report_type=? and version=?]; SQL [...
0
votes
1
answer
52
views
Is partitioning the correct feature for periodical deletions in large tables?
If I have two tables in a (Oracle) database that keeps an history of records through time :
CREATE TABLE Records(
RecordPackId NUMBER,
RecordId NUMBER,
Data VARCHAR2(4000),
PRIMARY KEY ...
- 321
0
votes
1
answer
135
views
automatic table partition when data insert
I have table which is contained list partition in postgres database
create table test(id int, name character varying) partition by list(id);
when I insert data, it is throwing an error.
insert into ...
- 1
0
votes
1
answer
87
views
Has anyone been able to use values regexp_like in partitioning by list in Oracle 19c?
I am attempting to partition a table based on a varchar2 column. I would like to partition it by 1) all alpha characters, 2) all numeric characters and 3) both alpha and numeric characters.
column to ...
0
votes
1
answer
573
views
For a table partitioning (by date) scenario seems I have to add the date column to the PK - but doesn't this devalue the PK?
I want to partition a large table that currently has an appropriate column as the primary key.
We want to, on a regular basis, remove older rows via partition switching.
The table has a date column ...
0
votes
1
answer
62
views
Query not optimized even after partitioned | Postgres
I have a table called tbl_inventory_detail_old as for the following DDL and it currently has 19M records. There are around 500 unique agent ids and around 35 unique product ids.
CREATE TABLE public....
- 461
0
votes
1
answer
179
views
How failures and restore operations in sharding (consistent hashing)
In consistent hashing, suppose we are using username as for hashing
hashFunction(username) = nodeA
Now from what I understand, if there is any failure or a node is removed requests will be directed to ...
- 47
0
votes
1
answer
65
views
How to create composite partition in mysql 5.7?
I have one table parcels, where I have 3 columns ID, PAYMENT_STATUS, CREATED_AT. It does not have any partition yet. This table has 30 million rows. Now I want to optimize this table, want to ...
- 2,963
1
vote
1
answer
148
views
How to get the set of the items by id with pagination in dynamodb
I have a table Topic, with multiple topics with topicId as primary key and updatedTime as sort key.
I can able to fetch single topic by topicId, but my requirement is to fetch more than 1 topics by id ...
- 85
0
votes
1
answer
89
views
Doubts/Questions related to list partition on existing table with existing indexes in Oracle
We want to partition two existing tables (INVOICE_ITEMS and INVOICE_ITEMS_EVENTS). These two existing tables already have indexes on it (as given in the image below).
INVOICE_ITEMS (existing indexes ...
- 97
1
vote
1
answer
50
views
MySQL 8.0 EXPLAIN Select shows that more query will read more subpartitions than necessary
I am creating a table to register the events from multiple sensors in a MySQL 8.0 database.
Since the data is going to be accessed mainly over time ranges (from one day to a month), I decided to ...
- 2,289
1
vote
1
answer
550
views
Еfficient method of a large table parallel reading in PostgreSQL
Consider a situation when there is a really large table (up to serveral TB), and for a some reason an application wants to scan this table full. There are multiple CPU cores both on the application ...
- 5,865
0
votes
1
answer
342
views
Postgres: Identifying partitions older than N days to drop them
I have a table partitioned by a timestamp field in a postgres database (v 13.11). I create new partitions every day. How do I look for partitions older than N days so I can drop then?
CREATE TABLE ...
- 81
1
vote
2
answers
311
views
Reverse key indexes with multiple columns strategy
I'm creating a local index on partitioned table the Table is partitioned X (List partition)
The table TEST looks like this:
X
Y
Z
AB
24
63
AB
24
65
CD
24
63
CD
24
65
EF
24
63
If I create an index
...
- 45
0
votes
1
answer
160
views
Partitioning of data in adx table
I was going through the microsoft docs of partition policy here. There it is written that By default, extents are partitioned by time of creation (ingestion), and in most cases there's no need to set ...
- 105
0
votes
1
answer
98
views
How to Determine Tablespace from ROWID if data_object_id is not Unique?
If the data_object_id is not unique within a database as Tanel Poder demonstrated (albeit from 2008), then how does Oracle uniquely determine the tablespace from the ROWID in order to make use of the ...
- 550
1
vote
1
answer
89
views
Query to find duplicates by an OR condition
I'm working on a query to eliminate duplicates from my table. I need to use this condition to identify duplicates:
(user_id IS NOT NULL AND user_id AND born_on matches) OR (first_name, last_name, and ...
- 2,662
0
votes
1
answer
55
views
GCP BigQuery wont access a single partition
I'm experiencing a problem with a GBQ query which goes through all the data in a table, even though the table is partitioned.
The partitioning is set to DAY on the datetime field.
Currently the query ...
- 177
2
votes
1
answer
366
views
Using PostgreSQL table as tumbling window persistence layer
Currently, I'm storing a large amount of time-series data into the following PostgreSQL table:
create table view (
user_id uuid,
product_id integer,
banner_id integer,
campaign_id integer,
...
- 7,064
0
votes
0
answers
531
views
Partitioning existing tables using PostgreSQL 13
I'm using PostgreSQL 13.8 and i have a lot of big tables (with a lot of records) and queries are spending too much. I though it's a good idea to partition some tables to speed them up. But i'm totally ...
0
votes
0
answers
88
views
Deadlock on insert trigger when creating DB partition - Sometimes
I have a table A. and some other tables B, C and D.
B, C and D are partitioned by the value of the ID column of A.
CREATE TABLE IF NOT EXISTS TABLE_A
(
id uuid CONSTRAINT a_guid UNIQUE not null
)
...
- 1,646