Software-update: PostgreSQL 17 bèta 1

De eerste bètarelease van PostgreSQL versie 17 is verschenen. Dit populaire 'opensource relational database management system' draait op een groot aantal besturingssystemen en is daardoor uitstekend inzetbaar in diverse omgevingen. Het is een afgeleide van Ingres, nadat de hoofdontwikkelaar daarvan voor zichzelf is begonnen en deze database van opensource closedsource werd. Uitgebreide releasenotes voor deze uitgave zijn op deze pagina te vinden; dit zijn de belangrijkste veranderingen die we in versie 17 kunnen verwachten:

Query and Operational Performance Improvements

PostgreSQL 17 builds on recent releases and continues to improve performance across the entire system. Vacuum, the PostgreSQL process responsible for reclaiming storage, has a new internal data structure that has shown up to a 20x memory reduction, along with improvements in overall time to complete its work. Additionally, the vacuum process no longer has a 1GB limit on the memory it can use (controlled by maintenance_work_mem), giving you the option to apply more resources to vacuuming.

This release introduces an interface to stream I/O, and can show performance improvements when performing sequential scans and running ANALYZE. PostgreSQL 17 also includes configuration parameters that can control scalability of transaction, subtransaction and multixact buffers.

PostgreSQL 17 can now use both planner statistics and the sort order of common table expressions (WITH queries) to further optimize these queries and help them to execute more quickly. Additionally, this release significantly improves execution time of queries that use the IN clause with a B-tree index. Starting with this release, PostgreSQL can remove redundant IS NOT NULL statements from execution on columns that have a NOT NULL constraint, and no longer needs to do work on queries that contain an IS NULL clause on an IS NOT NULL column. Now as of PostgreSQL 17, you can use parallel index builds for BRIN indexes.

Workloads with highly concurrent changes can benefit from PostgreSQL 17 with an improvement to how write-ahead log (WAL) locks are managed, with some tests showing up to a 2x performance improvement. Finally, PostgreSQL 17 adds more explicit SIMD instructions, including AVX-512 support for the bit_count function.

Partitioned and distributed workloads enhancements

PostgreSQL 17 brings more flexibility to partition management, adding the ability to both split and merge partitions, and adds support to partitioned tables for identity columns and exclusion constraints. Additionally, the PostgreSQL foreign data wrapper (postgres_fdw) can realize performance benefits from queries with EXISTS and IN subqueries as these can now be pushed down to the remote server.

PostgreSQL 17 adds new capabilities to logical replication that make it simpler to use it in high availability workloads and on upgrades. Starting with upgrades from PostgreSQL 17 to newer versions, you no longer need to drop logical replication slots when using pg_upgrade, allowing you to avoid resynchronizing data after an upgrade. Additionally, this release introduces failover control for logical replication, providing more control for managing PostgreSQL databases in high availability environments. PostgreSQL 17 also lets logical replication subscribers use hash indexes for lookups, and introduces the pg_createsubscriber command-line tool for adding logical replication on a replica using physical replication.

Developer Experience

PostgreSQL 17 continues to build on the SQL/JSON standard, adding support for the JSON_TABLE features that can convert JSON to a standard PostgreSQL table, and SQL/JSON constructor (JSON, JSON_SCALAR, JSON_SERIALIZE) and query functions (JSON_EXISTS, JSON_QUERY, JSON_VALUE). Notably, these features were originally planned for the PostgreSQL 15 release but were reverted during the beta period due to design considerations, which is one reason we ask for you to help us test features during beta! Additionally, PostgreSQL 17 adds more functionality to its jsonpath implementation, and the ability to convert JSON values to different data types.

The MERGE command now supports the RETURNING clause, letting you further work with modified rows. You can also see what part of the MERGE command modified the row using the new merge_action function. PostgreSQL 17 also lets you update views using the MERGE command, and adds a WHEN NOT MATCHED BY SOURCE clause to let you provide behavior if a source row doesn't meet the conditions.

COPY is used to efficiently bulk load and export data from PostgreSQL, and now with PostgreSQL 17 you may see up to a 2x performance improvement when exporting large rows. Additionally, COPY has improved performance when the source encoding matches the destination encoding, and has a new option, ON_ERROR, that lets the copy proceed even if there is an error inserting a row. PostgreSQL 17 also provides better support for asynchronous and more secure query cancellation routines, which drivers can adopt using the libpq API.

