
How Postgres is more than a relational database: Extensions - yannikyeo
https://www.citusdata.com/blog/2018/11/27/postgres-more-than-a-relational-database/
======
rleigh
I did this for the debversion datatype (on pgxn). It's surprisingly easy to
create custom types, along with all the necessary operators and hash functions
for them to be efficiently indexed and compared. And the speed is excellent,
so long as you're using a natively compiled library with the C bindings.

JSONB is clearly a lot more complex and demanding, but if you're ever using
PostgreSQL and hitting performance limitations with interpreted languages or
PL/pgSQL, it's an option which is not as scary and inaccessible as you might
imagine.

CREATE EXTENSION is what really made this stuff usable. Before this, you had
to get the user to run a bunch of raw SQL to set all the types and functions,
but now it's all wrapped in a single handy SQL statement which can even handle
upgrades and uninstallation.

Part of me wants the installation and upgrade mechanism to be reusable for my
own application database schemas, it's so convenient and well done.

------
just_myles
My first real foray into the new postgres that this article states is with
JSONB. I needed to create a specific output for a client in JSON format. The
formatting options available were pretty good and allowed me to create
something quick and dirty without having to write custom code to support the
output that I needed.

------
jonatron
It's also easy to create C language user defined functions:
www.postgresql.org/docs/9.0/xfunc-c.html

~~~
codetrotter
Since a lot of languages have FFIs for C this made me wonder, did anyone try
to write a PostgreSQL extension in... Rust?

Turns out a few people have been working on doing just that.

* JSONDC, a PostgreSQL extension written in Rust. Latest commit about a month ago. 161 commits in total. Authors say “It is written in Rust and, being short, is a good skeleton project for other would be plugin authors who'd like to use Rust to write Postgres extensions”. [https://github.com/instructure/jsoncdc](https://github.com/instructure/jsoncdc)

* pgxr. Latest commit 20 days ago. 23 commits in total. “Write PostgreSQL extension functions (as stored procedures) using Rust.”. [https://github.com/clia/pgxr](https://github.com/clia/pgxr)

* RPGFFI. Latest commit about a year ago. 33 commits in total. “Imagine being able to create postgresql extensions in rust. We generate bindgen (0.29.1) pg apis for [...]”. [https://github.com/posix4e/rpgffi/](https://github.com/posix4e/rpgffi/)

* postgres-extension.rs. Latest commit over 2 years ago. 23 commits in total. “Library to write Postgres extensions in Rust! Overall, the Postgres codebase has some pretty narly C macros everywhere, so we have to work around them a little bit.” [https://github.com/thehydroimpulse/postgres-extension.rs](https://github.com/thehydroimpulse/postgres-extension.rs)

------
gmenegatti
Very interesting.

Anyone knows how Postgres really handles data coming from Foreign data
wrappers?

Will Postgres build an index for the columns? Does it leverage the Postgres
query planning?

~~~
michelpp
You cannot have a local index for a FDW, how would it know to update when the
foreign data changed? an approach you can take if you want indexed data from
an fdw is to materialize the data (or a subset of it) and index that with
'CREATE MATERIALIZED VIEW... AS SELECT ... FROM my_fdw...' and then index
columns of that view.

You _can_ run ANALYZE on foreign tables:

"When use_remote_estimate is true, postgres_fdw obtains row count and cost
estimates from the remote server and then adds fdw_startup_cost and
fdw_tuple_cost to the cost estimates. When use_remote_estimate is false,
postgres_fdw performs local row count and cost estimation and then adds
fdw_startup_cost and fdw_tuple_cost to the cost estimates. This local
estimation is unlikely to be very accurate unless local copies of the remote
table's statistics are available. Running ANALYZE on the foreign table is the
way to update the local statistics; this will perform a scan of the remote
table and then calculate and store statistics just as though the table were
local. Keeping local statistics can be a useful way to reduce per-query
planning overhead for a remote table — but if the remote table is frequently
updated, the local statistics will soon be obsolete."

[https://www.postgresql.org/docs/11/postgres-
fdw.html#id-1.11...](https://www.postgresql.org/docs/11/postgres-
fdw.html#id-1.11.7.42.10)

------
mr_overalls
Slightly off-topic, but: has PostgreSQL largely replaced MySQL for new
projects? I'm seeing larger numbers of positions advertising for Postgres,
even here in the Midwest.

Postgres seems so chock-full of features now - is there a reason to prefer
MySQL? Easier replication?

~~~
diminoten
I dunno, it's still pretty damn hard to find a DBA with relevant PostgreSQL
experience on the east coast...

~~~
ghotli
I find this hard to believe.

~~~
diminoten
Have you tried hiring one lately? Oracle experience out the wazoo, but
PostgreSQL experience is much harder to come by.

~~~
coder543
Maybe there's a reason that there's a surplus of Oracle DBAs and a shortage of
Postgres DBAs.

~~~
penagwin
I'm in uni and everything is Microsoft, Oracle and Cisco.

~~~
coder543
Universities are often... _influenced_ by big companies, and do not
necessarily use technologies that are representative of what you'll see in the
real world. They could be, by random chance, but it’s not necessarily the
case. Anecdotally, I usually hear from people that it _wasn’t_ representative
when they are comparing what they learned in college to what they use in the
workforce.

It also really annoys me when universities teach their students expensive,
proprietary software that they won't be able to use outside of class when
there are competent and often superior free options. These students are paying
good money to learn, and the universities are intentionally choosing to teach
them something less than the most beneficial knowledge that those students
could reasonably be learning about.

~~~
da_chicken
IMX, universities are not influenced by companies as much as they are
influenced by the inertia of established curriculum and limited interest from
the professors to use a different tool that offers minimal benefits. Even if
I'm teaching a class and using Oracle, I'm probably mostly teaching general
relational databases where one is as good as any other. It's not the job of a
conceptual RDBMS class to teach you the implementation details of arbitrary
offerings; quite the opposite!

------
pjmlp
All major SQL databases support extensions though.

