Live data from Hacker News

PostgreSQL Rising

wekeroad.com

191–200 of 204 posts

Re: PostgreSQL Rising

#191

Earlier quoted context omitted.

I wouldn't consider them shortcuts. I use table inheritance as an actual sort of inheritance, to enforce a consistent interface over a set of relations. Yes, that could be a shortcut but it also allows for foreign keys to be against different relations on each of the partitions. Yes, that could be done with partitioning in other ways, so maybe the short cut analogy works. But others? I don't know. The ability to writ…

table inheritance is cool, but it's also incomplete, docs are pretty clear on that

Yeah, I want inherited unique constraints for things which are guaranteed unique in an inheritance tree.

However, there are ways around this. You can, for example, use a composite key with the something related to the specific table in it.

For example our note table (an abstract class if you will) has a note_class field which is set to different default values and with different check constraints on the child tables.

Also foreign keys are a bit of an annoyance in an inherited table environment.

Re: PostgreSQL Rising

#192

Earlier quoted context omitted.

First of all, the success of postgresql and mysql are not mutually exclusive. Postgres operates in a lot of markets that MySQL doesn't (as far as I know) like telecom and finance. Postgres gets entirely new users with a variety of use cases, as well as people from Oracle, SQL Server, MySQL, and even some converts from people who tried NoSQL and found it lacking in some way. An undeniably, there is a shift happening,…

> Postgres operates in a lot of markets that MySQL doesn't [..] like telecom and finance MySQL cluster is highly popular in telecom. http://www.mysql.com/customers/industry/?id=78

I stand corrected.

Re: PostgreSQL Rising

#193
post #84

The primary target audience for hacker-to-hacker Postgres evangelism is MySQL users. Because let's face it, the choice for DBs like Oracle is usually made upstairs, and for very different reasons. So why do Postgres advocates insist on dissing MySQL with false and misleading arguments? The usual target is some default settings, when obviously there are three kinds of MySQL users: the ones that actually have a reason…

I'm not sure where in the article the author insults the _users_ of MySQL as stupid, but rather insults some decisions made by MySQL as ill-advised. Incidentally, I was not aware of issues around default settings with MySQL when I switched our infrastructure to Postgres - the primary reason we switched was transactional and much more efficient DDL changes. I was at a company previously that I felt was seriously hampe…

> If you (or anyone else) can point out why this benchmark is false or misleading, I'm all ears.

You might want to check out the "Online DDL for InnoDB Tables" section of the manual, it should help with understanding the results. http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.htm...

Re: PostgreSQL Rising

#194
post #15

Earlier quoted context omitted.

> Why else would they buy MySQL? And the licensing is not as strong as you may think. To compete with Microsoft SQL Server and to further solidify their customer base (most Oracle customers already use MySQL).