PostgreSQL 17 includes a built-in collation provider that provides similar sorting semantics to the C collation except with UTF-8 encoding rather than SQL_ASCII. This new collation is guaranteed to be immutable, ensuring that the return values of your sorts won't change regardless of what system your PostgreSQL installation runs on.

Security Features

PostgreSQL 17 adds a new connection parameter, sslnegotiation, which allows PostgreSQL to perform direct TLS handshakes when using ALPN, eliminating a network roundtrip. PostgreSQL is registered as postgresql in the ALPN directory. This release introduces event triggers that execute during authentication, and in libpq includes a new API called PQchangePassword that automatically hashes passwords on the client-side to prevent accidental plaintext logging in the server.

PostgreSQL 17 adds a new predefined role called pg_maintain that gives users permission to execute VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX, and LOCK TABLE on all relations. This release also ensures the search_path is safe for maintenance operations like VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW and INDEX. Finally, users can now use ALTER SYSTEM to set the values for unrecognized custom server variables.

Backup and Export Management

PostgreSQL 17 introduces the ability to perform incremental backups using pg_basebackup, and adds a new utility called pg_combinebackup that's used as part of the backup restoration process. This release adds a new flag to pg_dump called --filter, letting you specify a file that contains instructions on what objects to include/exclude from a dump.

Monitoring

The EXPLAIN command, which provides information about query plans and execution, adds two new options: SERIALIZE, which shows the amount of time it takes to convert data for network transmission, and MEMORY, which reports optimizer memory usage. Additionally, EXPLAIN can now show how much time is spent for I/O block reads and writes.

PostgreSQL 17 normalizes the parameters for CALL in pg_stat_statements, reducing the number of entries for frequently called stored procedures. Additionally, VACUUM progress reporting now shows the progress of vacuuming indexes. PostgreSQL 17 also introduces a new view, pg_wait_events, which provides descriptions about wait events and can be combined with pg_stat_activity to give more insight into why an active session is waiting. Additionally, some information in the pg_stat_bgwriter view is now split out into the new pg_stat_checkpointer view.

Versienummer	17 bèta 1
Releasestatus	Final
Besturingssystemen	Linux, BSD, macOS, Solaris, Windows 10, Windows Server 2016, Windows Server 2019, Windows 11
Website	PostgreSQL
Download	https://www.postgresql.org/download/
Licentietype	Voorwaarden (GNU/BSD/etc.)

Reacties (20)

[The Third Man]

PostgreSQL 17 continues to build on the SQL/JSON standard, adding support for the JSON_TABLE features that can convert JSON to a standard PostgreSQL table, and SQL/JSON constructor (JSON, JSON_SCALAR, JSON_SERIALIZE) and query functions (JSON_EXISTS, JSON_QUERY, JSON_VALUE).

Dit klinkt als een uitgelezen optimalisatie voor API frameworks zodat je de DBMS direct de JSON I/O kan laten verwerken?

[rvt1]

Ja, maar ik denk niet dat je dat echt wilt, in iedergeval kan ik mij niet voorstellen welke usecase hier achter kan zitten, misschien public API’s, weer verspreiden of andere zaken ?

[The Third Man]

Ik dev zelf veel met Django en FastAPI icm Postgres, waarbij sommige API calls soms alleen maar als doorgeefluik werken, vooral met REST flows. Je kan met een dergelijke JSON-feature-set nog meer processing verplaatsen, ik zie niet waarom je dat niet 'echt wilt'?

Django implementeert sinds 5.0 ook bijvoorbeeld db-generated fields: stel je hebt een lengte en een breedte in een record maar wil de oppervlakte ook aanbieden, dan moest je eerst backend side (dus in Python) lengte*breedte doen, of met een annotation in je query. Daardoor moet je die logic maintainen en testen bij elke flow die het toepast. Db-generated betekent dat het DBMS het doet en dat elke user van de database, niet implementatie-afhankelijk, record.area kan gebruiken en de logic op 1 plek zit. Ook handig bij formating, dus aanhef + voornaam + achternaam etc kan je db-side op 1 plek implementeren zodat niet de ene user Hr Naam Achternaam doet en de ander weer N. Achternaam, maar gewoon record.full_name kan doorgeven.

