Salesforce, PostgreSQL and Bruce Momjian

Given Bruce's blog post today, I wanted to make a few things clear which might not be at all clear to folks outside our project:

1. Bruce is indulging in personal speculation, not speaking for the core team.
2. Bruce is not one of the core team members who deals with Salesforce, so he has no knowledge of their plans, strategy, or expected contributions to PostgreSQL.

In other words, Bruce's entire post is pure speculation, without any inside knowledge of any kind.

Unfortunately, the members of our team who do deal with Salesforce regularly are prohibited by NDA from discussing what their plans might be.

Maximum Importance Aggregation

You already know that I'm a big fan of custom aggregates.  What's even more fun is when you combine them with the ordered aggregate feature from PostgreSQL 9.0.  For example, let's take a fairly common task: selecting the item with the maximum importance.

This is a challenge for standard aggregation because you want to display a value which relates to an importance score contained in another table.  For example, the table could be a lookup list of error types, and you want to display the most severe error.  This is simple to do if you only want one row, but what if you want to group by other columns?  Well, here's a relatively neat way using ordered aggregates and custom aggregates.

First, we want to create a generic aggregate called "first", which simply returns the first non-null value in the column:

    CREATE FUNCTION first_state (
        anyelement, anyelement )
    RETURNS anyelement
    LANGUAGE SQL
    IMMUTABLE STRICT
    AS $f$
        SELECT $1;
    $f$;

    CREATE AGGREGATE first ( anyelement ) (
            SFUNC = first_state,
            STYPE = anyelement
    );

This is a generally useful aggregate to have around, btw, so I suggest adding it to your general library.

Now, let's look at our tables:

    table errors (
        error_code text primary key,
        severity int not null default 0,
        description text
    )

    table error_log (
        lo_time timestamptz,
        error_code text    
    )

So, say I wanted to see the count of errors for each hour in the log and the most severe error during that hour?  Well, what I need to do is to select the first error code which shows up when I order by severity.  For good measure, I order by error_code as well, in case there's two codes with the same severity:

    SELECT date_trunc('hour',log_time) as log_hour,
        count(*) as error_count,
        first(error_code order by severity desc, error_code) 
            as most_severe
    FROM error_log
        JOIN errors USING (error_code)
    GROUP BY date_trunc('hour',log_time)
    ORDER BY date_trunc('hour',log_time);
 
And that works a treat:

           log_hour        | error_count | most_severe
    -----------------------+-------------+-------------
    2013-06-17 00:00:00+00 |           6 | CX56B
    2013-06-17 01:00:00+00 |          16 | CX56B
    2013-06-17 02:00:00+00 |           4 | PPPMQ5
    2013-06-17 03:00:00+00 |           2 | PPPMQ5
    2013-06-17 04:00:00+00 |          24 | CX56B
    2013-06-17 05:00:00+00 |          15 | CX56B
    2013-06-17 06:00:00+00 |           5 | FM229A

I'm sure you can figure out ways to adapt this to your needs.  Enjoy!

9.4 Commitfest 1 Has Begun!

The first CommitFest for version 9.4 of PostgreSQL has started, and we have an inspiring list of new features, in 98 patches, ready to be reviewed.  Which means we need you to help review them!  Among the pending patches are:

• Logical Changeset Generation, the basis for the new replication;
• Setting postgresql.conf variables from the SQL command line;
• Transaction Log size autotuning;
• Transforms between custom PostgreSQL datatypes and PL/Python, Perl and Javascript datatypes;
• Row-Level Security;
• GIN Fast Scan, and other GIN index improvements;
• Block-based range indexes for very large tables;
• Concurrent update of materialized views;
• Multiple pgbench enhancements;
• Many performance improvements;
• A bunch of additional regression tests.

Given that that's nearly 100 patches, and the PostgreSQL project has only 20 committers, it means we need you to help us review patches.  Yes, you!  You don't have to be able to hack PostgreSQL code to review a patch; if you can apply a patch to source code and build PostgreSQL using "make", you can help.  More information here in How To Review A Patch.