How is MySQL competing with MS-SQL? I'm quite sure they're in different league. Price point wise MS-SQL isn't that much cheaper than Oracle. Here's one word: upsell _HARD_ That was my experience trying to buy support/license for GlassFish. Some sales guy tries very hard to upsell us to use WebLogic (of course, otherwise he's not going to fill his quota or get a bonus).

> How is MySQL competing with MS-SQL?

http://www.pcworld.com/businesscenter/article/222168/oracle_...

Re: PostgreSQL Rising

#195
post #129

Earlier quoted context omitted.

> MySQL is the storage platform for people who do not know about databases. Exactly. I mean seriously what do Facebook, Twitter, Yelp, LinkedIn, Flickr etc know about storing lots of data.

If FB or twitter miss a status update, it's no big deal. If you bank misses your salary going in, it is a HUGE deal. Use the right tool for the job.

Google's ad business has been (is?) backed by MySQL.

http://zurlocker.typepad.com/theopenforce/2005/12/googles_us... http://research.google.com/pubs/pub38125.html

Re: PostgreSQL Rising

#196
post #148

Earlier quoted context omitted.

Amazon runs its real-money handling on MySQL? Link please.

Nice shifting of the goalpost there. Let's add some more companies: 37Signals, DHL, Dropbox, Evernote, UPS, Kayak, LastMinute, Orbitz, Continental, Mint, Quora, Tumblr, Techcrunch, Slashdot, NYT, NBC, Reuters, Wotif, Zappos, Wikipedia, Youtube. You still think any of these companies would tolerate loss of ANY data for ANY reason ?

PS the cost of not losing "ANY data for ANY reason" is effectively infinite. You make a call on what data loss would cost your business then you decide how likely each scenario is and that gives you your budget. Datacentre disruptions I have been personally involved with include workmen digging up fibre (several times actually), deliberate vandalism of cable (during May Day riots), fire in the generator room (a couple of times), large truck losing control and driving through the walls, flooding, and several more. No one of these scenarios was sufficient by itself to hurt us because we had mitigated them. But 2 happening together, then yeah, there is a significant possibility that some data loss would occur (e.g. replication to datacentre B is disrupted, then datacentre A is catastrophically lost). The cost of protecting against that however is prohibitive.

So before you get all starry-eyed about these amazing companies and their amazing MySQL installations, understand that you are starting from a false premise. Oracle - for a cost - provides some powerful capabilities (e.g. stretch-RAC[1]). Is it worth the money? Well, it comes back to what your data is worth. This incidentally is why some organizations cling to paper forms - it's cheaper to re-key the data than build out the infrastructure to do it all online...

[1] Tho' if you think you need this, what you probably really need is DB/2 and Sysplex

Re: PostgreSQL Rising

#197
post #183

Earlier quoted context omitted.

I actually have implemented a multi-tenant application based on Postgres 3 years ago and we have been using it in production ever since (for paying customers that is). As such I have some experience in this field and I'm actually in the process of moving (back) to a model where each customer has its own database (and application process). For context: I have a relatively low number of tenants (tens) which in turn eac…

During my research prior to starting development, I read two very interesting articles from the msdn library [1] [2]. My take from them was that there's a spectrum of possible architectures for multi-tenancy and within that spectrum, there isn't exactly any wrong or right decision. Quoting the second article, The distinction between shared data and isolated data isn't binary. Instead, it's more of a continuum, with m…

Indeed there is no right or wrong in thus case. Just different shades of grey. The scale you're aiming for is an important factor though (and I don't know what you're aiming for so I provided ours for context). And perhaps even more important is the ratio between tenants and actual users.

In all cases there is work to do of course - I've just found that the 'one db per tenant' model allowed us to re-use more exiting infrastructure than the other models. For us that's a win. As always the devil is in the details - some of which do not become clear until after you've run into the corresponding devil.

With respect to security/isolation consider the following.

Case A: one schema per tenant, one db user shared across all tenants. Anyone with the ability to manipulate going to the database (e.g. a hacker via a sql injection vulnerability) can access all schema's and all tenants data.

Case B: one db per tenant, one db user per tenant, one app process per tenant running under its own os user. In this case an attacker is far less likely to be able to access any data from tenants other than the one to whom the compromised process belongs to. Of course if the processes of other tenants are also aproachable by the attacker it is very much possible that the processes of other tenants will be compromised through the same vulnerability. However in our situation that is not the case (well, I should know better: it will be very difficult for the attacker) , hence we benefit from the extra isolation.

Your assumption that Postgresqls authentication or even OS level authentication provides extra isolation is wrong - unless you use one db user per tenant and set permissions carefully. But as stated before, in that case you better double check if you still benefit as much from the schema-based multi tenancy as you may think. And even then the level of separation is still lower.

What isolation level you need depends on your audience of course but from a security point of view I do not consider a schema based approach as much better than 'all tenants in the same table with a tenant_id field'.

Re: PostgreSQL Rising

#198

Earlier quoted context omitted.

1. Yes, applications can set sql_mode themselves at the session level. An admin cannot prevent this. http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

So, what good is setting a sensible default as a DBA if the dev guys can override it? I mean it's fine if you only allow one app to write to your db, but once you have more than one, the fact that the option to ignore invalid data even exists ought to make one very nervous if the data stored is worth anything.

A dev can only override it for their session, not at the global level.

Re: PostgreSQL Rising

#199

Earlier quoted context omitted.

"Often the case for using custom data types, for example, is quite weak..." Lots of people come to the postgres community because of PostGIS, which is (among other things) a custom type distributed separately from postgres. Using custom types is not bad, the mistake is thinking that making a new type is easy. For a non-trivial custom data type, you need to tie it into the indexing system (GiST+KNN, GIN, SP-GiST, BTre…

The reason to use a phone number type if you want to use one is that you can, in theory, create functions against it. A good example of what might be possible with such a type can be gained by looking at the standard networking type. Now, it's rare that you are likely to get that into phone numbers, but there might be cases where you could decompose the data and do relevant searches on components might come in really…

You don't need to create a custom type to return tuples anymore. (that kind of went out with 8.1). You can use OUT parameters or RETURNS TABLE syntax. RETURNS TABLE is ANSI compliant and supported since 8.3. SQL Server supports RETURNS TABLE as well.

Re: PostgreSQL Rising

#200
post #173

The recent release of PostGIS 2, the spatial extension of Postgres, has been a huge benefit to geo software. It has increased the ease and speed at which we can now work with large data sets and do spatial computation on them. My favorite part of PostGIS 2, besides much of the cleaned up syntax, is the new Geography datatype. It has the spatial projection built in to it, so it automatically does great arc computation…

Geography data type came out in 1.5 NOT 2.0. Raster and better 3D support were the big story in 2.0 But stay tuned for 2.1 --> Geography will become MUCH faster: http://blog.opengeo.org/2012/07/12/making-geography-faster/
Post reply on HN