#top Przydatne informacje¶

#top Timeout¶

#top Database Storage Layout¶

#top Tablespace¶

#top SQL JOIN Visual Explanation¶

• [INNER] JOIN - słowo kluczowe INNER jest opcjonalne, złączenia JOIN i INNER JOIN są równoważne,
• LEFT [OUTER] JOIN - słowo kluczowe OUTER jest opcjonalne, złączenia LEFT JOIN i LET OUTER JOIN są równoważne,
• RIGHT [OUTER] JOIN - słowo kluczowe OUTER jest opcjonalne, złączenia RIGHT JOIN i RIGHT OUTER JOIN są równoważne,
• FULL [OUTER] JOIN - słowo kluczowe OUTER jest opcjonalne, złączenia FULL JOIN i FULL OUTER JOIN są równoważne,
• CROSS JOIN

-- utworzenie tabel
CREATE TABLE join01 (
idjoin integer NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY(idjoin)
);

CREATE TABLE join02 (
idjoin integer NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY(idjoin)
);

-- dodanie przykładowych danych
INSERT INTO join01(idjoin,name) VALUES(1,'t01name01');
INSERT INTO join01(idjoin,name) VALUES(2,'t01name02');
INSERT INTO join01(idjoin,name) VALUES(3,'t01name03');
INSERT INTO join01(idjoin,name) VALUES(4,'t01name04');
INSERT INTO join01(idjoin,name) VALUES(5,'t01name05');

INSERT INTO join02(idjoin,name) VALUES(3,'t02name03');
INSERT INTO join02(idjoin,name) VALUES(4,'t02name04');
INSERT INTO join02(idjoin,name) VALUES(5,'t02name05');
INSERT INTO join02(idjoin,name) VALUES(6,'t02name06');
INSERT INTO join02(idjoin,name) VALUES(7,'t02name07');

#top SQL JOIN INNER JOIN¶

SELECT * FROM join01 JOIN join02 ON (join01.idjoin=join02.idjoin);
SELECT * FROM join01 INNER JOIN join02 ON (join01.idjoin=join02.idjoin);

 idjoin |   name    | idjoin |   name
--------+-----------+--------+-----------
      3 | t01name03 |      3 | t02name03
      4 | t01name04 |      4 | t02name04
      5 | t01name05 |      5 | t02name05
(3 rows)

#top SQL JOIN LEFT JOIN¶

SELECT * FROM join01 LEFT JOIN join02 ON (join01.idjoin=join02.idjoin);
SELECT * FROM join01 LEFT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin);

 idjoin |   name    | idjoin |   name
--------+-----------+--------+-----------
      1 | t01name01 |        |
      2 | t01name02 |        |
      3 | t01name03 |      3 | t02name03
      4 | t01name04 |      4 | t02name04
      5 | t01name05 |      5 | t02name05
(5 rows)

#top SQL JOIN RIGHT JOIN¶

SELECT * FROM join01 RIGHT JOIN join02 ON (join01.idjoin=join02.idjoin);
SELECT * FROM join01 RIGHT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin);

 idjoin |   name    | idjoin |   name
--------+-----------+--------+-----------
      3 | t01name03 |      3 | t02name03
      4 | t01name04 |      4 | t02name04
      5 | t01name05 |      5 | t02name05
        |           |      6 | t02name06
        |           |      7 | t02name07
(5 rows)

#top SQL JOIN FULL JOIN¶

SELECT * FROM join01 FULL JOIN join02 ON (join01.idjoin=join02.idjoin);
SELECT * FROM join01 FULL OUTER JOIN join02 ON (join01.idjoin=join02.idjoin);

 idjoin |   name    | idjoin |   name
--------+-----------+--------+-----------
      1 | t01name01 |        |
      2 | t01name02 |        |
      3 | t01name03 |      3 | t02name03
      4 | t01name04 |      4 | t02name04
      5 | t01name05 |      5 | t02name05
        |           |      6 | t02name06
        |           |      7 | t02name07

#top SQL JOIN LEFT JOIN IS NULL¶

SELECT * FROM join01 LEFT JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join02.idjoin IS NULL;
SELECT * FROM join01 LEFT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join02.idjoin IS NULL;

 idjoin |   name    | idjoin | name
--------+-----------+--------+------
      1 | t01name01 |        |
      2 | t01name02 |        |
(2 rows)

#top SQL JOIN RIGHT JOIN IS NULL¶

SELECT * FROM join01 RIGHT JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL;
SELECT * FROM join01 RIGHT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL;

 idjoin | name | idjoin |   name
--------+------+--------+-----------
        |      |      6 | t02name06
        |      |      7 | t02name07
(2 rows)

#top SQL JOIN FULL JOIN IS NULL¶

SELECT * FROM join01 FULL JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL OR join02.idjoin IS NULL;
SELECT * FROM join01 FULL OUTER JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL OR join02.idjoin IS NULL;

 idjoin |   name    | idjoin |   name
--------+-----------+--------+-----------
      1 | t01name01 |        |
      2 | t01name02 |        |
        |           |      6 | t02name06
        |           |      7 | t02name07
(4 rows)

#top SQL JOIN CROSS JOIN¶

SELECT * FROM join01 CROSS JOIN join02;

 idjoin |   name    | idjoin |   name
--------+-----------+--------+-----------
      1 | t01name01 |      3 | t02name03
      1 | t01name01 |      4 | t02name04
      1 | t01name01 |      5 | t02name05
      1 | t01name01 |      6 | t02name06
      1 | t01name01 |      7 | t02name07
      2 | t01name02 |      3 | t02name03
      2 | t01name02 |      4 | t02name04
      2 | t01name02 |      5 | t02name05
      2 | t01name02 |      6 | t02name06
      2 | t01name02 |      7 | t02name07
      3 | t01name03 |      3 | t02name03
      3 | t01name03 |      4 | t02name04
      3 | t01name03 |      5 | t02name05
      3 | t01name03 |      6 | t02name06
      3 | t01name03 |      7 | t02name07
      4 | t01name04 |      3 | t02name03
      4 | t01name04 |      4 | t02name04
      4 | t01name04 |      5 | t02name05
      4 | t01name04 |      6 | t02name06
      4 | t01name04 |      7 | t02name07
      5 | t01name05 |      3 | t02name03
      5 | t01name05 |      4 | t02name04
      5 | t01name05 |      5 | t02name05
      5 | t01name05 |      6 | t02name06
      5 | t01name05 |      7 | t02name07
(25 rows)

#top Database Objects Size¶

-- wyswietlenie informacji o wszystkich bazach danych oraz ich wielkości
SELECT datname, pg_catalog.pg_database_size(datname) AS pg_database_size FROM pg_catalog.pg_database;
-- wyswietlenie informacji o bazie danych oraz jej wielkości podanej jako argument: dbname
SELECT datname, pg_catalog.pg_database_size(datname) AS pg_database_size FROM pg_catalog.pg_database WHERE datname='dbname';
-- wyswietlenie informacji o wszystkich bazach danych posortowanych wedlug wielkosci od najmniejszej
SELECT datname, pg_catalog.pg_database_size(datname) AS pg_database_size FROM pg_catalog.pg_database ORDER BY pg_catalog.pg_database_size(datname);

-- wyswietlenie wszystkich baz danych oraz ich wielkości
SELECT datname, pg_catalog.pg_database_size(datname) AS pg_database_size FROM pg_catalog.pg_database;
-- wyswietlenie informacji o bazach danych oraz ich wielkosci i wielkosci w bardziej czytelnym formacie
SELECT datname, pg_catalog.pg_database_size(datname) AS pg_database_size, pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname)) AS pg_database_hrsize FROM pg_catalog.pg_database;

  datname  | pg_database_size
-----------+------------------
 template1 |          6005252
 template0 |          6005252
 postgres  |          6005252
 temp      |          6711300
(4 rows)

  datname  | pg_database_size | pg_database_hrsize
-----------+------------------+--------------------
 template1 |          6005252 | 5865 kB
 template0 |          6005252 | 5865 kB
 postgres  |          6005252 | 5865 kB
 temp      |          6711300 | 6554 kB
(4 rows)

SELECT datname AS Name, pg_catalog.pg_get_userbyid(datdba) AS Owner, CASE WHEN pg_catalog.has_database_privilege(datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname)) ELSE 'No Access' END AS SIZE FROM pg_catalog.pg_database ORDER BY CASE WHEN pg_catalog.has_database_privilege(datname, 'CONNECT') THEN pg_catalog.pg_database_size(datname) ELSE NULL END DESC LIMIT 20;

   name    |  owner   |  size
