14 ms·

PostgreSQL 11.3 and 10.8

281 points by oskari 7y ago

deleted 7y ago
[deleted]
mistrial9 7y ago
impressive and .. upgrade on 10.x now in process, easily, quickly, thanks to the Postgres PGDG Debian/Ubuntu repos .. BUT do not choose meta-package postgres ! Under Ubuntu at least, upgrading the meta-package postgres adds an entire new server 11+ without confirmation .. why is this tolerated.. genuinely annoying
- shawnz 7y ago
  I think you are looking for "apt-get upgrade" and not "apt-get dist-upgrade". Or, just install the version you specifically want
micmil 7y ago
I'm not a database guy so have no clue, but why are there so many versions receiving support? Is there just that much legacy crap they can't get away from, like Python?
- Alex3917 7y ago
  Postgres users actually generally upgrade faster than those using other databases because there are a lot of new features each year. But once your database gets huge then upgrading still becomes a pain, so that's why they keep providing security support and bug fixes for older versions as well.
  - hans_castorp 7y ago
    pg_upgrade with the --link option is extremely fast and doesn't really depend on the size of the database.
    - Alex3917 7y ago
      Interesting. I just use RDS and it always seems to take 15+ min even though our database is tiny.
- todd3834 7y ago
  With semantic versioning each time the major version changes it signifies a breaking change. If you have an application that breaks from one of those breaking changes you may not see it as a business opportunity to update because it “works” as it is. However, minor version changes can include anything that doesn’t break. So security patches are hopefully added to any major version that is officially supported.
  - grzm 7y ago
    PostgreSQL versioning is similar to semantic versioning, but doesn't follow it precisely. Major versions require a dump and restore (or other transform, such as an upgrade) of the on-disk data. Minor versions are fixes. Prior to PostgreSQL 10, the changes in the second numeric place are considered "major" versions. So, the past 5 major versions are 11, 10, 9.6, 9.5, and 9.4. The most recent versions of each of those are respectively 11.3, 10.8, 9.6.13, 9.5.17, and 9.4.22.
- ddorian43 7y ago
  It's not "legacy crap". There just is long-term-support for versions cause it's not that easy to upgrade (both technically & others). There's legacy crap everywhere, all langs,db,versions etc. Supported sometimes for 10+ years.
- Erwin 7y ago
  With databases being often mission critical, the PostgreSQL people decided heroically to support major versions for 5 years -- and as they come out with a new major version every year, minor updates come out for 5 different branches. Note the recent versioning change: 9.4, 9.5, 9.6 were the previous 3 major versions bases, and the last two are 10 and 11.
- profmonocle 7y ago
  Moving between major versions of Postgres requires downtime proportional to the size of the database. Supporting older versions allows users to go many years without having to do this.
  - Symbiote 7y ago
    I upgraded from 9.3 → 11.2 a few months ago using pg_upgrade[1], on a master+slave database with 150GB of data. I did a fair amount of testing, but the final procedure was very fast and smooth. 1. Test the upgrade: set up an additional secondary (9.3), break the replication link (promote it to a master). Test the upgrade on that. It was really fast, under 30 seconds to shut down the old DB, run the in-place upgrade, and start up the new DB. 2a. In production: set up an additional secondary (9.3). Make the primary read-only. Promote the new secondary to a master. Shut down, upgrade to 11.2, restart. Point applications at it. 2b. Backout plan: leave the applications pointing at the original database server, make it read-write. There are other options, including with only seconds of downtime, but <1 minute with pg_upgrade was simple and very acceptable for us. [1] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html [2] https://www.postgresql.org/docs/current/upgrading.html https://www.postgresql.org/docs/current/upgrading.html
    - devereaux 7y ago
      This is a nice way to do that, but you have a low volume of data, and you think 30 seconds is fast and 1 minute of downtime is acceptable. I question these assumptions. Consider the situation when you're adding thousands of new records per seconds, and the database is being used every second (quite literally: to compute per seconds statistics). A better solution is to have triggers on the old master, to do the same inserts on the new master (after copying the data/promoting a replica/whatever), and have similar triggers on the new master when the IP is not the old master (to be able to backout to the old server) Then both the new and the old master run "in parallel", with the same data, and you can have the apps use the new server (on a new domain name, new ip, new port, whatever) when you want - on a app by app basis if you want. You can keep both until you decide to decommission the old master.
