I'm using postgresql right now.
My use case: I could have a table like:
create table items (id uuid primary key, attributes json);
I'd have a webapp using javascript for adding/editing new items. If the user wants to add a new item, the client code would generate the uuid.Editing and adding new rows would both use the same UPSERT/MERGE code -- no need for separate insert or update statements -- if the uuid already exists, it's assumed to be an update, if it doesn't, it's inserted as a new row.
While we're at it, here's a "find or create" in a single atomic statement: https://gist.github.com/paul/75ec84d131e36492b17b
create table people (uuid varchar, name varchar);
with new_rows as (select (unnest(ARRAY[('xxx','John Smith'), ('yyy', 'Ben Bitdiddle')]::people[])).*), updated as (UPDATE people SET name=new_rows.name FROM new_rows WHERE people.uuid=new_rows.uuid RETURNING people.uuid) insert into people select new_rows.* from new_rows left join updated using (uuid) where updated.uuid is null; UPDATE Table SET ... WHERE id=$1
if (rows_updated == 0) {
INSERT INTO Table ... WHERE NOT EXISTS (SELECT 1 FROM Table WHERE id=$1)
if (rows_inserted == 0) {
throw "Conflict!"
}
}
It's a bit of a hack, but works well enough. I agree would be nice to have a more transparent way of doing it.Quoting ocharles:
> susceptible to race conditions, which is why many of us want true UPSERT semantics
The snippet provided in the postgres doc is the current best, but it's still has a number of flaws:
> This coding assumes the unique_violation error is caused by the INSERT, and not by, say, an INSERT in a trigger function on the table. It might also misbehave if there is more than one unique index on the table, since it will retry the operation regardless of which index caused the error. More safety could be had by using the features discussed next to check that the trapped error was the one expected.
Another feature I would wish to see is ORDER BY + skip for update (or any equivalent solution) to easily implement queues with multiple consumers.
EDIT: Just ORDER BY (without skipping locked entries) for UPDATE would be excellent too to avoid deadlocks when doing multi row updates.
A minor feature I'd like to see is to allow ORDER BY on UPDATE statements to avoid deadlocks. If you have two multi-row UPDATEs that hit the same rows but in different order, you've got a classic deadlock setup. Apparently you can sometimes control UPDATE ordering by persuading Postgres to use certain query plans if you really know what you're doing, but that seems like an undesirable requirement for most users.
To answer your bullet points, Postgres is my #1 database choice, but I've also used Oracle and MySQL many times and sometimes still do. Neither of these features are things that are preventing me from using Postgres, but I've sure wished they were there. In particular bitmap indexes seem useful to many people.
[1] http://www.postgresql.org/message-id/20090111104442.GA3503@e... [2] http://www.postgresql.org/message-id/20081101000154.GO27872@...
That would be reasonable to do even if you don't know the internals very well. The index types (called "index access methods") are well-isolated (API-wise) from most of the rest of the code. Technically, you could even add a new index access method as an extension without even touching the core.
The exception to both of those things is the WAL recovery code, which can't be written as an extension and also requires more knowledge of the rest of the system.
But the WAL recovery code is not too bad for those that know it. So if you did the rest, then I wouldn't be surprised if someone jumped in to write it or help you write it.
"A minor feature I'd like to see is to allow ORDER BY on UPDATE statements to avoid deadlocks."
One solution here is to do a "SELECT ... FOR UPDATE" first. Unfortunately, that is redundant (and does redundant work).
EDIT: One thing that intimidated me was browsing through the mailing list thread in my [1] above and seeing people ask about lock correctness. That seems like something where you'd need a broad view of the system. I might give it a try anyway, though. One thing about Postgres is your team is incredibly helpful, professional, and kind. Tom Lane has given me all kinds of answers on the mailing list in the past. I have enough good will for the project that I'm pretty much just looking for an excuse to contribute something. It would be an honor to give something back, and I feel safe asking for help from you all.
I'm curious about what the performance ramifications of that would look like. I recently had to disable bitmap scans in a data warehouse-type-thing I run because filesystem fragmentation was apparently misleading Postgres into thinking it would be cheaper than an index scan. The bitmap scan plan takes ~15 minutes to run cold, and the index scan takes ~20 seconds cold. (Really odd thing is that index scan takes ~1.5 seconds warm, versus ~0.4 seconds warm with the bitmap scan).
One major stopping point for us is the fact that on SQL Server we use a Case Insensitive, Accent Insensitive collation. Postgres doesn't support this, and apparently has no plans to.
We have many stored procedures in TSQL which are built around the assumption of CI,AI. Converting these will be a lot of work, but the lack of CI,AI collation would be by far the biggest pain point.
citext: http://www.postgresql.org/docs/9.2/static/citext.html
unaccent: http://www.postgresql.org/docs/9.2/static/unaccent.html
I think you can make it work.
The main feature which I'd like the PG development team to look at next is implementing a more comprehensive partitioning solution.
As far as remaining competitive with the commercial database vendors, the one thing that is the primary reason I have seen for enterprises choosing to reluctantly pay for Oracle over using Postgres for free is RAC (Real Application Clusters). Active/active clustering of DB nodes seems to hit a sweet spot for businesses wanting uptime and scalability without some of the downsides of physical replication.
This is being developed as Postgres-XC. They released version 1.0 in June 2012.
"Postgres-XC is an open source project to provide a write-scalable, synchronous multi-master, transparent PostgreSQL cluster solution. "
And it's fast.
Oracle-like partitioning syntax support (including composite) would be great - although possible through inheritance and triggers/rules, it is the only bigger feature missing in PostgreSQL in my opinion. The current form requires a lot of overhead but is definitely worth the effort when working with 1B+ rows.
What I really miss in postgres is row level security like it's implemented in oracle.
To implement RLS in postgres I need to either install veil and go through the pain of configuring it or create a lot of views and work on them (but some scenarios can not be implemented like that).
json_merge('{"a":5,"b":7}', '{"b":6,"c":8}') -> '{"a":5,"b":6,"c":8}'
Some of the things I miss most frequently are:
- Easy table partitioning.
- Support for "phrase search" in fts.
- MERGE/UPSERT support.
- Columnar storage (and related optimizations) for "OLAP like" workloads.
- EXPLAIN ANALYSE that traces functions.
Really?
Heh, hopefully you're consistent and have similar antics for the likes of Apple, Google, et al
* we currently use postgresql, oracle, mysql, teradata, mssql
There's active development on this feature, so now's the time to influence it to better suit your needs. A separate blog post might be a better forum.
Have strong consistency by default with the option to define eventual consistency per query/session. It's so useful with Cassandra to have that flexibility to favour performance over accuracy.
Also OOTB should support EC2, ZeroConf/Bonjour and make it understand racks/data centres.
A well supported, polished multi-master implementation would definitely bring PostgreSQL into the 21st century. It's been far too long not to have this. And look to databases like Cassandra for ease of setup and maintenance.
To make the responses as constructive as possible, please also specify:
* Whether you currently use postgres, and what other systems you use (presumably ones that do have the feature that is missing in postgres)
* Whether you currently need the feature, or whether you anticipate the need in the future, or whether you expect that other people will need it
* Whether the missing feature is preventing you from using postgres, or just a significant pain point (which could include ease-of-use issues)