-----------+----------+---------
 temp      | temp     | 6554 kB
 template1 | postgres | 5865 kB
 template0 | postgres | 5865 kB
 postgres  | postgres | 5865 kB
(4 rows)

SELECT nspname AS "schemaname", relname AS "relation", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 'f' THEN 'foreign table' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast table' END as "type", c.relkind, pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 25;

 schemaname |            relation             |    type     | relkind |    size
------------+---------------------------------+-------------+---------+------------
 pg_toast   | pg_toast_2618                   | toast table | t       | 264 kB
 pg_toast   | pg_toast_2619                   | toast table | t       | 160 kB
 public     | net_temp_mailstat_queueid_idxh  | index       | i       | 32 kB
 public     | net_temp_mailstat_fromaddr_idxh | index       | i       | 32 kB
 public     | net_temp_mailstat_totoaddr_idxh | index       | i       | 32 kB
 public     | net_temp_mailstat_strdate_idxh  | index       | i       | 32 kB
 public     | words_fulltext_pkey             | index       | i       | 16 kB
 pg_toast   | pg_toast_2619_index             | index       | i       | 16 kB
 public     | temp_limit_pkey                 | index       | i       | 16 kB
 public     | words_fulltext_body_idxg        | index       | i       | 16 kB
 pg_toast   | pg_toast_2618_index             | index       | i       | 16 kB
 public     | words_fulltext                  | table       | r       | 8192 bytes
 pg_toast   | pg_toast_2604_index             | index       | i       | 8192 bytes
 pg_toast   | pg_toast_2606_index             | index       | i       | 8192 bytes
 pg_toast   | pg_toast_2620_index             | index       | i       | 8192 bytes
 pg_toast   | pg_toast_2609_index             | index       | i       | 8192 bytes
 pg_toast   | pg_toast_2964_index             | index       | i       | 8192 bytes
 pg_toast   | pg_toast_2396_index             | index       | i       | 8192 bytes
 pg_toast   | pg_toast_3596_index             | index       | i       | 8192 bytes
 pg_toast   | pg_toast_1255_index             | index       | i       | 8192 bytes
 pg_toast   | pg_toast_1262_index             | index       | i       | 8192 bytes
 pg_toast   | pg_toast_12302_index            | index       | i       | 8192 bytes
 pg_toast   | pg_toast_12307_index            | index       | i       | 8192 bytes
 pg_toast   | pg_toast_12317_index            | index       | i       | 8192 bytes
 pg_toast   | pg_toast_12322_index            | index       | i       | 8192 bytes
(25 rows)

SELECT nspname AS "schemaname", relname AS "relation", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 'f' THEN 'foreign table' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast table' END as "type", c.relkind, c.relkind, pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 25;
SELECT nspname AS "schemaname", relname AS "relation", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 'f' THEN 'foreign table' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast table' END as "type", c.relkind, pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname NOT LIKE 'pg_toast%' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 25;

 schemaname |            relation             |   type   | relkind | total_size
------------+---------------------------------+----------+---------+------------
 public     | net_temp_mailstat               | table    | r       | 144 kB
 public     | words_fulltext                  | table    | r       | 48 kB
 public     | net_temp_mailstat_queueid_idxh  | index    | i       | 32 kB
 public     | net_temp_mailstat_totoaddr_idxh | index    | i       | 32 kB
 public     | temp_limit                      | table    | r       | 32 kB
 public     | net_temp_mailstat_strdate_idxh  | index    | i       | 32 kB
 public     | net_temp_mailstat_fromaddr_idxh | index    | i       | 32 kB
 public     | temp_limit_pkey                 | index    | i       | 16 kB
 public     | words_fulltext_pkey             | index    | i       | 16 kB
 public     | words_fulltext_body_idxg        | index    | i       | 16 kB
 public     | word_fulltext_idword_seq        | sequence | S       | 8192 bytes
 public     | net_temp_mailstat_nowdate_idxb  | index    | i       | 8192 bytes
(12 rows)

#top Find Multiple Indexes¶

#top Find Duplicate Indexes¶

SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE, (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2, (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM ( SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY FROM pg_index ) sub
GROUP BY KEY HAVING COUNT(*)>1 ORDER BY SUM(pg_relation_size(idx)) DESC;

CREATE TABLE temp_multiidx (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);
CREATE INDEX temp_multiidx_idtemp_idxb ON temp_multiidx USING BTREE(idtemp);

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "temp_multiidx_pkey" for table "temp_multiidx"

 size  |           idx1            |        idx2        | idx3 | idx4
-------+---------------------------+--------------------+------+------
 16 kB | temp_multiidx_idtemp_idxb | temp_multiidx_pkey |      |
(1 row)

#top Find Unused Indexes¶

SELECT pg_stat_user_indexes.schemaname, pg_stat_user_indexes.relname AS indexname, indexrelname AS tablename, idx_scan, indisunique
FROM pg_stat_user_indexes JOIN pg_index ON (pg_stat_user_indexes.indexrelid=pg_index.indexrelid)
WHERE idx_scan = 0 AND indisunique IS false;

CREATE TABLE temp_unusedidx (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);
CREATE INDEX temp_unusedidx_idtemp_idxb ON temp_unusedidx USING BTREE(idtemp);
CREATE INDEX temp_unusedidx_name_idxb ON temp_unusedidx USING BTREE(name);
INSERT INTO temp_unusedidx(idtemp,name,value) VALUES(1,'name-01','value-01');
INSERT INTO temp_unusedidx(idtemp,name,value) VALUES(2,'name-02','value-02');
INSERT INTO temp_unusedidx(idtemp,name,value) VALUES(3,'name-03','value-03');
SELECT * FROM temp_unusedidx WHERE idtemp=1;

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "temp_unusedidx_pkey" for table "temp_unusedidx"

 schemaname |   tablename    |        indexname         | idx_scan | indisunique
------------+----------------+--------------------------+----------+-------------
 public     | temp_unusedidx | temp_unusedidx_name_idxb |        0 | f
(1 row)

#top get the data type of value¶

CREATE TYPE complex AS (
r double precision,
i double precision
);
CREATE TABLE datacolumntypes (
cnull varchar(16) NULL,
cbool boolean NOT NULL,
csint smallint NOT NULL,
cint integer NOT NULL,
cbint bigint NOT NULL,
cdec decimal NOT NULL,
cnum numeric NOT NULL,
creal real NOT NULL,
cdoublep double precision NOT NULL,
cserial serial NOT NULL,
cbserial bigserial NOT NULL,
cmoney money NOT NULL,
cchar char(16) NOT NULL,
cvchar varchar(255) NOT NULL,
cbytea bytea NOT NULL,
cts timestamp NOT NULL,
ctstz timestamp with time zone NOT NULL,
cdate date NOT NULL,
ctime time NOT NULL,
ctimetz time with time zone NOT NULL,
cival interval NOT NULL,
cinet inet NOT NULL,
ccidr cidr NOT NULL,
cmac macaddr NOT NULL,
cbit BIT(3) NOT NULL,
cvbit BIT VARYING(5) NOT NULL,
ciarray integer[][] NOT NULL,
ctarray text[][] NOT NULL,
ccomp complex NOT NULL
);

INSERT INTO datacolumntypes (cnull, cbool, csint, cint, cbint, cdec, cnum, creal, cdoublep, cserial, cbserial, cmoney, cchar, cvchar, cbytea,cts, ctstz, cdate, ctime, ctimetz, cival, cinet, ccidr, cmac, cbit, cvbit, ciarray, ctarray, ccomp)
VALUES (NULL, true, 1, 12, 123, 10, 10, 3.14, 3.14, 123, 123, 1234::text::money, 'textchar', 'textvarchar', 'textbytea', '2017/01/01 12:00:00', '2017/01/01 12:00:00', '2017/01/01', '12:00:00', '12:00:00', '1-2'::interval, '10.5.5.5', '10.5.5.5/32', '08:00:2b:01:02:03', B'100', B'101', '{{1,2},{3,4}}', '{{"123","456"},{"456","123"}}', ROW(1.2,2.4));

SELECT 'NULL' AS typename, pg_typeof(NULL) AS typeval;
SELECT 'true' AS typename, pg_typeof(true) AS typeval;
SELECT 'false' AS typename, pg_typeof(false) AS typeval;
SELECT '123' AS typename, pg_typeof(123) AS typeval;
SELECT '123.456' AS typename, pg_typeof(123.456) AS typeval;
SELECT now() AS typename, pg_typeof(now()) AS typeval;
SELECT current_timestamp AS typename, pg_typeof(current_timestamp) AS typeval;
SELECT current_date AS typename, pg_typeof(current_date) AS typeval;
SELECT current_time AS typename, pg_typeof(current_time) AS typeval;

SELECT 'cvchar' AS typename, pg_typeof('cvchar') AS typeval;
SELECT 'cvchar::varchar' AS typename, pg_typeof('cvchar'::varchar) AS typeval;
SELECT 'cvchar::text' AS typename, pg_typeof('cvchar'::text) AS typeval;
SELECT 'varchar(16)'              AS typename, pg_typeof(cnull)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'boolean'                  AS typename, pg_typeof(cbool)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'smallint'                 AS typename, pg_typeof(csint)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'integer'                  AS typename, pg_typeof(cint)     AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'bigint'                   AS typename, pg_typeof(cbint)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'decimal'                  AS typename, pg_typeof(cdec)     AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'numeric'                  AS typename, pg_typeof(cnum)     AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'real'                     AS typename, pg_typeof(creal)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'double precision'         AS typename, pg_typeof(cdoublep) AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'serial'                   AS typename, pg_typeof(cserial)  AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'bigserial'                AS typename, pg_typeof(cbserial) AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'money'                    AS typename, pg_typeof(cmoney)   AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'char(16)'                 AS typename, pg_typeof(cchar)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'varchar(255)'             AS typename, pg_typeof(cvchar)   AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'bytea'                    AS typename, pg_typeof(cbytea)   AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'timestamp'                AS typename, pg_typeof(cts)      AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'timestamp with time zone' AS typename, pg_typeof(ctstz)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'date'                     AS typename, pg_typeof(cdate)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'time'                     AS typename, pg_typeof(ctime)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'time with time zone'      AS typename, pg_typeof(ctimetz)  AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'interval'                 AS typename, pg_typeof(cival)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'inet'                     AS typename, pg_typeof(cinet)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'cidr'                     AS typename, pg_typeof(ccidr)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'macaddr'                  AS typename, pg_typeof(cmac)     AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'BIT(3)'                   AS typename, pg_typeof(cbit)     AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'BIT VARYING(5)'           AS typename, pg_typeof(cvbit)    AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'integer[][]'              AS typename, pg_typeof(ciarray)  AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'text[][]'                 AS typename, pg_typeof(ctarray)  AS typeval FROM datacolumntypes LIMIT 1;
SELECT 'complex'                  AS typename, pg_typeof(ccomp)    AS typeval FROM datacolumntypes LIMIT 1;

 typename | typeval
----------+---------
 NULL     | unknown
(1 wiersz)

 typename | typeval
----------+---------
 true     | boolean
(1 wiersz)

 typename | typeval
----------+---------
 false    | boolean
(1 wiersz)

 typename | typeval
----------+---------
 123      | integer
(1 wiersz)

 typename | typeval
----------+---------
 123.456  | numeric
(1 wiersz)

           typename            |         typeval
-------------------------------+--------------------------
 2017-11-15 22:09:08.609194+01 | timestamp with time zone
(1 wiersz)

           typename            |         typeval
-------------------------------+--------------------------
 2017-11-15 22:09:08.612194+01 | timestamp with time zone
(1 wiersz)

  typename  | typeval
------------+---------
 2017-11-15 | date
(1 wiersz)

      typename      |       typeval
--------------------+---------------------
 22:09:08.617194+01 | time with time zone
(1 wiersz)

 typename | typeval
----------+---------
 cvchar   | unknown
(1 wiersz)

    typename     |      typeval
-----------------+-------------------
 cvchar::varchar | character varying
(1 wiersz)

   typename   | typeval
--------------+---------
 cvchar::text | text
(1 wiersz)

  typename   |      typeval
-------------+-------------------
 varchar(16) | character varying
(1 wiersz)

 typename | typeval
----------+---------
 boolean  | boolean
(1 wiersz)

 typename | typeval
----------+----------
 smallint | smallint
(1 wiersz)

 typename | typeval
----------+---------
 integer  | integer
(1 wiersz)

 typename | typeval
----------+---------
 bigint   | bigint
(1 wiersz)

 typename | typeval
----------+---------
 decimal  | numeric
(1 wiersz)

 typename | typeval
----------+---------
 numeric  | numeric
(1 wiersz)

 typename | typeval
----------+---------
 real     | real
(1 wiersz)

     typename     |     typeval
------------------+------------------
 double precision | double precision
(1 wiersz)

 typename | typeval
----------+---------
 serial   | integer
(1 wiersz)

 typename  | typeval
-----------+---------
 bigserial | bigint
(1 wiersz)

 typename | typeval
----------+---------
 money    | money
(1 wiersz)

 typename |  typeval
----------+-----------
 char(16) | character
(1 wiersz)

   typename   |      typeval
--------------+-------------------
 varchar(255) | character varying
(1 wiersz)

 typename | typeval
----------+---------
 bytea    | bytea
(1 wiersz)

 typename  |           typeval
-----------+-----------------------------
 timestamp | timestamp without time zone
(1 wiersz)

         typename         |         typeval
--------------------------+--------------------------
 timestamp with time zone | timestamp with time zone
(1 wiersz)

 typename | typeval
----------+---------
 date     | date
(1 wiersz)

 typename |        typeval
----------+------------------------
 time     | time without time zone
(1 wiersz)

      typename       |       typeval
---------------------+---------------------
 time with time zone | time with time zone
(1 wiersz)

 typename | typeval
----------+----------
 interval | interval
(1 wiersz)

 typename | typeval
----------+---------
 inet     | inet
(1 wiersz)

 typename | typeval
----------+---------
 cidr     | cidr
(1 wiersz)

 typename | typeval
----------+---------
 macaddr  | macaddr
(1 wiersz)

 typename | typeval
----------+---------
 BIT(3)   | bit
(1 wiersz)

    typename    |   typeval
----------------+-------------
 BIT VARYING(5) | bit varying
(1 wiersz)

  typename   |  typeval
-------------+-----------
 integer[][] | integer[]
(1 wiersz)

 typename | typeval
----------+---------
 text[][] | text[]
(1 wiersz)

 typename | typeval
----------+---------
 complex  | complex
(1 wiersz)

#top Load Balancing with HAProxy¶

#top TLS SNI¶

#top SNI config¶

#top SNI check¶

#top Protocol Secure¶

#top Remove Service Version Information¶

#top Add HTTP Response Headers Security¶

#top TLS Secure¶

#top Disable SSLv2/SSLv3 Protocols¶

#top Disable weak Cipher Suites¶

#top Disable RC4 CipherSuite¶

#top Disable Anonymous CipherSuite¶

#top Disable SSL Compression¶

export OPENSSL_NO_DEFAULT_ZLIB=1

#top Set custom DH parameters¶

#top Avoid certificates with Signature Algorithm: SHA1¶

#top Performance Tuning¶

#top SYSVIPC¶

# used for managing connection, active operations
# should be 1/4 of the available memory
shared_buffers = 1024MB

# dedicated memory for each operation. Used basically for sorting
# should be available_memory/max_connections for normal operations. Max
# available_memory/(2*max_connections)
# another way of getting this number is using the EXPLAIN ANALYZE query. If
# the plan shows "sort method: external merge disk: 7532kb", then work_mem of
# 8Mb can do wonders.
work_mem = 1MB

# same as work_mem but for vaccum, alter, other ddl qry.
# should be around 256MB
maintenance_work_mem = 256MB

# size of write ahead log files
# default 8 KB. 1 MB is enough for large systems.
# SMP machines are better with 8 MB
wal_buffers = 1MB

# After every transaction, pgsql forces a commit to disk out its write-ahead log.
# defaults to fsync.
# generally switched to open_sync, but it is buggy on many platforms.
# Should be benchmarked with very heavy query, before switching.
wal_sync_method = fsync

# estimate of how much memory is available for disk caching by the OS and
# within the DB itself
# recomended to 1/2 of available memory. On unix can be set to free+cached
# from "free".
effective_cache_size = 512MB

# by default 3*16MB per segment = 48 MB. Can be resource intensive on modern systems.
# setting it to 32 - checkpoint every 512 MB can be effective in reducing disk io
checkpoint_segments = 32

# checkpoint occurs every 5 minutes. can be increased
checkpoint_timeout = 5min

# should be increased if you want to collect a lot of information for helping
# pgsql to create query plans
default_statistics_target=100

# Synchronous commit introduced in pgsql 8.3 allows a small amount of data
# loss (in case of failure) for large boost in number of updates on the database per second.
synchronous_commit=on

# If after tweaking every variable, your query execution plan still is not acceptable,
# you can bring down the random page cost to 3.0 or 2.0.
# setting this variable lower will encourage the query optimizer to use random access index scans.
random_page_cost=4.0