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

51–60 of 162 posts

Re: PostgreSQL 11.3 and 10.8

#51

Post by kangoo1707 »

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

Re: PostgreSQL 11.3 and 10.8

#52

Post by combatentropy »

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.

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.

Re: PostgreSQL 11.3 and 10.8

#53

Post by mixmastamyk »

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.

Re: PostgreSQL 11.3 and 10.8

#54

Post by BubRoss »

giovannibonetti wrote: post #46
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.

Good to know! Can you give an example? When I google "SQL Server" it seems like most results are related to MS SQL Server

An example of other SQL servers? PostgreSQL would be one, which is what this thread is about.

Re: PostgreSQL 11.3 and 10.8

#55

Post by mixmastamyk »

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…

> likely to change

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

Re: PostgreSQL 11.3 and 10.8

#56

Post by scardine »

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.

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.

Re: PostgreSQL 11.3 and 10.8

#57

Post by mr_overalls »

ape4 wrote: post #40
dspillett wrote: post #17

Earlier quoted context omitted.

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?

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.

Re: PostgreSQL 11.3 and 10.8

#58

Post by kangoo1707 »

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…

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.

Re: PostgreSQL 11.3 and 10.8

#59

Post by buster »

dragonwriter wrote: post #49
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.

> It kinda bugs me that people say "SQL Server" to mean "Microsoft SQL Server". I mean there are other sql servers. I don't mind that; sure, there are other RDBMS servers that support SQL as their main or exclusive language, but unless you are talking about ancient Sybase products (for which there is a very good reason for the shared branding) “SQL Server” is a clear, exclusive Microsoft product identity and not any…

https://en.wikipedia.org/wiki/SQL_Server

Same as "FTP Server" doesn't mean proftpd, "HTTP Server" doesn't mean apache and "C compiler" doesn't mean gcc. SQL is a language. Server is a generic term.

Another example: I may ask my colleague the following: "Which SQL server should we use in our new project?". Does this mean, i would like to know the edition and version of the MS SQL Server or maybe (and from my point of view more likely) i am looking for PostgreSQL, MySQL or MSSQL. If i'd like to know which edition and version i should use, i'd ask "Which MS SQL Server should i use?". That's a difference.

Might be that our views differ but i can understand the parents points.

Re: PostgreSQL 11.3 and 10.8

#60

Post by SnowingXIV »

skymt wrote: post #36
SnowingXIV wrote: post #14

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

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

Thanks, yeah I just did some testing locally and made the upgrade on Heroku (the documentation was rock solid).
Post reply on HN