[bzzzt]

Als je data via een API aanbiedt die business logica en toegangscontrole implementeert zou alle toegang via die API moeten lopen. Je loopt ook het risico dat bij complexe berekeningen iedere query op die tabel een per row overhead krijgt voor het berekenen van die waarde, ook als die data niet per se noodzakelijk is.

[The Third Man]

Als je data via een API aanbiedt die business logica en toegangscontrole implementeert zou alle toegang via die API moeten lopen.

Ik zie niet hoe dat anders zou zijn als Postgres doet wat mijn REST view nu in Python doet? Nu heb ik dat /pk de specifieke record opzoekt, dat naar het ORM vertaalt en dat wordt met orjson naar JSON geserializeerd, twee extra stappen. Met Postgres directe JSON_SERIALIZE hoeft het framework die alleen simpelweg door te geven en verdwijnt er dus overhead. Met name handig in async context, want db queries zijn nu goed async te draaien, maar CPU-bound acties als JSON serialisatie niet zo.

Je loopt ook het risico dat bij complexe berekeningen iedere query op die tabel een per row overhead krijgt voor het berekenen van die waarde, ook als die data niet per se noodzakelijk is.

Dat klopt niet, alleen de queries die dan wel het field opvragen of de wildcard gebruiken triggeren de actie, dat betekent inherent dat je impliceert dat je de data noodzakelijk acht (anders query je wel de specifiekere fields). Het klopt wel dat achteloos op de bonnefooi een select * draaien CPU intensiever wordt, maar dat dat performance implicaties heeft is niets nieuws (dat heb je ook met een view om maar wat te noemen).

[bzzzt]

[...]
Ik zie niet hoe dat anders zou zijn als Postgres doet wat mijn REST view nu in Python doet? Nu heb ik dat /pk de specifieke record opzoekt, dat naar het ORM vertaalt en dat wordt met orjson naar JSON geserializeerd, twee extra stappen.

Functioneel valt het op beide manieren op te lossen, maar er bestaat ook nog iets als 'architectuur' en 'single responsibility principe'. Als je gaat uitbouwen kan het heel onhandig zijn dat je zowel je python backend als alle andere software die direct in de database plugt moet gaan aanpassen (feitelijk wordt daarmee je database onderdeel van jouw API). Voor een hobbyist of klein bedrijf geen probleem, maar binnen een context waar tientallen of honderden mensen werken aan meerdere projecten wil je duidelijkheid over punten waar gekoppeld wordt.

[The Third Man]

Maar dat we juist de db zoveel mogelijk processing laten doen is volgens dat single responsibility principe. In plaats van dat men het in 3 verschillende Python en evt andere codeimplementaties doet die net even anders kunnen zijn.

[aikebah]

Dat is niet het single responsibility principe.

Je database krijgt heel veel businesslogica van allerlei domeinen en wordt dus de grote 'god class' die alles kan.

[cariolive23]

Jouw database bevat altijd heel veel business logica, tenzij je geen database wilt gebruiken. Een datatype is business logica, een unique constraint is business logica, etc.

Maar bijvoorbeeld nieuwe prijzen berekenen voor een miljoen producten door alle inkoopprijzen te vermenigvuldigen met een marge en dan afronden op 4.95 en 9.95, zo'n opdracht kun je gewoon door de database laten uitvoeren. Daarvoor hoef je echt niet al jouw data eerst over te halen naar de applicatie, dan de nieuwe prijs te berekenen in de applicatie, en dan een update statement terug te sturen naar de database. Tenzij jij wilt bereiken dat zo'n proces uren gaat duren, want dat gaat wel lukken met deze aanpak.

Wij komen dagelijks problemen tegen met data validatie. Bijvoorbeeld bedragen die als varchar worden opgeslagen en valuta codes als USD, dollar of $. En dan 100.00 of 100,00 of 100 of 10000 of 100,- en dat zou allemaal hetzelfde moeten zijn. De database mag geen business logica bevatten, maar zodra de shit de fan heeft geraakt, wordt van de database verwacht dat deze wel even het juiste antwoord geeft. Ik denk dat 9 van de 10 projecten die wij doen, dat wij eerst de data moeten opschonen en requirements schrijven voor de diverse applicaties om te voorkomen dat deze nog meer shit in de database gaan wegschrijven.

