follow from another instance
Not on this instance? Enter your own instance's domain to go straight to following c/postgresql@programming.dev there.
showing posts tagged #postgresql — clear
preview
#mastoadmin reminder - @postgresql has released a security focused set of releases (52 vulnerabilities and over 140 bugs).
This is the final release of PostgreSQL 14. PostgreSQL 14 is now end-of-life and will no longer receive security and bug fixes.
(I suspect that mastodon will upgrade the docker compose from PG14 to PG19 once it’s released next week to prepare for the upcoming 5.0.0 release)
https://www.postgresql.org/about/news/postgresql-187-1712-1616-1520-1425-and-19-rc-2-released-3442/
#mastoadmin reminder - @postgresql has released a security focused set of releases (52 vulnerabilities and over 140 bugs).
This is the final release of PostgreSQL 14. PostgreSQL 14 is now end-of-life and will no longer receive security and bug fixes.
(I suspect that mastodon will upgrade the docker compose from PG14 to PG19 once it’s released next week to prepare for the upcoming 5.0.0 release)
https://www.postgresql.org/about/news/postgresql-187-1712-1616-1520-1425-and-19-rc-2-released-3442/
preview
Wrote this after seeing partition pruning silently fail with a parameterized query - the planner can’t prune on a value it doesn’t have yet at plan time. Covers the three common ways people accidentally defeat pruning and how to check EXPLAIN to tell whether it actually fired.
Wrote this after seeing partition pruning silently fail with a parameterized query - the planner can’t prune on a value it doesn’t have yet at plan time. Covers the three common ways people accidentally defeat pruning and how to check EXPLAIN to tell whether it actually fired.
preview
Posting the HN link here because the pgrust author attempts to downplay and explain away the code as various internal optimization artifacts.
They admit that they submitted to ClickBench an incomplete, private build specifically created for the purpose (and including files hardcoded to the Clickbench test names), which could only interpret a limited subset of SQL (just enough to pass the tests). They said it was just overeagerness.
Particularly, they do not seem to think this counts as cheating, because the old engine was still in there technically even though it was disabled:
Once our new JIT compiler supported enough SQL to handle the ClickBench queries, we decided to submit it to ClickBench. AFAIK, ClickBench has no rules about how much SQL you need to support. On top of that, we did ship that we did ship 0.4-preview with our old query engine. It would have been a two-line change to fall back to it and support a much broader set of queries outside what our new engine could support.
Posting the HN link here because the pgrust author attempts to downplay and explain away the code as various internal optimization artifacts.
They admit that they submitted to ClickBench an incomplete, private build specifically created for the purpose (and including files hardcoded to the Clickbench test names), which could only interpret a limited subset of SQL (just enough to pass the tests). They said it was just overeagerness.
Particularly, they do not seem to think this counts as cheating, because the old engine was still in there technically even though it was disabled:
Once our new JIT compiler supported enough SQL to handle the ClickBench queries, we decided to submit it to ClickBench. AFAIK, ClickBench has no rules about how much SQL you need to support. On top of that, we did ship that we did ship 0.4-preview with our old query engine. It would have been a two-line change to fall back to it and support a much broader set of queries outside what our new engine could support.
preview
Many readers will be aware that an unusually high number of features have been reverted from the PostgreSQL 19 branch after the beta period began. Having some reverts is not unusual, maybe one per cycle could be expected. But this time around, about 8 to 10 significant features, depending on how you count, have been reverted, and some of them quite late in the beta period.
Many readers will be aware that an unusually high number of features have been reverted from the PostgreSQL 19 branch after the beta period began. Having some reverts is not unusual, maybe one per cycle could be expected. But this time around, about 8 to 10 significant features, depending on how you count, have been reverted, and some of them quite late in the beta period.
preview
A while back I had to integrate data from a third-party REST API into a Postgres-backed app. My solution at the time was a cron job that periodically fetched the API, parsed the response, and shoved it into the database. It worked. It was also annoying to maintain and broke in creative ways. Months later I discovered that Postgres could have queried that API directly (and I felt a bit dumb lol).
The feature is called Foreign Data Wrappers, and it’s been in Postgres for years. The idea: you create a virtual foreign table that maps to an external data source, then you query it with plain SQL. JOINs, WHERE clauses, INSERTs from SELECT, the whole deal.
Here’s what I’ve been using it for:
**CSV files without the import dance **
Postgres ships with file_fdw. You point it at a CSV, define the columns, and it’s a queryable table. You can JOIN it with your real tables or cherry-pick rows to INSERT into a permanent table. No more writing throwaway Python scripts to parse CSVs. One catch: file_fdw is read-only, so no writing back to the file.
Querying a remote Postgres database
postgres_fdw is also built-in. You set up a foreign server, map a user, create the foreign table, and suddenly you can query (and even UPDATE) another Postgres instance from your local one. Handy for migrations or cross-database reporting. Setting up the user mapping with credentials in plain SQL feels a bit rough, but it gets the job done.
**Talking to MongoDB (or any NoSQL store) **
This is where it gets fun. With Multicorn (a Python library) you can write your own FDW for pretty much anything. You define a Python class, implement an execute method that translates SQL qualifiers into queries for your target data source, and Postgres handles the rest. There are also ready-made FDWs for MongoDB, ElasticSearch, Redis, and others if you don’t want to roll your own ;)
REST APIs as tables
Same principle with Multicorn. You write a wrapper class that turns WHERE clauses into API query parameters, hits the endpoint, and yields rows back to Postgres. I used the Magic: The Gathering API as a test case, nothing mission-critical, but the pattern translates to any REST endpoint. For authenticated APIs you just add headers or tokens in the Python code.
That said, it’s not all smooth sailing. JOINs between foreign tables and local ones can get slow, especially with large external datasets. Also, debugging a misbehaving custom FDW is… not fun lol. And writing credentials in plain SQL for user mappings still makes me wince every time.
For those of you already running FDWs in production, how do you handle the performance tradeoff? Curious what strategies people have settled on ;)
A while back I had to integrate data from a third-party REST API into a Postgres-backed app. My solution at the time was a cron job that periodically fetched the API, parsed the response, and shoved it into the database. It worked. It was also annoying to maintain and broke in creative ways. Months later I discovered that Postgres could have queried that API directly (and I felt a bit dumb lol).
The feature is called Foreign Data Wrappers, and it’s been in Postgres for years. The idea: you create a virtual foreign table that maps to an external data source, then you query it with plain SQL. JOINs, WHERE clauses, INSERTs from SELECT, the whole deal.
Here’s what I’ve been using it for:
**CSV files without the import dance **
Postgres ships with file_fdw. You point it at a CSV, define the columns, and it’s a queryable table. You can JOIN it with your real tables or cherry-pick rows to INSERT into a permanent table. No more writing throwaway Python scripts to parse CSVs. One catch: file_fdw is read-only, so no writing back to the file.
Querying a remote Postgres database
postgres_fdw is also built-in. You set up a foreign server, map a user, create the foreign table, and suddenly you can query (and even UPDATE) another Postgres instance from your local one. Handy for migrations or cross-database reporting. Setting up the user mapping with credentials in plain SQL feels a bit rough, but it gets the job done.
**Talking to MongoDB (or any NoSQL store) **
This is where it gets fun. With Multicorn (a Python library) you can write your own FDW for pretty much anything. You define a Python class, implement an execute method that translates SQL qualifiers into queries for your target data source, and Postgres handles the rest. There are also ready-made FDWs for MongoDB, ElasticSearch, Redis, and others if you don’t want to roll your own ;)
REST APIs as tables
Same principle with Multicorn. You write a wrapper class that turns WHERE clauses into API query parameters, hits the endpoint, and yields rows back to Postgres. I used the Magic: The Gathering API as a test case, nothing mission-critical, but the pattern translates to any REST endpoint. For authenticated APIs you just add headers or tokens in the Python code.
That said, it’s not all smooth sailing. JOINs between foreign tables and local ones can get slow, especially with large external datasets. Also, debugging a misbehaving custom FDW is… not fun lol. And writing credentials in plain SQL for user mappings still makes me wince every time.
For those of you already running FDWs in production, how do you handle the performance tradeoff? Curious what strategies people have settled on ;)
preview
A few months ago, I struggled with a planning system. I needed to ensure that no 2 plans could overlap for the same period. My first instinct was to write application-level validation, but something felt off. I thought to myself that surely PostgreSQL had a better way.
Turns out, PostgreSQL is packed with a bunch of underrated (and often simply overlooked) features that can save you from writing complex application logic:
-
EXCLUDEconstraints: To avoid overlapping time slots -
CHECKconstraints: For validating data at the source -
GENERATEDcolumns: To let the database do the math -
DISTINCT ON: Cleaner than aGROUP BYwith subqueries -
FILTER: To add a condition directly on the aggregate
Even after years of using it, I still discover features that make me question why I ever wrote complex application logic for things the database could handle natively.
I wrote an even more detailed version with examples (in case anyone thinks this isn’t long enough lol)
Are there any other advanced PostgreSQL features I should know about?
A few months ago, I struggled with a planning system. I needed to ensure that no 2 plans could overlap for the same period. My first instinct was to write application-level validation, but something felt off. I thought to myself that surely PostgreSQL had a better way.
Turns out, PostgreSQL is packed with a bunch of underrated (and often simply overlooked) features that can save you from writing complex application logic:
-
EXCLUDEconstraints: To avoid overlapping time slots -
CHECKconstraints: For validating data at the source -
GENERATEDcolumns: To let the database do the math -
DISTINCT ON: Cleaner than aGROUP BYwith subqueries -
FILTER: To add a condition directly on the aggregate
Even after years of using it, I still discover features that make me question why I ever wrote complex application logic for things the database could handle natively.
I wrote an even more detailed version with examples (in case anyone thinks this isn’t long enough lol)
Are there any other advanced PostgreSQL features I should know about?
preview
David Steele, maintainer of the popular pgBackRest backup and restore project for PostgreSQL, has archived the project and announced that it is no longer being maintained.
After a lot of thought, I have decided to stop working on pgBackRest. I did not come to this decision lightly. pgBackRest has been my passion project for the last thirteen years, and I was fortunate to have corporate sponsorship for much of this time, but there were also many late nights and weekends as I worked to make pgBackRest the project it is today, aided by numerous contributors. Every open-source developer knows exactly what I mean and how much of your life gets devoted to a special project.
Since Crunchy Data was sold, I have been maintaining pgBackRest and looking for a position that would allow me to continue the work, but so far I have not been successful. Likewise, my efforts to secure sponsorship have also fallen far short of what I need to make the project viable.
David Steele, maintainer of the popular pgBackRest backup and restore project for PostgreSQL, has archived the project and announced that it is no longer being maintained.
After a lot of thought, I have decided to stop working on pgBackRest. I did not come to this decision lightly. pgBackRest has been my passion project for the last thirteen years, and I was fortunate to have corporate sponsorship for much of this time, but there were also many late nights and weekends as I worked to make pgBackRest the project it is today, aided by numerous contributors. Every open-source developer knows exactly what I mean and how much of your life gets devoted to a special project.
Since Crunchy Data was sold, I have been maintaining pgBackRest and looking for a position that would allow me to continue the work, but so far I have not been successful. Likewise, my efforts to secure sponsorship have also fallen far short of what I need to make the project viable.
preview
Mi chiedo: è possibile fare lo sviluppatore da una vita intera e non aver mai, e dico MAI provato #PostgreSQL? 🤌
Ma è giunta l’ora di rimediare: martedì esploriamo in live su #Twitch usando PostgreSQL @postgresql con #Delphi e la libreria #FireDAC! 🎉
🎯 Cosa faremo:
Setup e primo contatto 🖖 con PostgreSQL
Connessione in Delphi grazie a FireDAC
Query di base e primi test reali
📅 Quando?
Martedì 14 Aprile, ore 21:30
👉 Dove?
Sul mio canale Twitch!
https://twitch.tv/compilaquindiva
Mi chiedo: è possibile fare lo sviluppatore da una vita intera e non aver mai, e dico MAI provato #PostgreSQL? 🤌
Ma è giunta l’ora di rimediare: martedì esploriamo in live su #Twitch usando PostgreSQL @postgresql con #Delphi e la libreria #FireDAC! 🎉
🎯 Cosa faremo:
Setup e primo contatto 🖖 con PostgreSQL
Connessione in Delphi grazie a FireDAC
Query di base e primi test reali
📅 Quando?
Martedì 14 Aprile, ore 21:30
👉 Dove?
Sul mio canale Twitch!
https://twitch.tv/compilaquindiva
preview
Google’s recent massive contribution to @postgresql looks amazing: among the rest, proper active-active replication has landed: Oracle trembles!
https://opensource.googleblog.com/2026/03/google-cloud-investing-in-the-future-of-postgresql.html
Google’s recent massive contribution to @postgresql looks amazing: among the rest, proper active-active replication has landed: Oracle trembles!
https://opensource.googleblog.com/2026/03/google-cloud-investing-in-the-future-of-postgresql.html
preview
cross-posted from: lemmy.bestiver.se/post/1006496
cross-posted from: lemmy.bestiver.se/post/1006496
preview
DB-Engines Ranking climbers of the month (March 2026):
DB-Engines Ranking climbers of the month (March 2026):
preview
cross-posted from: lemmy.bestiver.se/post/948840
cross-posted from: lemmy.bestiver.se/post/948840
preview
cross-posted from: lemmy.bestiver.se/post/873373
cross-posted from: lemmy.bestiver.se/post/873373
preview
cross-posted from: lemmy.bestiver.se/post/858622
cross-posted from: lemmy.bestiver.se/post/858622
preview
cross-posted from: lemmy.bestiver.se/post/847006
cross-posted from: lemmy.bestiver.se/post/847006
preview
I have started dev tool for myself and now decided to release it. Maybe will be usefull for backend developers or those who are starting to learn sql Link to devtool: isra36.com Link to its documentation: isra36.com/documentation MySQL & PostgreSQL
I have started dev tool for myself and now decided to release it. Maybe will be usefull for backend developers or those who are starting to learn sql Link to devtool: isra36.com Link to its documentation: isra36.com/documentation MySQL & PostgreSQL
preview
To all the geospatial data scientists and geo database geeks out there: Happy PostGIS day! (: #postgis #postgisday2025 #postgisday #foss #postgresql #sql #osgeo @osgeo @postgis @postgresql
To all the geospatial data scientists and geo database geeks out there: Happy PostGIS day! (: #postgis #postgisday2025 #postgisday #foss #postgresql #sql #osgeo @osgeo @postgis @postgresql
preview
Just published a new How-to guide on installing PostgreSQL 18 on Ubuntu 25.10 (Questing Quokka). 🦫
Learn how to switch from the default PostgreSQL 17 to version 18 and try new features like UUIDv7 using the official PGDG repository: fast, clean, and developer-friendly setup. 🐘
https://www.paulox.net/2025/10/31/install-postgresql-18-on-ubuntu-25-10-questing-quokka/
CC @postgresql
#PostgreSQL #Ubuntu #Linux #Python #Django #UUID #UUID7 #UUIDv7
Just published a new How-to guide on installing PostgreSQL 18 on Ubuntu 25.10 (Questing Quokka). 🦫
Learn how to switch from the default PostgreSQL 17 to version 18 and try new features like UUIDv7 using the official PGDG repository: fast, clean, and developer-friendly setup. 🐘
https://www.paulox.net/2025/10/31/install-postgresql-18-on-ubuntu-25-10-questing-quokka/
CC @postgresql
#PostgreSQL #Ubuntu #Linux #Python #Django #UUID #UUID7 #UUIDv7
preview
cross-posted from: feddit.nl/post/43404968
How-to: Cloudnative PG serving MongoDB with Automated Recovery from Continuous Backups
First post on my personal blog!
cross-posted from: feddit.nl/post/43404968
How-to: Cloudnative PG serving MongoDB with Automated Recovery from Continuous Backups
First post on my personal blog!