27 Comments

• 
  Josh Berkus April 14, 2011 at 4:31 pm

  Nathan,

  No argument that most of those things would be very nice to have. I especially lust after query tracing. Are you up for a little hacking?

  You’ll be glad to know, at least:
  re: 7 PostgreSQL 9.1 will be getting Unlogged Tables
  re: 8 PostgreSQL 9.1 will be getting attached tables via SQL/MED.

  That’s the nice thing about Postgres … wait a few months, it’ll have more features.

  Reply
  • 
    Nathan Thom April 14, 2011 at 4:40 pm

    I can’t stand editing my own code, let alone something written by others

    Thanks for the heads up!

    Reply
    • 
      Jeff Davis April 15, 2011 at 2:33 am

      pg_upgrade allows you to upgrade from 8.3 or later without reloading data. It takes just seconds, even if there is a TB of data.

      Reply
• 
  seymourz April 14, 2011 at 4:36 pm

  This is somewhat non-sense. PostgreSQL by no means have future if becoming another Oracle copy.

  Reply
• 
  Rob Wultsch April 14, 2011 at 6:33 pm

  Work was done on a progress indicator in 2010. There is also enough use case that someone might pick it up in the near future.

  Reply
• 
  Sebastiano Pilla April 14, 2011 at 8:16 pm

  I could not disagree more about the import/export tools, PostgreSQL’s pg_dump and pg_restore are actually usable compared to the junk that are Oracle’s tools. Just a few examples off the top of my head:

  - imp cannot import a CLOB or BLOB column in the default tablespace of the user doing the import, it insists on having the tablespace of the CLOB/BLOB column in the target database
  - exp in 11g is unable to export tables without rows, unless you set a relatively obscure database parameter (DEFERRED_SEGMENT_CREATION = false)
  - exp is deprecated in 11g, instead we get expdp and impdp which are more difficult to use and cannot be used between different point versions of the database

  There’s so many issues with Oracle’s import/export tools that I’ve started to use the dbms_metadata package to export the DDL and DbUnit to export the data in XML format.

  Reply
  • 
    Nathan Thom April 14, 2011 at 9:25 pm

    I gladly tossed exp/imp aside in favour of expdp/impdp, I think they’re awesome. I’m also pretty certain you can expdp/impdp between versions. You can even do it between operating systems.

    Reply
    • 
      Riva April 15, 2011 at 12:43 am

      There’s a parameter for expdp/impdp

      VERSION Version of objects to export where valid keywords are:
      (COMPATIBLE), LATEST, or any valid database version.

      If you need to export for a 10gR1, VERSION=10.1.0.2 will work right when exporting from a 11gR2.

      I agree they are more complicated than exp/imp, I spent some months using the old tools but at the first non-trivial need I had, i loved the novelty

      Reply
  • 
    Mladen Gogala August 5, 2011 at 6:30 am

    Sebastiano, normal “exp” is de-supported in 11g. If you are talking about expdp, the parameter CONTENT=METADATA_ONLY will export the metadata, without the rows.

    Reply
• 
  Taylor Steil April 14, 2011 at 9:09 pm

  My #1 missing feature is analytic functions. As a developer (not a DBA), I use them all the time.

  Reply
  • 
    Nathan Thom April 14, 2011 at 9:21 pm

    It has a few. Some of the ones I use: http://www.postgresql.org/docs/current/static/tutorial-window.html

    Reply
  • 
    Hans April 25, 2011 at 11:09 pm

    @Taylor: Analytical functions are called “windowing functions” in the SQL standard and PostgreSQL supports them since 8.4 The number of functions is more or less on par with Oracle, but the window definition (“partition by…”) is actually more advanced than in Oracle.

    Reply