100% van de business logica implementeren in vele verschillende applicaties en data jaar in jaar uit op een goede manier volhouden, is imho een kansloze zaak. Data wil je minimaal valideren tegen de data types, maar een bruikbare foutmelding voor de gebruiker, wil je door de applicatie laten presenteren. De database steekt gewoon een middelvingertjes op wanneer "hundred" geen valide waarde is voor een numeriek veld:
ERROR: invalid input syntax for type numeric: "hundred". Zonder business logica in de database, is ook dit "valide" data. Dat je dan later in je rapportages problemen krijgt, dat weet je van te voren al.

De eerste relationele databases kwamen eind jaren '70 op de markt en zijn nu dus een kleine 50 jaar geoptimaliseerd voor gigantische workloads. De kans dat je in jouw eigen applicatie in een paar weken tijd een soortgelijke performance haalt, is gewoon nihil. Je bent dan in je eentje of met een klein team het wiel opnieuw aan het uitvinden en dat wordt geen succes. Leer een beetje SQL en lees een handleiding, en je komt heel veel verder.

[Barsonax]

Het gaat niet om performance maar om een goede architectuur die makkelijk aanpasbaar blijft. Dat doe je toch echt liever in een taal als python of C# ipv SQL.

Dat er soms uitzonderingen zijn waar die performance zo belangrijk wordt dat je dat in de database oplost is dan (hopelijk) een bewuste afweging die je maakt (en je blijkbaar geen enkele andere mogelijkheden meer hebt?).

Meestal is het juist de db die het moeilijk heeft is mijn ervaring trouwens.

[Reactie gewijzigd door Barsonax op 22 juli 2024 20:44]

[cariolive23]

Meestal is het juist de db die het moeilijk heeft is mijn ervaring trouwens.

Mijn ervaring is dat de oorzaak van die moeilijkheden vaak achter de computer zit en probeert om bestaande database functionaliteit te herbouwen in de eigen applicatie.

Klanten van ons verwerken tot een paar honderd miljoen events per dag, in één enkele PostgreSQL database. Wel gerepliceerd vanwege failover eisen, maar niet eens voor het offloaden van database reads. Hun applicaties valideren en verrijken de klantdata, stoppen dat in een json en gooien dat in een database functie. Die functie pakt de diverse onderdelen uit de json op, stopt het in de juiste tabellen en prepareert de data voor de volgende stappen in het proces. En dat alles binnen enkele milliseconden. Zodra de applicaties iets willen weten over de data, kunnen ze dat opvragen en is het resultaat ook weer binnen milliseconden beschikbaar. En ook (lees: altijd) met complexe queries. Snelheid en flexibiliteit zijn non-existing problems. Wanneer wij een nieuwe versie uitleveren en aanpassingen in het data model hebben gedaan, kan dat worden uitgerold zonder dat de klant iets aan zijn software hoeft aan te passen.

En dat sinds 2010, toen we de eerste versie hebben uitgebracht. Inmiddels doen we ook machine learning in de database. Het trainen van modellen doen we buiten de database, maar het scoren van de modellen met de nieuwe data, dat doen we in de database. De applicaties van onze klanten zijn hiervoor niet aangepast, ipv de oude beslisbomen en de door hen geconfigureerde if-then-else structuren, beschikten ze ook ineens over AI functionaliteit. Ook in COBOL, waar een klant van ons nog steeds gebruik van maakt.

Je brengt de data naar de logica of de logica naar de data, één van beide. En even een paar TB aan data vanuit de database naar de applicatie overhalen, uitpluizen en een ms later antwoord geven, is niet realistisch, je netwerk is niet snel genoeg. Met dezelfde logica in de database, is dat ineens wel realistisch.

Een goede architectuur is niet afhankelijk van een taal, maar van het doel. En een taal is géén doel.

[Reactie gewijzigd door cariolive23 op 22 juli 2024 20:44]

[Barsonax]

Klinkt eerder alsof je een afwijkende situatie hebt met oa zoveel events en een shared database die door meerdere applicaties wordt gebruikt.

