PostgreSQL 内存表

背景

在某些场景，要求快速的DML，并且对数据可靠性要求不是非常高。

例如游戏的会话信息，传感器上传的最新数据，运算的中间结果，等等。

例如在一个场景中，有非常多的传感器的数据要不断的被更新和查询，可以使用这种方法，每个传感器的ID哈希后分配给对应的会话，这个传感器。

上面的需求，PostgreSQL 临时表都能满足。

但是临时表也存在一定的限制或弊端。

临时表为会话级内存表，跨会话不共享数据和结构，不写REDO。
超过一定大小时会落盘，不是纯内存的。
同时临时表继承了普通表的多版本，但是实际上会话级不需要多版本。
会话断开需要重建临时表结构。

虽然现在的SSD IO能力很强，但是与内存还有一定的插件，同时SSD 擦写次数受限，所以临时表或者普通表难以满足本文开头提到的场景需求。

Oracle 12C 推出列存储内存表，但是它是基于OLAP的应用场景，并不适合本文开头提到的场景。

PostgreSQL 社区也在考虑增加内存表的功能，本文是一个引子，可以看到社区在这方面的努力。

PostgreSQL内存表之路

在postgrespro发表的postgres roadmap中，可以看到，他们正在搞热插拔的存储引擎。

https://wiki.postgresql.org/wiki/Postgres_Professional_roadmap

Pluggable storages

We achieved a significant progress in PostgreSQL extendability:
  FDWs, custom access methods, generic WAL. And we're not so far from having pluggable storage engines.
  Concept of API will be presented at PGCon.

We are planning to implement the following types of storage engines:

In-memory row-oriented storage engine with optional support of transactions and optional support of persistency;
Columnar storage engine;
In-memory columnar storage engine;
On-disk row-oriented storage engine with undo-log for better bloat control.

行式内存引擎、列式内存引擎、列存磁盘存储引擎、以及回滚段模式的行式磁盘存储引擎。

目前还没有看到代码方面的输出，但是在postgrespro的项目里有一个与内存表非常类似的项目，会话级变量。

数据保存在内存中，目前支持如下操作
https://github.com/postgrespro/pg_variables

操作看起来是不是有点像redis呢?

Integer variables

Function Returns
pgv_set_int(package text, name text, value int) void
pgv_get_int(package text, name text, strict bool default true) int

Text variables

Function Returns
pgv_set_text(package text, name text, value text) void
pgv_get_text(package text, name text, strict bool default true) text

Numeric variables

Function Returns
pgv_set_numeric(package text, name text, value numeric) void
pgv_get_numeric(package text, name text, strict bool default true) numeric

Timestamp variables

Function Returns
pgv_set_timestamp(package text, name text, value timestamp) void
pgv_get_timestamp(package text, name text, strict bool default true) timestamp

Timestamp with timezone variables

Function Returns
pgv_set_timestamptz(package text, name text, value timestamptz) void
pgv_get_timestamptz(package text, name text, strict bool default true) timestamptz

Date variables

Function Returns
pgv_set_date(package text, name text, value date) void
pgv_get_date(package text, name text, strict bool default true) date

Jsonb variables

Function Returns
pgv_set_jsonb(package text, name text, value jsonb) void
pgv_get_jsonb(package text, name text, strict bool default true) jsonb

同样支持集合哦

Records

The following functions are provided by the module to work with collections of record types.

To use pgv_update(), pgv_delete() and pgv_select() functions required package and variable must exists.
Otherwise the error will be raised.
It is necessary to set variable with pgv_insert() function to use these functions.

pgv_update(), pgv_delete() and pgv_select() functions check the variable type.
If the variable type does not record type the error will be raised.

Function Returns Description
pgv_insert(package text, name text, r record) void Inserts a record to the variable collection. If package and variable do not exists they will be created. The first column of r will be a primary key. If exists a record with the same primary key the error will be raised. If this variable collection has other structure the error will be raised.
pgv_update(package text, name text, r record) boolean Updates a record with the corresponding primary key (the first column of r is a primary key). Returns true if a record was found. If this variable collection has other structure the error will be raised.
pgv_delete(package text, name text, value anynonarray) boolean Deletes a record with the corresponding primary key (the first column of r is a primary key). Returns true if a record was found.
pgv_select(package text, name text) set of record Returns the variable collection records.
pgv_select(package text, name text, value anynonarray) record Returns the record with the corresponding primary key (the first column of r is a primary key).
pgv_select(package text, name text, value anyarray) set of record Returns the variable collection records with the corresponding primary keys (the first column of r is a primary key).