• 
  Ryan Bair April 15, 2011 at 1:24 am

  Index organized tables. Retrieving the row by primary key in a single operation should be faster than having to do separate index and heap lookups. Always having the table in physical order for efficient range scans can be very nice for certain workloads as well.

  Reply
  • 
    who_cares April 26, 2011 at 12:29 am

    you mean “cluster” ?

    http://www.postgresql.org/docs/9.0/static/sql-cluster.html

    Reply
    • 
      dw May 17, 2011 at 9:09 pm

      I’m no PostgreSQL expert, but IOTs are not heap tables sorted by index, they _are_ the index.

      Consider “select a, b, c from x where a between 1 and 10″ where “a” is PK and x is IOT. In this scenario, blocks retrieved by index scan are the data and there is no need for additional table access to get it. If x wasn’t IOT, the scan would give us a list of rows to read, most probably lying in different blocks.

      Also, reading ‘cluster’ command reference I see that clustering is not automatic for subsequent updates, blocks table access, and requires twice as much space as data size. Updatable tables holding a considerable amount of data would be a problem with this.

      Reply
• 
  Marti Raudsepp April 25, 2011 at 8:17 pm

  You say:
  > The parameter log_min_duration_statement can be set to log every SQL
  > statement that is executed by every user in the entire database cluster

  In fact, this parameter, like many other session attributes, *can* be changed selectively:
  * per-user: ALTER USER foo SET log_min_duration_statement=X
  * per-database: ALTER DATABASE foo SET log_min_duration_statement=X
  * Superusers can also change it per-session SET log_min_duration_statement=X

  Reply
  • 
    Nathan Thom May 1, 2011 at 7:39 pm

    Thanks for pointing that out, but it doesn’t help me trace what is happening from a connection pooled app server. I can’t set the per-session parameter from another session can I?

    Reply
• 
  Ora-gel it will leave you feeling numb April 26, 2011 at 12:27 am

  Nathan,

  if I see tablespace management come into postgres like it exists in ora-gel I will probably vomit.

  honestly if there is one part of oracle that even non-DBA folks can clearly see is troublesome legacy junk left in oracle it is tablespace management.

  Reply
• 
  Greg Smith April 26, 2011 at 6:15 am

  “Multiple Block I/O” doesn’t really work as you’re hoping for given PostgreSQL’s disk access model. We’ve benchmarked it multiple times. The part that does help, helping the OS schedule reads, is implemented for bitmap table scans–the only case where there’s been a clear improvement measured. On sequential ones, OS read-ahead already does such a good job on so much I/O that trying to get the database involved doesn’t show any consistent benefit. This really isn’t a surprise on Linux in particular; the kernel has been playing around with improvements specifically targeting this workload for years, i.e. http://lkml.org/lkml/2006/5/24/71

  The suggestion that the database needs to provide better caching control fall into a similar bucket. There is already some logic for keeping sequential scans and vacuums from using all of the cache. But even those fall down because you can still trash the operating system cache. And there’s little PostgreSQL can do about that without taking over all I/O, which is a questionable design direction. The assumption that the kernel developers are working on those low-level problems is a reasonable one, and it allows the database to advance faster than if it tried to address all those issues itself.

  Would would work better in the place of these two is promoting the fragmentation management items from a footnote to the main list; it’s at #1 on my own prioritization list. Queries that get lost navigating bloated indexes for example is a large component to the problem you’d like to diagnose with “Query progress”, particularly on larger databases. And that wouldn’t actually help you all the time; bloat is often invisible to the query execution code, due to how query nodes pull data from one another on demand.

  Reply
  • 
    Mladen Gogala August 5, 2011 at 6:15 am

    Greg, fact is that Oracle does a full table scan much faster than Postres, even old versions like Oracle 9.2.0.8. Postgres performance group is full of questions like that. Here is the result that’s hard to explain, especially having in mind that the machine that runs Oracle is much weaker than the machine running Postgres:

    Oracle 9i:
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.8.0 – Production
    With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.8.0 – Production

    SQL> set timing on
    SQL> select count(*) from moreover_sm_documents
    2 where created_at>=’01-JUL-11′ and
    3 created_at \timing
    Timing is on.
    news=> select count(*) from moreover_sm_documents_y2011m07;
    count
    ———-
    36986831
    (1 row)

    Time: 295965.113 ms
    news=>
    (almost 5 minutes)
    This is 64 bit machine with 16GB RAM, running RH 5.5

    Records are exactly the same, as Postgres is copied from Oracle. The hardware advantage weighs heavily on the Postgres side, yet Oracle does a full table scan 4 times faster. There is nothing to add. Please note that I can make things even faster with Oracle by using several processes to scan simultaneously:
    SQL> select /*+ parallel(sm,4) */ count(*)
    2 from moreover_sm_documents sm
    3 where created_at>=’01-JUL-11′ and
    4 created_at< ‘01-AUG-11′;

    COUNT(*)
    ———-
    36986831

    Elapsed: 00:00:57.13

    That is something that Postgres definitely cannot do.
    To put it simply, the performance hit incurred when moving from Oracle to Postgres is simply too great and, along with silly things like hints, prevents me from using Postgres. Postgres is simply not there yet. Judging by the debate about hints, it’s not going to get there for quite some time.

    Reply
    • 
      Mladen Gogala August 5, 2011 at 6:25 am

      For some reason, Oracle result got lost. Here it is:

      SQL> set timing on
      SQL> select count(*) from moreover_sm_documents
      2 where created_at>=’01-JUL-11′ and
      3 created_at< ‘01-AUG-11′;

      COUNT(*)
      ———-
      36986831

      Elapsed: 00:01:13.75

      Reply
