12 ms·

How Postgres is more than a relational database: Extensions

161 points by yannikyeo 8y ago

mr_overalls 8y ago
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?
- nateguchi 8y ago
  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.
- jordanthoms 8y ago
  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.
  - paulryanrogers 8y ago
    EnterpriseDB offers some additional compatibility features in their flavor of Postgresql. It looked a lot easier than a move to MySQL.
- meritt 8y ago
  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.html?compare=MySQL&compare=Postgresql&compare=&compare= https://www.hntrends.com/2018/oct-react-holds-off-python.htm... [2] https://trends.google.com/trends/explore?date=2012-01-01%202018-11-28&geo=US&q=mariadb,percona https://trends.google.com/trends/explore?date=2012-01-01%202...
  - mlinksva 8y ago
    Another mentions-based trends site https://db-engines.com/en/ranking_trend https://db-engines.com/en/ranking_trend that shows Postgres uptrend. Most surprising thing to me there is that MongoDB is still going up.
    - nateguchi 8y ago
      Is anyone using Mongo in a large volume production system?
      - threeseed 8y ago
        Lots of major players are using it: https://www.mongodb.com/who-uses-mongodb https://www.mongodb.com/who-uses-mongodb We use it for large volumes of machine learning features and it's been amazing.
        dx034 8y ago
        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.
      - emmelaich 8y ago
        MongoDB has improved a lot in the last few years.
- Thaxll 8y ago
  Not really, reading HN / Reddit doesn't reflect of the real world. Most people I know use MySQL / Aurora.
- diminoten 8y ago
  I dunno, it's still pretty damn hard to find a DBA with relevant PostgreSQL experience on the east coast...
  - ghotli 8y ago
    I find this hard to believe.
    - diminoten 8y ago
      Have you tried hiring one lately? Oracle experience out the wazoo, but PostgreSQL experience is much harder to come by.
      - coder543 8y ago
        Maybe there's a reason that there's a surplus of Oracle DBAs and a shortage of Postgres DBAs.
        penagwin 8y ago
        I'm in uni and everything is Microsoft, Oracle and Cisco.
        coder543 8y ago
        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.
- threeseed 8y ago
  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.
- liftbigweights 8y ago
  > 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/ http://www.postgresqltutorial.com/postgresql-vs-mysql/
  - MisterOctober 8y ago
    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.
    - snaky 8y ago
      Yes, the most of migrations to PostgreSQL is not from MySQL, but from Oracle.
- lettergram 8y ago
  At least on HN PostgreSQL is discussed 60% more than MySQL, but both are discussed positively: https://hnprofile.com/compare?search=postgres,mysql https://hnprofile.com/compare?search=postgres,mysql Reddit has PostgreSQL up by about 40% over MySQL: https://redditprofile.com/compare?search=postgres,mysql https://redditprofile.com/compare?search=postgres,mysql [1] https://imgur.com/a/COee2qz https://imgur.com/a/COee2qz
- paulddraper 8y ago
  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.
- dano 8y ago
  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.
jonatron 8y ago
It's also easy to create C language user defined functions: www.postgresql.org/docs/9.0/xfunc-c.html
- codetrotter 8y ago
  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
just_myles 8y ago
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.
rleigh 8y ago
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.
pjmlp 8y ago
All major SQL databases support extensions though.
gmenegatti 8y ago
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 8y ago
  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.7.42.10 https://www.postgresql.org/docs/11/postgres-fdw.html#id-1.11...

161 points by yannikyeo 8y ago

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?

nateguchi 8y ago
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.
jordanthoms 8y ago
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.
- paulryanrogers 8y ago
  EnterpriseDB offers some additional compatibility features in their flavor of Postgresql. It looked a lot easier than a move to MySQL.
meritt 8y ago
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.html?compare=MySQL&compare=Postgresql&compare=&compare= https://www.hntrends.com/2018/oct-react-holds-off-python.htm... [2] https://trends.google.com/trends/explore?date=2012-01-01%202018-11-28&geo=US&q=mariadb,percona https://trends.google.com/trends/explore?date=2012-01-01%202...
- mlinksva 8y ago
  Another mentions-based trends site https://db-engines.com/en/ranking_trend https://db-engines.com/en/ranking_trend that shows Postgres uptrend. Most surprising thing to me there is that MongoDB is still going up.
  - nateguchi 8y ago
    Is anyone using Mongo in a large volume production system?
    - threeseed 8y ago
      Lots of major players are using it: https://www.mongodb.com/who-uses-mongodb https://www.mongodb.com/who-uses-mongodb We use it for large volumes of machine learning features and it's been amazing.
      - dx034 8y ago
        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.
    - emmelaich 8y ago
      MongoDB has improved a lot in the last few years.
Thaxll 8y ago
Not really, reading HN / Reddit doesn't reflect of the real world. Most people I know use MySQL / Aurora.
diminoten 8y ago
I dunno, it's still pretty damn hard to find a DBA with relevant PostgreSQL experience on the east coast...
- ghotli 8y ago
  I find this hard to believe.
  - diminoten 8y ago
    Have you tried hiring one lately? Oracle experience out the wazoo, but PostgreSQL experience is much harder to come by.
    - coder543 8y ago
      Maybe there's a reason that there's a surplus of Oracle DBAs and a shortage of Postgres DBAs.
      - penagwin 8y ago
        I'm in uni and everything is Microsoft, Oracle and Cisco.
        coder543 8y ago
        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.
threeseed 8y ago
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.
liftbigweights 8y ago
> 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/ http://www.postgresqltutorial.com/postgresql-vs-mysql/
- MisterOctober 8y ago
  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.
  - snaky 8y ago
    Yes, the most of migrations to PostgreSQL is not from MySQL, but from Oracle.
lettergram 8y ago
At least on HN PostgreSQL is discussed 60% more than MySQL, but both are discussed positively: https://hnprofile.com/compare?search=postgres,mysql https://hnprofile.com/compare?search=postgres,mysql Reddit has PostgreSQL up by about 40% over MySQL: https://redditprofile.com/compare?search=postgres,mysql https://redditprofile.com/compare?search=postgres,mysql [1] https://imgur.com/a/COee2qz https://imgur.com/a/COee2qz
paulddraper 8y ago
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.
dano 8y ago
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.

jonatron 8y ago

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

codetrotter 8y ago
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

just_myles 8y ago

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.

rleigh 8y ago

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.

pjmlp 8y ago

All major SQL databases support extensions though.

gmenegatti 8y ago

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 8y ago
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.7.42.10 https://www.postgresql.org/docs/11/postgres-fdw.html#id-1.11...