12 ms·

Rapid schema development with PostgreSQL

104 points by pearkes 13y ago

josephlord 13y ago
Hmmm... I'm having evil thoughts about putting a whole web application including templating inside of Postgres and just exposing a few functions via a web interface. Don't worry I won't do it really but does anyone worry that Postgres is doing too much and that focus might be lost on being a reliable and fast relational DB? I haven't seen any signs of problems but I do have this slight concern with all the array/hstore/json features they have been adding recently.
- mclarke 13y ago
  I think it's impressive how quickly the Postgres world was able to adapt to the shifting needs of webapps and the whole nosql thing. The latest JSON features are a natural extension of the key-value & array stuff that has been around for years. There's also a bunch of working focusing on replication enhancements that is underway; adding first-party replication tooling will be a huge reliability improvement for Postgres clusters. I don't think it's that they lost focus, it's that the project is picking up steam.
  - josephlord 13y ago
    Yes you are probably right. I really like Postgres, the attitude (reliability, standards etc.), the documentation, basically everything and don't want it to change too much.
    - einhverfr 13y ago
      It's worth looking at things like hstore and json through the history of the project. The project has always been one which has focused on how to manage complex data in a relatively relational way, but has tended to go where no other database has gone before (table inheritance for example). Now, it is true that when you get into the advanced capabilities of the database you run into hard edges that just don't make much sense at first, in part because they represent real disputes regarding how everything is supposed to work. Composite types in fields and table inheritance are well known for these sorts of problems but once you get used to the ideosyncracies they aren't bad. This is true for JSON too, as there is no real way to map nested composite types to JSON constructs both ways (you can do tuple -> json, but not json -> tuple if the json object is nested). But a lot of these things just take time.
  - threeseed 13y ago
    Interesting. I believe that PostgreSQL hasn't moved quickly enough. After all these years they still don't have a clear and coherent clustering or sharing story.
- IsTom 13y ago
  I guess C++-like philosophy of "pay (with performance) only for what you use" applies here. If you're not using hstore/json/v8 it probably won't affect you. Also they keep doing solid work on the SQL front, just see http://www.postgresql.org/docs/devel/static/release-9-3.html http://www.postgresql.org/docs/devel/static/release-9-3.html.
- drbawb 13y ago
  I was a little hesitant to use hstore in my latest project, but it ended up being really cool. I'm basically storing a trees of metadata that catalogs, in this case, a "season" of television as a root and then each individual episode as a leaf. What I thought was really cool was that I can store the object references (as a self-referential foreign key) outside of the hstore: this means that the join I use when fetching the tree is still indexed and very inexpensive. However the actual metadata itself is stored as an hstore payload -- which can be indexed in addition to being part of your query. The only downside is you lose a bit of typesafety since hstore is `text->text`. It let me throw an application together without committing to a rigid schema, which was nice -- and it actually ends up being pretty easy to work with. I just thought it was really cool how easy it was to separate the unstructured data, and the well-structured relation of that data.
  - joevandyk 13y ago
    Postgresql 9.4 will likely extend hstore to store things other than strings. http://obartunov.livejournal.com/172503.html http://obartunov.livejournal.com/172503.html
- asdasf 13y ago
  That isn't evil at all, and I saw someone was actually working on doing that. For web apps that just need to expose a json api, we already just generate boilerplate code from the database anyways, we literally don't write any code for the webapp. Postgresql continues to be better in real practical ways for us every release. I haven't see any features cause problems or interfere with other features. I am not concerned that it is going to happen.
- jmspring 13y ago
  It isn't quite the same, but a large amount of configuration and content gets commingled in Drupal databases. It can make for interesting upgrade / migration challenges if you aren't careful.
- einhverfr 13y ago
  I think you could turn JSON into XML with a little work, and then run xslt on it in the db ;-) > Don't worry I won't do it really but does anyone worry that Postgres is doing too much and that focus might be lost on being a reliable and fast relational DB? Not really. PostgreSQL has never been a purely relational DB. See table inheritance for example (another feature very easily misused but which makes some very difficult things very possible).