- deleted 7y ago
  [deleted]
- dspillett 7y ago
  People are slow to upgrade database systems, as it can take a log of regression testing to make absolutely sure your applications don't rely on unsupported/undocumented/undefined behaviours that make them compatible with the newest release (or are affected by officially acknowledged breaking changes). Especially in enterprise systems. Even if developers upgrade quickly, their clients with on-prem installations may not. That means that to be taken seriously you need to support your major and minor releases for some time to be accepted as a serious option in some arenas. Supporting five versions is no more than MS do: currently SQL Server versions 2017, 2016sp2, 2016sp1, 2014sp3, 2014sp2, 2012sp4, 2008R2sp2 and 2008sp3. 2008sp3, 2008R2sp2, and 2016sp1 will hit their final EOL in a couple of months taking SQL Servers's supported list back down to 5 too. I expect other significant DB maintainers have similar support life-time requirements for much the same reasons, though I'll leave researching who does[n't] as an exercise for the reader.
  - greggyb 7y ago
    2008 and R2 are still in a supported phase of life. It's the "exorbitant support fee" phase. Nevertheless, you can still get Microsoft support for the two after the "EOL". It's more an end-of-public life
    - dspillett 7y ago
      Aye, and by the same technicality you can still get support for 2005. Similar with PG I assume. You could always pay someone an expensive contracting fee to support your use of an older version than is publicly supported.
- baq 7y ago
  not broken, don't fix
  - chungy 7y ago
    There are some shockingly old releases of PostgreSQL still in production for this reason. Security updates should push the upgrade path a little harder, but there are still cases where a database can be completely isolated from the network and that might not even matter.
    - Torgo 7y ago
      I inherited a production system with a PostgreSQL 8.1 database. It's one of the most reliable systems I have.
- 75dvtwin 7y ago
  Well supported older releases of the database engine, with clearly defined migration documentation and technology -- are the hallmark of successful Open source software ecosystem. Because it mirrors and supports the reality of the business world. Every large or small organization that manages their business, every year make 'Grow/Invest', 'Maintain', 'Disinvest' decision for each of the product/service lines. Does not matter if is software, or making kielbasa. Postgres is exceptional, and is supporting the first 2.
- sargun 7y ago
  1) It's stateful, so upgrades also have to upgrade the state (MBs, GBs, TBs of data) 2) It's horrifically high risk because downgrading is usually not a thing 3) It usually requires downtime.
- FraaJad 7y ago
  why did you have to bring Python into this? Every language used widely will have "legacy" crap.
rtpg 7y ago
For those stuck on older versions of Postgres, I highly recommend paying the downtime to upgrade. Going from 9.x to 11 will get you a measurably large performance gain for free.
- dspillett 7y ago
  Out of interest (SQL Server guy mainly, so only partly keep up with what other engines are doing), what changes significantly affect performance (without making changes to your own code/configuration to make use of new features) in 10.x & 11.x?
  - greggyb 7y ago
    Big parallelism updates that the query planner can take advantage of. I believe also updates to index seek or scan in that time.
  - fabian2k 7y ago
    There is usually a bunch of small improvements in every release, and those can add up over time. In Postgres 10 and 11 a lot of stuff happened related to parallel queries, and many more queries can be run in parallel now. 11 added a JIT compiler to the query planner, but I'm not sure whether that is enabled by default yet.
  - cldellow 7y ago
    The query planner in 10 got a lot better at enforcing row-level security constraints efficiently for some common scenarios, like 10-20x speedups. See https://github.com/postgres/postgres/commit/215b43cdc8d6b4a1700886a39df1ee735cb0274d https://github.com/postgres/postgres/commit/215b43cdc8d6b4a1... and the linked mailing list thread for more info, if you're curious.
  - Scarbutt 7y ago
    Out of interest ;) SQL Server is such an expensive beast, ~$15K per core, what are your reasons for prefering it over PG?
