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

281–290 of 365 posts

Re: PostgreSQL is the worlds’ best database

#281

Post by ksec »

yxhuvud wrote: post #122
ksec wrote: post #24

Earlier quoted context omitted.

>Replication, failover and high availability are complicated matters. I wonder why Postgre hasn't improved in these areas and instead leaving it to third party solution. I mean every year these few points are still listed as something that flavours MySQL.

Why are you saying they have not improved? That is flat out false - postgres provides a lot better tools for this now than 10 years ago. They still leave a lot of the plumbing to third parties, but they are now at least giving those third parties a lot better tools than they used to.

I think a more accurate way to put it, why isn't there any default solution? But I guess the Open Core model another comment pointed out makes sense.

Re: PostgreSQL is the worlds’ best database

#282

Post by einpoklum »

PostgreSQL is a transaction-oriented row-store. It is slow for analytics, and I mean by something like two orders of magnitude, if not more, relative to state-of-the-art systems like HyperDB or Actian Vector, on typical analytic workloads. You can extend it any which way you like, that won't change. (Ok, if you can extend it with essentially a different DBMS then that could be fast.)

Security, concurrency control - those are important for transactional work facing numerous users. Not for analytic processing speed.

Re: PostgreSQL is the worlds’ best database

#283

Post by mulmen »

jacques_chester wrote: post #234
mulmen wrote: post #69

Earlier quoted context omitted.

You just said you never saw Postgres at Yahoo. Yet Yahoo had possibly the world’s largest Postgres installation. I consider that legendary. I’ve certainly been aware of it for over a decade. It was also an early (?) columnar store which is now commonplace among OLAP databases. Seems like if it didn’t start a trend it was at least an early adopter. I’d say that’s legendary too. Even today the scale is impressive. Now…

I believe the database described is Greenplum[0], which was a fork of PostgreSQL at 8.3, I think. It handles truly enormous datasets. There's been an ongoing multi-year project to merge Greenplum up to the mainline so that it's no longer a hard fork. Disclosure: I work for VMware, which sponsors development and sells commercial offerings of Greenplum. [0] https://greenplum.org/

The article says Yahoo bought Mahat Technologies for their columnar version of Postgres. That sounds similar to Redshift or Greenplum but I think it is different. I can’t find a clear history of Greenplum’s origins or what happened to Mahat. Looks like Redshift came from ParAccel which was a separate project. From what I can find there were a lot of similar projects at the time.

Re: PostgreSQL is the worlds’ best database

#285

Post by lazyant »

PostreSQL is absolutely fantastic and for most projects, unless there's an existing legacy database or a purely Microsoft environment should be the default RDMS.

My only complaints are:

1) No built-in or easy failover mechanism (and will never have one because of their estated philosophy). I'll settle for a standard one and yes I know there are different requirements and things to optimize for and there are 3rd party solutions (just not one to integrate easily).

2) Horizontal Scalability. And yes I know of some solutions and other dbs being more apt for this and asking the wrong thing.

Re: PostgreSQL is the worlds’ best database

#286

Post by jeffdavis »

anarazel wrote: post #269
jeffdavis wrote: post #264

Earlier quoted context omitted.

"better document binary and add an intent to remove text data types" For all-binary, the client would have to know about all data types and how to represent them in the host language. But that seems clunky. Consider NUMERIC vs. float vs. int4 vs int8: should the client really know how to parse all of those from binary? It makes more sense to optimize a few common data types to be transferred as binary, and the rest w…

> The driver knows which types it can parse, but it doesn't know what types a given query will return. [...] Postgres could know the data types, but that means that the driver would need to first prepare the query (which is sometimes a good idea anyway, but other times the round trip isn't worth it). We return the types of the result set separately even when not preparing. The harder part is doing it without adding r…

"I've argued before that we should allow the client to specify which types it wants as binary, unless explicitly specified. IMO that's the only way to solve this incrementally from where we currently are."

I don't understand the idea, can you clarify?

Re: PostgreSQL is the worlds’ best database

#287

Post by smilliken »