nsitarz 13y ago
I'm pretty sure that "ALTER TABLE ... ADD COLUMN ... NULL;" holds an AccessExclusiveLock on the relation being altered for the duration of the transaction in PG 9.1 and 9.2. Is there some trick to the zero downtime schema changes that are mentioned in this slide deck? I only ask because I recently had to get creative with zero downtime schema migrations for my current project.
- gwy 13y ago
  I believe the AccessExclusive is only if you are setting to NOT NULL, such as "ALTER TABLE my_table ADD COLUMN my_col boolean NOT NULL DEFAULT false" We were just down for 56 hours due to our 3rd party platform vendor applying that type of update. AFAICT the only workaround is to do it in steps: set the DEFAULT, fill existing rows with the default, then apply the NOT NULL constraint (which will still lock it for a full table scan to check the validity of the constraint).
  - joevandyk 13y ago
    ALTER TABLE always creates the AccessExclusive lock. If you add a 'not null default something' to the column, it'll rewrite the whole table, which can take some time. And since the table has the AccessExclusive lock, this is what will block reads/writes. Doesn't matter for small tables. For large tables, you want to add the column without a not null or default, commit that transaction, then populate the data, then add the not null and default constraint.
    - einhverfr 13y ago
      More specifically in the multistage work flow, you are only holding the lock while writing the changes to the system catalogs which is not a significant period of time.
    - fdr 13y ago
      You are right: in the fast case of "NULL" the amount of physical work amounts to diddling catalogs around rather than copying potentially gigabytes of data. So in any case a lock is taken, but I've never seen anyone get too bent out of shape over that momentary mutual exclusion to deform the table's type (exception: in transactional DDL where cheap and expensive steps are inter-mingled).
moron4hire 13y ago
I'm failing to see the utility of hstore or json over adding nullable columns here, especially if the latest version makes adding a nullable column essentially instantaneous. It doesn't seem to simplifying the handling of missing/unavailable data, but does manage to complicate the SQL syntax. I have clients that use a variety of databases for a variety of reasons, not the least of which is "just because that is where our data is." All the value of most projects is in the data, and the value of data grows with age, because you cannot recreate the past. If you lose something or you fail to record something, then you can't ever get it back in a way that will stand up to audit scrutiny. This has the awful effect of making technology-specific details of databases get pushed into the business-decision realm, rather than the technology-decision realm. So complicating the SQL syntax is a significant issue for me. Sticking to as much standard, ANSI SQL as possible makes my programs more portable across RDBMSes. With some of the tools I've written, I can make a full transition from MySQL to MS SQL Server and back again and the application doesn't care. Having that sort of power makes upgrading your database a technology decision, not a business one. Yes, the features that Postgres have are nice, but to me they represent a very great chance of vendor lockin. I don't believe that the Postgres team will ever pull anything to make me hate them, but then I thought the same about Sun at one point, too.
- tieTYT 13y ago
  http://www.postgresql.org/docs/9.2/static/datatype-json.html http://www.postgresql.org/docs/9.2/static/datatype-json.html > Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support functions available
  - moron4hire 13y ago
    That doesn't answer my question. Storing structured data in any single-field, be it checked or unchecked, violates the first normal form, if you're going to be querying directly on sub-fields of that field. If the json document is just getting exported out to the client without any inspection of the document from the SQL side, then it's fine and I see the point of having a JSON type that can validate the data. But to extend the SQL syntax to allow for querying sub-fields of that data is the wrong solution to the problem. SQL Server has had this feature for quite some time as well, in the ability to store and query on XML documents. It's awful. The few times that it has "saved the day" on certain queries just because the data was already stored as XML, it turned out to be a false profit and became a serious issue down the road.
deleted 13y ago
[deleted]
craigkerstiens 13y ago
If you're looking to try Postgres as a full on document store there's more and more tooling around making this feasible. Here's one newer gem that makes it pretty straightforward for Rails: https://github.com/webnuts/post_json https://github.com/webnuts/post_json
CraigJPerry 13y ago
When would the hybrid schema approach be useful? To my mind it doesn't offer any extra ability to add or remove columns over what we have already. E.g. I'd never write select * I'd always name columns in my query so that I can be immune to reordering or adding columns to the underlying table or view.
- einhverfr 13y ago
  Where we are looking at it in LedgerSMB is to allow user defined extra fields for forms. A previous approach was a framework for managing join tables, but json would be much cleaner.