SnowingXIV 7y ago
Running 10.7 and 10.6 on two production applications with Heroku. Thinking about moving to 11 to ensure support for the long run as I rarely need to touch this and it's very stable but would like to minimize any headaches in the future. Any complications or hiccups I need to worry about moving from 10 to 11? Per Heroku Docs: By supporting at least 3 major versions, users are required to upgrade roughly once every three years. However, you can upgrade your database at any point to gain the benefits of the latest version.
- skymt 7y ago
  The release notes for version 11 include a list of potentially incompatible changes: https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.8.4 https://www.postgresql.org/docs/11/release-11.html#id-1.11.6...
  - SnowingXIV 7y ago
    Thanks, yeah I just did some testing locally and made the upgrade on Heroku (the documentation was rock solid).
Tomdarkness 7y ago
Totally wish we could upgrade but for some reason AWS have still not implemented any upgrade path for Aurora PostgreSQL other than dump and reimport despite apparently working on it for a year...
- jontonsoup 7y ago
  this is a huge issue for us and I'm extremely unhappy this was not clear in the docs
  - Tomdarkness 7y ago
    What's worse is the documentation straight up lies. It states you can perform a major version upgrade by resorting a snapshot and selecting a higher version. I mean it's still not ideal except if you do try this you'll find the option doesn't actually exist - either via the console or API/CLI! https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.Upgrading.html#USER_UpgradeDBInstance.Upgrading.Manual https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... This has really put us off using other AWS managed products and was a major factor in us deciding against using Amazon Elasticsearch Service.
- mevile 7y ago
  Does AWS Aurora actually use postgres or is it simply a postgres compatible API on top of their own technology?
  - jadbox 7y ago
    I'm pretty sure it's a fork of PG based on my experience.
  - darkr 7y ago
    As with RDS Postgres, it's Amazon's fork of Postgres. With Aurora, the storage layer is swapped out entirely for a distributed storage engine, that I believe is based upon DynamoDB. The wire protocol and server interface are much the same as regular Postgres, though there are some additional benefits as well as caveats as you might expect
dochtman 7y ago
It's unfortunate that the official Docker images haven't been updated yet (on DockerHub).
- Xylakant 7y ago
  keep in mind that the "official" docker images are "offical" in the sense of docker inc marking them as official, not in the sense of "the upstream provides these". This is the repo for the Dockerfiles https://github.com/docker-library/postgres https://github.com/docker-library/postgres and it begins with: > This is the Git repo of the Docker "Official Image" for postgres (not to be confused with any official postgres image provided by postgres upstream)
brightball 7y ago
It's hard to believe that Google Cloud SQL still only has 9.6 available. EDIT: Apparently 11.1 is available in beta as of April 9th.
- cglace 7y ago
  Actually, 11 is now in Beta. If you create a new instance it is listed as an option.
  - brightball 7y ago
    I tested it just before I posted the comment (to confirm) and didn't see it listed. Maybe it depends on your account? EDIT: I'll try again. Looks like it was added April 9th https://cloud.google.com/sql/docs/postgres/create-instance https://cloud.google.com/sql/docs/postgres/create-instance
    - fernandotakai 7y ago
      i created an 11 (beta) instance yesterday and it worked as expected.
rooam-dev 7y ago
Question for PG happy users. How do you manage failover and replication? At my previous job this was done by a consultant. Is this doable on a self hosted setup? Thank you in advance.
- cromantin 7y ago
  We've been doing replication for 3+ years with zolando patroni. It works great. We run pg in docker and patroni too. First it was patroni with consul and right now its patroni with kubernes store (it store leader in endpoint). Highly recommend. There are other popular tools for this, it just a preference.
- truth_seeker 7y ago
  Citus extension: https://github.com/citusdata/citus https://github.com/citusdata/citus
- combatentropy 7y ago
  PostgreSQL has replication built in now. I set it up at work, and it replicates reliably, in a fraction of a second. I've never had to fail over, but it seems straightforward to do so. The only hard part was following Postgres's documentation in setting it all up. It seemed to me a bit scattered to me. I had to jump around to different sections before I put it all together in my mind.
  - throw0101a 7y ago
    What do you use? Are there some instructions/articles that you'd recommend reading? Is it anything like Galera? I know of BDR, but there hasn't much news about it lately, especially with more recent versions of Pg. We like Galera for our simple needs: we use keepalived to do health checks, and if they pass the node participates in the VRRP cluster. If one node goes down/bad, another takes over.
    - duckehlabs 7y ago
      If you want multi master in Postgres, I think BDR is going to be your best option, but the version for PG 10+ isn't open source so you'll have to pay for it. We're using the open source version on PG 9.4 currently in production, it's worked fine so far. If you're just looking for a hot standby and dont need a multi master setup, you can set those up just with pg. https://www.postgresql.org/docs/9.4/hot-standby.html https://www.postgresql.org/docs/9.4/hot-standby.html
