PostgreSQL. Keep the table in random access memory (RAM)

Despite the fact that PostgreSQL is quite a powerful database, it does not include full support for storing tables in memory.

Below I will describe how to get PostgreSQL to store the selected table in RAM for fast operations with them.

Everything will happen in Debian.

Create an empty folder to mount

mkdir /mnt/ramfs

1	mkdir /mnt/ramfs

And smonriruem in the ramfs

mount -t ramfs none /mnt/ramfs

1	mount -t ramfs none /mnt/ramfs

Create a folder for PostgreSQL and assign it rights.

mkdir /mnt/ramfs/pgdata

1	mkdir /mnt/ramfs/pgdata

chown postgres:postgres /mnt/ramfs/pgdata

1	chown postgres:postgres /mnt/ramfs/pgdata

chmod 600 /mnt/ramfs/pgdata

1	chmod 600 /mnt/ramfs/pgdata

Next enter superuser postgres — PostgreSQL database

su postgres

1	su postgres

psql

psql

And create a new TABLESPACE, accommodation which we ukažim in the folder with the installed ramfs

postgres=# CREATE TABLESPACE ram LOCATION '/mnt/ramfs/pgdata';

1	postgres=# CREATE TABLESPACE ram LOCATION '/mnt/ramfs/pgdata';

Will issue permissions for working with this TABLESPACE our user (for example, myuser)

postgres=# GRANT CREATE ON TABLESPACE ram TO myuser;

1	postgres=# GRANT CREATE ON TABLESPACE ram TO myuser;

Now we just have to create a new table and specify when creating a TABLESPACE ram.

For example:

CREATE TABLE mytesttable (
    begin_ip ip4 NOT NULL,
    end_ip ip4 NOT NULL,
    begin_num BIGINT NOT NULL,
    end_num BIGINT NOT NULL,
    country_code CHARACTER(2) NOT NULL,
    country_name CHARACTER VARYING(255) NOT NULL,
    ip_range ip4r
)
TABLESPACE ram;

CREATE TABLE mytesttable (

begin_ip ip4 NOT NULL,

end_ip ip4 NOT NULL,

begin_num BIGINT NOT NULL,

end_num BIGINT NOT NULL,

country_code CHARACTER(2) NOT NULL,

country_name CHARACTER VARYING(255) NOT NULL,

ip_range ip4r

)

TABLESPACE ram;

Now PostgreSQL will work with this table as with the other without even knowing that she “lies” in RAM.

That’s all.

Good luck.

Leave a Reply Cancel reply