CREATE STATISTICS flights_from_to (dependencies, MCV)
    ON departure_airport, arrival_airport
    FROM flights;
\x
\dX

List of extended statistics
-[ RECORD 1 ]+------------------------------------------------
Schema       | bookings
Name         | flights_from_to
Definition   | departure_airport, arrival_airport FROM flights
Ndistinct    |
Dependencies | defined
MCV          | defined

\dtS pg_toast.*165*

List of relations
  Schema  |      Name      |    Type     |  Owner
----------+----------------+-------------+----------
pg_toast | pg_toast_16529 | TOAST table | postgres
pg_toast | pg_toast_16539 | TOAST table | postgres
pg_toast | pg_toast_16580 | TOAST table | postgres

SET idle_session_timeout = '1000ms';
-- пауза
\! tail -n 1 logfile

2021-02-01 12:25:06.716 MSK [5262] FATAL:  terminating connection due to idle-session timeout

SHOW idle_session_timeout;

FATAL:  terminating connection due to idle-session timeout
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

SHOW idle_session_timeout;

idle_session_timeout
----------------------
0

CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;

NOTICE:  relation "tickets" already exists, skipping

EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;

QUERY PLAN
-----------------------------------------------------------------
Seq Scan on bookings  (cost=0.00..4301.88 rows=262788 width=21)

EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;

ERROR:  relation "tickets" already exists

EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;

NOTICE:  relation "tickets" already exists, skipping
QUERY PLAN
------------
(0 rows)

EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;

NOTICE:  relation "tickets" already exists, skipping
QUERY PLAN
------------
(0 rows)

\d pg_class

Table "pg_catalog.pg_class"
       Column        |     Type     | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
oid                 | oid          |           | not null |
relname             | name         |           | not null |
relnamespace        | oid          |           | not null |
...пропущено ...
Indexes:
    "pg_class_oid_index" PRIMARY KEY, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

SELECT fktable, fkcols, is_array, is_opt
FROM   pg_get_catalog_foreign_keys()
WHERE  pktable = 'pg_attribute'::regclass
AND    pkcols = ARRAY['attrelid','attnum'];

fktable        |        fkcols         | is_array | is_opt
----------------------+-----------------------+----------+--------
pg_attrdef           | {adrelid,adnum}       | f        | f
pg_constraint        | {conrelid,conkey}     | t        | t
pg_constraint        | {confrelid,confkey}   | t        | f
pg_index             | {indrelid,indkey}     | t        | t
pg_statistic_ext     | {stxrelid,stxkeys}    | t        | f
pg_statistic         | {starelid,staattnum}  | f        | f
pg_trigger           | {tgrelid,tgattr}      | t        | f
pg_partitioned_table | {partrelid,partattrs} | t        | t
(8 rows)

ALTER SYSTEM SET log_recovery_conflict_waits = on;
SELECT pg_reload_conf();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;

DELETE FROM t;
VACUUM t;

LOG:  recovery still waiting after 1023.267 ms: recovery conflict on snapshot
DETAIL:  Conflicting process: 29119.
CONTEXT:  WAL redo at 0/1001BEB0 for Heap2/CLEAN: latestRemovedXid 717; blkref #0: rel 1663/16384/17198, blk 0

SELECT now(), pg_stat_statements_reset();

now              | pg_stat_statements_reset
-------------------------------+--------------------------
2021-02-03 13:25:44.738188+03 |

SELECT * FROM pg_stat_statements_info;

dealloc |          stats_reset
---------+-------------------------------
       0 | 2021-02-03 13:25:44.738468+03

\! pg_dump -d demo -Fc -f demo.dump

\! pg_restore tickets.dump -d postgres -j 2 &
SELECT pid, datname, relid::regclass, bytes_processed, bytes_total, lines_processed
FROM   pg_stat_progress_copy\gx