eberkund 7y ago
I maintain a couple of MySQL based applications. I don't really use any features outside of "standard SQL" is there a reason to switch over to Pg? I haven't used Pg before and usually default to MySQL.
- kangoo1707 7y ago
  At my PHP-shop company, most projects are limited to MySQL 5.7 (legacy reason, dependency reason, boss-likes-MySQL reason...). They are all handicapped by MySQL featureset, and can't update to 8 yet. If they had used Postgres some years ago, they would get: - JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres) - Window functions (available in MySQL 8x only, while this has been available since Postgres 9x) - Materialized views, views that is physical like a table, can be used to store aggregated, pre-calculated data like sum, count... - Indexing on function expression - Better query plan explanation
  - Macha 7y ago
    Also suffering under mysql 5.7 here and agree. Also even stuff like CTEs/WITH make queries more readable and composite field types like ARRAY are still missing (you see GROUP_CONCAT shenanigans being used instead). For indexing on function expressions in particular, the workaround we use is to add a generated column and index that.
    - colanderman 7y ago
      Be warned that in PostgreSQL, WITH is an optimization barrier, and is planned to remain that way to serve that purpose. If you can, prefer using views to enhance readability (and testability as a bonus). PostgreSQL views (unlike those in MySQL) do not prevent optimization across them.
      - zkomp 7y ago
        No, CTEs are not planned to remain a barrier, this is already fixed in the next version which is in feature freeze right now. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
kumarvvr 7y ago
Question from a Python web developer. (Django mainly, exploring Flask presently) For a complex web-app, would you suggest an ORM (looking at SQLAlchemy) or a custom module with hand written queries and custom methods for conversion to python objects? My app has a lot of complex queries, joins, etc. and the data-model is most likely to change quite a bit as the app nears production. I feel using an ORM is an unnecessary layer of abstraction in the thinking process. I feel comfortable with direct SQL queries, and in some cases, want to directly get JSON results from PGSQL itself. Would that be a good idea, and more importantly, scalable? Note : My app will be solely developed by me, not expecting to have a team or even another developer work on it.
- mixmastamyk 7y ago
  > likely to change Hard to say, but don't forget about migration support, which is quite helpful.
- kangoo1707 7y ago
  Use both. Many of the business logics are just as simple as query by id, filter/sort by a couple of columns. A smart ORM will handle fetching relationships without hitting N+1 problem For advanced queries, you can write raw SQL The way I see it, an ORM has three useful features: - A migration/seed mechanism (you will need it anyway) - A schema definition for mapping tables to object - A query builder If you feel that an ORM is too heavy, you can seek for just the query builder.
  - fernandotakai 7y ago
    i worked on a mid-sized django app and that was basically what we did: * for normal queries (select /cols from table where id etc etc) we just used plain django orm. even for weird joins, django orm makes it a lot easier than using raw sql when we needed raw speed, we just wrote raw sql and delegated to django sql layer -- that way we leverage everything the framework has with raw sql power.
- scardine 7y ago
  Even when the ORM models start to get cumbersome I like to use sqlalchemy.sql to assemble SQL queries. It maps pretty much 1:1 to SQL and for me it beats the alternative (using text interpolation for composing queries).
throw0101a 7y ago
MySQL has Galera: is there a multi-master option for Pg? I know of BDR, earlier versions of which are open source, but there hasn't been much movement with Pg 10 or 11 AFAICT. We don't do anything complicated, but simply want two DBs (with perhaps a quorum system) that has a vIP that will fail-over in case one system goes down (scheduled or otherwise). Galera provides this in a not-too-complicated fashion.
- smilliken 7y ago
  PostgreSQL has logical replication built-in since version 10. This allows you to replicate specific tables between multiple master databases, accepting writes on each. You define a merge function in case there's conflicts.

