postgresql的分库分表与存储过程(函数)的使用

前一段时间项目从MySQL完全转为PostgreSQL,用到了大量的PG分库分表和存储过程的特性。确实和MySQL有大不一样,难怪几年前就爆火,如今社区也一片欣欣向荣。

刚好能有时间好好总结下,前段时间天天9-11-6节奏真的太累。话不多说,

PG官方文档,再贴个CRUD的中文文档链接,

PG自动分表,处理日志流水业务

jsonb处理大数据

目录索引:

  1. pg的索引类型
  2. pg的函数使用
  3. pg的分库分表(先说下之前项目的某些场景,新建audit模式,新建表 audit.user_log 。)
  4. db实例化来实现分布式方案
  5. pg的FDW

 

pg的索引类型

postgresql的常见索引类型B-tree,Hash, GiST和GIN。

大部分情况和默认都是使用B-tree。

Hash索引只能用于处理简单的等于比较,也就是用得少,使用需谨慎。

GiST和GIN是支持自定义的索引策略。

 

pg的函数使用

例子如下,假设已经存在表: pulbic.users, public.users_log。

 

表users: 

表users_log:

通过构建函数,在数据层来抽出业务逻辑,pg的函数代码:

CREATE OR REPLACE FUNCTION public.user_op(user_name text,user_email text)
    RETURNS integer
    LANGUAGE 'plpgsql'
    VOLATILE
    PARALLEL UNSAFE
    COST 100
AS $BODY$DECLARE 
    user_id integer;
    affected INTEGER;
    op varchar;

BEGIN
    UPDATE users SET username = user_name WHERE email = user_email RETURNING id INTO user_id;
    GET DIAGNOSTICS affected = ROW_COUNT;
    op := 'update';
    IF affected = 0 THEN
        INSERT INTO users ( username, email ) VALUES ( user_name, user_email ) RETURNING id INTO user_id;
        op := 'insert';
    END IF;

    INSERT INTO users_log ( user_id, op ) VALUES ( user_id, op );
    GET DIAGNOSTICS affected = ROW_COUNT;
    RETURN affected;
END;
$BODY$;

declare 声明函数 user_op 的变量,需要注意下 returning...into... 语法。函数 user_op 主要是对 表users 的 insert和update,

运行的时候,直接调用:

SELECT user_op('tester','tester@t.cc');

 

虽然这个例子比较简单,但是对于复杂的业务场景,通过存储过程(函数)来抽离DB操作,能够很大程度简化代码。不过缺点可能就是维护与升级。