-[ RECORD 1 ]---+-------------------------
pid             | 18771
datname         | postgres
relid           | bookings.tickets
bytes_processed | 19088527
bytes_total     | 0
lines_processed | 189820
-[ RECORD 2 ]---+-------------------------
pid             | 18772
datname         | postgres
relid           | bookings.boarding_passes
bytes_processed | 14833287
bytes_total     | 0
lines_processed | 567652

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (dbname 'postgres');
CREATE USER MAPPING FOR postgres
    SERVER remote_server
    OPTIONS (user 'postgres');

postgres=# CREATE TABLE bookings(
    book_ref char(6),book_date timestamptz, total_amount numeric(10,2)
);

CREATE TABLE bookings_local (LIKE bookings);
\timing

INSERT INTO bookings_local SELECT * FROM bookings;

INSERT 0 262788
Time: 165,653 ms

CREATE FOREIGN TABLE bookings_remote_no_batch (
    book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
    schema_name 'public', table_name 'bookings'
);
INSERT INTO bookings_remote_no_batch SELECT * FROM bookings;

INSERT 0 262788
Time: 6729,867 ms (00:06,730)

CREATE FOREIGN TABLE bookings_remote_batch_100 (
    book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
    schema_name 'public', table_name 'bookings',
    batch_size '100'
);
INSERT INTO bookings_remote_batch_100 SELECT * FROM bookings;

INSERT 0 262788
Time: 679,632 ms

CREATE TABLE t(col1 int, col2 int) WITH (autovacuum_enabled=off);
CREATE INDEX t_col1 ON t(col1);
CREATE INDEX t_col2 ON t(col2);
INSERT INTO t VALUES (1, 1);
SELECT pg_relation_size('t') AS t_size,
       pg_relation_size('t_col1') AS t_col1_size,
       pg_relation_size('t_col2') AS t_col2_size;

t_size | t_col1_size | t_col2_size
--------+-------------+-------------
   8192 |       16384 |       16384

SELECT 'UPDATE t SET col2 = col2+1' FROM generate_series(1,100000)\gexec
SELECT pg_relation_size('t') AS t_size,
       pg_relation_size('t_col1') AS t_col1_size,
       pg_relation_size('t_col2') AS t_col2_size;

t_size  | t_col1_size | t_col2_size
---------+-------------+-------------
2818048 |     2121728 |     2260992

t_size  | t_col1_size | t_col2_size
---------+-------------+-------------
2818048 |      663552 |     2260992

t_size  | t_col1_size | t_col2_size
---------+-------------+-------------
2818048 |       16384 |     2260992

DO $$
<<local>>
DECLARE
    a bookings[];
    x bookings;
BEGIN
    /* Присвоение элементу массива составного типа */
    local.a[1].book_ref := 'ABCDEF';
    local.a[1].book_date := current_date;
    local.a[1].total_amount := 0;
    /* Присвоение срезу массива */
    local.a[2:3] := (SELECT array_agg(t.*)
                        FROM  (SELECT b.* FROM bookings b LIMIT 2) AS t
                       );
    FOREACH x IN ARRAY a LOOP
            RAISE NOTICE '%', x;
    END LOOP;
END;
$$;

NOTICE:  (ABCDEF,"2021-02-04 00:00:00+03",0.00)
NOTICE:  (00000F,"2017-07-05 03:12:00+03",265700.00)
NOTICE:  (000012,"2017-07-14 09:02:00+03",37900.00)
DO

DO $$ BEGIN RAISE NOTICE '%', 2 + 'a'; END; $$;

ERROR:  invalid input syntax for type integer: "a"
LINE 1: 2 + 'a'
            ^
QUERY:  2 + 'a'
CONTEXT:  PL/pgSQL function inline_code_block line 1 at RAISE

CREATE STATISTICS s ON departure_airport, arrival_airport FROM flights;
ANALYZE flights;

EXPLAIN SELECT *
FROM   flights
WHERE (departure_airport = 'SVO' AND arrival_airport = 'LED')
OR    (departure_airport = 'LED' AND arrival_airport = 'SVO');