281 points by oskari 7y ago

deleted 7y ago

[deleted]

impressive and .. upgrade on 10.x now in process, easily, quickly, thanks to the Postgres PGDG Debian/Ubuntu repos .. BUT do not choose meta-package postgres ! Under Ubuntu at least, upgrading the meta-package postgres adds an entire new server 11+ without confirmation .. why is this tolerated.. genuinely annoying

shawnz 7y ago
I think you are looking for "apt-get upgrade" and not "apt-get dist-upgrade". Or, just install the version you specifically want

micmil 7y ago

I'm not a database guy so have no clue, but why are there so many versions receiving support? Is there just that much legacy crap they can't get away from, like Python?

Alex3917 7y ago
Postgres users actually generally upgrade faster than those using other databases because there are a lot of new features each year. But once your database gets huge then upgrading still becomes a pain, so that's why they keep providing security support and bug fixes for older versions as well.
- hans_castorp 7y ago
  pg_upgrade with the --link option is extremely fast and doesn't really depend on the size of the database.
  - Alex3917 7y ago
    Interesting. I just use RDS and it always seems to take 15+ min even though our database is tiny.
todd3834 7y ago
With semantic versioning each time the major version changes it signifies a breaking change. If you have an application that breaks from one of those breaking changes you may not see it as a business opportunity to update because it “works” as it is. However, minor version changes can include anything that doesn’t break. So security patches are hopefully added to any major version that is officially supported.
- grzm 7y ago
  PostgreSQL versioning is similar to semantic versioning, but doesn't follow it precisely. Major versions require a dump and restore (or other transform, such as an upgrade) of the on-disk data. Minor versions are fixes. Prior to PostgreSQL 10, the changes in the second numeric place are considered "major" versions. So, the past 5 major versions are 11, 10, 9.6, 9.5, and 9.4. The most recent versions of each of those are respectively 11.3, 10.8, 9.6.13, 9.5.17, and 9.4.22.
ddorian43 7y ago
It's not "legacy crap". There just is long-term-support for versions cause it's not that easy to upgrade (both technically & others). There's legacy crap everywhere, all langs,db,versions etc. Supported sometimes for 10+ years.
Erwin 7y ago
With databases being often mission critical, the PostgreSQL people decided heroically to support major versions for 5 years -- and as they come out with a new major version every year, minor updates come out for 5 different branches. Note the recent versioning change: 9.4, 9.5, 9.6 were the previous 3 major versions bases, and the last two are 10 and 11.
profmonocle 7y ago
Moving between major versions of Postgres requires downtime proportional to the size of the database. Supporting older versions allows users to go many years without having to do this.
- Symbiote 7y ago
  I upgraded from 9.3 → 11.2 a few months ago using pg_upgrade[1], on a master+slave database with 150GB of data. I did a fair amount of testing, but the final procedure was very fast and smooth. 1. Test the upgrade: set up an additional secondary (9.3), break the replication link (promote it to a master). Test the upgrade on that. It was really fast, under 30 seconds to shut down the old DB, run the in-place upgrade, and start up the new DB. 2a. In production: set up an additional secondary (9.3). Make the primary read-only. Promote the new secondary to a master. Shut down, upgrade to 11.2, restart. Point applications at it. 2b. Backout plan: leave the applications pointing at the original database server, make it read-write. There are other options, including with only seconds of downtime, but <1 minute with pg_upgrade was simple and very acceptable for us. [1] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html [2] https://www.postgresql.org/docs/current/upgrading.html https://www.postgresql.org/docs/current/upgrading.html
  - devereaux 7y ago
    This is a nice way to do that, but you have a low volume of data, and you think 30 seconds is fast and 1 minute of downtime is acceptable. I question these assumptions. Consider the situation when you're adding thousands of new records per seconds, and the database is being used every second (quite literally: to compute per seconds statistics). A better solution is to have triggers on the old master, to do the same inserts on the new master (after copying the data/promoting a replica/whatever), and have similar triggers on the new master when the IP is not the old master (to be able to backout to the old server) Then both the new and the old master run "in parallel", with the same data, and you can have the apps use the new server (on a new domain name, new ip, new port, whatever) when you want - on a app by app basis if you want. You can keep both until you decide to decommission the old master.