下面更像redis了

Miscellaneous functions

Function Returns Description
pgv_exists(package text, name text) bool Returns true if package and variable exists.
pgv_remove(package text, name text) void Removes the variable with the corresponding name. Required package and variable must exists, otherwise the error will be raised.
pgv_remove(package text) void Removes the package and all package variables with the corresponding name. Required package must exists, otherwise the error will be raised.
pgv_free() void Removes all packages and variables.
pgv_list() table(package text, name text) Returns set of records of assigned packages and variables.
pgv_stats() table(package text, used_memory bigint) Returns list of assigned packages and used memory in bytes.

Note that pgv_stats() works only with the PostgreSQL 9.6 and newer.

目前数据仅支持会话级，会话断开则自动释放，期待真正的内存表引擎吧，这只是个引子。

存储逻辑结构

术语

package : 包名
name : 变量名
value : 标量类型的值
r : 集合类型的单条记录
pk : 集合类型的主键

测试

安装内存表插件

export PATH=/home/digoal/pgsql9.6/bin:$PATH

git clone https://github.com/postgrespro/pg_variables

cd pg_variables/

make USE_PGXS=1

make USE_PGXS=1 install

make USE_PGXS=1 installcheck

postgres=# create extension pg_variables;
CREATE EXTENSION

标量测试

postgres=# select pgv_set_int('pkg1','k1',100);
 pgv_set_int
-------------

(1 row)

postgres=# select pgv_get_int('pkg1','k1');
 pgv_get_int
-------------
         100
(1 row)

postgres=# select pgv_set_jsonb('pkg1','k2','{"a":"b", "c":{"hello":"digoal"}}');
 pgv_set_jsonb
---------------

(1 row)

postgres=# select pgv_get_jsonb('pkg1','k2');
            pgv_get_jsonb
--------------------------------------
 {"a": "b", "c": {"hello": "digoal"}}
(1 row)

更新与自增用法

postgres=# select pgv_set_int(pkg,k, pgv_get_int(pkg,k)+1 ) from (values ('pkg1','k1')) t(pkg,k);
 pgv_set_int
-------------

(1 row)

postgres=# select pgv_get_int('pkg1','k1');
 pgv_get_int
-------------
         102
(1 row)

postgres=# select pgv_set_int(pkg,k, pgv_get_int(pkg,k)+1 ) from (values ('pkg1','k1')) t(pkg,k);
 pgv_set_int
-------------

(1 row)

postgres=# select pgv_get_int('pkg1','k1');
 pgv_get_int
-------------
         103
(1 row)

性能，每秒标量更新达到了239万次。

postgres=# select count(*) from (select pgv_set_int('pkg1','k1',id) from generate_series(1,10000000) t(id) ) t;
  count
----------
 10000000
(1 row)
Time: 4185.179 ms

postgres=# select pgv_get_int('pkg1','k1');
 pgv_get_int
-------------
    10000000
(1 row)
Time: 0.470 ms

postgres=# select 10000000/4.185;
       ?column?
----------------------
 2389486.260454002389
(1 row)
Time: 0.869 ms

集合测试

postgres=# select pgv_insert('pkg2', 'k1', row(1::int, 'hello world'::text, current_date::date));
 pgv_insert
------------

(1 row)

postgres=# select * from pgv_select('pkg2', 'k1') as t(c1 int,c2 text,c3 date);
 c1 |     c2      |     c3
----+-------------+------------
  1 | hello world | 2016-08-18
(1 row)

postgres=# select count(*) from (select pgv_insert('pkg2', 'k1', row(c1,'test'::text,current_date::date)) from generate_series(2,100000) t(c1)) t;
 count
-------
 99999
(1 row)

postgres=# select * from pgv_select('pkg2', 'k1', array[1,2,3]) as t(c1 int,c2 text,c3 date);
 c1 |     c2      |     c3
----+-------------+------------
  1 | hello world | 2016-08-18
  2 | test        | 2016-08-18
  3 | test        | 2016-08-18
(3 rows)

