Forgot your password?
typodupeerror
Data Storage Programming

New PostgreSQL Guns For NoSQL Market 162

Posted by samzenpus
from the coming-for-you dept.
angry tapir (1463043) writes "Embracing the widely used JSON data-exchange format, the new version of the PostgreSQL open-source database takes aim at the growing NoSQL market of nonrelational data stores, notably the popular MongoDB. The first beta version of PostgreSQL 9.4, released Thursday, includes a number of new features that address the rapidly growing market for Web applications, many of which require fast storage and retrieval of large amounts of user data."
This discussion has been archived. No new comments can be posted.

New PostgreSQL Guns For NoSQL Market

Comments Filter:
  • next for NoSQL (Score:5, Insightful)

    by SchroedingersCat (583063) on Friday May 16, 2014 @12:45AM (#47015177)
    Next, NoSQL databases will add schema and ACID support and the circle will be complete.
    • Impossible.

      The entire premise behind NoSQL is trading consistency for availability (which actually means "latency" since everything is eventually available). You will never ever get ACID from NoSQL databases.

      • Re:next for NoSQL (Score:4, Informative)

        by bdares (1042128) on Friday May 16, 2014 @02:31AM (#47015471)
        "NoSQL" doesn't mean "No SQL". At least, not all the time. I've heard it pronounced "Not Only SQL" more than once. RDF triple stores can also be considered NoSQL databases, and they can provide ACID. (They use SPARQL instead of SQL as a query language - hence being something other than a SQL DB.)
      • Re:next for NoSQL (Score:5, Interesting)

        by greg1104 (461138) <gsmith@gregsmith.com> on Friday May 16, 2014 @04:11AM (#47015667) Homepage

        All "NoSQL" means is that the database doesn't use SQL as its interface, nor the massive infrastructure needed to implement the SQL standard. This lets you build some things that lighter than SQL-based things, like schemaless data stores. There several consistency models that let you have a fair comparison. It's not the case that NoSQL must trade consistency for availability in a way that makes it impossible to move toward SQL spec behavior.

        Differences include:

        • Less durability for writes. Originally PostgreSQL only had high durability, NoSQL low. Now both have many options going from commit to memory being good enough to move on, up to requiring multiple nodes get the data first.
        • No heavy b-tree indexes on the data.
          Key-value indexes are small and efficient to navigate,
        • No complicated MVCC model for complicated read/write mixes.

          Today NoSQL solutions like MongoDB still have a better story for sharding data across multiple servers. NoSQL also give you Flexible schemaless design, scaling by adding nodes, and simpler/lighter query and indexes.

          PostgreSQL is still working on a built-in answer for multi-node sharding. A lot of the small NoSQL features have been incorporated, with JSON and the hstore key-value index being how Postgres does that part. Both system have converged so much, either one is good enough for many different types of applications.

        • by cjc25 (1961486)

          the SQL standard.

          That's cute

        • Re: (Score:2, Interesting)

          by Anonymous Coward

          "Schemaless design" always just sounds like a whitewashed buzzword for "Excel spreadsheet" to me.

          There's a very simple way to make a "schemaless design" within a relational database, and it's generally regarded as Not Best Practice (tm). You need a table with a unique PK (any old GUID or autoincrementing integer will do just fine), a FK to whatever bit of "real indexing" you need (user id or whatever), and two string fields (varchar, nvarchar, character varying, whatever your RDBMS likes to call them). One

          • by drinkypoo (153816)

            Nobody that feels a need to use NoSQL is going to consider using PostgreSQL for that task

            Unless they're already using Postgres for something else, and the server is already running, and they'd like to be able to use all the same tools and scripts to manage their NoSQL databases as their PostgreSQL databases. In which case, this could be a big help.

            Why so negative? Just a hobby?

            • While your parent *was* a bit snarky in his reply, I can see only two reasons why you would try to finagle your NoSQL needs into a PostgreSQL server: you don't understand how to use a traditional RDBMS but would still like to advertise that you're using PostgreSQL instead of MongoDB (not likely for most devs), or the decision is made for you by management for the reasons you mention. If you need some NoSQL solution in a new project it's not very difficult to create an instance, and the infrastructure for th

          • by umdesch4 (3036737)
            Dammit, I wish I had mod points for this. It restored my faith in humanity a little bit...so thanks for that.
        • "NoSQL" is a pretty bad name actually. They should be called non-relational databases. In many cases you can use SQL or something like SQL on them.

          People never use NoSQL to get away from the SQL language (although I don't like SQL at all). They use it to change the trade-offs in ACID complacence and to not have to keep their data completely relational.

          • by ultranova (717540)

            "NoSQL" is a pretty bad name actually. They should be called non-relational databases.

            A typical file system is a non-relational database. Does that make it NoSQL?

          • by acscott (1885598)

            Martin Fowler discusses the NoSQL moniker and seems to agree with you: https://www.youtube.com/watch?... [youtube.com] It' NoSQL Distilled to an hour by Martin Fowler from NoSQL Matters Conference

      • by Slackus (598508)

        Impossible.

        The entire premise behind NoSQL is trading consistency for availability (which actually means "latency" since everything is eventually available). You will never ever get ACID from NoSQL databases.

        There are already NoSQL databases supporting ACID: http://ravendb.net/ [ravendb.net]

  • by wispoftow (653759) on Friday May 16, 2014 @01:04AM (#47015229)

    NB: I love PostgreSQL with all my heart. I always upgrade to the most recent version, because they implement features that I really need. Added to the existing features of Postgres, it's totally awesome.

    But as I have moved toward "Big Data" and the market segment that these new-fangled (non-relational) databases target, I find myself wishing that Postgres would be able to run my vanilla query (*singular*) using all processors. As it is now, I have to either write some awful functions that query manually-partitioned subtables, or simply wait while it plods through all billion or so rows.

    • by mlyle (148697) on Friday May 16, 2014 @01:49AM (#47015351)

      Look at Postgres-XL [postgres-xl.org] that we just released. It's a clustered database and can do MPP execution of your queries and has good write-scalability too. (To use all the processors in each machine, you'll want to have a few data nodes per machine.) It's pretty clever about planning a lot of things.

      • Do you know if there are any plans to merge Postgres-XL into vanilla Postgres?
        • by mlyle (148697)

          Really premature, and unlikely in any event.

          I think Postgres is good for what it is-- a clean, single-node database system. Clustering adds some complexity in deployment (we're working on making this easier) that you wouldn't want to incur for a typical Postgres install.

          I think there are pieces of Postgres-XL that make sense to be in core/vanilla PostgreSQL, and we'll be working to contribute them upstream. Likewise, there are more pieces from TransLattice's commercial database offering, TED, that Postgre

      • by Wootery (1087023)

        I like the way the linked page uses Web 2.0 when it means scalability.

        Great job with the buzzwords.

        • I like the way the linked page uses Web 2.0 when it means scalability.

          Great job with the buzzwords.

          You know, I was just going to let this go, chalked up as random Internet stranger being an asshat, but seriously. Are you SO bored or jealous of other people's achievements that you have nothing better to do than to sit around and nitpick the friggin' ad copy of a marketing page that was undoubtedly written not just for people who want to know the technical specifications of the product, but common usage applications for it also? What you're calling a "buzzword" is information that business wonks need to

          • by fuzzytv (2108482)

            So what exactly does 'web 2.0' mean? Because I can tell you it's a completely vague term, used to create hype around so many disparate concepts it lost all the meaning it once had. And even if you manage come up with some definition, do you really think the business wonks will understand it / should be responsible for choosing the technology?

            Wootery implied nothing about Postgres being incapable database system, just that the 'web 2.0' is a buzzword. That says nothing about Postgres (or rather Postgres-XL,

          • by Wootery (1087023)

            Are you SO bored or jealous of other people's achievements that you have nothing better to do than to sit around and nitpick the friggin' ad copy of a marketing page that was undoubtedly written not just for people who want to know the technical specifications of the product, but common usage applications for it also?

            I'm a techie. Like many, I find vacuous marketing to be grating. That's really all I was trying to get across. It's a very minor detail, and a very minor dig. I'm not attacking your product.

            (I can play at quasi-psychoanalysis though, if that's the game: are you so insecure about your product you have to rant at snarky Slashdotters, and imagine further insult which isn't there?)

            What you're calling a "buzzword" is information that business wonks need to know when faced with the question, "Will this solve my problem/fulfill my needs?"

            Other than a mention of JSON support, nowhere on the page are there any web-specific points being made. The section labelled 'Web 2.

    • by Anonymous Coward

      Do you really have enough I/O bandwidth in the machine to keep multiple processors busy on a single query?

  • by TechyImmigrant (175943) on Friday May 16, 2014 @01:08AM (#47015239) Journal

    It would be nice if noSQL databases adhered to the promise in the name. They replace the query language with something sane and secure.

    • by bhcompy (1877290)
      Depends on what you mean by safe. Pick ENGLISH queries are strictly read only.
    • All databases are relational (noSQL or otherwise). SQL formalizes some aspects of relational algebra but this does not imply anything about the implementation nor necessarily about the interface. If you like "simpler" interfaces use ODBC/ORMs on SQL or noSQL databases.
      • by jbolden (176878)

        No they aren't. Object databases are not relational they are hierarchical. Associative databases are associative not relational. And many of the "NoSQL" databases the partitioning scheme changes the outcome of queries which is a complete violation of the relational concept that order of rows doesn't effect the return from invoking queries.

        • by drinkypoo (153816)

          No they aren't. Object databases are not relational they are hierarchical.

          The only CORBA implementation with which I've ever been familiar definitely used a relational store, and made relational queries. I think you are speaking a little too generally.

          • by jbolden (176878)

            I haven't played around with Object Database and CORBA. But at least on first glance the two seem to be pulling in opposite directions. An Object Database at its best is an extension of memory for a family of applications all sharing a common object library. Essentially one big application. CORBA is moving in the idea of relational, but even further, trying to separate data from the underlying application. It seems to me that's an either / or choice.

  • by michaelmalak (91262) <michael@michaelmalak.com> on Friday May 16, 2014 @01:08AM (#47015241) Homepage
    A hallmark of NoSQL is horizontal scalability. The lack of schema in NoSQL was a brief rebellion against ivory tower DBAs that has since been regretted once it was realized that merely transferred the schema and schema versioning implicitly into the source code, and spread throughout it. Sounds like PostgreSQL got the bad part of NoSQL but not the good part.
    • by mlyle (148697) on Friday May 16, 2014 @01:50AM (#47015357)

      We just released Postgres-XL [postgres-xl.org] so you can have horizontal scalability and MPP.

      • by salimma (115327)
        Is there a reason for adopting a different license from core PostgreSQL ? Seems like it makes the information flow a one-way street (from SQL into XL but not vice versa). Looks like an interesting project! Throw in EnterpriseDB-level Oracle compatibility and there's a captive market out there
        • by mlyle (148697)

          Honestly, just organizational readiness. We are likely to move to a BSD-style license in the future... but initially taking a license that made it a little harder to have a closed-source fork was easier to convince some members of the team/board. (There's been a significant amount of investment in producing this codebase and we wanted to make sure we didn't immediately enable a competitor).

  • Most people don't need NoSQL. Last I checked, most people aren't Facebook or Google. And ironically those two companies are lining up behind http://en.wikipedia.org/wiki/N... [wikipedia.org]

    ACID is here to stay. We will see conventional databases improve in the latency space and NoSQL will (mostly) go away.

    • by Tablizer (95088) on Friday May 16, 2014 @01:40AM (#47015325) Homepage Journal

      Most people don't need NoSQL. Last I checked, most people aren't Facebook or Google

      Some people get overly optimistic about their start-ups or new projects. It's like planning on where to park all the beemers before you even get your first sale.

    • by Anonymous Coward

      "NoSQL" is a band-aid while programmer competence catches up with resource constraints. This old cartoon continues to be relevant [youtube.com], but mostly it's, "We don't know how to use this wheel, so we're going to reinvent it poorly. Eventually our duplication of effort will be complex enough that we're going to need to move something indistinguishable from the traditional system, but nobody needs to say that yet."

      It's like the programming language rule that everything eventually looks like a reimplementation of LISP

    • Actually, more than you think should probably use NoSQL. It isn't really any harder if you build it that way from the start and if your startup happens to get gigantic you won't have a relational database to migrate away from as one of your problems. You'll still have problems though, and even with NoSQL you need to "do it right" or it will still have issues when it gets huge.
      • And I might add that one of the most painful parts of migrating away from relational databases after you are already huge and bursting at the seams is that usually folks will have relied on the transactional consistency they provide for all the app logic and business processes. Suddenly wanting to change all that code to handle eventual consistency is not trivial at all, but if you were doing it all along because you started out that way... fewer pains.
  • by Anonymous Coward

    Is Postgres now web scale?

    • Is Postgres now web scale?

      That depends: are you going to blow some project to hell because you get a woody playing with software like it's a sex doll?

  • the hype (Score:5, Insightful)

    by Tom (822) on Friday May 16, 2014 @04:18AM (#47015679) Homepage Journal

    As a big fan of SQL database, I've been watching this NoSQL hype for a few years now, and I'm still not impressed.

    No doubt, there are a few scenarios where a conventional database isn't the best solution. But quite honestly, 90% of the people jumping on the bandwaggon would be served just as well with an SQL database - except that like so many things, you need to do it right.

    I'm no database expert (but I know a couple), so my SQL isn't perfectly optimized and stuff, but even with a little bit of interest I see that putting some effort into your database and query design pays off massively.

    And I've seen enough cases where someone scraped their SQL database and went NoSQL for absolutely no good reason. You think you're huge and SQL is too slow? Unless you just sold to FB or Google for a couple billions, you very likely are not as huge as you think. I'm running a PostGIS database doing fairly complex geography calculations on non-trivial datasets, and it's blazing fast, and whenever it isn't one hour with an SQL expert and some experimenting makes it so, because it always, with no exception, turns out that my SQL or my database design is at fault, not the database itself.

    If you've got a billion users, I will grant you that you have special needs. But every NoSQL use I have seen has been a case of people moving database work to software code instead, mostly because programmers are plenty and cheap, while experienced database experts are not.

    So I'm still amused and very little impressed, and I'm certain NoSQL will go the way of Java or every other hype ever - for a while it's everyone's darling, then people realize it still doesn't give us AI and it can't make coffee, and will start to figure out where it actually is the best solution and stop using it for everything else.

    • I've seen this a million times. People with poorly designed relational databases with no thought given to query plans complain that their database is slow. They then migrate said database to a NoSQL solution (typically a document database like MongoDB) and then find that it is still slow! . In a few cases, the NoSQL solution is significantly slower.

      The problem is NoSQL encompasses many different types of solutions. Key value stores like Redis are pretty good (key lookups support wildcards!!!) and I use the

    • There's a pretty good book I own in paperback (electronic versions available) for high performance stuff from PostgreSQL. It's called PostgreSQL 9.0 High Performance [amazon.com]. It's probably beyond what you want, but if you're interested in looking at it, let me know and I'll bring it next time we get together.

    • by dave420 (699308)
      And your work is entirely not intended to be performed in a NoSQL DB. There are still plenty of uses for NoSQL, and it is most certainly not hype itself (although there has been lots of hype about it). It will be here for a long, long time, as it has some incredibly useful use-cases. You should accept that there are uses you are unaware of :)
      • by Tom (822)

        I do. Re-read my original posting, and this time until the end. :-)

    • I don't have a problem with relational databases, but even though I'm pretty good at writing SQL queries, I don't like SQL as a language.

      There should be a middle ground somewhere between SQL, Map-Reduce and Object Oriented coding styles. I want code reuse, I want encapsulation, I want LLVM-like compiler optimisations. I want to push as much processing down to where the data is, without needing to hand optimise everything.

      SQL doesn't provide much of those things.

      • by Tom (822)

        Because it's a database.

        SQL is 40 years old. In that time, dozens of programming languages, patterns and styles have come and gone. And SQL is still here, exactly because it doesn't care if your language is OO, functional, redundant, brainfuck or agile deployment for optimized synergies with next generation engagement framework whatever.

        A database needs to concern itself with the data, not with the programming patterns of the application.

        • Writing queries on databases with large numbers of heavily normalised tables is a pain. Many queries end up similar or the same, with common table joins, and similar filter criteria. Sometimes this means you end up writing front end code to emit SQL, so that you can increase the flexibility of your application. And then you want to change the structure of a few tables. Good luck hunting down all of the queries in your app that need to change.

          Or if a query gets too complicated for the database to use the ri

          • by Tom (822)

            Good luck hunting down all of the queries in your app that need to change.

            You've heard of database abstraction layers, yes? :-)

            Here is how I and everyone sane I know develops: Write straighforward code and queries first. Then check where your performance bottlenecks are and optimize those, ignore the stuff where it doesn't matter if you could improve performance. In one current app, I have some queries that get executed on the order of 200,000 times whenever I make a run, and some that get executed ten times. Do I give a flying fuck if I could optimize the queries that get called

        • SQL is still there because it is (almost) pure relational algebra, the math is there to prove its correctness and the math can be used to optimize your queries. You can prove that one query returns the exact same lines as another, so the DB can find and run the faster query instead of your slower one.

    • by jbolden (176878)

      Java hasn't exactly gone away.

      Anyway there are fundamental design choices.

      Relational requires that table row order is completely unimportant i.e. essentially fully commutative
      No SQL requires that computations on table row order is merely associative

      Many many mathematical operations are associative that are not commutative. That can be a huge change in how data is manipulated computationally far far faster. You don't have to be Google or Facebook you just have to have enough data and complexity that CPU is

      • by Tom (822)

        Java hasn't exactly gone away.

        Which is exactly what I'm saying.

        Every hype ever has always followed the same pattern. First it is the second coming of christ (or, for hypes prior to 0 AD, the first). Then, it is the solution to all your problems and everyone uses it for everything. You can't get venture capital, employment or a marriage without it. After a while, people realize that for some strange reason, sliced bread is really cool, but it isn't really the best armour and the roof is always leaking and the wheels could really be more

    • No matter how much you optimize your schema and your queries there are limits to what one machine can handle. Depending on what your application or business needs are, this may happen MUCH sooner than a billion users. For many, merely tens of millions of really active users are enough to exceed these limits, and when you are a startup trying to grow and add features it is easier said than done to ensure that every piece of code you release is so perfect that you will not rock the boat at all, since one mi
      • by Tom (822)

        If your application is really, really simple and you need truly massive amounts of throughput, then NoSQL is no doubt the way to go.

        Somewhere between 1% and 10% of the shops doing NoSQL really fall into that category. Maybe as many again might, with enough growth.

        Many years ago, long before NoSQL was a thing, I was the sysadmin of one of the largest e-commerce operations in my country. We had enough users and data and throughput that a big consulting company that was tasked with developing the next-gen syst

    • Just like to point out that performance is not the _only_ reason to switch to a noSQL database. For example, in my project we are switching our very small DB to a noSQL solution to have schema-less data. Other examples include: proper Object-Oriented mapping to the database (no hibernate hell,) graph databases, distributed databases with auto-sincronization (part of the database is on a mobile phone and when it connects to the internet it syncs with the remote server automatically.)

      Sure you can do all that

      • by iggymanz (596061)

        there are plenty of mature and better persistence layers out there than hibernate, that work with SQL database. You lose a lot when you lose schemas, structuring and knowing types of data is very useful for later analysis. Pay now, don't worry about it later

        • Some noSQL allows for hybrid schemas (have part of the record be structured and optionally with indexes, but also allow any other data to be added to the record) and that is what I am using. Personally I have not used other persistence layers other than hibernate, but my time with hibernate left me very bitter.

          But anyway, that was not the point I was trying to make, the point is that whenever people say "database" they think they need the absolute most efficient thing ever. But there are a myriad of cases w

          • by Tom (822)

            People often forget to weight performance against programmer productivity.

            That's exactly when you want to use a library, framework, etc.

            Personally, I've become a fan of Doctrine 2, which does all the nice object-orientation and other stuff and I still have a SQL database behind it all with its power and performance. Not to mention the crazy stuff you can do with good queries.

    • by Jawnn (445279)

      But every NoSQL use I have seen has been a case of people moving database work to software code instead, mostly because programmers are plenty and cheap, while experienced database experts are not.

      This, in spades. And my life ( a large part of which is seeing to the performance of our applications) is hell because of it. If I had a dollar...

    • But java IS coffee!
  • When we were looking into new options to supplement our MSSQL servers, we settled on Mongo. We were aware that Postgres will act as a document store in addition to being a traditional RDBMS, but our decision was largely based on 2 things: We acknowledge that we'll likely never be able to completely eliminate our use of MSSQL. So, if we need an RDBMS, it will still be there. The other main factor was that Mongo makes replication, failover, and sharding a snap, relative to other systems. We don't have a DBA

Saliva causes cancer, but only if swallowed in small amounts over a long period of time. -- George Carlin

Working...