deleted 7y ago
[deleted]
dspillett 7y ago
People are slow to upgrade database systems, as it can take a log of regression testing to make absolutely sure your applications don't rely on unsupported/undocumented/undefined behaviours that make them compatible with the newest release (or are affected by officially acknowledged breaking changes). Especially in enterprise systems. Even if developers upgrade quickly, their clients with on-prem installations may not. That means that to be taken seriously you need to support your major and minor releases for some time to be accepted as a serious option in some arenas. Supporting five versions is no more than MS do: currently SQL Server versions 2017, 2016sp2, 2016sp1, 2014sp3, 2014sp2, 2012sp4, 2008R2sp2 and 2008sp3. 2008sp3, 2008R2sp2, and 2016sp1 will hit their final EOL in a couple of months taking SQL Servers's supported list back down to 5 too. I expect other significant DB maintainers have similar support life-time requirements for much the same reasons, though I'll leave researching who does[n't] as an exercise for the reader.
- greggyb 7y ago
  2008 and R2 are still in a supported phase of life. It's the "exorbitant support fee" phase. Nevertheless, you can still get Microsoft support for the two after the "EOL". It's more an end-of-public life
  - dspillett 7y ago
    Aye, and by the same technicality you can still get support for 2005. Similar with PG I assume. You could always pay someone an expensive contracting fee to support your use of an older version than is publicly supported.
baq 7y ago
not broken, don't fix
- chungy 7y ago
  There are some shockingly old releases of PostgreSQL still in production for this reason. Security updates should push the upgrade path a little harder, but there are still cases where a database can be completely isolated from the network and that might not even matter.
  - Torgo 7y ago
    I inherited a production system with a PostgreSQL 8.1 database. It's one of the most reliable systems I have.
75dvtwin 7y ago
Well supported older releases of the database engine, with clearly defined migration documentation and technology -- are the hallmark of successful Open source software ecosystem. Because it mirrors and supports the reality of the business world. Every large or small organization that manages their business, every year make 'Grow/Invest', 'Maintain', 'Disinvest' decision for each of the product/service lines. Does not matter if is software, or making kielbasa. Postgres is exceptional, and is supporting the first 2.
sargun 7y ago
1) It's stateful, so upgrades also have to upgrade the state (MBs, GBs, TBs of data) 2) It's horrifically high risk because downgrading is usually not a thing 3) It usually requires downtime.
FraaJad 7y ago
why did you have to bring Python into this? Every language used widely will have "legacy" crap.

rtpg 7y ago

For those stuck on older versions of Postgres, I highly recommend paying the downtime to upgrade. Going from 9.x to 11 will get you a measurably large performance gain for free.

dspillett 7y ago
Out of interest (SQL Server guy mainly, so only partly keep up with what other engines are doing), what changes significantly affect performance (without making changes to your own code/configuration to make use of new features) in 10.x & 11.x?
- greggyb 7y ago
  Big parallelism updates that the query planner can take advantage of. I believe also updates to index seek or scan in that time.
- fabian2k 7y ago
  There is usually a bunch of small improvements in every release, and those can add up over time. In Postgres 10 and 11 a lot of stuff happened related to parallel queries, and many more queries can be run in parallel now. 11 added a JIT compiler to the query planner, but I'm not sure whether that is enabled by default yet.
- cldellow 7y ago
  The query planner in 10 got a lot better at enforcing row-level security constraints efficiently for some common scenarios, like 10-20x speedups. See https://github.com/postgres/postgres/commit/215b43cdc8d6b4a1700886a39df1ee735cb0274d https://github.com/postgres/postgres/commit/215b43cdc8d6b4a1... and the linked mailing list thread for more info, if you're curious.
- Scarbutt 7y ago
  Out of interest ;) SQL Server is such an expensive beast, ~$15K per core, what are your reasons for prefering it over PG?

SnowingXIV 7y ago

