Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL 11.3 and 10.8

postgresql.org

Post reply on HN
Print view View original

81–90 of 162 posts

Re: PostgreSQL 11.3 and 10.8

#81

Post by atombender »

eberkund wrote: post #44

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.

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

Re: PostgreSQL 11.3 and 10.8

#82

Post by dliff »

kumarvvr wrote: post #47

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 unnecessa…

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!

Re: PostgreSQL 11.3 and 10.8

#83

Post by kumarvvr »

dliff wrote: post #82
kumarvvr wrote: post #47

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 unnecessa…

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 manual…

I usually put all my SQL statements in a single python file, and import the file as a module. I get to have descriptive variable names for the queries.

Re: PostgreSQL 11.3 and 10.8

#84

Post by duckehlabs »

throw0101a wrote: post #69
combatentropy wrote: post #52

Earlier quoted context omitted.

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.

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.

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

Re: PostgreSQL 11.3 and 10.8

#85

Post by evanelias »

kangoo1707 wrote: post #51
eberkund wrote: post #44

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.

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 availab…

> Indexing on function expression

MySQL 5.7 fully supports this. See https://dev.mysql.com/doc/refman/5.7/en/create-table-generat... and https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda...

> JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres)

Actually MySQL 5.6 doesn't support this, but 5.7 does, quite well: https://dev.mysql.com/doc/refman/5.7/en/json.html

Re: PostgreSQL 11.3 and 10.8

#86

Post by GordonS »

AlphaSite wrote: post #80
grouseway wrote: post #68

Earlier quoted context omitted.

There are no more server CPUs with less than 6 cores though. We could happily manage with just 2 dedicated to SQL server, but you have to license all the cores you have. So 3K worth of low end hardware ends up costing you several multiples of that in licensing.

I imagine a VM is a good solution to that.

Windows also let's you set CPU affinity per process - could that be used as a simple workaround?

Re: PostgreSQL 11.3 and 10.8

#87

Post by evanelias »

mixmastamyk wrote: post #53
eberkund wrote: post #44

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.

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.

Frankly, data reliability concerns with modern MySQL / InnoDB are very outdated FUD.

Many of the largest tech companies rely on MySQL as their primary data store. They would not do so if it was unreliable with persistence.

There are many valid reasons to choose Postgres over MySQL, or vice versa -- they have different strengths and weaknesses. But there are no major differences regarding data reliability today, nor have there been for many years now.

Re: PostgreSQL 11.3 and 10.8

#88

Post by oarabbus_ »

eberkund wrote: post #44

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.

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

Re: PostgreSQL 11.3 and 10.8

#89

Post by colanderman »

Macha wrote: post #75
kangoo1707 wrote: post #51

Earlier quoted context omitted.

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 availab…

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.

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.

Re: PostgreSQL 11.3 and 10.8

#90

Post by sandstrom »

oarabbus_ wrote: post #88
eberkund wrote: post #44

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.

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

Interesting, can you elaborate, I'm considering a switch.
Post reply on HN