104 points by pearkes 13y ago

Hmmm... I'm having evil thoughts about putting a whole web application including templating inside of Postgres and just exposing a few functions via a web interface. Don't worry I won't do it really but does anyone worry that Postgres is doing too much and that focus might be lost on being a reliable and fast relational DB? I haven't seen any signs of problems but I do have this slight concern with all the array/hstore/json features they have been adding recently.

mclarke 13y ago
I think it's impressive how quickly the Postgres world was able to adapt to the shifting needs of webapps and the whole nosql thing. The latest JSON features are a natural extension of the key-value & array stuff that has been around for years. There's also a bunch of working focusing on replication enhancements that is underway; adding first-party replication tooling will be a huge reliability improvement for Postgres clusters. I don't think it's that they lost focus, it's that the project is picking up steam.
- josephlord 13y ago
  Yes you are probably right. I really like Postgres, the attitude (reliability, standards etc.), the documentation, basically everything and don't want it to change too much.
  - einhverfr 13y ago
    It's worth looking at things like hstore and json through the history of the project. The project has always been one which has focused on how to manage complex data in a relatively relational way, but has tended to go where no other database has gone before (table inheritance for example). Now, it is true that when you get into the advanced capabilities of the database you run into hard edges that just don't make much sense at first, in part because they represent real disputes regarding how everything is supposed to work. Composite types in fields and table inheritance are well known for these sorts of problems but once you get used to the ideosyncracies they aren't bad. This is true for JSON too, as there is no real way to map nested composite types to JSON constructs both ways (you can do tuple -> json, but not json -> tuple if the json object is nested). But a lot of these things just take time.
- threeseed 13y ago
  Interesting. I believe that PostgreSQL hasn't moved quickly enough. After all these years they still don't have a clear and coherent clustering or sharing story.
IsTom 13y ago
I guess C++-like philosophy of "pay (with performance) only for what you use" applies here. If you're not using hstore/json/v8 it probably won't affect you. Also they keep doing solid work on the SQL front, just see http://www.postgresql.org/docs/devel/static/release-9-3.html http://www.postgresql.org/docs/devel/static/release-9-3.html.
drbawb 13y ago
I was a little hesitant to use hstore in my latest project, but it ended up being really cool. I'm basically storing a trees of metadata that catalogs, in this case, a "season" of television as a root and then each individual episode as a leaf. What I thought was really cool was that I can store the object references (as a self-referential foreign key) outside of the hstore: this means that the join I use when fetching the tree is still indexed and very inexpensive. However the actual metadata itself is stored as an hstore payload -- which can be indexed in addition to being part of your query. The only downside is you lose a bit of typesafety since hstore is `text->text`. It let me throw an application together without committing to a rigid schema, which was nice -- and it actually ends up being pretty easy to work with. I just thought it was really cool how easy it was to separate the unstructured data, and the well-structured relation of that data.
- joevandyk 13y ago
  Postgresql 9.4 will likely extend hstore to store things other than strings. http://obartunov.livejournal.com/172503.html http://obartunov.livejournal.com/172503.html
asdasf 13y ago
That isn't evil at all, and I saw someone was actually working on doing that. For web apps that just need to expose a json api, we already just generate boilerplate code from the database anyways, we literally don't write any code for the webapp. Postgresql continues to be better in real practical ways for us every release. I haven't see any features cause problems or interfere with other features. I am not concerned that it is going to happen.
jmspring 13y ago
It isn't quite the same, but a large amount of configuration and content gets commingled in Drupal databases. It can make for interesting upgrade / migration challenges if you aren't careful.
einhverfr 13y ago
I think you could turn JSON into XML with a little work, and then run xslt on it in the db ;-) > Don't worry I won't do it really but does anyone worry that Postgres is doing too much and that focus might be lost on being a reliable and fast relational DB? Not really. PostgreSQL has never been a purely relational DB. See table inheritance for example (another feature very easily misused but which makes some very difficult things very possible).

nsitarz 13y ago

