利用PostgreSQL中的NoSQL功能

相关文章推荐

忧郁的作业本 · 【海棠推文】《人人都馋娇软小美人》by雪花泥 ...· 1 周前 ·

温文尔雅的豆腐 · 联想拯救者刃9000(i7 ...· 1 月前 ·

越狱的扁豆 · Python实现PDF到HTML的转换_pd ...· 2 月前 ·

成熟的春卷 · URL重写 · ...· 2 月前 ·

爱喝酒的开水瓶 · Fabric Chaincode调试 —— ...· 3 月前 ·

> SELECT 'foo => bar, prompt => "hello world", pi => 3.14'::hstore;
                      hstore                       
-----------------------------------------------------
"pi"=>"3.14", "foo"=>"bar", "prompt"=>"hello world"
(1 row)

每个HStore的键都是唯一的。如果一个HStore声明有重复的键，那么只有一个重复的键会被存储，而且不能保证是哪一个。

> SELECT 'key => value1, key => value2'::hstore;
    hstore     
-----------------
"key"=>"value1"
(1 row)

由于其扁平的键值结构，HStore提供了简单和快速的查询功能，使其成为简单场景的理想选择。然而，HStore只支持文本数据，不支持嵌套数据，这使得它在复杂的数据结构中受到限制。

另一方面，JSONB可以处理更多种类的数据类型。

JSONB

JSONB数据类型接受JSON格式的输入文本，然后以分解的二进制格式存储。虽然这种转换使输入稍慢，但结果是快速处理和有效的索引。JSONB不保留白色空间或对象键的顺序。

> SELECT '{"foo": "bar", "pi": 3.14, "nested": { "prompt": "hello", "count": 5 } }'::jsonb;
                                jsonb                                
-----------------------------------------------------------------------
{"pi": 3.14, "foo": "bar", "nested": {"count": 5, "prompt": "hello"}}
(1 row)

如果给出了重复的对象键，则保留 最后的 值。

> SELECT '{"key": "value1", "key": "value2"}'::jsonb;
      jsonb      
-------------------
{"key": "value2"}
(1 row)

因为JSONB支持复杂的结构和完整的JSON功能，它是复杂或嵌套数据的理想选择，比HStore或JSON更适合。然而，与HStore相比，使用JSONB会带来一些性能开销和增加存储用量。

实际例子：使用HStore和JSONB工作

让我们考虑一些实际的例子来演示如何使用这些数据类型。我们将看一下创建表、基本查询和操作以及索引。

基本的HS存储操作

就像对待其他数据类型一样，你可以在PostgreSQL的数据表中定义字段为HStore数据类型。

> CREATE TABLE articles (    id serial primary key,    title varchar(64),    meta hstore  );

插入一条带有HStore属性的记录看起来像这样：

> INSERT INTO articles (title, meta)
  VALUES (
    'Data Types in PostgreSQL',
    'format => blog, length => 1350, language => English, license => "Creative Commons"');
> SELECT * FROM articles;
id |          title           | meta                                     ----+--------------------------+------------------------------------------  1 | Data Types in PostgreSQL | "format"=>"blog", "length"=>"1350", "license"=>"Creative Commons", "language"=>"English"(1 row)

通过HStore字段，你可以从字段中获取由你提供的键指定的特定键值对：

> SELECT title,          meta -> 'license' AS license,         meta -> 'format' AS format  FROM articles;
              title              |     license      |   format  
---------------------------------+------------------+------------
Data Types in PostgreSQL        | Creative Commons | blog
Advanced Querying in PostgreSQL | None             | blog
Scaling PostgreSQL              | MIT              | blog
PostgreSQL Fundamentals         | Creative Commons | whitepaper
(4 rows)

你也可以根据HStore字段内的特定值用标准来查询。

> SELECT id, title FROM articles WHERE meta -> 'license' = 'Creative Commons';
id |          title          
----+--------------------------
  1 | Data Types in PostgreSQL
  4 | PostgreSQL Fundamentals
(2 rows)

有时你可能只想查询那些在HStore字段中包含特定键的记录。例如，下面的查询只返回元HStore包含备注键的记录。要做到这一点，你需要使用"？"操作符。

> SELECT title, meta->'note' AS note FROM articles WHERE meta ? 'note';
              title              |      note      
---------------------------------+-----------------
PostgreSQL Fundamentals         | hold for review
Advanced Querying in PostgreSQL | needs edit
(2 rows)

在这里可以找到一个有用的HStore操作符和函数的列表。例如，你可以将HStore的键提取为数组，或者你可以将HStore转换为JSON表示。

> SELECT title, akeys(meta) FROM articles where id=1;
          title           |              akeys              
--------------------------+----------------------------------
Data Types in PostgreSQL | {format,length,license,language}
(1 row)
> SELECT title, hstore_to_json(meta) FROM articles where id=1;
          title           |            hstore_to_json
--------------------------+------------------------------------------------
Data Types in PostgreSQL | {"format": "blog", "length": "1350", "license": "Creative Commons", "language": "English"}
(1 row)

基本的JSONB操作

在PostgreSQL中使用JSONB数据类型是很简单的。表的创建和记录的插入看起来像这样：

> CREATE TABLE authors (id serial primary key, name varchar(64), meta jsonb);
> INSERT INTO authors (name, meta)  VALUES    ('Adam Anderson',     '{ "active":true, "expertise": ["databases", "data science"], "country": "UK" }');

注意jsonb meta字段是以JSON格式的文本字符串提供的。如果你提供的值不是一个有效的JSON，PostgreSQL会抱怨。

> INSERT INTO authors (name, meta)  VALUES ('Barbara Brandini', '{ "this is not valid JSON" }');
ERROR:  invalid input syntax for type json

与HStore类型不同，JSONB支持嵌套数据。

> INSERT INTO authors (name, meta)  VALUES ('Barbara Brandini',          '{ "active":true,             "expertise": ["AI/ML"],             "country": "CAN",             "contact": {               "email": "[email protected]",               "phone": "111-222-3333"             }           }');

与HStore类似，JSONB字段可以被部分检索，只用某些键。比如说：

> SELECT name, meta -> 'country' AS country FROM authors;
      name       | country ------------------+--------- Adam Anderson    | "UK" Barbara Brandini | "CAN" Charles Cooper   | "UK"(3 rows)

JSONB数据类型有许多操作符，其用法与HStore类似。例如，下面使用? 操作符只检索那些元字段包含联系人关键字的记录。

> SELECT name,         meta -> 'active' AS active,         meta -> 'contact' AS contact  FROM authors  WHERE meta ? 'contact';
      name       | active |                 contact                         
------------------+--------+-----------------------------------------------
Barbara Brandini | true   | {"email": "[email protected]", "phone": "111-222-3333"}
Charles Cooper   | false  | {"email": "[email protected]"}
(2 rows)

使用索引的工作

根据文档，HStore数据类型 "具有GiST和GIN索引，支持@>、?、?&和?|操作符"。关于这两种类型的索引的区别的详细解释，请看这里。 JSONB的索引使用GIN索引来促进键或键值对的有效搜索。

创建索引的语句就像人们所期望的那样：

> CREATE INDEX idx_hstore ON articles USING GIN(meta);
> CREATE INDEX idx_jsonb ON authors USING GIN(meta);

具有NoSQL灵活性的SQL结构

让我们重温一下我们在介绍中提到的原始用例。想象一下，一个新闻内容机构以与NoSQL文档存储相同的方式来存储其文章。也许文章可以用JSON表示为代表章节的对象的有序数组，每个对象都有文本内容、注释和格式。此外，每篇文章都有大量的元数据，而这些元数据属性在不同的文章中是不一致的。

上述描述概括了该组织的大部分NoSQL需求，但关于它如何管理和组织其数据的其他一切都与关系型数据模型密切相关。

通过将JSONB这样的数据类型的NoSQL能力与PostgreSQL的传统SQL优势相结合，组织可以享受灵活的模式和在嵌套数据中的快速查询，同时仍然能够执行联合操作和执行数据关系。PostgreSQL的HStore和JSONB数据类型为那些需要关系型数据库的结构但又需要NoSQL风格的数据存储的开发者提供了强大的选择。

规模化的PostgreSQL

你是否希望在传统关系型数据库的框架内支持NoSQL风格的数据存储和查询？也许你的组织处理文档的方式与我们在这篇文章中描述的类似。或者，你正在寻找处理非结构化数据的选项，用于大型语言模型（LLM）或其他一些AI/ML工作。

Linode市场中的PostgreSQL集群为你提供了SQL数据库的关系模型和结构，以及NoSQL数据库的横向扩展性。结合使用HStore或JSONB数据类型，你就有了一个理想的混合解决方案，可以在PostgreSQL中利用NoSQL功能。