内存表和普通表的JOIN

postgres=# select t1.,t2. from (select * from pgv_select('pkg2', 'k1') as t(c1 int,c2 text,c3 date)) t1, tbl1 t2 where t1.c1=t2.id and t2.id<10;
 c1 |     c2      |     c3     | id |               info
----+-------------+------------+----+----------------------------------
  8 | test        | 2016-08-18 |  8 | a8a7e0f849c5895820bbca32d7e798b1
  4 | test        | 2016-08-18 |  4 | f6954fb12336881d590fa7a50dd03916
  9 | test        | 2016-08-18 |  9 | 45ff843fcd5372e525368829f9846def
  5 | test        | 2016-08-18 |  5 | d8afe53f0a7d553716caa9ffaef7ea3d
  7 | test        | 2016-08-18 |  7 | 2b20f485974500d7b3ecb1f4c1d0f975
  2 | test        | 2016-08-18 |  2 | 3d36418926b2e0e2dc7090da17e39451
  6 | test        | 2016-08-18 |  6 | 6923416bbca7634f01f7f79030609f64
  1 | hello world | 2016-08-18 |  1 | 3bb6c833f1b10139edf7e2f2eb4f4a69
  3 | test        | 2016-08-18 |  3 | de5b51374e1db3ccac9c61af75b69a33
(9 rows)

更新与删除内存表的数据

postgres=# select pgv_update('pkg2', 'k1', t) from (select c1,'new val'::text,'2017-01-01'::date from pgv_select('pkg2', 'k1', 1) as tb(c1 int, c2 text, c3 date)) t;
 pgv_update
------------
 t
(1 row)

Time: 0.665 ms

postgres=# select * from pgv_select('pkg2', 'k1', 1) as tb(c1 int, c2 text, c3 date);
 c1 |   c2    |     c3
----+---------+------------
  1 | new val | 2017-01-01
(1 row)

Time: 0.518 ms

postgres=# select  pgv_delete('pkg2', 'k1', 1);
 pgv_delete
------------
 t
(1 row)

Time: 0.440 ms

管理内存对象

postgres=# select * from pgv_exists('pkg1','k1');
 pgv_exists
------------
 f
(1 row)

Time: 0.491 ms

postgres=# select pgv_list();
 pgv_list
-----------
 (pkg2,k1)
(1 row)

Time: 0.455 ms

postgres=# select pgv_stats();
    pgv_stats
-----------------
 (pkg2,16785408)
(1 row)

Time: 0.514 ms

postgres=# select pgv_remove('pkg2','k1');
 pgv_remove
------------

(1 row)

Time: 1.868 ms
postgres=# select pgv_stats();
  pgv_stats
--------------
 (pkg2,24576)
(1 row)

Time: 0.367 ms
postgres=# select pgv_remove('pkg2');
 pgv_remove
------------

(1 row)

Time: 0.415 ms
postgres=# select pgv_stats();
 pgv_stats
-----------
(0 rows)

Time: 0.369 ms

数据持久化

postgres=# select count(*) from (select pgv_insert('pkg2', 'k1', row(c1,'test'::text,current_date::date)) from generate_series(2,10000000) t(c1)) t;
  count
---------
 9999999
(1 row)

在事务中持久化数据
postgres=# begin;
postgres=# create table tbl as select * from pgv_select('pkg2','k1') as t(c1 int, c2 text, c3 date);
postgres=# end;

postgres=# select count(*) from tbl;
  count
---------
 9999999
(1 row)

祝大家玩得开心，欢迎随时来 阿里云促膝长谈业务需求 ，恭候光临。

阿里云的小伙伴们加油，努力 做好内核与服务，打造最贴地气的云数据库 。

时间： 2024-11-20 12:17:06

PostgreSQL 内存表的相关文章

mysql 临时表、内存表和视图

