back to article PostgreSQL puts the pedal to the metal with some smart indexing tweaks in version 12

Open-source database darling PostgreSQL emitted a new version of its eponymous database last night with more nods to standard SQL and a performance boost. The performance gains can be attributed to improvements in the indexing system as well as to partitioning. Frequently modified B-tree indexes have been optimised and the …

  1. sum_of_squares
    Thumb Up

    PostgreSQL is great. With a slightly better toolset and some more goodies it would be an adequate replacement for SQL server. Unfortunately there are a couple of things where SQL Server is a tad more advanced, but I really like PostgreSQL and use it wherever I can.

    1. bombastic bob Silver badge
      Devil

      "PostgreSQL is great"

      Absolutely!

      From what I have seen it is _the_ most standards-compliant DBMS out there, more compliant than MySQL or Maria from what I can tell (both still have issues with embedded quote marks in strings last I checked, having to escape them etc. - it would break too much code if they ever fixed it, probably)

      In any case I got used to using psql to fix things for this one project. "No B.S."

      It'll be nice to see if PG ever has the back-end features needed to take on Oracle in the same markets.

      1. Warm Braw Silver badge

        PostgreSQL is wonderful - used it for years. I particularly like the way you can use INHERITS on CREATE TABLE to create variants of a basic data type but still have one set of queries that will find any of them.

        1. Tom 7 Silver badge

          I think you can do that on MySql too.

    2. Tom 7 Silver badge

      PostgresSQL is good. It should say in large letter on the front somewhere that it does procedural overloading. I was working with it in a group and I like to write procedures to help keep things tidy and separate and no-one told me it did overloading. Wierded me out for a few days - especially as the experienced workers there didnt seem to know.

    3. Charlie Clark Silver badge

      I'm struggling to think of features that SQL Server has that Postgres doesn't come with or have extensions for. Maybe management functions? In other areas Postgres seems to be leading the pack.

      For really high-end deployments, EnterpriseDB's version comes with support for Oracle's backend which makes migration of some large installs feasible.

      1. Tom 7 Silver badge

        One thing about SQL Server is it was easy to write extensions, I remember my PHB explaining why we needed to spend budged on 4.5 -> 6.5 upgrade and training for him rather than me getting a pay rise and slowly going through all the reasons and pointing out I'd written extensions to do everything we needed. He was well pissed off.

        1. Charlie Clark Silver badge

          You probably don't mean to say it, but your post implies that writing extensions for Postgres isn't easy. This is, of course, far from the truth.

      2. werdsmith Silver badge

        Postgre would replace SQL Server in most case where just a database is required.

        However, a SQL server license covers Integration Services, Analysis Services, Report Services and other stuff as a (mostly) integrated suite as long as you run them on one box (which is really a RAM question).

        I’m 100% a Postgre supporter but SQL Server has its place.

        1. Anonymous Coward
          Anonymous Coward

          Postgres built it's reputation and user base on being a fast, lightweight read-only database (in an explosively expanding market for web servers). Users who required good transactional performance went somewhere else.

          Naturally, PostgreSQL users want to use their well-known tool for other uses, and PostgreSQL has been chipping away at those requitements for years. Every release gets better and better at supporting more use cases.

          1. Charlie Clark Silver badge

            It sounds to me like you're describing MySQL. Postgres has always had excellent transactional and parallel features.

    4. Hans 1 Silver badge

      With a slightly better toolset and some more goodies it would be an adequate replacement for SQL server.

      Would you be so kind as to list any toolsets/goodies you would like to see in PostgreSql ?

      1. Anonymous Coward
        Anonymous Coward

        Always on type cross data centre clustering? In memory / in process database engine? Proper automated query and index optomisers? Resumable online indexing? Full reporting and analytics engines? Proper management toolset? Performance not an order of magnitude slower than SQL Server? Support integrated for AI / ML? Proper CMDB solution with per line config ACLs and auditing?

        I could list plenty more but that's enough for starters.

        1. Charlie Clark Silver badge

          If I see something like query speed compared then I generally think, someone hasn't thought enough about their indices, or tried to use the analyzer. Now, while there is definitely scope for the Postgres analyzer tools to improve so that you know what to look for, that doesn't mean the database is slow. If something is "mission critical", be prepared to spend some money on external support to get the design right.

          What do machine learning and "AI" have to do with a relational database? Please take your buzzwords with you and close the door on the way out.

    5. TheVogon Silver badge

      A couple of things? SQL Server is more advanced in every aspect and has hundreds of features that Postgresql doesn't. Not to mention that SQL Server is many times faster than Postgresql in typical use.

      Postgresql is OK only for something where you don't need an enterprise grade solution and support. You get what you pay for.

      1. Charlie Clark Silver badge

        Doesn't "enterprise grade" mean licence fees so exorbitant that no one is going to admit to it being a mistake? I'm sure several Oracle DBAs just smile when they see someone talking up SQL Server, even though it is a fine database.

  2. james_smith Bronze badge

    Recently replaced a "hybrid" MySQL and Solr setup with PostgreSQL, using the full text indexing support in place of Solr. No more MySQL "quirks", better performance and much easier to test or tweak the full text searching. All that, and one less dependency on Oracle owned stuff!

    1. Charlie Clark Silver badge

      The text search engine is awesome.

      1. werdsmith Silver badge

        The GIS support is also pretty effective.

  3. Malcolm Herbert 1

    PostgreSQL has the best date math calculation I know - I have been known to fire up the client just to do time/date math rather than a full-blown calendar app. It could be seen as being super pedantic, but as they say, there is no reliable context-free way to convert between intervals of seconds, minutes, days, months or years ... minutes may have 61s, days might have 23-25h, everyone knows the issues with months which then affects years ...

  4. rmullen0

    Can you configure a database as case insensitive now???

    Has anyone got case insensitive collations working? I installed it a couple days ago and couldn't get it working. The documentation is poor. Case insensitive collations should be included by default. It is ridiculous that this feature has been missing all this time. Also, as far as a know computed/generated columns have to be stored on disk, not computed a query time. PostrgreSQL seems good. I just can't understand how something like case insensitivity could be missing all this time. I know about CITEXT. The point is that you should not have to use proprietary syntax like that. Or, do things like convert everything to lower case. That just lame. Other than that, it seems all right and is a lot faster that MySQL. SQL Server will remain my top pick until case insensitive searches are working. As far as I'm concerned, case insensitive should be the default.

    1. cbars

      Re: Can you configure a database as case insensitive now???

      Definitely not the default. Perhaps you can not imagine a scenario where performance matters, but I think that's the more common case and the defaults should always back the most common case. The solution to your problem is to standardise your data once before you store it, rather than to waste time on every single subsequent search. The data in your columns should be homogeneous, the most common case problems come in for things like Names, which is a handful of text columns across the whole database - if it's not then perhaps a graph/noSQL DB would better suit the use case. If one is searching for free text typed into a field like 'Notes' then I would say some better categorisation columns are required. Notes should only be relevant once you are looking at a record - not be used to find a record - if desperate then sure, go ahead and search on it - but let's not change the defaults to fit the one desperate use case of a poorly designed system. (To be clear, I am not saying your system is poorly designed, just my example 'Notes' use case system)

    2. Charlie Clark Silver badge

      Re: Can you configure a database as case insensitive now???

      Searching and sorting are not the same so I'm not quite sure what the issue. In general, for columns you should normalise your data for whatever processing you're going to do. Case-sensitive sorts are almost always going to be fastest and you can define pro-table or pro-column collations and you can even do this pro query. See the docs for more information. The docs aren't perfect but that was the first page that popped up when I searched for collation, though as long as you don't use the LOCALE_C you shouldn't have too much trouble.

      For searching, there are worlds of difference between using the builtin LIKE or REGEX and the text search extensions. If you do have data that is case sensitive but want to do case insensitive search (you have "JOBS", "jobs" and "job" in your data), then you really should normalise your data and/or query, or at least use a relevant index.

      For full-text searches case sensitivity is the least of your worries!

  5. ozreg

    Re: Good reasonable and balanced.

    When did 385 days mean an "almost two year gap"?!?

    postgres=# select '2016-01-07'::date - '2014-12-18'::date;

    ?column?

    ----------

    385

    (1 row)

    https://www.postgresql.org/docs/release/9.4.0/

    https://www.postgresql.org/docs/release/9.5.0/

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Biting the hand that feeds IT © 1998–2019