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 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:

1. EXCLUDE constraints: To avoid overlapping time slots

2. CHECK constraints: For validating data at the source

3. GENERATED columns: To let the database do the math

4. DISTINCT ON: Cleaner than a GROUP BY with subqueries

5. 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?

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.

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

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

cross-posted from: https://lemmy.bestiver.se/post/1006496

Comments

DB-Engines Ranking climbers of the month (March 2026):

1. @postgresql

2. #MicrosoftFabric

3. #MongoDB

http://db-engines.com/en/ranking

cross-posted from: https://lemmy.bestiver.se/post/948840

Comments

cross-posted from: https://lemmy.bestiver.se/post/873373

Comments

cross-posted from: https://lemmy.bestiver.se/post/858622

Comments

cross-posted from: https://lemmy.bestiver.se/post/847006

Comments