51–60 of 204 posts
What are the scaling differences between MySQL and PostgreSQL? That's the main reason we haven't shifted and we have a new project coming up that I've been interested to use PostgreSQL with as one our developers prefers it, but are we opening a whole new can of worms on that front?
PostgreSQL is massively more scalable than MySQL.
On my way to build a multi-tenant application I went through a great deal of articles recommending various architecture strategies. I was looking for an approach to organize the data for the app's various customers (multi-tenant). Most recommendations revolved around 2 solutions: 1 db per tenant, or 1 db for all tenants with a tenant_id in each table. Lucky me , I eventually stumbled upon a thread where someone menti…
Wow, just this week I started a project that will be my first multitenant website, using PostgreSQL no less, and have been wondering how to handle that in the db. I take a break, pop onto HN, and the top comment of the top story explains how to do exactly that. Thanks! You wouldn't happen to have come across any good tutorials on using PG schemas for this purpose have you? Also, do schemas provide enough separation o…
http://blog.jerodsanto.net/2011/07/building-multi-tenant-rai...
Earlier quoted context omitted.
PostgreSQL is massively more scalable than MySQL.
Definitely. When your application needs hundreds of read slaves in order to scale the load, PostgreSQL has always been everyone's first choice due to it's mature and historically awesome replication system. That's why companies who need to scale big (YouTube, Facebook, Yahoo, LinkedIn, Wikipedia, Twitter etc) all have hundreds (if not thousands) of PostgreSQL machines in their infrastructure.
Earlier quoted context omitted.
I've been the victim of that flaw - I biffed a Rails migration and sent (what should have been) $200 transactions into the DB as $99. My local tests used SQLite, so I didn't catch it until we went live - true deal there. Very dumb on my part. At the same time - changing data values not stupid? We may differ on this point but I believe it's insanity. I'd love to see NULLs honored as well as constraints. That said - 1/…
> 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).
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).
I'm a huge PostgreSQL fanboy but I think it's worth mentioning that it's usually not a good idea to use too many esoteric database features when building an app, since it couples your system with a particular database. That said, even if you don't use PostgreSQL's whiz bang features, its stability, performance, and outright sanity with regards to handling data make it the right database to reach for in many cases. An…
With LedgerSMB we went with a tight-coupling approach. This gives us the power of the esoteric features and the ability to to write stored procedures in whatever languages we want later. HStore and Javascript, or JSON and Javascript might be used in the future for some things if we want to.
There are many applications which may not need this. In those cases, you are better off focusing on portability (only ANSI SQL features).
What tool is he using to do the postgres part of that video? I've been looking for a decent postgres gui and can't find one. EDIT: Navicat it looks like. One comment, way back int he mists of times (the 90's) MySQL was known as the database that would eat your data. It had a number of data gobbling bugs, as well as no transactions. I'm genuinely surprised how far good marketing and ease of use will go to make a produ…
Unrelated, but hopefully a good place to stick this: What was that Mac 10.7-only, command-line but standalone database tool, designed for high-level analysis of data via queries?
I'm a huge PostgreSQL fanboy but I think it's worth mentioning that it's usually not a good idea to use too many esoteric database features when building an app, since it couples your system with a particular database. That said, even if you don't use PostgreSQL's whiz bang features, its stability, performance, and outright sanity with regards to handling data make it the right database to reach for in many cases. An…
At least with PostgreSQL, most of the features are really shortcuts, rather than kludges masquerading as features. E.g. - extending a table as if it were a superclass, rather than creating a supplemental "joiner" table and creating a writeable view on top of the "super" table + the "sub" table.
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 write a stored procedure as a quick Perl regular expression and then call it from a SQL query doesn't strike me as a short-cut. Nor does the extensible type system, or the ability to caste complex types as simple types with defined functions.
A lot of these are features which make PostgreSQL a development platform in a box, rather than a data store.
On my way to build a multi-tenant application I went through a great deal of articles recommending various architecture strategies. I was looking for an approach to organize the data for the app's various customers (multi-tenant). Most recommendations revolved around 2 solutions: 1 db per tenant, or 1 db for all tenants with a tenant_id in each table. Lucky me , I eventually stumbled upon a thread where someone menti…
Earlier quoted context omitted.
Yeah, there's a lot of other things we use it for. We have a lot of hierarchical data that uses the CONNECT BY statement, and I hate it because it has issues with scaling and bad execution plans, so I'm working on migrating all of that to a closure table instead.
Does Postgres have an equivalent Connect By feature? Last I checked it didn't. If you don't need to scale your hierarchical data much, its pretty handy. I'd prefer to go to Postgres too.
The better way is to use WITH RECURSIVE CTE's. See http://ledgersmbdev.blogspot.com/2012/07/ctes-and-ledgersmb.... for how we use them in LedgerSMB for hierarchical data.