• 
  SQLpro April 27, 2011 at 1:24 am

  For me the 10 most important missing features of PG are, in order :

  1) Muti processing Query processor (the ability to execute some stepds of a query plan via more thant one thread)

  2) Indexed views (like MS SQL Server or materialized one like Oracle)

  3) Storage dataspace management (min space, max space, space growth step and locations of file et collection of fiel via storage or tablespace).

  4) Semantic optimisation technics
  For instance the abality to return no rows at all, without scanning the table if a query ask for some values wich cannot appear in the table due to CHECK constraint (price >=0). In a same way, simplifying joins where FK are set.

  5) DDL triggers to cath event like CONNECT, CREATE, ALTER, DROP…

  6) a command to switch context from one database to one another (like USE in MS SQL Server)

  7) a change data capture system to track the evolution of data in tables (INSERT, UPDATE, DELETE…)

  a data auditing mechanism to track how make what (SELECT include)

  9) auditing indexes : some internal views to know which index are not used and the ones that are missing

  10) a synchro mirroring high avialability per database (like MS SQL Server database mirroring)

  Reply
• 
  max ogden May 31, 2011 at 4:45 pm

  i think that couchdb style p2p http replication at the document level with sequence indexes would be awesome for #opendata everywhere

  Reply
• 
  victor tan June 1, 2011 at 12:30 am

  Bitmap indexes. I know that it isn’t straightforward due to MVCC and other architectural reasons but my gut tells me that it is still worth it for DW type situations.

  Reply
• 
  Mladen Gogala August 5, 2011 at 5:53 am

  I am perplexed by unwillingness to implement any meaningful system of hints which would enable a human to override the optimizer decision. The debate that I had about this convinced me that PostgreSQL is a project which doesn’t have much of a future.

  Reply
  • 
    Chris Travers January 15, 2012 at 5:40 pm

    I disagree with the statement about query hints. The one case where I ran into planner optimization issues, I wrote the performance list, got high quality workarounds within hours, and the problem was fixed in the next major release.

    No, the planner will never be perfect, and I think there are a few areas (where query plans are to be cached) where hints should be allowed but that case is only because in those cases the planner fundamentally lacks sufficient information to provide the best general plan for successive calls.

    In other words, I would suggest that the discussion needs to be whether the planner has sufficient information to make good planner choices, and where it does not, query plans may need to be supported. Such a minimalistic system would, I think, provide the best of both worlds, and there is precedent set in the few hints that can be passed to the create function statement.

    Reply
• 
  Samuel Hwang September 17, 2011 at 5:54 am

  I would love to see PostgreSQL have some kind of global query plan cache to reduce parsing/planning overhead.

  Reply