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

PostgreSQL is the worlds’ best database

2ndquadrant.com

Post reply on HN
Print view View original

111–120 of 365 posts

Re: PostgreSQL is the worlds’ best database

#111

Post by fullstop »

mulmen wrote: post #41
forinti wrote: post #33

I manage Oracle and Postgresql instances and I agree with this, except for one small thing: Postgresql doesn't have packages. Otherwise, it is much simpler to manage than Oracle (which really is a system turned inside out). The documentation is really good and straight to the point (Oracle can't help but turn everything into an ad - nvl uses the best null handling technology on the market and such).

I spent many years on Oracle. I do not miss smug Tom Kyte blog posts about the pointlessness of BOOLEANs. Nor do I miss endless hours Googling around Burleson Consulting pages because we couldn’t afford support. I do kinda miss how easy it was to do partitioning but I’m on Redshift now so it doesn’t matter.

Oh, man, that shirtless guy. Thanks for putting that back into my mind!

Re: PostgreSQL is the worlds’ best database

#113

Post by remus »

BiteCode_dev wrote: post #62
onion2k wrote: post #7

Earlier quoted context omitted.

One of Postgres's biggest pros (over other commonly used dbs in web dev) is Row Level Security. Controlling what data a user can access at the database rather than the application or as part of a query makes the application logic a lot simpler. That's a big win.

But that also means that: - the code is not reusable outside of a database setting. So not cacheable. - the code is not reusable accross different storage layers. So not portable. - the code may needs updating if the schema change, you can't abstract that - changing the logic means a db migration - testing the code requires a DB - tooling support to check that code si limited to SQL tooling, which is very weak, espec…

It also means you need a per-user database connection which isn't feasible in many applications as the recommended number of concurrent connections is typically quite low.

Re: PostgreSQL is the worlds’ best database

#114

Post by sterwill »

redis_mlc wrote: post #36

Ironically, the blog post shoots itself in the foot by starting with Postgres security. Postgres has very poor security compared to MySQL, and in fact, I tell companies implementing compliance policies to shift to MySQL. https://www.cvedetails.com/metasploit-modules/vendor-336/Pos... The reasons are: - Postgres' grant model is overly complex. I haven't seen anybody maintain the grants correctly in production for non-…

I've deployed, administered, and developed with PostgreSQL in a regulated, audited environment with external and internal compliance requirements (US healthcare; database contains protected health information). None of the security model issues you mention is anything like a realistic concern in that environment.

And I'd love to see your sources for all your "exploits".

Re: PostgreSQL is the worlds’ best database

#115

Post by willvarfar »

wlll wrote: post #57
willvarfar wrote: post #18

Earlier quoted context omitted.

The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnitude more data than using Postgres. These days its MyRocks, in the past it was TokuDB. Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it. TimescaleDB is an interesting new…

Do these satisfy the compression requirement? https://stackoverflow.com/questions/1369864/does-postgresql-...

No, that's a compressed row format, not a compressed block format.

MySQL's innodb, for example, supports per-row-compression. Its completely ineffective. Block compression in tokdub and myrocks etc is a completely different class.

MySQL's innodb also supporta kind of 'page compression' using the file-system's sparse pages. Its also naff.

The closest postgres can get is using zfs with compression. Its a lot better than nothing.

Re: PostgreSQL is the worlds’ best database

#116

Post by blackflame7000 »

zaarn wrote: post #91
Joeri wrote: post #77

Earlier quoted context omitted.

It depends a lot on the implementation strategy. Some databases have GIS features, allowing you to query all points within a radius directly. I know oracle can do this, but it’s a paid addon (spatial and graph) and I don’t know how well it scales. On databases that have good scanning behavior you can use a geohash approach instead. Insert all points with a geohash as key, determine a geohash bounding box that roughly…

Postgres can be combined with the PostGIS addon (which is free) to get a reasonable feature set on top of PG with GIS features.

Yea PostGIS is a must if you plan on doing any sort of geospatial work with Postgres. PostGIS is way ahead of most other databases when it comes to processing geographic (3D) types. For example calculating a 100km buffer zone for 2D geometry types is easy, but only PostGIS can support buffer zone calculations on 3D geographic types. Because of the curvatures of the earth, the math is quite involved but it can make a big difference in accuracy

Re: PostgreSQL is the worlds’ best database

#117

Post by xiljin »

INTPenis wrote: post #29

My first job in IT, I was 18, it was the early 2000s. I was a nerd but in professional IT I was essentially a blank slate. I lucked up and ended up with a real hacker for a boss. As early as 2001-2002 he had saved entire businesses by migrating them from mysql to postgres. He was a BSD guy and a postgres guy. He made me into a fanboy of both those technologies. So out of sheer luck I've preferred Postgres for over 15…

I discovered PostgreSQL in a similar happenstance sort of way - working at a large telecom directly out of college in the late 90s, tasked with setting up a MySQL backed webapp to manage some internal processes. Fortunately, I was unable to compile MySQL on our HPUX workstations (issue with threads IIRC), PostgreSQL to the rescue. Been using it ever since.

Re: PostgreSQL is the worlds’ best database

#118

Post by johannes1234321 »

Joeri wrote: post #77
unnouinceput wrote: post #72

For most of the projects where the DB really mattered, throughout my 10+ freelancer carrier, it came down to one thing that client really cared about. Performance. Nothing else mattered, not license price, not whistles and bells, not hype. My clients wanted to have data in front of their eyes the same second when they clicked the button. And when you have a table with 100 million rows in it, and an application is not…

It depends a lot on the implementation strategy. Some databases have GIS features, allowing you to query all points within a radius directly. I know oracle can do this, but it’s a paid addon (spatial and graph) and I don’t know how well it scales. On databases that have good scanning behavior you can use a geohash approach instead. Insert all points with a geohash as key, determine a geohash bounding box that roughly…

MySQL also hast lots of GIS features, including r-tree spatial indexes.

Re: PostgreSQL is the worlds’ best database

#119

Post by LunaSea »

endymi0n wrote: post #5
Jonnax wrote: post #2

This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences. Anyone have some pros and cons of others? Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres? Apart from support that you gotta pay for.

Having worked with dozens of different databases over lots of years, I can certainly say that Postgres is my go-to _default_ database over all the other relational ones. In the same way that Golang claims it's the 90% language ( https://talks.golang.org/2014/gocon-tokyo.slide#1 ), I'd say Postgres is the perfect 90% database. However, we're still using at least four other databases in production, and the reason why i…

Could you expand on the issues with PostgreSQL full text search for Korean?

I'b be interested to know about the limits of the full text feature.

Re: PostgreSQL is the worlds’ best database

#120

Post by chatmasta »

remus wrote: post #113
BiteCode_dev wrote: post #62

Earlier quoted context omitted.

But that also means that: - the code is not reusable outside of a database setting. So not cacheable. - the code is not reusable accross different storage layers. So not portable. - the code may needs updating if the schema change, you can't abstract that - changing the logic means a db migration - testing the code requires a DB - tooling support to check that code si limited to SQL tooling, which is very weak, espec…

It also means you need a per-user database connection which isn't feasible in many applications as the recommended number of concurrent connections is typically quite low.

Not necessarily. You can SET ROLE at beginning of transaction and DISCARD afterward. This is what postgraphile and postgrest do, for example.
Post reply on HN