Seq Scan on flights  (cost=0.00..1054.42 rows=885 width=63)

Seq Scan on flights  (cost=0.00..1054.42 rows=607 width=63)

SELECT (ARRAY[10,20,30,40,50])[3];

array
-------
    30

SELECT contact_data, contact_data['phone'] AS phone
FROM   tickets
WHERE  ticket_no = '0005432000994'\gx

-[ RECORD 1 ]+-----------------------------------------------------------------------------------------------------------------------------------------------------------
contact_data | {"email": "antonova.irina04121972@postgrespro.ru", "phone": "+70844502960"}
phone        | "+70844502960"

UPDATE tickets
SET    contact_data['address'] =
           '{"city": "Москва",
             "street": "Дмитрия Ульянова",
             "building": "7А"
            }'::jsonb
WHERE ticket_no = '0005432000994';

SELECT contact_data['address'] AS address,
       contact_data['address']['city'] AS city,
       contact_data['address']['street'] AS street,
       contact_data['address']['building'] AS building,
       contact_data['phone'] AS phone,
       contact_data['email'] AS email
FROM   tickets
WHERE  ticket_no = '0005432000994'\gx

-[ RECORD 1 ]----------------------------------------------------------------
address  | {"city": "Москва", "street": "Дмитрия Ульянова", "building": "7А"}
city     | "Москва"
street   | "Дмитрия Ульянова"
building | "7А"
phone    | "+70844502960"
email    | "antonova.irina04121972@postgrespro.ru"

CREATE TABLE pg_commitfest (
    version text,
    working_period tstzmultirange
);

INSERT INTO pg_commitfest VALUES
('13', tstzmultirange(
           tstzrange('2019-07-01', '2019-08-01', '[)'),
           tstzrange('2019-09-01', '2019-10-01', '[)'),
           tstzrange('2019-11-01', '2019-12-01', '[)'),
           tstzrange('2020-01-01', '2020-02-01', '[)'),
           tstzrange('2020-03-01', '2020-04-07', '[]')
       )
),
('14', tstzmultirange(
           tstzrange('2020-07-01', '2020-08-01', '[)'),
           tstzrange('2020-09-01', '2020-10-01', '[)'),
           tstzrange('2020-11-01', '2020-12-01', '[)'),
           tstzrange('2021-01-01', '2021-02-01', '[)'),
           tstzrange('2021-03-01', '2021-04-01', '[)')
       )
);

SELECT version
FROM   pg_commitfest
WHERE  working_period @> '2021-01-01'::timestamptz;

version
---------
14

SELECT lower(working_period), upper(working_period)
FROM   pg_commitfest
WHERE  version = '13';

lower          |         upper
------------------------+------------------------
2019-07-01 00:00:00+03 | 2020-04-07 00:00:00+03

CREATE FUNCTION time_diff(a time, b time) RETURNS double precision
AS $$
    SELECT extract(epoch FROM (a - b));
$$ LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_diff,
    multirange_type_name = timemultirange
);

SELECT timemultirange(
           timerange('09:00', '13:00', '[)'),
           timerange('14:00', '18:00', '[)')
       ) AS working_hours;

working_hours
-------------------------------------------
{[09:00:00,13:00:00),[14:00:00,18:00:00)}

GRANT ALL ON TABLE table_name TO role_specification
    [GRANTED BY role_specification];
REVOKE ALL ON TABLE table_name FROM role_specification
    [GRANTED BY role_specification];

Success. You can now start the database server using:
    pg_ctl -D /usr/local/pgsql/data -l logfile start

CREATE TABLE t (id int) PARTITION BY RANGE(id);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES FROM (1) TO (1001);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES FROM (1001) TO (2001);
\! pg_dump -Fc -f db.dump
\! pg_restore db.dump -t t_p1 -f -

...
CREATE TABLE public.t_p1 (
    id integer
);
ALTER TABLE ONLY public.t ATTACH PARTITION public.t_p1 FOR VALUES FROM (1) TO (1001);
...