If you want to help, but aren't sure what to review, please join the pgsql-rrreviewers mailing list and announce yourself there.  I will assign you a patch to review.

For those of you who are experienced reviewers or who have submitted a patch to this CommitFest, please note the following:

1. If you submitted a patch to this CommitFest, you are expected to review at least one other person's patch.  Should you choose not to do so, the community will not be obligated to guarantee you a review of your patch.
2. If you put your name down as a reviewer on a patch, and you don't post anything for 5 days, we will send you a reminder and take your name off the patch, to prevent you from blocking other reviewers.
3. If your patch goes to "waiting on author" and you don't respond for 5 days, the patch will be Returned with Feedback.

Rules 2 and 3 are new per this year's developer meeting.  Thanks, and lets have a fast and productive CommitFest!

First-ever pgCon Unconference: Success

Our first-ever unconference at pgCon was a smashing success this year, greatly enhancing collaboration and development for the upcoming versions of PostgreSQL.  Among other things, I think we got a specification for pluggable storage out of it, which is far beyond anything I'd expected.  Were you there?  How did you like it?  Give your feedback in the comments.

About 75 people attended, and hashed various topics out in 12 discussion sessions. These included expanding PostgreSQL testing, creating demo databases, enhancing full text search, upcoming JSON features, pg_upgrade, and of course the two-part Pluggable Storage/Foreign Data Wrapper discussion.  Anyway, it's all on the wiki if you missed it.

We're definitely doing one next year, so plan to stay an extra day at pgCon. 

I thank the following people for making the first unconference a success: Salesforce.com, our unconference sponsor;  Stacey Haysler, Nikhil Sontakke, Susanne Ebrecht, Hartmut and Ian Barwick for helping to run the event; and Dan Langille, conference organizer.  Thank you folks!

Yes, I know the Unconference was almost two weeks ago, but I lost my phone in Ottawa and this is the first I could get pictures up.

PostgreSQL New Development Priorities 5: New User Experience

So, I started this looking for our five major goals for future PostgreSQL develoment.  The last goal is more nebulous, but I think equally important with the other goals.  It's this: improve the "new user experience".

This is not a new goal, in some ways.  Improving installation, one of our previous 5 goals, was really about improving the experience for new users.  But the new user experience goes beyond installation now, and competition has "raised the bar".  That is, we matched MySQL, but now that's not good enough; we need to match the new databases.   It should be as easy to get started on a dev database with PostgreSQL as it is with, for example, Redis.  Let me give you a summary of the steps to get up, running, and developing an application in the two platforms:

Redis:

1. install Redis, either from packages or multiplatform binaries.  No root access is required for the binaries.
2. read a 1-page tutorial
3. run redis-server
4. run redis-cli or install drivers for your programming language
5. start developing
6. when your app works, deploy to production
7. in production, tune how much RAM Redis gets.

PostgreSQL:

1. install PostgreSQL from packages or the one-click installer.  Root/Admin access is usually required.
2. search the documentation to figure out how to get started. 
3. figure out whether or not your packages automatically start Postgres.  If not, figure out how to start it.  This may require root access.
4. Install drivers for your programming language.
5. Figure out how to connect to PostgreSQL.  This may require making changes to configuration files.
6. Read more pages of documentation to learn the basics of PostgreSQL's variety of SQL, or how to program an ORM which works with PostgreSQL.
7. Start developing.
8. Deploy to production.
9. Read 20 pages of documentation, plus numerous blogs, wiki pages and online presentations in order to figure out how to tune PostgreSQL.
10. Tune PostgreSQL for production workload.  Be unsure if you've done it right.

The unfortunate reality is that a new user will hit a lot of points in the "getting to know PostgreSQL" where they can be stuck, confused, and at a loss.  At those points, they may decide to try something else, and never come back.  I've seen it happen; just last SFPUG I was talking to a guy who started on Postgres, ran into a shared memory issue, switched to Mongo, and didn't come back to Postgres for 2 years.

So, what can we do about it?  Well, a few things:

• better new user tutorials, such as the ones on postgresguide.org
• better autotuning, made a lot easier to implement as of version 9.3.
• a "developer mode PostgreSQL"