Running 10.7 and 10.6 on two production applications with Heroku. Thinking about moving to 11 to ensure support for the long run as I rarely need to touch this and it's very stable but would like to minimize any headaches in the future. Any complications or hiccups I need to worry about moving from 10 to 11? Per Heroku Docs: By supporting at least 3 major versions, users are required to upgrade roughly once every three years. However, you can upgrade your database at any point to gain the benefits of the latest version.

skymt 7y ago
The release notes for version 11 include a list of potentially incompatible changes: https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.8.4 https://www.postgresql.org/docs/11/release-11.html#id-1.11.6...
- SnowingXIV 7y ago
  Thanks, yeah I just did some testing locally and made the upgrade on Heroku (the documentation was rock solid).

Tomdarkness 7y ago

Totally wish we could upgrade but for some reason AWS have still not implemented any upgrade path for Aurora PostgreSQL other than dump and reimport despite apparently working on it for a year...

jontonsoup 7y ago
this is a huge issue for us and I'm extremely unhappy this was not clear in the docs
- Tomdarkness 7y ago
  What's worse is the documentation straight up lies. It states you can perform a major version upgrade by resorting a snapshot and selecting a higher version. I mean it's still not ideal except if you do try this you'll find the option doesn't actually exist - either via the console or API/CLI! https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.Upgrading.html#USER_UpgradeDBInstance.Upgrading.Manual https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... This has really put us off using other AWS managed products and was a major factor in us deciding against using Amazon Elasticsearch Service.
mevile 7y ago
Does AWS Aurora actually use postgres or is it simply a postgres compatible API on top of their own technology?
- jadbox 7y ago
  I'm pretty sure it's a fork of PG based on my experience.
- darkr 7y ago
  As with RDS Postgres, it's Amazon's fork of Postgres. With Aurora, the storage layer is swapped out entirely for a distributed storage engine, that I believe is based upon DynamoDB. The wire protocol and server interface are much the same as regular Postgres, though there are some additional benefits as well as caveats as you might expect

dochtman 7y ago

It's unfortunate that the official Docker images haven't been updated yet (on DockerHub).

Xylakant 7y ago
keep in mind that the "official" docker images are "offical" in the sense of docker inc marking them as official, not in the sense of "the upstream provides these". This is the repo for the Dockerfiles https://github.com/docker-library/postgres https://github.com/docker-library/postgres and it begins with: > This is the Git repo of the Docker "Official Image" for postgres (not to be confused with any official postgres image provided by postgres upstream)

brightball 7y ago

It's hard to believe that Google Cloud SQL still only has 9.6 available. EDIT: Apparently 11.1 is available in beta as of April 9th.

cglace 7y ago
Actually, 11 is now in Beta. If you create a new instance it is listed as an option.
- brightball 7y ago
  I tested it just before I posted the comment (to confirm) and didn't see it listed. Maybe it depends on your account? EDIT: I'll try again. Looks like it was added April 9th https://cloud.google.com/sql/docs/postgres/create-instance https://cloud.google.com/sql/docs/postgres/create-instance
  - fernandotakai 7y ago
    i created an 11 (beta) instance yesterday and it worked as expected.

rooam-dev 7y ago

Question for PG happy users. How do you manage failover and replication? At my previous job this was done by a consultant. Is this doable on a self hosted setup? Thank you in advance.

cromantin 7y ago
We've been doing replication for 3+ years with zolando patroni. It works great. We run pg in docker and patroni too. First it was patroni with consul and right now its patroni with kubernes store (it store leader in endpoint). Highly recommend. There are other popular tools for this, it just a preference.
truth_seeker 7y ago
Citus extension: https://github.com/citusdata/citus https://github.com/citusdata/citus
combatentropy 7y ago
PostgreSQL has replication built in now. I set it up at work, and it replicates reliably, in a fraction of a second. I've never had to fail over, but it seems straightforward to do so. The only hard part was following Postgres's documentation in setting it all up. It seemed to me a bit scattered to me. I had to jump around to different sections before I put it all together in my mind.
- throw0101a 7y ago
  What do you use? Are there some instructions/articles that you'd recommend reading? Is it anything like Galera? I know of BDR, but there hasn't much news about it lately, especially with more recent versions of Pg. We like Galera for our simple needs: we use keepalived to do health checks, and if they pass the node participates in the VRRP cluster. If one node goes down/bad, another takes over.
  - duckehlabs 7y ago
    If you want multi master in Postgres, I think BDR is going to be your best option, but the version for PG 10+ isn't open source so you'll have to pay for it. We're using the open source version on PG 9.4 currently in production, it's worked fine so far. If you're just looking for a hot standby and dont need a multi master setup, you can set those up just with pg. https://www.postgresql.org/docs/9.4/hot-standby.html https://www.postgresql.org/docs/9.4/hot-standby.html

