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

111–120 of 162 posts

Re: PostgreSQL 11.3 and 10.8

#111

Post by evanelias »

mixmastamyk wrote: post #96
evanelias wrote: post #94

Earlier quoted context omitted.

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.

Even if OP is using 5.6 or previous, strict mode has been available as an option for over 15 years, and can be enabled dynamically (no restart required).

I simply don't see any valid argument for avoiding MySQL due to "data reliability" concerns in 2019.

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

Well, I can't really respond to non-specific points from a book I haven't read. I'm happy to respond to any specifics re: data reliability concerns, if you want to cite them. FWIW, I have quite extensive expertise on the subject of massive-scale MySQL (16 years of MySQL use; led development of Facebook's internal DBaaS; rebuilt most of Tumblr's backend during its hockey-stick growth period).

Re: PostgreSQL 11.3 and 10.8

#112

Post by smilliken »

throw0101a wrote: post #65

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.

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.

Re: PostgreSQL 11.3 and 10.8

#113

Post by Deimorz »

atombender wrote: post #104
Deimorz wrote: post #95

Earlier quoted context omitted.

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

Transactional tests have some downsides, unfortunately. If your tests test transactional code, that code itself cannot create transactions; they have to use savepoints, which aren't quite the same. Transactional tests also don't work with testing anything concurrent, unless you share the session across threads/goroutines/whatever. Lastly, if a test fails you'd typically like to leave the data behind so that you can i…

That's true, thanks. None of those are relevant for me, but there definitely are cases where it wouldn't be the right approach.

Re: PostgreSQL 11.3 and 10.8

#114

Post by munk-a »

atombender wrote: post #104
Deimorz wrote: post #95

Earlier quoted context omitted.

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

Transactional tests have some downsides, unfortunately. If your tests test transactional code, that code itself cannot create transactions; they have to use savepoints, which aren't quite the same. Transactional tests also don't work with testing anything concurrent, unless you share the session across threads/goroutines/whatever. Lastly, if a test fails you'd typically like to leave the data behind so that you can i…

Save points, with proper management of them, seem to match a conceptual nested transaction as far as I've seen. We've got a test bed connection manager that mocks savepoint creation, rollback and committal into transaction management functions so doing something like beginTransaction && beginTransaction works fine.

Re: PostgreSQL 11.3 and 10.8

#115

Post by luhn »

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…

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.

Re: PostgreSQL 11.3 and 10.8

#116

Post by atombender »

munk-a wrote: post #114
atombender wrote: post #104

Earlier quoted context omitted.

Transactional tests have some downsides, unfortunately. If your tests test transactional code, that code itself cannot create transactions; they have to use savepoints, which aren't quite the same. Transactional tests also don't work with testing anything concurrent, unless you share the session across threads/goroutines/whatever. Lastly, if a test fails you'd typically like to leave the data behind so that you can i…

Save points, with proper management of them, seem to match a conceptual nested transaction as far as I've seen. We've got a test bed connection manager that mocks savepoint creation, rollback and committal into transaction management functions so doing something like beginTransaction && beginTransaction works fine.

There may be some semantic nuances. Savepoints aren't real transactions, so they "inherit" the locks of the parent transaction, for example. But it might not matter in practice in the context of tests.

Re: PostgreSQL 11.3 and 10.8

#117

Post by munk-a »

paulddraper wrote: post #93
zkomp wrote: post #92

Earlier quoted context omitted.

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.

I have tried to express my joy at this news to my less SQL literate co-workers... that failed so I wanted to let it out here. This is the best news, I am overjoyed!

Re: PostgreSQL 11.3 and 10.8

#118

Post by evanelias »

sandstrom wrote: post #90
oarabbus_ wrote: post #88

Earlier quoted context omitted.

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.

Not the parent, but even as a MySQL expert I'd agree that Postgres is often a better choice for OLAP-heavy workloads (e.g. very complex reporting and analytical queries). MySQL's query planner historically hasn't been great for these, and ditto for its feature set overall, although it is improving.

Meanwhile, for high-volume OLTP workloads, MySQL (with either InnoDB or MyRocks on the storage engine side) has some compelling advantages... this is one reason why social networks lean towards MySQL for their OLTP product data, and/or have stayed on MySQL despite having the resources to switch.

As with all things in computer science, there are trade-offs and it all depends on your workload :)

Re: PostgreSQL 11.3 and 10.8

#119

Post by barrkel »

rtpg wrote: post #13

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.

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.

Re: PostgreSQL 11.3 and 10.8

#120

Post by astine »

mr_overalls wrote: post #57
ape4 wrote: post #40

Earlier quoted context omitted.

It kinda bugs me that people say "SQL Server" to mean "Microsoft SQL Server". I mean, there are other sql servers.

Same here. FWIW, I informally refer to it as "MS-SQL" to differentiate.

When I do that, the less technical people I report to get it confused with MySQL which is not what I want.
Post reply on HN