13 ms·
PostgreSQL 17
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]
- kiwicopple 2y agoAnother amazing release, congrats to all the contributors. There are simply too many things to call out - just a few highlights: Massive improvements to vacuum operations: "PostgreSQL 17 introduces a new internal memory structure for vacuum that consumes up to 20x less memory." Much needed features for backups: "pg_basebackup, the backup utility included in PostgreSQL, now supports incremental backups and adds the pg_combinebackup utility to reconstruct a full backup" I'm a huge fan of FDW's and think they are an untapped-gem in Postgres, so I love seeing these improvements: "The PostgreSQL foreign data wrapper (postgres_fdw), used to execute queries on remote PostgreSQL instances, can now push EXISTS and IN subqueries to the remote server for more efficient processing."
- deleted 2y ago[deleted]
- ellisv 2y ago> I'm a huge fan of FDW's Do you have any recommendations on how to manage credentials for `CREATE USER MAPPING ` within the context of cloud hosted dbs?
- darth_avocado 2y agoIf your company doesn't have an internal tool for storing credentials, you can always store them in the cloud provider's secrets management tool. E.g. Secrets Manager or Secure String in Parameter Store on AWS. Your CI/CD pipeline can pull the secrets from there.
- kiwicopple 2y agoin supabase we have a “vault” utility for this (for example: https://fdw.dev/catalog/clickhouse/#connecting-to-clickhouse https://fdw.dev/catalog/clickhouse/#connecting-to-clickhouse). Sorry I can’t make recommendations for other platforms because i don’t want to suggest anything that could be considered unsafe - hopefully others can chime in
- frankramos 2y agoJust upgraded Supabase to a Pro account to try FDW but there doesn’t seem to be solid wrappers for MySQL/Vitess from Planetscale. This would help a ton of people looking to migrate. Does anyone have suggestions?
- brunoqc 2y agoI batch import XMLs, CSVs and mssql data into postgresql. I'm pretty sure I could read them when needed with fdw. Is it a good idea? I think it can be slow but maybe I could use materialized views or something.
- kiwicopple 2y ago“it depends”. Some considerations for mssql: - If the foreign server is close (latency) that’s great - if your query is complex then it helps if the postgres planner can “push down” to mssql. That will usually happen if you aren’t doing joins to local data I personally like to set up the foreign tables, then materialize the data into a local postgres table using pg_cron. It’s like a basic ETL pipeline completely built into postgres
- victorbjorklund 2y agoOh. That is smart using it as a very simple ETL pipeline.
- mind-blight 2y agoI've been using duckdb to import data into postgres (especially CSVs and JSON) and it has been really effective. Duckdb can run SQL across the different data formats and insert or update directly into postgres. I run duckdb with python and Prefect for batch jobs, but you can use whatever language or scheduler you perfer. I can't recommend this setup enough. The only weird things I've run into is a really complex join across multiple postgres tables and parquet files had a bug reading a postgres column type. I simplified the query (which was a good idea anyways) and it hums away
- brunoqc 2y agoThanks. My current pattern is to parse the files with rust, copy from stdin into a psql temp table, update the rows that have changed and delete the rows not existing anymore. I'm hoping it's less wasteful than truncating and importing the whole table every time there is one single change.
- peiskos 2y agoA bit off topic, can someone suggest how I can learn more about using databases(postgres specifically) in real world applications? I am familiar with SQL and common ORMs, but I feel the internet is full of beginner level tutorials which lack this depth.
- Superfud 2y agoFor PostgreSQL, the manual is extremely well written, and is warmly recommended reading. That should give you a robust foundation.
- veggieroll 2y agoLoving the continued push for JSON features. I'm going to get a lot of use out of JSON_TABLE. And json_scalar & json_serialize are going to be helpful at times too. JSON_QUERY with OMIT QUOTES is awesome too for some things. I hope SQLite3 can implement SQL/JSON soon too. I have a library of compatability functions to generate the appropriate JSON operations depending on if it's SQLite3 or PostgreSQL. And it'd be nice to reduce the number of incompatibilities over time. But, there's a ton of stuff in the release notes that jumped out at me too: "COPY .. ON_ERROR" ignore is going to be nice for loading data anywhere that you don't care if you get all of it. Like a dev environment or for just exploring something. [1] Improvements to CTE plans are always welcome. [2] "transaction_timeout" is an amazing addition to the existing "statement_timeout" as someone who has to keep an eye on less experienced people running SQL for analytics / intelligence. [3] There's a function to get the timestamp out of a UUID easily now, too: uuid_extract_timestamp(). This previously required a user defined function. So it's another streamlining thing that's nice. [4] I'll use the new "--exclude-extension" option for pg_dump, too. I just got bitten by that when moving a database. [5] "Allow unaccent character translation rules to contain whitespace and quotes". Wow. I needed this! [6] [1] https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-HIGHLIGHTS https://www.postgresql.org/docs/17/release-17.html#RELEASE-1... [2] https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-OPTIMIZER https://www.postgresql.org/docs/17/release-17.html#RELEASE-1... [3] https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-SERVER-CONFIG https://www.postgresql.org/docs/17/release-17.html#RELEASE-1... [4] https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-FUNCTIONS https://www.postgresql.org/docs/17/release-17.html#RELEASE-1... [5] https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-SERVER-APPS https://www.postgresql.org/docs/17/release-17.html#RELEASE-1... [6] https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-MODULES https://www.postgresql.org/docs/17/release-17.html#RELEASE-1...
- nbbaier 2y ago> I hope SQLite3 can implement SQL/JSON soon too. I have a library of compatability functions to generate the appropriate JSON operations depending on if it's SQLite3 or PostgreSQL. And it'd be nice to reduce the number of incompatibilities over time. Is this available anywhere? Super interested
- on_the_train 2y agoMy boss insisted on the switch from oracle to mssql. Because "you can't trust open source for business software". Oh the pain
- gigatexal 2y agoSo from one expensive vendor to another? Your boss seems smart. ;-) What’s the rationale? What do you gain?
- on_the_train 2y agoExactly. Supposedly the paid solution ensures long term support. The most fun part is that our customers need to buy these database licenses, so it directly reduces our own pay. Say no to non-technical (or rational) managers :<
- remram 2y agoDid you not pay for Oracle?
- on_the_train 2y agoWe pay, but what hurts is that our customers need to pay, too. For both oracle and ms of course
- systems 2y agoWell, from one VERY expensive vendor, to another considerably less expensive vendor Also, MSSQL have few things going for it, and surprisingly no one seem to be even trying to catch up - Their BI Stacks (PowerBI, SSAS) - Their Database Development (SDK) ( https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver16 ) The MSSQL BI stack is unmatched , SSAS is the top star of BI cubes and the second option is not even close SSRS is ok, SSIS is passable , but still both are very decent PowerBI and family is also the best option for Mid to large (not FAANG large, but just normal large) companies And finally the GEM that is database projects, you can program your DB changes declaratively, there is nothing like this in the market and again, no one is even trying The easiest platformt todo evolutionary DB development is MS SQL I really wish someone will implement DB Projects (dacpac) for Postgresql
- pestaa 2y agoVery impressive changelog. Bit sad the UUIDv7 PR didn't make the cut just yet: https://commitfest.postgresql.org/49/4388/ https://commitfest.postgresql.org/49/4388/
- ellisv 2y agoI've been waiting for "incremental view maintenance" (i.e. incremental updates for materialized views) but it looks like it's still a few years out.
- whitepoplar 2y agoThere's always the pg_ivm extension you can use in the meantime: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm
- ellisv 2y agoUnfortunately we use a cloud provider to host our databases, so I can only install limited extensions.
- gregwebs 2y agoREFRESH CONCURRENTLY is already an incremental update of sorts although you still pay the price of running a full query.
- isoprophlex 2y agoWow, brilliant! I never knew this existed. Going to try this out tomorrow, first thing!
- JamesSwift 2y agoI'm a huge fan of views to serve as the v1 solution for problems before we need to optimize our approach, and this is the main thing that serves as a blocker in those discussions. If only we were able to have v2 of the approach be an IVM-view, we could leverage them much more widely.
- miohtama 2y agoThe titan keeps rocking.
- yen223 2y agoMERGE support for updating views is huge. So looking forward to this
- majkinetor 2y agoNow it also supports RETURNS keyword !
- jackschultz 2y agoVery cool with the JSON_TABLE. The style of putting json response (from API, created from scraping, ect.) into jsonb column and then writing a view on top to parse / flatten is something I've been doing for a few years now. I've found it really great to put the json into a table, somewhere safe, and then do the parsing rather than dealing with possible errors on the scripting language side. I haven't seen this style been used in other places before, and to see it in the docs as a feature from new postgres makes me feel a bit more sane. Will be cool to try this out and see the differences from what I was doing before!
- ellisv 2y agoIt is definitely an improvement on multiple `JSONB_TO_RECORDSET` and `JSONB_TO_RECORD` calls for flattening nested json.
- abyesilyurt 2y ago> putting json response (from API, created from scraping, ect.) into jsonb column and then writing a view on top to parse That’s a very good idea!
- 0cf8612b2e1e 2y agoA personal rule of mine is to always separate data receipt+storage from parsing. The retrieval is comparatively very expensive and has few possible failure modes. Parsing can always fail in new and exciting ways. Disk space to store the returned data is cheap and can be periodically flushed only when you are certain the content has been properly extracted.
- erichocean 2y agoI ended up with the same design after encountering numerous exotic failure modes.
- cjonas 2y agoDid you mean "retrieval is comparatively inexpensive"? I think I'm on the same page but this threw me off.
- ksec 2y agoWith 17, is Vacuum largely a solved issue?
- ses1984 2y agoI’m not up to date on the recent changes, but problems we had with vacuum were more computation and iops related than memory related. Basically in a database with a lot of creation/deletion, database activity can outrun the vacuum, leading to out of storage errors, lock contention, etc In order to keep throughput up, we had to throttle things manually on the input side, to allow vacuum to complete. Otherwise throughput would eventually drop to zero.
- sgarland 2y agoYou can also tune various [auto]vacuum settings that can dramatically increase the amount of work done in each cycle. I’m not discounting your experience as anything is possible, but I’ve never had to throttle writes, even on large clusters with hundreds of thousands of QPS.
- jabl 2y agoThere was a big project to re-architect the low level storage system to something that isn't dependent on vacuuming, called zheap. Unfortunately it seems to have stalled and nobody seems to be working on it anymore? I keep scanning the release notes for each new pgsql version, but no dice.
- qazxcvbnm 2y agoI think OrioleDB is picking up from where zheap left things, and seems quite active in upstreaming their work, you might be interested to check it out.
- imbradn 2y agoNo, vacuum issues are not solved. This will reduce the amount of scanning needed in many cases when vacuuming indexes. It will mean more efficient vacuum and quicker vacuums which will help in a lot of cases.
- deleted 2y ago[deleted]
- ktosobcy 2y agoWould be awesome if PostgreSQL would finally add support for seamless major version upgrade…
- ellisv 2y agoI'm curious what you feel is specifically missing.
- levkk 2y agopg_upgrade is a bit manual at the moment. If the database could just be pointed to a data directory and update it automatically on startup, that would be great.
- olavgg 2y agoI agree, why is this still needed? It can run pg_upgrade in the background.
- vbezhenar 2y agoIt needs binaries for both old and new versions for some reason.
- justinclift 2y agoWhen you say "in the background" what are you meaning? Unless something has radically changed with this last release, then the PostgreSQL database needs to be offline while pg_upgrade is running.
- ktosobcy 2y agoBeing able to simply switch from "postgres:15" to "postgres:16" in docker for example (I'm aware about pg_autoupdate but it's external and I'm a bit iffy about using it) What's more, even outside of docker running `pg_upgrade` requires both version to be present (or having older binary handy). Honestly, having the previous version logic to load and process the database seems like it would be little hassle but would improve upgrading significantly...
- h1fra 2y agoAmazing, it's been a long time since I have been that much excited by a software release!
- monkaiju 2y agoPG just continues to impress!
- clarkbw 2y agoSome awesome quality-of-life improvements here as well. The random function now takes min, max parameters SELECT random(1, 10) AS random_number;
- clarkbw 2y agoIt's never available on homebrew the same day so we all worked hard to make it available the same day on Neon. If you want to try out the JSON_TABLE and MERGE RETURNING features you can spin up a free instance quickly. https://neon.tech/blog/postgres-17 https://neon.tech/blog/postgres-17 (note that not all extensions are available yet, that takes some time still)
- dewey 2y agoHow is your hosted managed Postgres platform an alternative to Homebrew (Installing PG on your local Mac)? There's also many Homebrew formulas that have PG 17 already like (https://github.com/petere/homebrew-postgresql/commit/2faf43804e109c43a946f1be7fbb3802359d3f07 https://github.com/petere/homebrew-postgresql/commit/2faf438...).
- clarkbw 2y agooh, i see I'm getting downvoted; this isn't a sales pitch for my "managed postgres platform". every year a new postgres release occurs and i want to try out some of the features i have to find a way to get it. usually nobody has it available. here's the list of homebrew options I see right now: brew formulae | grep postgresql@ postgresql@10 postgresql@11 postgresql@12 postgresql@13 postgresql@14 postgresql@15 postgresql@16 maybe you're seeing otherwise but i updated a min ago and 17 isn't there yet. even searching for 'postgres' on homebrew doesn't reveal any options for 17. i don't know where you've found those but it doesn't seem easily available. and i'm not suggesting you use a cloud service as an alternative to homebrew or local development. neon is pure postgres, the local service is the same as whats in the cloud. but right now there isn't an easy local version and i wanted everyone else to be able to try it quickly.
- paws 2y agoI'm looking forward to trying out incremental backups as well as JSON_TABLE. Thank you contributors!
- andreashansen 2y agoOh how I wish for Postgres to introduce system-versioned (bi-temporal) tables.
- qianli_cs 2y agoWhat's your use case for system-versioned tables? You could use some extensions like Periods that support bi-temporal tables: https://wiki.postgresql.org/wiki/Temporal_Extensions https://wiki.postgresql.org/wiki/Temporal_Extensions Or you could use triggers to build one: https://hypirion.com/musings/implementing-system-versioned-tables-in-postgres https://hypirion.com/musings/implementing-system-versioned-t...
- baq 2y agoany system of record has a requirement to be bitemporal, it just isn't discovered until too late IME. I don't know if there's a system anywhere which conciously decided to not be bitemporal during initial design.
- jeltz 2y agoIt will hopefully be in PostgreSQL 18.
- infamia 2y agoIs there an active effort to get temporal tables into Postgres at the moment?
- infamia 2y agoIt looks like Paul A. Jungwirth and others are trying to get Temporal Tables into Postgres 18. https://illuminatedcomputing.com/posts/2024/07/temporal-reverted/ https://illuminatedcomputing.com/posts/2024/07/temporal-reve...
- lpapez 2y agoAmazing release, Postgres is a gift that keeps on giving. I hope to one day see Incremental View Maintenance extension (IVM) be turned into a first class feature, it's the only thing I need regularly which isn't immediately there!
- nikita 2y agoA number of features stood out to me in this release: 1. Chipping away more at vacuum. Fundamentally Postgres doesn't have undo log and therefore has to have vacuum. It's a trade-off of fast recovery vs well.. having to vacuum. The unfortunate part about vacuum is that it adds load to the system exactly when the system needs all the resources. I hope one day people stop knowing that vacuum exists, we are one step closer, but not there. 2. Performance gets better and not worse. Mark Callaghan blogs about MySQL and Postgres performance changes over time and MySQL keep regressing performance while Postgres keeps improving. https://x.com/MarkCallaghanDB https://x.com/MarkCallaghanDB https://smalldatum.blogspot.com/ https://smalldatum.blogspot.com/ 3. JSON. Postgres keep improving QOL for the interop with JS and TS. 4. Logical replication is becoming a super robust way of moving data in and out. This is very useful when you move data from one instance to another especially if version numbers don't match. Recently we have been using it to move at the speed of 1Gb/s 5. Optimizer. The better the optimizer the less you think about the optimizer. According to the research community SQL Server has the best optimizer. It's very encouraging that every release PG Optimizer gets better.
- sgarland 2y agoMySQL can be faster in certain circumstances (mostly range selects), but only if your schema and queries are designed to exploit InnoDB’s clustering index. But even then, in some recent tests I did, Postgres was less than 0.1 msec slower. And if the schema and queries were not designed with InnoDB in mind, Postgres had little to no performance regression, whereas MySQL had a 100x slowdown. I love MySQL for a variety of reasons, but it’s getting harder for me to continue to defend it.
- netcraft 2y agoI remember when the json stuff started coming out, I thought it was interesting but nothing I would ever want to rely on - boy was I wrong! It is so nice having json functionality in a relational db - even if you never actually store json in your database, its useful in so many situations. Being able to generate json in a query from your data is a big deal too. Looking forward to really learning json_table
- openrisk 2y agoThere was LAMP and then MERN and MEAN etc. and then there was Postgres. Its not quite visible yet, but all this progres by postgres (excuse the pun) on making JSON more deeply integrated with relational principles will surely at some point enable a new paradigm, at least for full stack web frameworks?
- winrid 2y agoThe problem with postgres's JSON support is it leads to last write win race conditions compared to actual document stores. Once they can support operations like incrementing a number while someone else updates another field, without locking the row, then maybe. I don't think you'll see a paradigm shift. You'll just see people using other documents stores less.
- sgarland 2y agoWe’re already in a new paradigm, one in which web devs are abandoning referential integrity guarantees in favor of not having to think about data modeling. I’d say they’re then surprised when their DB calls are slow (nothing to do with referential integrity, just the TOAST/DETOAST overhead), but since they also haven’t experienced how fast a DB with local disks and good data locality can be, they have no idea.
- pnt12 2y agoCan you elaborate on what's TOAST/DETOAST?
- davidrowley 2y agoThe Oversized-Attribute Storage Technique. https://www.postgresql.org/docs/17/storage-toast.html https://www.postgresql.org/docs/17/storage-toast.html
- switch007 2y agoProps to all the people (and companies) behind Postgres https://www.postgresql.org/community/contributors/ https://www.postgresql.org/community/contributors/
- Rican7 2y agoWow, yea, the performance gains and new UX features (JSON_TABLE, MERGE improvements, etc) are huge here, but these really stand out to me: > PostgreSQL 17 supports using identity columns and exclusion constraints on partitioned tables. > PostgreSQL 17 also includes a built-in, platform independent, immutable collation provider that's guaranteed to be immutable and provides similar sorting semantics to the C collation except with UTF-8 encoding rather than SQL_ASCII. Using this new collation provider guarantees that your text-based queries will return the same sorted results regardless of where you run PostgreSQL.
- __natty__ 2y agoThis release is good on so many levels. Just the performance optimisations and the JSON TABLE feature could be entirely separated release, but we got so much more.
- nojvek 2y agoI wish postgres supports parquet file imports and exports. COPY command with csv is really slooooooooow. Even BINARY is quite slow and bandwidth heavy. I wonder how open postgres is and what kind of pull requests postgres team considers? I'd like to learn how to contribute to PG in baby steps and eventually get to a place where I could contribute substantial features.
- ioltas 2y agoThere has been a patch to extend the COPY code with pluggable APIs, adding callbacks at start, end, and for each row processed: https://commitfest.postgresql.org/49/4681/ https://commitfest.postgresql.org/49/4681/. I'd guess that this may fit your purpose to add a custom format without having to fork upstream.
- ssfak 2y agoUsing the pg_duckdb[1] is an option, if you can install extensions on your setup. [1]. https://github.com/duckdb/pg_duckdb https://github.com/duckdb/pg_duckdb
- SOLAR_FIELDS 2y agoLooking forward to AWS DMS supporting this nice software release. They were super quick about supporting PG16 so this should be easy, right? https://repost.aws/questions/QUbIn2WmXgTbiVu_4wua9tUw/dms-with-rds-postgresql-16-as-source https://repost.aws/questions/QUbIn2WmXgTbiVu_4wua9tUw/dms-wi...
- lukaslalinsky 2y agoWow, it finally has failover support for logical replication slots. That was biggest reason why I couldn't depend on logical replication, since the master DB failover handling was too complex for me to deal with.