I'm pretty sure that "ALTER TABLE ... ADD COLUMN ... NULL;" holds an AccessExclusiveLock on the relation being altered for the duration of the transaction in PG 9.1 and 9.2. Is there some trick to the zero downtime schema changes that are mentioned in this slide deck? I only ask because I recently had to get creative with zero downtime schema migrations for my current project.

gwy 13y ago
I believe the AccessExclusive is only if you are setting to NOT NULL, such as "ALTER TABLE my_table ADD COLUMN my_col boolean NOT NULL DEFAULT false" We were just down for 56 hours due to our 3rd party platform vendor applying that type of update. AFAICT the only workaround is to do it in steps: set the DEFAULT, fill existing rows with the default, then apply the NOT NULL constraint (which will still lock it for a full table scan to check the validity of the constraint).
- joevandyk 13y ago
  ALTER TABLE always creates the AccessExclusive lock. If you add a 'not null default something' to the column, it'll rewrite the whole table, which can take some time. And since the table has the AccessExclusive lock, this is what will block reads/writes. Doesn't matter for small tables. For large tables, you want to add the column without a not null or default, commit that transaction, then populate the data, then add the not null and default constraint.
  - einhverfr 13y ago
    More specifically in the multistage work flow, you are only holding the lock while writing the changes to the system catalogs which is not a significant period of time.
  - fdr 13y ago
    You are right: in the fast case of "NULL" the amount of physical work amounts to diddling catalogs around rather than copying potentially gigabytes of data. So in any case a lock is taken, but I've never seen anyone get too bent out of shape over that momentary mutual exclusion to deform the table's type (exception: in transactional DDL where cheap and expensive steps are inter-mingled).

moron4hire 13y ago

I'm failing to see the utility of hstore or json over adding nullable columns here, especially if the latest version makes adding a nullable column essentially instantaneous. It doesn't seem to simplifying the handling of missing/unavailable data, but does manage to complicate the SQL syntax. I have clients that use a variety of databases for a variety of reasons, not the least of which is "just because that is where our data is." All the value of most projects is in the data, and the value of data grows with age, because you cannot recreate the past. If you lose something or you fail to record something, then you can't ever get it back in a way that will stand up to audit scrutiny. This has the awful effect of making technology-specific details of databases get pushed into the business-decision realm, rather than the technology-decision realm. So complicating the SQL syntax is a significant issue for me. Sticking to as much standard, ANSI SQL as possible makes my programs more portable across RDBMSes. With some of the tools I've written, I can make a full transition from MySQL to MS SQL Server and back again and the application doesn't care. Having that sort of power makes upgrading your database a technology decision, not a business one. Yes, the features that Postgres have are nice, but to me they represent a very great chance of vendor lockin. I don't believe that the Postgres team will ever pull anything to make me hate them, but then I thought the same about Sun at one point, too.

tieTYT 13y ago
http://www.postgresql.org/docs/9.2/static/datatype-json.html http://www.postgresql.org/docs/9.2/static/datatype-json.html > Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support functions available
- moron4hire 13y ago
  That doesn't answer my question. Storing structured data in any single-field, be it checked or unchecked, violates the first normal form, if you're going to be querying directly on sub-fields of that field. If the json document is just getting exported out to the client without any inspection of the document from the SQL side, then it's fine and I see the point of having a JSON type that can validate the data. But to extend the SQL syntax to allow for querying sub-fields of that data is the wrong solution to the problem. SQL Server has had this feature for quite some time as well, in the ability to store and query on XML documents. It's awful. The few times that it has "saved the day" on certain queries just because the data was already stored as XML, it turned out to be a false profit and became a serious issue down the road.

deleted 13y ago

[deleted]

craigkerstiens 13y ago

If you're looking to try Postgres as a full on document store there's more and more tooling around making this feasible. Here's one newer gem that makes it pretty straightforward for Rails: https://github.com/webnuts/post_json https://github.com/webnuts/post_json

CraigJPerry 13y ago

When would the hybrid schema approach be useful? To my mind it doesn't offer any extra ability to add or remove columns over what we have already. E.g. I'd never write select * I'd always name columns in my query so that I can be immune to reordering or adding columns to the underlying table or view.

einhverfr 13y ago
Where we are looking at it in LedgerSMB is to allow user defined extra fields for forms. A previous approach was a framework for managing join tables, but json would be much cleaner.