PostgreSQL 11.3 and 10.8 (opens in new tab)

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?

I've just upgraded my hobby app from 9.6 to 11 and some of my old queries are now timing out :-|

I'll update this thread when I find out why.

One big argument: Transactional DDL. For example:

  begin;
  alter table foos add answer int not null default 42;
  alter table foos drop column plumbus;
  update foos set name = upper(name);
  create table bars (t serial);
  drop table dingbats;
  rollback;  // Or, of course, commit

What's the benefit? Atomic migrations. You can create, alter, drop tables, update data, etc. in a single transaction, and it will either commit complete if all the changes succeed, or roll back everything.

This is not possible in MySQL, or almost any other database [1], including Oracle — DDL statements aren't usually transactional. (In MySQL, I believe a DDL statement implicits commits the current transactions without warning, but I could be wrong.)

Beyond that, I'd mention: PostGIS, arrays, functional indexes, and window functions. You may not use these things today, but once you discover them, you're bound to.

[1] https://wiki.postgresql.org/wiki/Transactional_DDL_in_Postgr...

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

If there is an analytics db/replica, your data analysts will worship the ground you walk on if you migrate from MySQL to Postgres.

- A robust security model with role inheritance that supports both column and row policies.

- PLV8/PLPython/C functions/etc (with security!)

- TimescaleDB

- Better JSON query support

- Foreign Data Wrappers

- Better window function support

- A richer extension ecosystem (IMO)

Honestly, at this point I wouldn't use MySQL unless you only care about slightly better performance for very simple queries and simpler multi-master scaling/replication. Even saying that, if you don't need that simple multi-master scaling RIGHT NOW, improvements to the Postgres multi-master scaling story are not too far off on the roadmap, so I would still choose PG in that case.

The benefits are better defaults in terms of data reliability. Hard to say if migration is worth it to you without a lot more details, but I'd definitely recommend trying it in a new project.

I can't remember the last time I started a project using MySQL, may be it is catching up - but PITR, partition tables and document-oriented columns are some features I use a lot. Postgis also used to be stronger then the MySQL counterpart.

I'm pretty sure you're frequently using LIMIT, which (TIL) is non-standard SQL. (PostgreSQL shares that syntax though.)

Actually, 11 is now in Beta. If you create a new instance it is listed as an option.

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.

Citus extension:

https://github.com/citusdata/citus

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.

One option for automated health monitoring and failover is pglookout: https://github.com/aiven/pglookout

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.

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

SQL alchemy is good for fairly straightforward queries where you mainly need to do "select * from ..." and you want to pull down related rows from another table based on a foreign key in the first table as a separate query. It's also good if you have a lot of junior devs that don't know SQL and you want to encapsulate complex sql logic for them.

If you're doing anything more complex than these basic sorts of queries and subqueries, or your developers are proficient in sql, using even a very good ORM like sqlalchemy is going to be a step down.

Since you say you're doing this all yourself, and SQL is probably the most ubiquitous programming language (in terms of percentage of jobs requiring it, not total LOC) so learning opportunities there are more valuable, I would go direct.

I have used Postgres with both Django and Flask quite a bit now.

Since you're probably used to dealing with and migrating your tables manually, I would keep custom SQL for all your complex operations, and use SQLAlchemy for doing basic insert/update/select. Django also has an "unmanaged" mode where you can create a model and it will avoid trying to create a migration to create the table.

Of course, you have to manually update the model if you manually change your DDL.

Watch out for differences on how you are serializing data from Django/SQLAlchemy models vs. raw dicts from PsychoPG.

I like to organize my SQL by keeping each query in a separate .sql file and writing a little wrapper that fetches the files (+1 for caching it) and then executing it. I'm not a fan of lots of inline SQL mixed with Python.

Overall I think it's a great + powerful setup!

Pyscopg2 + Raw SQL inside of “”” “”” strings, and use %(foo)s as a parameter placeholder. Cur.execute will accept a parameter dictionary like:

cur.execute(query, {‘foo’: bar})

Passing values directly into cur.execute is the best way to prevent SQL injection as well since it will sanitize the input params upon running

Fellow Python-Postgres web dev here. (Pyramid is my framework of choice, check it out!)

I'm developing a web application that uses SQLAlchemy. The ORM has been a huge boon for CRUD functionality. We also have some very complicated reporting features and use SQLAlchemy's query builder almost exclusively. I find that the query builder maps very cleanly to SQL, so I can still "think" in SQL while writing and reading it. And the query builder makes complex query composition easier to manage.

I've found that 'sqlalchemy.sql.text' works well for complex queries that don't need to be built up incrementally, and the fluent sql interface is great otherwise. Also, reflection can be really useful when working with existing databases, and for maintenance scripts that might not need to be tied directly to your model.

SQLAlchemy provides more than just the ORM... I actually wish the docs were structured differently to better emphasize that in search results, etc.

You can use SQLAlchemy Core for SQL generation and execution, without using its ORM. This lets you build queries from reusable Python objects rather than strings, and use Alembic for DB migrations, while still retaining control over the generated SQL.

> likely to change

Hard to say, but don't forget about migration support, which is quite helpful.

Does AWS Aurora actually use postgres or is it simply a postgres compatible API on top of their own technology?

this is a huge issue for us and I'm extremely unhappy this was not clear in the docs

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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.

why did you have to bring Python into this? Every language used widely will have "legacy" crap.

not broken, don't fix