7 ms·

PostgreSQL 9.4 – What I was hoping for

112 points by craigkerstiens 13y ago

fleetfox 13y ago
JSON in postgres is just a container type isn't it? Why is everyone expecting it to become document storage?
- sehrope 13y ago
  > JSON in postgres is just a container type isn't it? At the moment JSON is stored in a text field so accessing a JSON field involves parsing the entire text value. If instead the JSON is stored in a parsed binary format then you can more efficiently access individual fields. If you're only accessing a small set of known fields you can work around the issue by using plv8 to create function indexes on the fields that you'll be using. This doesn't work for arbitrary expressions though. If you want to filter a WHERE clause based on a non-indexed field the entire JSON text will need to be parsed. Binary JSON storage makes all of this much faster with no change on the user's side. It's all transparent and just faster! In theory it could also reduce storage space for tuples. Duplicate field names need not be repeated. I don't think it's in scope in the Postgres JSON improvements but it's a possibility. > Why is everyone expecting it to become document storage? Document storage in a relational database is really useful and there are plenty of use cases for it. The standard example I use for JSON (or hstore) usage for Postgres is an audit table. You'd have all the usual audit fields (who/what/when) but you'd also have a "detail" field with event specific details. Using hstore or JSON for this is perfect. Improved support for JSON makes it much easier to provide event specific search.
  - gdulli 13y ago
    > If you're only accessing a small set of known fields you can work around the issue by using plv8 to create function indexes on the fields that you'll be using. You don't need plv8 to create an index on a JSON field, it's supported natively.
    - sehrope 13y ago
      Yes my mistake on that. I'm mixing up 9.2 (where you had to do use plv8) and 9.3 (which has the native operator). The rest of the comment still stands though, a parsed representation makes the operator faster and more efficient.
  - deleted 13y ago
    [deleted]
silvestrov 13y ago
I'd rather have a solid codebase that's easy to maintain and has no quick hacks. Better take the time to do it correctly than having features that sometimes doesn't work. PostgreSQL didn't become PostgreSQL by doing it the MySQL way.
- craigkerstiens 13y ago
  I'm definitely not advocating for rushing features in just for the sake of it, much of the reason for these missing was not due to poor implementations of them which is what makes it unfortunate.
- lucian1900 13y ago
  I would agree entirely, except perhaps about MERGE. I really wanted that. Unifying JSON and hstore can happen later, since its wouldn't necessarily enable new features, just make some things nicer.
  - yeukhon 13y ago
    Yeah. I see this will go on until 9.7, 9.8 before we can call things "really nice and stable" for JSON and HSTORE.
- redstripe 13y ago
  But the crappy codebases already exist outside of postgres anytime people have to roll their own upsert implementation. The fact that it's hard is exactly why it should be in the database.
  - cookiecaper 13y ago
    I don't think there's any contention at this point that it's a useful feature to have in the database. There just seems to be some disagreement on the finer points of the implementation. It'll get worked out, and Postgres will make it through another release with or without upsert.
    - 13y ago
