#postgres #tuning

Отличный рассказ про тюнинг параметров СУБД.

https://www.youtube.com/watch?v=IFIXpm73qtk&ab_channel=HighLoadChannel

#postgres #db

А тем временим готовится к релизу Постгре 16.

Из интересного:

Allow parallelization of FULL and internal right OUTER hash joins (Melanie Plageman, Thomas Munro)
Allow optimization of always-increasing window functions ntile(), cume_dist() and percent_rank() (David Rowley)

Add system view pg_stat_io view to track IO statistics (Melanie Plageman)
Record statistics on the last sequential and index scans on tables (Dave Page) This information appears in pg_stat_all_tables and pg_stat_all_indexes.

Create a predefined role and grantable privilege with permission to perform maintenance operations (Nathan Bossart) The predefined role is is called pg_maintain.
Add predefined role pg_create_subscription with permission to create subscriptions (Robert Haas)

Add support for regular expression matching on database and role entries in pg_hba.conf (Bertrand Drouvot)

Allow NUMERIC to process hexadecimal, octal, and binary integers of any size (Dean Rasheed) Previously only unquoted eight-byte integers were supported with these non-decimal bases.
Allow underscores in integer and numeric constants (Peter Eisentraut, Dean Rasheed) This can improve readability for long strings of digits.
Accept the spelling "+infinity" in datetime input (Vik Fearing)

Allow parallel application of logical replication (Hou Zhijie, Wang Wei, Amit Kapila)

Allow the STORAGE type to be specified by CREATE TABLE (Teodor Sigaev, Aleksander Alekseev) Previously only ALTER TABLE could control this

Allow subqueries in the FROM clause to omit aliases (Dean Rasheed)

Change date_trunc(unit, timestamptz, time_zone) to be an immutable function (Przemyslaw Sztoch) This allows the creation of expression indexes using this function.
Add functions array_sample() and array_shuffle() (Martin Kalcher)
Add aggregate function ANY_VALUE() which returns any value from a set (Vik Fearing)
Add function random_normal() to supply normally-distributed random numbers (Paul Ramsey)
Add error function erf() and its complement erfc() (Dean Rasheed)

Add libpq option sslcertmode to control transmission of the client certificate (Jacob Champion) The option values are "disable", "allow", and "require".

Add LZ4 and Zstandard compression to pg_dump (Georgios Kokolatos, Justin Pryzby)
Allow pg_dump and pg_basebackup to use "long" mode for compression (Justin Pryzby)

Add support for SSE2 (Streaming SIMD Extensions 2) vector operations on x86-64 architectures (John Naylor) вот это странно, этой поддержки не было, чтоль?
Add support for Advanced SIMD (Single Instruction Multiple Data) (NEON) instructions on ARM architectures (Nathan Bossart)

Require Windows 10 or newer versions (Michael Paquier, Juan José Santamaría Flecha) Previously Windows Vista and Windows XP were supported. Вот это вряд ли можно считать улучшением.


https://www.postgresql.org/docs/16/release-16.html

#databases #postgres

А тем временем вышла 17-я постгре! Много улучшений. Я не понимаю, как у них поднимается рука релизить заведомо слабые решения, а потом течение лет и десятилетий их постепенно допиливать, но хоть так.

https://www.postgresql.org/about/news/postgresql-17-beta-1-released-2865/

#postgres #databases

Темпоральный уникальный ключ - крутая идея! Как и триггер ON LOGIN.

https://www.youtube.com/watch?v=peLXtGorl8A