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

61–70 of 162 posts

Re: PostgreSQL 11.3 and 10.8

#61

Post by scardine »

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…

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

Re: PostgreSQL 11.3 and 10.8

#62

Post by dbrgn »

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'm pretty sure you're frequently using LIMIT, which (TIL) is non-standard SQL. (PostgreSQL shares that syntax though.)

Re: PostgreSQL 11.3 and 10.8

#63

Post by SEJeff »

dspillett wrote: post #17
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.

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?

One of the huge ones was the ability to use > 1 cpu core for big aggregations or huge select queries. That and a massively better query planner.

Re: PostgreSQL 11.3 and 10.8

#64

Post by mr_overalls »

Scarbutt wrote: post #29
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?

Out of interest ;) SQL Server is such an expensive beast, ~$15K per core, what are your reasons for prefering it over PG?

I use MS-SQL for my day job, and Postgres for personal projects. Pros of MS-SQL are mainly 1) easy integration with the universe of Microsoft dev tools, frameworks, and infrastructure, and 2) great database tooling in itself:

* SSMS - SQL Server Management Studio

* The MS BI stack: SSRS (reporting), SSAS (analysis), SSIS (integration)

Re: PostgreSQL 11.3 and 10.8

#65

Post by throw0101a »

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.

Re: PostgreSQL 11.3 and 10.8

#66

Post by FraaJad »

micmil wrote: post #4

I'm not a database guy so have no clue, but why are there so many versions receiving support? Is there just that much legacy crap they can't get away from, like Python?

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

Re: PostgreSQL 11.3 and 10.8

#67

Post by stilldavid »

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.

We just migrated a medium size project using pgloader with great success and minimal headaches, which seems like a big step up from the last time I had to migrate a project. Highly recommended, and it might be easier than you think!

Re: PostgreSQL 11.3 and 10.8

#68

Post by grouseway »

Keyframe wrote: post #41
eropple wrote: post #33

Earlier quoted context omitted.

I quite like Postgres and use it preferentially, but your numbers are off. SQL Server Enterprise costs more like $7500/core; that $15K pack, as far as I am aware, comes with two core licenses. SQL Server Standard 2016 costs $931 for a license if you use CALs ($209 a pop), or $3700/core. Also bear in mind that almost nobody pays list price for any of this.

$7500 per core, but only $15,000 for two cores sounds like a sweet deal though.

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.

Re: PostgreSQL 11.3 and 10.8

#69

Post by throw0101a »

combatentropy wrote: post #52
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.

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.

Re: PostgreSQL 11.3 and 10.8

#70

Post by mitch3x3 »

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…

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

Post reply on HN