chandrew 13y ago
I used postgres for the first time last week, and was wondering, "WHERE IS UPSERT?" Ended up going with a "UPDATE table SET field='C', field2='Z' WHERE id=3; INSERT INTO table (id, field, field2) SELECT 3, 'C', 'Z' WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);" (as seen on http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql http://stackoverflow.com/questions/1109061/insert-on-duplica... - although, not the chosen answer). Anyone come up with better or their own solutions?
- SDGT 13y ago
  I built the pgscript version of the some upsert example and use it as a stored function. I'm no pgsql pro, so sorry if this is total shit. Works for what I needed. This is controlling a list of access rules to different services for doling out access on the controller level globally, or down to an individual action inside that controller. If I rewrote this on newer PG, I'd store the permissions text as a JSON datatype. CREATE OR REPLACE FUNCTION merge_privileges(key integer, data_controller text, data_permissions text) RETURNS void AS $BODY$ BEGIN LOOP -- first try to update the key UPDATE privileges SET controller = data_controller, permissions = data_permissions WHERE user_id = key AND controller = data_controller; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO privileges(user_id, controller, permissions) VALUES (key, data_controller, data_permissions); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
- burntsushi 13y ago
  Nope, that's exactly what I use when I really need it, generalized to simple table/PK schemes.[1] At first I wanted to try and do it within PL/pgSQL (e.g., from your SO link), but I couldn't figure out how to generalize it like I could with my the UPDATE ... INSERT WHERE. (I haven't really studied PL/pgSQL too hard yet.) [1] - https://github.com/BurntSushi/nfldb/blob/master/nfldb/db.py#L344 https://github.com/BurntSushi/nfldb/blob/master/nfldb/db.py#...
gleenn 13y ago
Upsert is really exciting, as the author mentions, because it is a huge thing keeping people from choosing Postgres instead of MySQL.
- gaadd33 13y ago
  Really? Why is that a primary show stopper when there are numerous solutions to it that live on the database side as a stored procedure?
  - burntsushi 13y ago
    I think gleenn is talking about the abstraction known as "upsert". Namely, there exists some syntax for expressing "either insert if it doesn't exist or update if it does exist; determine existence by primary key." This process can be achieved in Postgres, but I'm not aware of any simple abstraction for it. But I could be wrong. Does there exist a stored procedure that can be generalized to the point of upsert abstractions in other relational databases? (In this thread, I see a stored procedure for a specific table/set of columns.)
  - gleenn 13y ago
    There might be stored procedures etc, but not being an expert means I'd be concerned about performance and correctness. And the point is especially for perf because that's exactly when I want upsert: to save a lot of back and forth when inputting a lot of data. Also, not being standard means things like bulk upload Ruby gems not supporting faster methods of upload because they can't depend on stored procedures in any easy way that I can tell.
  - pornel 13y ago
    Yes, for me this is a showstopper. All MySQL projects I've worked on have REPLACE INTO and/or INSERT … ON DUPLICATE. Postgres' equivalents of UPSERT that I've found either only work correctly with single-row inserts or are inefficient and complicated. It's really hard to sell Postgresql to developers of a MySQL project when a simple 1-line query explodes into a complicated stored procedure for apparently no good reason.
bostik 13y ago
Logical replication. Please let that be true. I didn't know live data migration from one database version to another had a name, but I have been waiting for it for years. (Since 7.3, in fact.) And if I'm interpreting this right, that feature could also allow for a fairly straightforward way to move data from a running instance to a new one. Perhaps not yet in the first release, but eventually that too could work.
- jeltz 13y ago
  The plans for logical replication and the amount of work put into it seems serious so I would expect logical replication to land sooner or later (my guess is in 9.5, but I may be optimistic here). Several patches of the ground work have already been committed with a bunch of other patches being worked on.
- twic 13y ago
  > that feature could also allow for a fairly straightforward way to move data from a running instance to a new one That's already relatively straightforward with PostgreSQL's existing replication. This has worked since 9.1: http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/ http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgr... It involves bouncing the master, but it doesn't involve extended downtime. Or is that not what you meant? However, the 'logical log streaming replication' that is being implemented looks even better. A small but useful thing is that it works at database granularity, rather than having to replicate the whole installation. The huge thing is that because it works with logical rather than physical representations of changes, it allows replication into a database which is also being updated locally - slaves can be read/write rather than read-only. That itself is not immediately exciting, but the logical consequence of this is that two databases can both accept changes and replicate to each other - 'bi-directional replication', or what in MySQL is called 'multi-master replication'. The holy grail of database clustering!
  - bostik 13y ago
    > The holy grail of database clustering! Thank you for pointing this out, I would have missed it.In fact, thank you everyone who has answered with helpful technical details. I have a couple of use-cases in mind where logical streaming replication could solve a number of problems. A personal project would benefit from a database-level setup due to easier data migration, a professional one would benefit from bi-directionality and logically straightforward clustering. (I also have some future designs in mind where replicating only subsets of a database would be VERY useful.) Owing thanks to the replies, I now have some extra reading to do. :)
dlau1 13y ago
Two of those features: Logical Replication Upsert -Would be HUGE for postgres
justintocci 13y ago
It doesn't matter what feature's you add to postgres, it will never be less appropriate for a project than another database that has some magic feature. The reason people choose another database is because they favor an architecture where all the things that should be done in the database get done in the middleware. Features are irrelevant since the goal is do everything in the middleware anyway. Once a person converts to the idea that its faster, safer and more reliable to use the database for what it was designed to do then they have left the jungle and found the road. And all roads lead to postgres. I really don't care one iota about any of these features. They are all obscure and I avoid obscure features in deference to the guy that has to read my code next (mostly me).
atesti 13y ago
Isn't there an in place update of an existing database without dump and restore now for quite some versions (http://www.postgresql.org/docs/9.3/static/pgupgrade.html http://www.postgresql.org/docs/9.3/static/pgupgrade.html)? A tool which keeps the relation files, but builds new system catalogs around them? It had the impression that this tool would be very fast, but a bit risky. The linked article makes it sound like there is a need for local replication in order to do an update without (much) downtime. Is this just a (less risky!) alternative to the available in place update?

112 points by craigkerstiens 13y ago

fleetfox 13y ago

JSON in postgres is just a container type isn't it? Why is everyone expecting it to become document storage?

sehrope 13y ago
> JSON in postgres is just a container type isn't it? At the moment JSON is stored in a text field so accessing a JSON field involves parsing the entire text value. If instead the JSON is stored in a parsed binary format then you can more efficiently access individual fields. If you're only accessing a small set of known fields you can work around the issue by using plv8 to create function indexes on the fields that you'll be using. This doesn't work for arbitrary expressions though. If you want to filter a WHERE clause based on a non-indexed field the entire JSON text will need to be parsed. Binary JSON storage makes all of this much faster with no change on the user's side. It's all transparent and just faster! In theory it could also reduce storage space for tuples. Duplicate field names need not be repeated. I don't think it's in scope in the Postgres JSON improvements but it's a possibility. > Why is everyone expecting it to become document storage? Document storage in a relational database is really useful and there are plenty of use cases for it. The standard example I use for JSON (or hstore) usage for Postgres is an audit table. You'd have all the usual audit fields (who/what/when) but you'd also have a "detail" field with event specific details. Using hstore or JSON for this is perfect. Improved support for JSON makes it much easier to provide event specific search.
- gdulli 13y ago
  > If you're only accessing a small set of known fields you can work around the issue by using plv8 to create function indexes on the fields that you'll be using. You don't need plv8 to create an index on a JSON field, it's supported natively.
  - sehrope 13y ago
    Yes my mistake on that. I'm mixing up 9.2 (where you had to do use plv8) and 9.3 (which has the native operator). The rest of the comment still stands though, a parsed representation makes the operator faster and more efficient.
- deleted 13y ago
  [deleted]

silvestrov 13y ago

I'd rather have a solid codebase that's easy to maintain and has no quick hacks. Better take the time to do it correctly than having features that sometimes doesn't work. PostgreSQL didn't become PostgreSQL by doing it the MySQL way.

craigkerstiens 13y ago
I'm definitely not advocating for rushing features in just for the sake of it, much of the reason for these missing was not due to poor implementations of them which is what makes it unfortunate.
lucian1900 13y ago
I would agree entirely, except perhaps about MERGE. I really wanted that. Unifying JSON and hstore can happen later, since its wouldn't necessarily enable new features, just make some things nicer.
- yeukhon 13y ago
  Yeah. I see this will go on until 9.7, 9.8 before we can call things "really nice and stable" for JSON and HSTORE.
redstripe 13y ago
But the crappy codebases already exist outside of postgres anytime people have to roll their own upsert implementation. The fact that it's hard is exactly why it should be in the database.
- cookiecaper 13y ago
  I don't think there's any contention at this point that it's a useful feature to have in the database. There just seems to be some disagreement on the finer points of the implementation. It'll get worked out, and Postgres will make it through another release with or without upsert.
  - 13y ago

chandrew 13y ago

I used postgres for the first time last week, and was wondering, "WHERE IS UPSERT?" Ended up going with a "UPDATE table SET field='C', field2='Z' WHERE id=3; INSERT INTO table (id, field, field2) SELECT 3, 'C', 'Z' WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);" (as seen on http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql http://stackoverflow.com/questions/1109061/insert-on-duplica... - although, not the chosen answer). Anyone come up with better or their own solutions?

SDGT 13y ago
I built the pgscript version of the some upsert example and use it as a stored function. I'm no pgsql pro, so sorry if this is total shit. Works for what I needed. This is controlling a list of access rules to different services for doling out access on the controller level globally, or down to an individual action inside that controller. If I rewrote this on newer PG, I'd store the permissions text as a JSON datatype. CREATE OR REPLACE FUNCTION merge_privileges(key integer, data_controller text, data_permissions text) RETURNS void AS $BODY$ BEGIN LOOP -- first try to update the key UPDATE privileges SET controller = data_controller, permissions = data_permissions WHERE user_id = key AND controller = data_controller; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO privileges(user_id, controller, permissions) VALUES (key, data_controller, data_permissions); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
burntsushi 13y ago
Nope, that's exactly what I use when I really need it, generalized to simple table/PK schemes.[1] At first I wanted to try and do it within PL/pgSQL (e.g., from your SO link), but I couldn't figure out how to generalize it like I could with my the UPDATE ... INSERT WHERE. (I haven't really studied PL/pgSQL too hard yet.) [1] - https://github.com/BurntSushi/nfldb/blob/master/nfldb/db.py#L344 https://github.com/BurntSushi/nfldb/blob/master/nfldb/db.py#...

gleenn 13y ago

Upsert is really exciting, as the author mentions, because it is a huge thing keeping people from choosing Postgres instead of MySQL.

gaadd33 13y ago
Really? Why is that a primary show stopper when there are numerous solutions to it that live on the database side as a stored procedure?
- burntsushi 13y ago
  I think gleenn is talking about the abstraction known as "upsert". Namely, there exists some syntax for expressing "either insert if it doesn't exist or update if it does exist; determine existence by primary key." This process can be achieved in Postgres, but I'm not aware of any simple abstraction for it. But I could be wrong. Does there exist a stored procedure that can be generalized to the point of upsert abstractions in other relational databases? (In this thread, I see a stored procedure for a specific table/set of columns.)
- gleenn 13y ago
  There might be stored procedures etc, but not being an expert means I'd be concerned about performance and correctness. And the point is especially for perf because that's exactly when I want upsert: to save a lot of back and forth when inputting a lot of data. Also, not being standard means things like bulk upload Ruby gems not supporting faster methods of upload because they can't depend on stored procedures in any easy way that I can tell.
- pornel 13y ago
  Yes, for me this is a showstopper. All MySQL projects I've worked on have REPLACE INTO and/or INSERT … ON DUPLICATE. Postgres' equivalents of UPSERT that I've found either only work correctly with single-row inserts or are inefficient and complicated. It's really hard to sell Postgresql to developers of a MySQL project when a simple 1-line query explodes into a complicated stored procedure for apparently no good reason.

bostik 13y ago

Logical replication. Please let that be true. I didn't know live data migration from one database version to another had a name, but I have been waiting for it for years. (Since 7.3, in fact.) And if I'm interpreting this right, that feature could also allow for a fairly straightforward way to move data from a running instance to a new one. Perhaps not yet in the first release, but eventually that too could work.

jeltz 13y ago
The plans for logical replication and the amount of work put into it seems serious so I would expect logical replication to land sooner or later (my guess is in 9.5, but I may be optimistic here). Several patches of the ground work have already been committed with a bunch of other patches being worked on.
twic 13y ago
> that feature could also allow for a fairly straightforward way to move data from a running instance to a new one That's already relatively straightforward with PostgreSQL's existing replication. This has worked since 9.1: http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/ http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgr... It involves bouncing the master, but it doesn't involve extended downtime. Or is that not what you meant? However, the 'logical log streaming replication' that is being implemented looks even better. A small but useful thing is that it works at database granularity, rather than having to replicate the whole installation. The huge thing is that because it works with logical rather than physical representations of changes, it allows replication into a database which is also being updated locally - slaves can be read/write rather than read-only. That itself is not immediately exciting, but the logical consequence of this is that two databases can both accept changes and replicate to each other - 'bi-directional replication', or what in MySQL is called 'multi-master replication'. The holy grail of database clustering!
- bostik 13y ago
  > The holy grail of database clustering! Thank you for pointing this out, I would have missed it.In fact, thank you everyone who has answered with helpful technical details. I have a couple of use-cases in mind where logical streaming replication could solve a number of problems. A personal project would benefit from a database-level setup due to easier data migration, a professional one would benefit from bi-directionality and logically straightforward clustering. (I also have some future designs in mind where replicating only subsets of a database would be VERY useful.) Owing thanks to the replies, I now have some extra reading to do. :)

dlau1 13y ago

Two of those features: Logical Replication Upsert -Would be HUGE for postgres

justintocci 13y ago

It doesn't matter what feature's you add to postgres, it will never be less appropriate for a project than another database that has some magic feature. The reason people choose another database is because they favor an architecture where all the things that should be done in the database get done in the middleware. Features are irrelevant since the goal is do everything in the middleware anyway. Once a person converts to the idea that its faster, safer and more reliable to use the database for what it was designed to do then they have left the jungle and found the road. And all roads lead to postgres. I really don't care one iota about any of these features. They are all obscure and I avoid obscure features in deference to the guy that has to read my code next (mostly me).

atesti 13y ago

Isn't there an in place update of an existing database without dump and restore now for quite some versions (http://www.postgresql.org/docs/9.3/static/pgupgrade.html http://www.postgresql.org/docs/9.3/static/pgupgrade.html)? A tool which keeps the relation files, but builds new system catalogs around them? It had the impression that this tool would be very fast, but a bit risky. The linked article makes it sound like there is a need for local replication in order to do an update without (much) downtime. Is this just a (less risky!) alternative to the available in place update?