The last would be a version of PostgreSQL which starts when the developer opens a psql prompt, shuts down when they exit, starts with minimal processes and crash safety turned off, and above all with a security configuration which allows that user to immediately connect to PostgreSQL without figuring anything else out.  With some of the work on recovery mode supplying a single-user Postgres, this should become easier, but it needs a lot more work.

Those are the five things I can see which would greatly expand the market for PostgreSQL and keep us competitive against the new databases.  Yes, I'm talking really big features, but any two out of the five would still make a big difference for us.  There may be others; now that you've seen the kind of big feature I'm talking about, put your suggestions below.

PostgreSQL New Development Priorities 4: Parallel Query

Parallel query is the first priority from those suggested in the comments that I agree should be a major PostgreSQL development priority.  I think that Joel Jacobson summarized it neatly: Bring Back Moore's Law.  Vertical scaling has always been one of PostgreSQL's strengths, but we're running into hard limits as servers are getting more cores but not faster cores.  We need to be able to use a server's full CPU capacity.

(note: this series of articles is my personal opinion as a PostgreSQL core team member)

The benefits to having some kind of parallel query are obvious to most users and developers today.  Mostly, people tend to think of analytics and parallel query across terabyte-sized tables, and that's definitely one of the reasons we need parallel query.  But possibly a stronger reason, which isn't much talked about, is CPU-heavy extensions -- chief among them, PostGIS.  All of those spatial queries are very processor-heavy; a location search takes a lot of math, a spatial JOIN more so.  While most users of large databases would like parallel query in order to do things a bit faster, PostGIS users need parallism yesterday.

Fortunately, work on parallelism has already started.  Even more fortunately, parallel query isn't a single monumental thing which has to be done as one big chunk; we can add parallelism piecemeal over the next few versions of Postgres.  Rougly, parallel query breaks down into parallelizing all of the following operations:

• Table scan
• Index scan
• Bitmap scan
• In-memory sort
• On-disk sort
• Hashing
• Merge Join
• Nested loop join
• Aggregation
• Framework for parallel functions

Most of these features can be worked on independently, in any order -- dare I say, developed in parallel?  Joins probably need to be done after sorts and scans, but that's pretty much it.  Noah Misch has chosen to start with parallel in-memory sort, so you can probably expect that for version 9.4.

PostgreSQL New Development Priorities 3: Pluggable Parser

Really, when you look at the long-term viability of any platform, pluggability is where it's at.  A lot of the success of PostgreSQL to date has been built on extensions and portability, just as the success of AWS has been built on their comprehensive API.   Our future success will be built on taking pluggability even further. 

In addition to pluggable storage, a second thing we really need is a pluggable parser interface.  That is, it should be possible to generate a statement structure, in binary form, and hand that off to libpq for execution.  There was recently some discussion about this on -hackers. 

If there were a way to hand off expression trees directly to the planner, then this would allow creating extensions which actually had additional syntax, without having to fork PostgreSQL.  This would support most of those "compatibility" extensions, as well as potentially allowing extensions like SKYLINE OF which change SQL behavior.

It would also help support PostgreSQL-based clustered databases, by allowing all of the parsing for a particular client to happen on a remote node and get passed to the clustered backends.  The pgPool2 project has asked for this for several years for that reason.

More intriguingly, it would allow for potentially creating an "ORM" which doesn't have to serialize everything to SQL, but can instead build expression trees directly based on client code.  This would both improve response times, and encourage developers to use a lot of PostgreSQL's more sophisticated features since they could access them directly in their code.

Taking things a step further, we could extend this to allow users to hand a plan tree directly to the executor.  This would fix things for all of the users who actually need query hints (as opposed to those who think they need them), as well as taking efficiency a step beyond cached plans.

There are a lot of reasons this would be just as difficult to do as pluggable storage.  Currently parsing depends on a context-dependant knowledge of system catalogs, including things like search_path.  So I have no idea what it would even look like.  But a parser API is something that people who hack on Postgres and fork it will continue to ask for.