How Postgres is more than a relational database: Extensions | Hacker News Reader

How Postgres is more than a relational database: Extensions

citusdata.com

161 pointsyannikyeo37 comments

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.

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.

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

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

* pgxr. Latest commit 20 days ago. 23 commits in total. “Write PostgreSQL extension functions (as stored procedures) using Rust.”. 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/

* 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

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?

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

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?

Yes, MySQL has more developed methods of replication. There are also some specific functionalities available in either MySQL or Postgres that you may pick based on your use case (geo, upsert, insert and replace). There's definite variation with feature parity and performance that makes either a better choice depending on use case.

Yeah I'm seeing postgres far more than MySQL, and even in larger businesses I've seen a lot moving from Oracle/SQL Server to PostgreSQL.

EnterpriseDB offers some additional compatibility features in their flavor of Postgresql. It looked a lot easier than a move to MySQL.

There's been a definite surge the past 4 years [1] however I think at least part of that is due to growth of extremely capable MySQL forks (MariaDB and Percona) [2].

[1] https://www.hntrends.com/2018/oct-react-holds-off-python.htm...

[2] https://trends.google.com/trends/explore?date=2012-01-01%202...

Another mentions-based trends site https://db-engines.com/en/ranking_trend that shows Postgres uptrend. Most surprising thing to me there is that MongoDB is still going up.

Is anyone using Mongo in a large volume production system?

Lots of major players are using it: https://www.mongodb.com/who-uses-mongodb

We use it for large volumes of machine learning features and it's been amazing.

Major players using it doesn't mean it's used for large-scale projects. I've seen several companies testing it but none of them uses it for any of their core production systems.

MongoDB has improved a lot in the last few years.

Not really, reading HN / Reddit doesn't reflect of the real world. Most people I know use MySQL / Aurora.

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

I find this hard to believe.

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

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

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

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.

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!

That's a shame, and increasingly out of touch.

Yeah, it pays way more.

If you're saying there are more Oracle DBAs because they are paid more, that's the opposite of the point I was making. Basic supply/demand economics would indicate that you'll have a surplus of something that is falling out of favor, and that surplus will be inexpensive. There would be many looking for jobs because it used to pay well when it was in demand and there was a shortage, not because they're making lots of money looking for jobs.

If you're having trouble finding decent Postgres DBAs to hire, it could be that other companies are snapping up all the good ones, and that you're not offering enough.

You're presenting anecdotes, I'm presenting anecdotes. Exciting stuff. Market shifts take quite a few years, so I'm sure there are still plenty of high paying Oracle DBA jobs, and I'm sure that there will probably always be some of those jobs to support legacy systems.

Totally agree. We're in NYC and so hard to find a very very good PostgreSQ DBA.

Find an Oracle DBA instead. The similarity is obvious, and PostgreSQL have less quirks.

Most businesses are right now transitioning to the cloud. And so often they are moving to managed databases like Aurora, Redshift etc where things like replication are handled for you.

Personally not seeing large scale move away from MySQL.

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

Perhaps, but I suspect people who are migrating from mysql are moving to mariadb, not postresql.

> Postgres seems so chock-full of features now

Now? PostgreSQL has always been more feature-rich ( especially compared to mysql ). What made mysql popular was it's lack of features ( simplicity ) and its tie-in with php/etc web development. Web developers loved mysql because it was simple and easier to manage because it was so feature poor. Give credit where it is due. MySQL really rode the web development momentum. But PostgreSQL has always been the more mature RDBMS.

This is a basic features list ( maybe somewhat biased and somewhat dated ) but the overall trend still holds today.

http://www.postgresqltutorial.com/postgresql-vs-mysql/

Postgres has really picked up steam with enterprise and institutional users in my neck of the woods also [southwestern US]. The emphasis on role-based security and, lately, the ability to handle JSON, geolocation data, etc [plus finally getting some overdue query features like ROLLUP] have made some folks who previously swore by SQL Server or Oracle take it more seriously as an option for inhouse systems.

I see a fair number of local job postings that specify Postgres experience.

Yes, the most of migrations to PostgreSQL is not from MySQL, but from Oracle.

At least on HN PostgreSQL is discussed 60% more than MySQL, but both are discussed positively:

https://hnprofile.com/compare?search=postgres,mysql

Reddit has PostgreSQL up by about 40% over MySQL:

https://redditprofile.com/compare?search=postgres,mysql

[1] https://imgur.com/a/COee2qz

One small thing: AWS Aurora MySQL is lightyears ahead of AWS Aurora PostgreSQL.

As an AWS and PostgreSQL user, I wish it were the other way around, but that is currently the unfortunate truth.

PostgreSQL has been in my toolbox since 2003 having proved itself as reliable, stable, always free, well tested, and with a vibrant community on IRC. freenode #postgresql The company I built in 2003 is still using PostgreSQL for all of its operating data, web services, APIs, and royalty calculations.

Someone once said, and I agree, "PostgreSQL is the answer unless proven otherwise." If you've not tried PostgreSQL I encourage you to give it a whirl, I think you'll like it. I'll also say that #postgresql is really a friendly place for newbies and experts alike.

All major SQL databases support extensions though.

Reply on news.ycombinator.com