Nizhe ya rasskazhu kak zastavit’ PostgreSQL khranit’ vybrannyye tablitsy v operativnoy pamyati dlya bystrykh operatsiy s nimi.
Vso budet proiskhodit’ v Debian.
Sozdadim pustuyu papku dlya montirovaniya
mkdir /mnt/ramfs
I smonriruyem v neyo ramfs
mount -t ramfs none /mnt/ramfs
Sozdadim papku dlya PostgreSQL i naznachim na neyo prava.
mkdir /mnt/ramfs/pgdata
chown postgres:postgres /mnt/ramfs/pgdata
chmod 600 /mnt/ramfs/pgdata
Daleye zaydom pod superpol’zovatelem bazy dannykh PostgreSQL — postgres
su postgres
psql
I sozdadim novyy TABLESPACE, razmeshcheniye kotorogo my ukazhim v papke s smontirovannoy ramfs
postgres=# CREATE TABLESPACE ram LOCATION ‘/mnt/ramfs/pgdata’;
Vydadim prava na rabotu s etom TABLESPACE nashemu pol’zovatelyu (naprimer myuser)
postgres=# GRANT CREATE ON TABLESPACE ram TO myuser;
Teper’ nam ostalos’ tol’ko sozdat’ novuyu tablitsu i ukazat’ pri yeyo sozdanii TABLESPACE ram.
Naprimer:
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;
Teper’ PostgreSQL budet rabotat’ s etoy tablitsey kak i s drugimi dazhe ne podozrevaya chto ona «lezhit» v OZU.
Despite the fact that PostgreSQL is quite a powerful database, it lacks full support for storing tables in memory.
Here I will explain how to get PostgreSQL to store the selected tables in memory for fast operations with them.
Everything will happen in Debian.
Let’s create an empty folder to mount
mkdir / mnt / ramfs
And smonriruem ramfs there
mount -t ramfs none / mnt / ramfs
Create a folder for PostgreSQL and assign it to the right.
mkdir / mnt / ramfs / pgdata
chown postgres: postgres / mnt / ramfs / pgdata
chmod 600 / mnt / ramfs / pgdata
Let’s go further superuser database PostgreSQL – postgres
su postgres
psql
And create a new TABLESPACE, we determine placement in a folder mounted ramfs
postgres = # CREATE TABLESPACE ram LOCATION ‘/ mnt / ramfs / pgdata’;
Will issue the right to work with this TABLESPACE our users (for example, myuser)
postgres = # GRANT CREATE ON TABLESPACE ram TO myuser;
Now we just need to create a new table and point at its creation 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;
Now PostgreSQL will work with this table as the other without even knowing that it “lies” in RAM.