eberkund 7y ago

I maintain a couple of MySQL based applications. I don't really use any features outside of "standard SQL" is there a reason to switch over to Pg? I haven't used Pg before and usually default to MySQL.

kangoo1707 7y ago
At my PHP-shop company, most projects are limited to MySQL 5.7 (legacy reason, dependency reason, boss-likes-MySQL reason...). They are all handicapped by MySQL featureset, and can't update to 8 yet. If they had used Postgres some years ago, they would get: - JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres) - Window functions (available in MySQL 8x only, while this has been available since Postgres 9x) - Materialized views, views that is physical like a table, can be used to store aggregated, pre-calculated data like sum, count... - Indexing on function expression - Better query plan explanation
- Macha 7y ago
  Also suffering under mysql 5.7 here and agree. Also even stuff like CTEs/WITH make queries more readable and composite field types like ARRAY are still missing (you see GROUP_CONCAT shenanigans being used instead). For indexing on function expressions in particular, the workaround we use is to add a generated column and index that.
  - colanderman 7y ago
    Be warned that in PostgreSQL, WITH is an optimization barrier, and is planned to remain that way to serve that purpose. If you can, prefer using views to enhance readability (and testability as a bonus). PostgreSQL views (unlike those in MySQL) do not prevent optimization across them.
    - zkomp 7y ago
      No, CTEs are not planned to remain a barrier, this is already fixed in the next version which is in feature freeze right now. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

kumarvvr 7y ago

Question from a Python web developer. (Django mainly, exploring Flask presently) For a complex web-app, would you suggest an ORM (looking at SQLAlchemy) or a custom module with hand written queries and custom methods for conversion to python objects? My app has a lot of complex queries, joins, etc. and the data-model is most likely to change quite a bit as the app nears production. I feel using an ORM is an unnecessary layer of abstraction in the thinking process. I feel comfortable with direct SQL queries, and in some cases, want to directly get JSON results from PGSQL itself. Would that be a good idea, and more importantly, scalable? Note : My app will be solely developed by me, not expecting to have a team or even another developer work on it.

mixmastamyk 7y ago
> likely to change Hard to say, but don't forget about migration support, which is quite helpful.
kangoo1707 7y ago
Use both. Many of the business logics are just as simple as query by id, filter/sort by a couple of columns. A smart ORM will handle fetching relationships without hitting N+1 problem For advanced queries, you can write raw SQL The way I see it, an ORM has three useful features: - A migration/seed mechanism (you will need it anyway) - A schema definition for mapping tables to object - A query builder If you feel that an ORM is too heavy, you can seek for just the query builder.
- fernandotakai 7y ago
  i worked on a mid-sized django app and that was basically what we did: * for normal queries (select /cols from table where id etc etc) we just used plain django orm. even for weird joins, django orm makes it a lot easier than using raw sql when we needed raw speed, we just wrote raw sql and delegated to django sql layer -- that way we leverage everything the framework has with raw sql power.
scardine 7y ago
Even when the ORM models start to get cumbersome I like to use sqlalchemy.sql to assemble SQL queries. It maps pretty much 1:1 to SQL and for me it beats the alternative (using text interpolation for composing queries).

throw0101a 7y ago

MySQL has Galera: is there a multi-master option for Pg? I know of BDR, earlier versions of which are open source, but there hasn't been much movement with Pg 10 or 11 AFAICT. We don't do anything complicated, but simply want two DBs (with perhaps a quorum system) that has a vIP that will fail-over in case one system goes down (scheduled or otherwise). Galera provides this in a not-too-complicated fashion.

smilliken 7y ago
PostgreSQL has logical replication built-in since version 10. This allows you to replicate specific tables between multiple master databases, accepting writes on each. You define a merge function in case there's conflicts.