postgresql里面partition by用法

在PostgreSQL中，`PARTITION BY`是一个用于数据库分区的关键字，它常用于表级分区（Table Partitioning）。分区有助于将大表分割成更小、更易管理的部分，提高查询性能。当你按照特定列的值对数据进行分组，例如日期、范围或哈希，可以创建多个物理分区。

基本语法如下：

CREATE TABLE parent_table (
    ... -- 表的列定义
)
PARTITION BY column_to_partition ON value_range OR range_type;

这里 `parent_table` 是包含分区的顶层表，`column_to_partition` 是你想要依据其值进行分区的列名，`value_range` 或 `range_type` 可能包括：

- `range`: 指定一个数值范围，如 `PARTITION BY date_column (range (date_col))` 分区按日期。
- `list`: 列表值，如 `PARTITION BY category_id (list (1, 2, 3))` 根据类别ID列表分区。
- `hash`: 数据通过哈希函数均匀分布在各个分区，如 `PARTITION BY hash (column)`。

每个分区会创建一个独立的数据文件，这使得针对某些范围内的数据进行查询更快，特别是当涉及到大量数据时。

相关问题

postgresql 效能

在提高PostgreSQL数据库的效能方面，有一些方法可以考虑。首先，你可以使用pg_index和pg_stat_user_indexes视图来查找未使用的索引。通过运行以下查询，你可以找到未被使用的索引并删除它们：

SELECT indrelid::regclass AS tab_name, pi.indexrelid::regclass AS unused_index, idx_scan 
FROM pg_index pi, pg_stat_user_indexes psui 
WHERE pi.indexrelid = psui.indexrelid 
AND NOT indisunique 
AND idx_scan = 0;

然后，你可以使用分区表的功能将大型的表分成子表。这样可以提高查询效率，尤其是当表的记录非常多时。通过将数据分散到多个子表中，可以减少查询的数据量，提高查询性能。你可以使用以下语法来创建一个分区表：

CREATE TABLE parent_table (
    column1 data_type,
    column2 data_type,
    ...
)
PARTITION BY RANGE (column1);

最后，当你找到未使用的索引后，不要急于直接删除它们。你可以使用以下SQL语句将这些索引标记为无效，然后观察应用程序的行为，确保没有出现任何性能问题：

UPDATE pg_index SET indisvalid = false WHERE indexrelid::regclass::text IN (<Unused indexes name>);

一旦确认没有问题，你可以使用`concurrently`关键字来并发地删除这些索引。这样可以避免对其他正在运行的查询造成影响。<span class="em">1</span><span class="em">2</span><span class="em">3</span>
#### 引用[.reference_title]
- *1* *3* [postgresql定位未使用的索引（unused index)](https://blog.csdn.net/weixin_43230594/article/details/123293465)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"]
- *2* [PostgreSQL之分区表(partitioning)](https://download.csdn.net/download/weixin_38729022/13700051)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"]
[ .reference_list ]

postgresql 分表

### PostgreSQL 分表方法与实现方案

#### 使用pg_partman扩展进行分表操作
`pg_partman` 是一个非常流行的PostgreSQL扩展工具，专门用来管理和维护分区表。此工具支持时间戳、整数等多种类型的列作为分区键，并能够自动化创建新的分区以及清理过期的分区数据[^2]。

对于基于范围的时间序列数据存储来说特别有用，比如日志记录或传感器采集的数据流。通过配置参数可以设定每天、每周甚至每月自动生成一个新的分区来保存新增加的信息条目；而对于不再活跃的历史部分，则可通过设置保留期限，在达到一定年龄之后被自动删除掉以释放空间资源。

CREATE EXTENSION IF NOT EXISTS pg_partman;
SELECT partman.create_parent('public.sales', 'sale_date', 'native', 'daily');

上述SQL命令安装了 `pg_partman` 并将其应用于名为 "sales" 的销售订单表格之上，按照日期字段 ("sale_date") 进行每日级别的划分处理。

#### 利用原生特性——声明式分区(Declarative Partitioning)
从PostgreSQL 10开始引入了更为简便易用的新功能即声明式的分区机制。这种方式允许用户定义父级关系及其子集之间的逻辑连接而无需借助第三方插件的支持即可完成复杂度较高的多层嵌套结构设计工作[^4]。

下面是一个简单的例子展示如何利用这种内置能力构建按月份分割的大规模交易流水账本：

CREATE TABLE orders (
    order_id bigint,
    order_date date,
    amount numeric
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_january PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE orders_february PARTITION OF orders FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
...

这里先建立了基础框架——orders 表格本身并不实际存有任何具体实例化后的实体对象而是充当着容器角色容纳后续所指定时间段内的各个独立片段集合体。接着再分别针对每个月份建立相应的物理载体承接对应区间内产生的业务活动详情记录。

为了提高查询效率并减少不必要的扫描动作次数，应当考虑为每一个单独存在的分支单元附加适当的选择性过滤条件表达式从而引导优化器做出更明智合理的路径选取决策过程中的判断依据之一。