我应该在更新的数据模型中使用hstore吗？ [英] Should I use hstore in renewed data model?

问题描述

在我的旧数据库（Postgres 9.1）中，我有几个表，其中包含各种文档（假设它们是父表）。
此外，对于这些文档，还有一个带有各种参数的表：

In my legacy database (Postgres 9.1) I have several tables containing divers kinds of documents (let's say they are 'parent' tables). Additionally, there is a table with various parameters for these documents:

create table params (
    kind integer,
    docid integer,
    parname text,
    parvalue text,
    constraint params_pk primary key (kind, docid, parname));

一个文档可能有许多（parname，parvalue）对。
由于种类指向不同的表，因此不能用作外键。

There may be many (parname, parvalue) pairs for one document. As kind points to different tables it cannot be used as a foreign key.

多年来，由于 params 仅用于打印文档。
现在该表包含500万行，并且该数据还用于其他目的。
因此，现在是更新该模型的时候了。

It has been working well for years as params were used only for printing documents. Now this table contains 5 millions rows and the data is needed also for other purposes. So it is high time to renew this model.

基本上 params 会为文档插入一次，很少更新。它们将作为一个整体被阅读（用于文档）。不需要搜索特定的 parname 。

Basically params are inserted once for a document and very seldom updated. They will be read as a whole (for a document). There is no need to search for a specific parname.

我有三个想法：

变体A.
根据父表将表 params 拆分为多个表，并使用 docid 作为外键。

Variant A. Split table params into several tables according to parent tables and use docid as foreign key.

变量B。
像变体A一样拆分表 params 并将（parname，parvalue）存储为hstore。

Variant B. Split table params as in variant A and store (parname, parvalue) as hstore.

变体C。
在每个父表中添加一个hstore字段，而忘记了其他表。

Variant C. Add a hstore field in every parent table and forget additional tables.

我对hstore没有经验。
每个变体的利弊是什么？你会选哪一个？ hstore可以让我感到奇怪吗？

I have no experience with hstore. What are the cons and pros of each variant? Which one would you choose? Can hstore baffle me with something strange?

推荐答案

我投票赞成第三个选项。桌子越少，睡眠越好。

I vote for the third option. The fewer tables the better sleep.

Hstore是为一级参数列表而发明的。它稳定，快速，简单，非常适合您的需求。我前段时间有类似的任务。我写了一个汇总以便于转换。

Hstore was invented for one-level parameter lists. It is stable, fast and simple, and perfectly fits your needs. I had similar task some time ago. I wrote an aggregate for easier conversion.

create or replace function hstore_add(hstore, text, text)
returns hstore language plpgsql 
as $$
begin
    return case
        when $1 isnull then hstore($2, $3)
        else $1 || hstore($2, $3) end;
end $$;

create aggregate hstore_agg (text, text) (
    sfunc = hstore_add,
    stype = hstore
);

我认为这可以节省您的时间。

I think it may save your time.

select kind, docid, hstore_agg(parname, parvalue)
from params
group by 1, 2
order by 1, 2;

这篇关于我应该在更新的数据模型中使用hstore吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！