Vraag mij wel af hoe complex is de business logica dan of valt dat wel mee? Doen jullie aan automated testing? Versie controle? Hoe gaat het met onboarding van nieuwe mensen? Opzich geen dingen die ineens onmogelijk zijn maar wel benieuwd hoe jullie dat hebben aangepakt.

[cariolive23]

Klopt, onze applicatie (die voor 90% uit de database bestaat) wordt door meerdere applicaties bij de klant gebruikt. Zijn meestal een 10 tot 20 verschillende applicaties, beetje afhankelijk van hoe zij hun zaken hebben georganiseerd.

Hoe complex het is, daar vraag je naar een mening... Ik vind het niet complex, maar klanten kopen onze oplossing juist omdat het heel complex voor hen is.

Testen is volledig geautomatiseerd, niets gaat handmatig. Version control is standard, en zijn er nog mensen/organisaties die nog zonder werken? Nieuwe medewerkers worden geselecteerd op hun kennis en ervaring met SQL. Alleen junior medewerkers voor wie het hun eerste baan is, worden geaccepteerd zonder ervaring.

Je bent volkomen kansloos in ons werk wanneer je niet met SQL uit de voeten kan. Wanneer klanten hun data naar ons systeem overpompen, moet dat uit een database komen. Dus daar is SQL voor nodig en daar moeten wij vrijwel altijd bij helpen.

[knabbelaar]

Naast de architectuur-argumentatie (business logica op één plaats, ...) wil je dit vaak ook niet omwille van performantie / schaalbaarheid.
Webservers / middleware servers zijn normaal gezien stateless en triviaal horizontaal schaalbaar.
De database server is vaak moeilijker schaalbaar. Dus wil je de CPU load op de database laag houden, zodat je die niet hoeft te schalen. Of, anders gezegd: CPU load op de database server is veel schaarser/duurder dan CPU load op de webserver.

[cariolive23]

De database server is vaak moeilijker schaalbaar.

Een argument dat ik vaak hoor, maar wat verdwijnt zodra alle door de applicatie ingebouwde beperkingen zijn verwijderd.

Dus wil je de CPU load op de database laag houden, zodat je die niet hoeft te schalen.

Dus je betaalt voor CPU en vervolgens wil je die CPU niet gebruiken?

Wij doen projecten in de hoek van data analyse waar we vrijwel altijd eerst de shit mogen/moeten opruimen die door vele applicaties wordt aangeleverd. Initieel dus data exporteren, alle shit vastleggen, ons werk doen, etc. En vaak ook voorstellen schrijven om dit applicaties te herschrijven en daar bugs te gaan fixen. In vrijwel 100% van de gevallen betekent dit ook om database werk door de database te laten uitvoeren ipv door de applicatie. Optimalisatie van het data model, wat slimme indexen, etc. Laatst nog een klant op Azure Flex, 8-core met een berg RAM gemiddeld op 80% CPU gebruik. Na 3 dagen bugfixen en wat indexes, dit terug gebracht naar 2% CPU load. Vervolgens is de klant gaan terugschalen, omdat het kon.

Oorzaak van de database load: Programmeurs die niet goed weten wat een database voor hen kan doen.

[bzzzt]

Als je data binnenkrijgt in json formaat en niet kan garanderen dat het altijd exact aan een bepaald schema voldoet is het handig de json 'as is' op te kunnen slaan. PostgreSQL biedt wel de mogelijkheid om indices op json velden te bouwen dus je kan wel vrij efficient zoeken op onderdelen van zo'n veld.

[Ablaze]

In principe is het gewoon de use case van NoSQL. Als je data hebt die te dynamisch is om in een schema te vatten, of als je een hoge throughput moet hebben. Met postgres kun je dan alsnog tot op het niveau van een enkel veld in een JSON document queries draaien.

[Sefyu]

Het maakt van Postgres meer een 'multi-modal' database waarmee je features van NoSQL database kan gebruiken met SQL. Zoals hieronder aangegeven kan je dan bijvoorbeeld inkomende JSON data as-is opslaan (als JSONB) in een 'datalake'-laag en hier met SQL transformaties op toepassen. Of je kan je database ORM-achtige taken toebedelen.

[Aduen]

ORM

[cariolive23]

Daar zou je ook https://postgrest.org/ voor kunnen gebruiken