2

PostgreSQL表分区详解

 1 year ago
source link: https://www.jdon.com/67666.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

PostgreSQL表分区详解

表分区是一种非常有效的技术，用于提高大型数据库表的性能。通过将表的内容划分为较小的子表（称为分区），可以减小表的整体大小，从而显着提高性能。

什么时候应该对表进行分区？
  • 如果您的表对于服务器的 RAM 来说太大。您应该考虑对其进行分区：当表的大小达到几 GB 时，就该将其拆分。
  • 如果您正在处理大量数据，那么在拥有数百万条记录之前，不必费心进行分区。否则，您不会看到太多的性能提升。
  • 如果您的表可以在逻辑上分解为更小的块，例如：您有一个充满服务器日志的表。您可以按日期将它们拆分，因此同一天的所有日志都位于一个分区中。这使得执行诸如删除旧日志之类的任务变得更加容易，只需删除分区即可。

Postgres 内置支持三种类型的分区：

按range范围分区

该表被分区为由键列或一组列定义的“范围ranges”，分配给不同分区的值范围之间没有重叠

在以下示例中，人员表将按birth_date分区

CREATE TABLE people (
    id int not null,
    birth_date date not null,
    country_code character(2) not null,
    name text
) PARTITION BY RANGE (birth_date);

CREATE TABLE people_y2000 PARTITION OF people
    FOR VALUES FROM ('2000-01-01') TO ('2001-01-01');

CREATE TABLE people_y2001 PARTITION OF people
    FOR VALUES FROM ('2001-01-01') TO ('2002-01-01');

CREATE TABLE people_y2002 PARTITION OF people
    FOR VALUES FROM ('2002-01-01') TO ('2003-01-01');

我们来尝试一下：

INSERT INTO people (id, birth_date, country_code, name) VALUES
   (1, '2000-01-01', 'US', 'John'),
   (2, '2000-02-02', 'IT', 'Jane'),
   (3, '2001-03-03', 'FR', 'Bob');
> INSERT 0 3

SELECT schemaname,relname,n_live_tup 
   FROM pg_stat_user_tables 
   ORDER BY n_live_tup DESC;

schemaname  |   relname    | n_live_tup 
------------+--------------+------------
 public     | people_y2000 |          2
 public     | people_y2001 |          1
 public     | people_y2002 |          0

我们在主表people 中插入了三条记录。由于该表是按birth_date分区的，因此已将两条记录添加到分区people_y2000中，一条记录添加到people_y2001中，而people_y2002仍然为空。

按列表分区

通过显式列出每个分区中出现的键值来对表进行分区。

以同一示例为例，我们添加一个country_code列并将其用作分区键

CREATE TABLE people (
    id int not null,
    birth_date date not null,
    country_code character(2) not null,
    name text
) PARTITION BY LIST (country_code);

-- Partition for people living in Europe
CREATE TABLE people_EU PARTITION OF people
    FOR VALUES IN ('AT', 'DE', 'IT', 'FR', 'ES', ..... );

-- Partition for people living in United States
CREATE TABLE people_US PARTITION OF people
    FOR VALUES IN ('US');

我们来尝试一下：

INSERT INTO people (id, birth_date, country_code, name) VALUES
   (1, '2000-01-01', 'US', 'John'),
   (2, '2000-02-02', 'IT', 'Jane'),
   (3, '2001-03-03', 'FR', 'Bob');
> INSERT 0 3

SELECT schemaname,relname,n_live_tup 
   FROM pg_stat_user_tables 
   ORDER BY n_live_tup DESC;

 schemaname |  relname  | n_live_tup 
------------+-----------+------------
 public     | people_eu |          2
 public     | people_us |          1

PostgreSQL 再次将每一行移动到正确的分区。

按哈希分区

通过为每个分区指定模数和余数来对表进行分区。每个分区将保存分区键的哈希值除以指定模数将产生指定余数的行。

当我们无法逻辑地划分数据，但我们只能通过将行分散到许多较小的分区来减小表大小时，这种类型非常有用。

下面的 SQL 将把人分成三个表，每个表将包含（几乎）相同的行数。

CREATE TABLE people (
    id int not null,
    birth_date date not null,
    country_code character(2) not null,
    name text
) PARTITION BY HASH (id);

CREATE TABLE people_1 PARTITION OF people
    FOR VALUES WITH (MODULUS 3, REMAINDER 0);

CREATE TABLE people_2 PARTITION OF people
    FOR VALUES WITH (MODULUS 3, REMAINDER 1);

CREATE TABLE people_3 PARTITION OF people
    FOR VALUES WITH (MODULUS 3, REMAINDER 2);

我们来尝试一下：

INSERT INTO people (id, birth_date, country_code, name) VALUES
   (1, '2000-01-01', 'US', 'John'),
   (2, '2000-02-02', 'IT', 'Jane'),
   (3, '2001-03-03', 'FR', 'Bob');
> INSERT 0 3

SELECT schemaname,relname,n_live_tup 
   FROM pg_stat_user_tables 
   ORDER BY n_live_tup DESC;

 schemaname | relname  | n_live_tup 
------------+----------+------------
 public     | people_1 |          1
 public     | people_2 |          1
 public     | people_3 |          1

正如您所看到的，这三个记录已均匀地分布在所有可用分区中。

默认分区

当您尝试插入无法放入任何分区的记录时会发生什么？

让我们回到列表分区章节中定义的 people 表，并尝试添加来自加拿大的 Linda：

INSERT INTO people (id, birth_date, country_code, name) VALUES
   (4, '2002-04-04', 'CA', 'Linda');

ERROR:  no partition of relation "people" found for rowDETAILS: Partition key of the failing row contains (country_code) = (CA).

INSERT 将失败，因为 PostgreSQL 不知道在哪里添加该记录。

最明显的解决方案是添加一个新分区，但如果我们必须为世界上的每个国家/地区执行此操作，我们最终会得到数百个记录数量很少的表。不太好。

幸运的是，可以定义默认分区！

CREATE TABLE people_default PARTITION OF people DEFAULT;

再次尝试相同的插入，将导致：

INSERT INTO people (id, birth_date, country_code, name) VALUES
   (1, '2000-01-01', 'US', 'John'),
   (2, '2000-02-02', 'IT', 'Jane'),
   (3, '2001-03-03', 'FR', 'Bob'),
   (4, '2002-04-04', 'CA', 'Linda');
> INSERT 0 4

schemaname |    relname     | n_live_tup 
------------+----------------+------------
 public     | people_eu      |          2
 public     | people_us      |          1
 public     | people_default |          1

如您所见，Linda 现已添加到people_default中。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK