Slashdot Mirror
PostgreSQL 9.2 Out with Greatly Improved Scalability
The PostgreSQL project announced the release of PostgreSQL 9.2 today. The headliner: "With the addition of linear scalability to 64 cores, index-only scans and reductions in CPU power consumption, PostgreSQL 9.2 has significantly improved scalability and developer flexibility for the most demanding workloads. ... Up to 350,000 read queries per second (more than 4X faster) ... Index-only scans for data warehousing queries (2–20X faster) ... Up to 14,000 data writes per second (5X faster)" Additionally, there's now a JSON type (including the ability to retrieve row results in JSON directly from the database) ala the XML type (although lacking a broad set of utility functions). Minor, but probably a welcome relief to those who need them, 9.2 adds range restricted types. For the gory details, see the what's new page, or the full release notes.
If you're sitting alone in your cube, and you suddenly smell a horrible fecal smell, you should:
A) Loudly blame the lady in the next cube over to divert attention from yourself
B) Go to the bathroom to check your underwear for unexpected deposits
C) Just get up and leave for 5 minutes to let the dissipate
D) Spray some air freshener around and keep coding
LOL just use SQLServer you nubs.
So, before is was that bad, huh ?? And next time ?? Yeah, yeah, you gotta let your hair grow !!
From the summary:
"9.1 adds range restricted types"
nice proof reading...
When are they going to come out with the feature where it installs on OS X without requiring a human sacrifice? :P
I read TFA and all I got was this lousy cookie
I think everyone has glossed over the single most important feature in the Postgre SQL that they have refined in this release, IMHO. Ranged data types. Let's say you have a meeting schedule DB application. Well currently if you want to restrict a room between two times (start and stop) so that no one else can have the room during that time, you are going to have to write that logic in your application.
Postgre's range data type allows you to create unique checks on ranges of time. This can in two lines of code, do every single logic check that is needed to ensure no two people schedule the same room at the same time.
How this is not showing up on anyone's radar is beyond me, or maybe we all just use Outlook or Google Calendar now. However, the range types are not just limited to the application of time, but of anything that requires uniqueness along a linear fashion, as opposed to just checking to see if any other record matches the one that you are trying to insert.
TL;DR: Is there an advanced PostgreSQL for MySQL Users guide out there somewhere? Something more than basic command-line equivalents? And preferably from the last two major releases of the software?
Long version
I've been using MySQL personally and professionally for a number of years now. I have setup read-only slaves, reporting servers, multi-master replication, converted between database types, setup hot backups (Regardless of database engine), recovered crashed databases, and I generally know most of the tricks. However I'm not happy with the rumors I'm hearing about Oracle's handling of the software since their acquisition of MySQL's grandparent company, and I'm open to something else if it's more flexible, powerful, and/or efficient.
I've always heard glowing, wonderful things online about PostgreSQL, but I know no one who knows anything about it, let alone advanced tricks like replication, performance tuning, or showing all the live database connections and operations at the current time. So for any Postgres fans on Slashdot, is there such a thing as a guide to PostgreSQL for MySQL admins, especially with advanced topics like replication, tuning, monitoring, and profiling?
... And so it comes to this.
Wake me when it catches up with MemSQL.
Oracle is not that big a of concern.
There is MariaDB which is data-compatible with MySQL, and has some nice additions (like microsecond performance data), and there is also Percona Server.
If Oracle messes up, like they did with OpenOffice, there will be another version that they cannot touch, like LibreOffice.
2bits.com, Inc: Drupal, WordPress, and LAMP performance tuning.
I've been searching for a comparison chart of various SQLs but all I can find are very very old articles
There's a database project that I'm working on and I'm choosing which SQL to be employed
MySQL is obviously not up to par
I don't know how good PostgreSQL is - so, is there a comparison chart or something that can facilitate us, the one who are going to make purchasing decision, to make one choice over the other?
Thank you !
Muchas Gracias, Señor Edward Snowden !
Was the scalability that bad to begin with ?
You might want to just read the Postgres documentation. It's very well written.
To me, JSON very interesting. I don't know how exactly I'll use it, but it combines all that's great about PostgreSQL with some of what was interesting about CouchDB and other projects like it.
Mainly, one-to-many relationships may be easier. Usually, they are two separate select statements. For example, one to get the article, another to get the comments. Then you patch it all together in PHP, or whatever middle language you're using. With JSON support, that could be a single SELECT, crammed up in JSON, which you then uncram with a single json_decode function call in PHP, which would yield nice nested arrays.
Until the fix the TX number issue ( the infamous rollover ) then they are pretty much out of the running in DB's that have VERY high insert levels since the vacuum process cannot hope to keep up with tables that have 100's of millions of rows.
I am an Oracle professional but I do keep track of Postgres and like it, but the 32 bit TX t is a bit of an Achilles heel.
Hey KID! Yeah you, get the fuck off my lawn!
Wasn't support for checksums meant to _finally_ get into 9.2? Postponed again?
I'm sure most of this applies to MySQL these days but historically it didn't and I never saw the attraction of a DB which went through a succession of backends in order to obtain the behaviour PostgreSQL always supplied. It doesn't help that MySQL is Oracle owned and all the issues with licencing and forking which have arisen out of that.
First, its 9.2, not 9.1.
Second, (as shown in the link) these are range types, not range-restricted types. Range-restricted types (as known from, e.g., Ada) are something that (via domains with check constraints) PostgreSQL has supported for a very long time.
Range types, combined with 9.2s support for exclusion constraints, are a pretty major new feature that give 9.2 a great facility in dealing with (among other things) temporal data and enforcing common logical constraints on such data in the database as simple-to-express constraints rather than through triggers.
I think it is a good decision in that it provides a syntactic construct for ranges that are unbounded on either end -- so it applies well to all types -- and works correctly with types that have an infinity value if that is used. You probably don't want the syntactic construct to have to be specially-aware of all the special values within types (because that increases the cost of expanding the type system), and the decision that syntactically-unbounded is "outside" (on either end) of any value in the domain (including, where it exists, an "infinite" value) is the decision that allows the range syntax to not be aware of special type values.
OTOH, code that deals with ranges ought to be type-aware, and as a general rule should use ranges bounded by the types infinity rather than unbounded ranges where the type has an infinity (and, for range values stored in tables, this should probably be enforced by an appropriate constraint which is quite straightforward with the exclusion constraint support implemented alongside range types in 9.2.)
Still, yeah, I can see the argument that more type-aware ranges that treated [,y] as equivalent to [-infinity,y] for types with a meaningful -infinity (and the equivalent for +infinity as the upper bound) would be slightly more convenient.