Tyson

天道唯一 大道至简

发表于 本文字数： 597 阅读时长 ≈ 1 分钟

在 PostgreSQL 中，VACUUM 和 VACUUM FULL 是两种不同的操作，它们用于维护和优化数据库表。

VACUUM:

  • VACUUM 是用于释放表中未使用的空间、删除已删除行的存储空间、更新统计信息的操作。它并不重建表，而是通过清理不再需要的数据来优化表的存储。VACUUM 操作是常规性的，用于维护数据库性能。
  • VACUUM 不会移动表中的数据，而是标记被删除的行以便后续的清理。
1
VACUUM table_name;

VACUUM FULL:

  • VACUUM FULL 则是一种更为彻底的操作，它不仅释放未使用的空间，还重新组织表的物理存储以减小表的大小。具体地说，VACUUM FULL 创建一个新的表文件，将表的数据移动到新的文件中，然后删除旧文件。这可以显著减小表的大小，但是由于需要创建新文件和移动数据，因此 VACUUM FULL 通常比普通的 VACUUM 操作更耗时，而且可能需要较长的停机时间。

    阅读全文 »

发表于 本文字数： 22k 阅读时长 ≈ 20 分钟

  • 从原表复制表结构，如果分区键不是原表主键需要一步一步创建

    从其它表复制过来的表不能使用like origin_table including all,只能用like origin_table 不带including，除非原表的主键和分区表要使用的分区键是同一个，index索引后续手动创建。

    阅读全文 »

发表于 本文字数： 1.7k 阅读时长 ≈ 2 分钟

前提：

​ t1 - 原始业务表

​ t2 - 数据仓库表

要求：

​ 数仓表固定周期更新，需要将原始业务表中： 1. 新增的数据插入 2. 修改的数据更新 到数仓表中，可采用ON CONFLICT，注意constraint_column_name 必须是unique index，或者constraint_column_name组合索引必须是unique index;

阅读全文 »

发表于 本文字数： 1.3k 阅读时长 ≈ 1 分钟

OLTP

OLTP（ Online Transaction Processing）在线事务处理系统

用途： 用于支持日常的业务交易和事务处理。OLTP系统旨在处理大量的短期交易，例如订单处理、库存管理、在线银行交易等。

设计： 数据库设计是面向事务的，关注数据的一致性和完整性。通常采用规范化的数据模型，以避免数据冗余。

查询： 主要用于插入、更新和删除数据，支持简单的查询，通常涉及特定的业务实体。

性能： 对写入操作有较好的性能，支持高并发的事务处理。

阅读全文 »

发表于 本文字数： 2.1k 阅读时长 ≈ 2 分钟

  • 查询某张表的索引

    1
    2
    3
    SELECT * 
    FROM pg_stat_user_indexes
    WHERE relname = 'stock_move';

  • 查询索引大小

    1
    SELECT pg_size_pretty(pg_indexes_size('stock_move'));

  • 查询某张表的所有索引大小

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT 
        c.relname AS `表名`,
        i.relname AS `索引名`,
        pg_size_pretty(pg_indexes_size(c.oid)) AS `索引大小`
    FROM 
        pg_class c
    JOIN 
        pg_class i ON i.relparent = c.oid
    WHERE 
        c.relname = 'stock_move'  -- 替换为具体表名
        AND c.relkind = 'r'  -- 'r'表示普通表
    ORDER BY 
        pg_indexes_size(c.oid) DESC;
    阅读全文 »

发表于 本文字数： 6.2k 阅读时长 ≈ 6 分钟

PostgreSQL | Function&Procedure | 函数与存储过程的区别

1. 简述

1.1 书面说法

函数（Function）和存储过程（Stored Procedure）都是数据库中的可重用代码块，用于执行特定的任务。

  1. 两者都提供了代码封装的机制，可以将一系列操作组织成单个单元。

  2. 都可以接受参数，并且可以返回结果。

  3. 存储过程和函数都可以在多个地方重复使用，减少了代码的冗余。

  4. 存储过程更倾向于执行一系列的操作，而函数更专注于返回一个值。

    阅读全文 »

发表于 本文字数： 2.6k 阅读时长 ≈ 2 分钟

插件awesome：https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8aee9d47

德哥打包好的docker镜像：https://github.com/digoal/blog/blob/master/202307/20230710_03.md

pg_freespacemap

可用于查看数据库表对应数据块的空余空间的比例和数量；

pg_freespace(rel regclass IN, blkno bigint IN) returns int2

pg_freespace(rel regclass IN, blkno OUT bigint, avail OUT int2)

发表于 本文字数： 1.4k 阅读时长 ≈ 1 分钟

[TOC]

CTE（Common Table Expressions）

简单讲，CTE就是日常SQL中出现的with语句，其原理就是通过提前将数据查询出来后作为临时结果集使用，可以与SELECT \ INSERT \ UPDATE \ DELETE的SQL连用。

优点

  1. 可读性强
    • CTE 允许你将复杂的查询拆分成易于理解和管理的块。这使得查询更易于阅读、理解和维护。
  2. 重用性
    • CTE 可以在一个查询中多次引用，这使得可以将复杂的逻辑组件分解成可重复使用的部分。
  3. 递归查询
    • CTE 允许你执行递归查询，这是一种对于层次化数据结构（如组织结构或树形结构）非常有用的功能。
  4. 优化器支持
    • PostgreSQL 的查询优化器可以对 CTE 进行优化，以确保最佳执行计划。
阅读全文 »

发表于 本文字数： 4k 阅读时长 ≈ 4 分钟

[TOC]

PG事务等级

国际SQL标准事务等级：

PostgreSQL事务等级

  1. 可以看到PG中不允许出现脏读，其实最终就三个事务隔离级别：RC、RR、SR；
  2. PG默认RC模式；
  3. PG不允许在可重复读的情况下产生幻读。
  4. 可以手动调整为可序列化SR，但因为SR是序列化的，性能较低一般没人用。
阅读全文 »
0%