ggm wrote: post #51

PostgreSQL and ZFS is a marriage made in heaven. Block aligns to disk behaviour, snapshots make great low latency db dumps trivial. Never regretted making the combo

FWIW, I've often called out my decision to use PostgreSQL and ZFS (on Linux) as my worst.

Memory usage was extremely inefficient. ARC had to be set to half of what it should've been because it varied so dramatically, so half the system memory was wasted. ZFS would occasionally exhaust system memory causing a block on allocations for 10+ minutes almost daily, no ssh or postgresql connections could be opened in the meantime. Sometimes a zfs kernel process would get stuck in a bad state and require restarting the server. Many days were wasted testing different raid schemes to work around dramatic space inefficiencies (2-8x IIRC) with the wrong combinations of using disk block sizes, zfs record sizes, postgresql block sizes, and zfs record compression. Because zfs records are atomic and larger than disk pages, writes have to be prefaced with reads for the other disk pages, adding lots of latency and random IOPS load. Bunch of other issues, I could go on.

I've since switched back to ext4 and hardware RAID. Median and average latency dropped an order of magnitude. 99th percentile latency dropped 2 orders of magnitude.

These databases are at high load. If they had low load, and I wasn't expecting to grow into high load, I'd consider ZFS since it does have a bunch of nice features.

Re: PostgreSQL is the worlds’ best database

#288

Post by AnonymousPlanet »

zaat wrote: post #275
AnonymousPlanet wrote: post #273

Earlier quoted context omitted.

> Because certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing. In addition this ties an identity across system and facilitates SSO on the one hand and auditing on the other. There's more to it but these are the most obvious. That is precisely what I meant with messed up requirements . Why would any of your SSO be relev…

There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups. I have done my fair share of integration with AD, including in some of the largest enterprises existing on this planet, where you have hundreds of groups of developers and admins. I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on thei…

> There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups.

Ah, so this is a special case with a shop that has developers and DBAs in their user base. Now that I can understand.

> I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on their Kerberos implementation details.

Then you know what a giant mess all of it is. I have done my share of LDAP/AD integration as well and found that LDAP is a very malleable tool that has been bent in many ways. There are many details to get right. MS use a certain subset of schemata and have added their special flavoured behaviour in the background. That means, what the AD admin sees is not exactly what an LDAP query might return. Similarly Kerberos or any other authentication scheme like e.g. SAML. Getting all of this right from the view of a third party application is not simple. That's why I'm never surprised why pretty much all of them are incomplete or incorrect in this manner.

Oracle offers a similar integration for AD. Do you happen to have any experience with it and if so, how does it compare to MS SQL?

Re: PostgreSQL is the worlds’ best database

#289

Post by manigandham »

anarazel wrote: post #253
manigandham wrote: post #190

Earlier quoted context omitted.

All of these databases have geospatial data types for efficient querying and indexing. It's been a standard feature for years. Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes.

> Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. Not that trivial if you're not just dealing with points.

So you have more complicated SQL then, but it doesn't make a difference between DBs selecting from a 100M table with indexes.

If it ends up being a full table scan then PG only recently got query compilation and parallel execution so at best it would be even with, but not better than, MSSQL/Oracle that can do vectorized executions on columnstores.

Re: PostgreSQL is the worlds’ best database

#290

Post by ggm »

smilliken wrote: post #287
ggm wrote: post #51

PostgreSQL and ZFS is a marriage made in heaven. Block aligns to disk behaviour, snapshots make great low latency db dumps trivial. Never regretted making the combo

FWIW, I've often called out my decision to use PostgreSQL and ZFS (on Linux) as my worst. Memory usage was extremely inefficient. ARC had to be set to half of what it should've been because it varied so dramatically, so half the system memory was wasted. ZFS would occasionally exhaust system memory causing a block on allocations for 10+ minutes almost daily, no ssh or postgresql connections could be opened in the mea…

I'm on a Read-mostly DB. Also, I have SSD backed ARC and its recent ZFS with a lot of memory (512+GB IIRC)
Post reply on HN