虚拟表,顾名思义,就是实际上并不存在(物理上不存在),但是逻辑上存在的表.这样说很抽象,还是看一些实际的例子吧.在MySQL中,存在三种虚拟表:临时表.内存表和视图. 一.mysql临时表1.什么是临时表临时表是建立在系统临时文件夹中的表,如果使用得当,完全可以像普通表一样进行各种操作. 临时表的数据和表结构都储存在内存之中,退出时,其所占的空间会自动被释放.2.创建临时表(1)定义字段CREATE TEMPORARY TABLE tmp_table (        name VARCHAR(

MYSQL使用心得(四) 临时表与内存表

mysql5.5性能优化-内存表 内存表分为2种,但共同点是,重起数据库以后,内存中的数据全部丢失,内存表的功能 有部分的限制,有些属性不能像正常表一样使用,所以请大家使用的时候谨慎参照官方文档.下面只是抛砖引玉. 1.临 时表:表建在内存里,数据在内存里 2.内存表:表建在磁盘里,数据在内存里 其中包括2个重要的参数 [mysqld] # 内存表容量 max_heap_table_size=1024M # 临时表容量 tmp_table_size=1024M 建立内存表的时候,在5.5里,需要

link中如何跨数据库和内存表联合查询？如何同时判断列表中多个元素的存在性？

问题描述 link中如何跨数据库和内存表联合查询?如何同时判断列表中多个元素的存在性? link中如何跨数据库和内存表联合查询?如何同时判断列表中多个元素的存在性? 解决方案 直接用数组是可以的. int[] ids = { 1, 2, 3, 4, 5 }; var query = from x in db.Table where ids.Contains(x) select x;

link中内存表和数据库表联合查询怎么做？为什么提示invaild invoke？

问题描述 link中内存表和数据库表联合查询怎么做?为什么提示invaild invoke? link中内存表和数据库表联合查询怎么做?为什么提示invaild invoke? 解决方案 检查下是不是访问了null的对象?

阿里云HybridDB for PostgreSQL内存与负载管理(resource queue)实践

标签 PostgreSQL , Greenplum , 阿里云HybridDB for PostgreSQL , 内存管理 , OOM , 操作系统内核参数 , 资源队列 , 数据库内存保护参数 背景 Greenplum是一个重计算和重资源的MPP数据库,可谓有多少资源就能消耗多少资源,带来的好处是处理速度变快了,坏处就是容易用超. CPU.网络.硬盘用超的话,关系不大,因为大不了就是到硬件瓶颈,但是内存用超的话会带来较大的负面影响,例如操作系统OOM用户进程,导致数据库崩溃等. 如果要达到非常

SQL Server在AlwaysOn中使用内存表的“踩坑”记录

前言 最近因为线上alwayson环境的一个数据库上使用内存表.经过大概一个星期监控程序发现了一个非常严重问题这个数据库的日志文件不会截断,已用空间一直在增加(存在定时的每个小时的日志备份),同时内存表数据库文件也无法删除,下面就介绍一下后面我的处理过程,话不多说了,来一起看看详细的介绍吧. 数据库:SQL Server2014 Enterprise Edition (64-bit) 删除文件 使用一个单独非alwayson环境的数据库测试. 一.创建内存表 ---创建内存表文件组 ALTER

【转载】MySQL 之临时表和内存表

      今天(2013-10-17)又看了一篇很久以前的博客文章,题目是< How to eliminate temporary tables in MySQL >,里面的一些观点和结论摘录如下:  to make statement-based replication reliable is eliminate temporary tables. true temporary tables are created with CREATE TEMPORARY TABLE, and inte

做WEB，怎么用内存表，怎么向内存表插入数据。请指点。最好用C#

问题描述 做WEB,怎么用内存表,怎么向内存表插入数据.请指点.最好用C# 解决方案 解决方案二:利用DataTable和SESSION把TABLE表里的值覆给SESSION,之后进行操作表!购物车有很多都是利用这一点做的解决方案三:????????????解决方案四:内存表...-------->DataTabledt=newDataTable();dt.Columns.Add(...);dt.Columns.Add(...);DataRowdr=dt.NewRows();dr[0]=text

SQL SERVER 2014 内存表 测试 结果慢 (2016-05-16)

问题描述 SQL SERVER 2014 内存表 测试 结果慢 (2016-05-16) 50C 大家好, 最近下了一个 SQL 2014 64位企业版, 安装环境:WIN7 X64 企业版 台式机环境为: I7 4770K, 16G 内存 , 机械硬盘: 一张是传统的表 disk_card 一张是内存表 snap_card,结构完全相同 两张表记录条数,均为 27752311 条, 而且内容完全一样(从disk__card 整表复制到snap__card的) disk_card 无索引 ( 其