Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
1,098 questions
1
vote
1
answer
105
views
How do I partition a large table?
I have a table that contains a billion rows. I want to partition that table for multiple reasons (i.e. deleting from table blows up log space).
My problem is since the table is so big, dropping the ...
Henry
0
votes
1
answer
33
views
How to view contents of a specific table partition in ssms
I have a table that currently has 14 partitions
right click table --> properties --> storage
How can I view the data in each partition?
- 187
0
votes
1
answer
30
views
How to query all data belonging to a specific HASH partition (bucket) in a composite database?
I am using DolphinDB to store stock market data. I have created a distributed table with a Composite Partition scheme:
Value Partition by Month (nActionDay)
Hash Partition (20 buckets) by Symbol (...
5
votes
0
answers
143
views
Does partitioning allow filegroups with LOB data to be shrunk?
It is extensively documented that shrinking any data files that have LOB pages is awful:
Microsoft says you need to read the whole partition once for every page you want to move.
The well-known ...
- 1,488
1
vote
1
answer
207
views
What does trace flag 11024 do?
Context
I have been experimenting with incremental statistics a lot. I do not know of any online resource about them that I have not read. Despite all of this, I just don't understand what trace flag ...
- 1,488
-1
votes
1
answer
140
views
Best Design and Implementation Practices for a long (billions) and narrow table [closed]
I am about to start working on a project that aims at modeling all trees in France using the LiDAR coverage of the territory.
I won't get into the detail of the modeling but basically after ...
1
vote
1
answer
92
views
Is there a limit to the number of values used for a list partition?
We have sensor data used for analytics that has for columns:
CREATE TABLE sensor_data
(
dt date NOT NULL,
timeofday time without time zone NOT NULL,
sensor_id TEXT NOT NULL,
--...
- 615
1
vote
0
answers
97
views
Emptying partition, Sch-M locks
I'm trying to find a good way of refilling partition of the table without locking other partitions.
Essentially, I have partitioned clustered columnstore table, which I query often and need to refill ...
- 304
1
vote
2
answers
282
views
What thresholds are used to trigger automatic statistics updates for incremental statistics? And how can I tell which partition updated?
The Question
The idea of incremental statistics is that they make statistics updates faster. However, no documentation comments on how they interact with automatic statistics updates. This leaves me ...
- 1,488
1
vote
0
answers
30
views
GridDB Time-Series Retention: Built-in TTL or recommended purge/archival pattern?
We ingest time-series sensor data into a GridDB TimeSeries container on GridDB Cloud (Free plan). I’m trying to keep only the most recent N days (e.g., 90 days) online with minimal impact on ingest ...
- 111
1
vote
1
answer
130
views
Why is GROUP BY on partitioned table slower than querying child partition directly?
I have the following table partitioned by projects.
CREATE TABLE IF NOT EXISTS entries
(
id bigint NOT NULL,
status_id bigint NOT NULL,
group_id bigint NOT NULL,
project_id bigint ...
- 13
1
vote
1
answer
137
views
partitioning by varchar column
have a log table that stores execution logs for many different functions.
Each row contains the function name, and the table has millions of rows.
To improve performance and manage the data better, I ...
- 11
0
votes
2
answers
107
views
Partitioning on a categorical (low-cardinality) column
Similar to Improving performance with a common low-cardinality field
We get a large dataset and we load it by source. Let's say team_id. We currently have our data partitioned by team_id and then by ...
- 615
0
votes
0
answers
65
views
Slow DELETE due to FK trigger on partitioned child table
I'm working with PostgreSQL 15 and experimenting with table partitioning to improve performance.
Original setup:
I have two tables: tasks (parent) with ~65M rows and records (child) with ~200M ...
- 145
3
votes
2
answers
598
views
Partitioning heaps - Why?
I know, at least theoretically, that heap tables can be partitioned. As I learn more about table partitioning, I realise that the use case is very limited. Similarly, the use case for heaps in SQL ...
- 1,488