Category Archives: Database

Best practice: all Postgres tables can have these columns

They should all have these columns:

  • Primary key: id , bigserial (which creates a sequence behind the scene)
  • Date created: utc_created – Use current timestamp as a default value
  • Date updated: utc_updated – Use a trigger to automatically update it (See code below)
  • Creator: created_by – text type, can be anything
  • Updator: updated_by – text type , can be anything

Code:

create table my_table
(
    id          bigserial      not null ,
    
    utc_created timestamptz not null default current_timestamp,
    created_by  text        not null,
    utc_updated timestamptz null,
    updated_by  text        null,

    primary key (id)
);


CREATE TRIGGER my_table_updated_by_trigger BEFORE UPDATE
    ON my_table FOR EACH ROW EXECUTE PROCEDURE
    update_utc_updated_column(); -- see below
-- See https://www.revsys.com/tidbits/automatically-updating-a-timestamp-column-in-postgresql/

CREATE OR REPLACE FUNCTION update_utc_updated_column()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.utc_updated = current_timestamp;
    RETURN NEW;
END;
$$ language 'plpgsql';

Surprising features of postgres

Disclaimer: Not sure if they will surprise you

A role can be a user

This is STUPID ! It’s anti common sense.

utf8 charset supports 4-byte chars

In mysql you have to specify the charset to be “utf8mb4”, but in postgres it can just be “utf8”

When you create a new database, any role is allowed to create objects in the public schema of this database

This is crazy. You should revoke the creating permissions of other roles. See https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql/39029296#39029296

MySQL两种备份模式:Logical Backup 和 Raw Backup

Logical Backup 就是用诸如mysqldump这样的工具把数据导出为可视的sql文件或csv文件

Raw Backup 则是指直接备份数据库的数据文件

Raw Backup执行起来比Logical Backup快的多,因为它不需要消耗CPU/内存把数据变成sql或csv(顺便说一下,变csv比变sql要快很多)。

但Raw Backup有一个缺点:Raw File的跨平台能力比较弱。 一个windows下的文件在linux下可能就用不了,一个从mysql 5.1导出的文件可能无法被mysql 5.5识别。

Raw Backup还有一个严重的缺点: 如果Raw File是一个Corrupted File, 复制后产生的备份仍是一个Corrupted File; 这个备份就没有作用,因为你没法利用它来恢复数据。

对InnoDb执行mysqldump时应该加上 –single-transaction参数

mysqldump默认情况下并不保证数据一致性。 在innodb中,可以这样:

mysqldump  --single-transaction mydb > mydb-backup.sql

如果有 –single-transaction参数,mysqldump会将整个数据的读取过程置于一个满足repeatable read的事务中,最后导出的数据也是相互一致的。

由于innodb的mvcc特性,使用 –single-transaction执行mysqldump, 不会阻塞其他进程的读写。

innodb: 两个事务在binlog文件中不会交叉

在innodb的binlog文件中,如果一个事务从第m行开始,在第n行结束;那么从m到n之间只会记录本事务的操作,不会记录其他事务的行为。

这是因为innodb中binary log entries总是以事务为单位整体写入文件的。

引用

Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as BDB or InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

推论:

1. 只有事务完成了,相关记录才会进入binlog文件

2. 一个写事务不应该太长,否则当它被整体写入binlog文件时,会长时间占用binlog,导致其他事务在这段时间内无法写入binlog,因而无法提交,最终影响并发性能。