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

91–100 of 162 posts

Re: PostgreSQL 11.3 and 10.8

#91

Post by mixmastamyk »

evanelias wrote: post #87
mixmastamyk wrote: post #53

Earlier quoted context omitted.

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

To be clearer, it's about the defaults regarding strictness (of the acceptance) of data that help to avoid trouble up front, not that mysql randomly corrupts/deletes it later.

Re: PostgreSQL 11.3 and 10.8

#92

Post by zkomp »

colanderman wrote: post #89
Macha wrote: post #75

Earlier quoted context omitted.

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.

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

Re: PostgreSQL 11.3 and 10.8

#93

Post by paulddraper »

zkomp wrote: post #92
colanderman wrote: post #89

Earlier quoted context omitted.

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.

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

This is the best news I've heard all week.

Re: PostgreSQL 11.3 and 10.8

#94

Post by evanelias »

mixmastamyk wrote: post #91
evanelias wrote: post #87

Earlier quoted context omitted.

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

To be clearer, it's about the defaults regarding strictness (of the acceptance) of data that help to avoid trouble up front, not that mysql randomly corrupts/deletes it later.

Sure, and MySQL 5.7, released 4 years ago, fixed those bad defaults.

It isn't fair to compare Postgres-of-today to MySQL-of-over-4-years-ago.

Re: PostgreSQL 11.3 and 10.8

#95

Post by Deimorz »

atombender wrote: post #81
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 comple…

I use transactional DDL in my tests. All the tables, triggers, etc. are set up inside a transaction, and then the actual tests run inside nested transactions. At the end of the test run, the outer transaction gets rolled back, and everything disappears.

I don't know if it accomplishes anything truly new (other than ideas that aren't very useful in practice like being able to have multiple test runs going in parallel), but it's a pretty neat way to be able to do it and works well.

Re: PostgreSQL 11.3 and 10.8

#96

Post by mixmastamyk »

evanelias wrote: post #94
mixmastamyk wrote: post #91

Earlier quoted context omitted.

To be clearer, it's about the defaults regarding strictness (of the acceptance) of data that help to avoid trouble up front, not that mysql randomly corrupts/deletes it later.

Sure, and MySQL 5.7, released 4 years ago, fixed those bad defaults. It isn't fair to compare Postgres-of-today to MySQL-of-over-4-years-ago.

Ok, looks like that version is in LTS dists now. Perhaps to poster's legacy apps are taking advantage of them.

There were other deficiencies mentioned in Klepmann's book on Designing Data apps, but I don't remember the specifics now.

Re: PostgreSQL 11.3 and 10.8

#97

Post by rch »

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

Re: PostgreSQL 11.3 and 10.8

#98

Post by hans_castorp »

colanderman wrote: post #89
Macha wrote: post #75

Earlier quoted context omitted.

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.

Which is very often a good thing. I have tuned more than one query by moving a sub-query/derived table into a CTE.

What bothers me more, that a CTE prevents parallel execution, but I think that too is fixed with Postgres 12

Re: PostgreSQL 11.3 and 10.8

#99

Post by jeltz »

zkomp wrote: post #92
colanderman wrote: post #89

Earlier quoted context omitted.

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.

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

My favorite feature of PostgreSQL 12 except perhaps REINDEX CONCURRENTLY, but I am very biased since I was involved in both patches (both were large projects involving many devs and reviewers). It is awesome to finally see both land.

Re: PostgreSQL 11.3 and 10.8

#100

Post by htn »

rooam-dev wrote: post #39

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.

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