4 ms·

I'm a huge PostgreSQL fanboy but I think it's worth mentioning that it's usually not a good idea to use too many esoteric database features when building an app

by gfodor 14y ago

I'm a huge PostgreSQL fanboy but I think it's worth mentioning that it's usually not a good idea to use too many esoteric database features when building an app, since it couples your system with a particular database. That said, even if you don't use PostgreSQL's whiz bang features, its stability, performance, and outright sanity with regards to handling data make it the right database to reach for in many cases. And, for smaller projects where you are willing to couple yourself with it, its additional data types, query features, and so on are awesome.

pvh 14y ago
I absolutely disagree. Drop-in portability between databases is an operational myth for any production application anyway. Whether you're using Postgres, Riak, Mongo or Oracle, you're going to have to do a lot of work to change your database infrastructure. Further, every database, noSQL or otherwise, offers a different set of features and functionality. Why the heck wouldn't you take advantage of k-nearest-neighbors indices? Why would you want to roll your own full text search functionality? The alternative is to introduce whole other services to your infrastructure! In closing: if you've got 'em, smoke 'em.
- monstrado 14y ago
  Depending on your application and how you're taking advantage of your database, you could drop-in replace a database. For example, people using some sort of abstraction on top of their database (an ORM) often switch between databases. With that being said, if you're actually designing a complex database back-end for an application you will likely want to spend time becoming very familiar with the database of choice, learn the advantages and then exploit them. This could prove to be pretty difficult to just replace on a whim later on. edit: Switching between an RDBMS and a "NoSQL" database would be much different though. Switching between one SQL server to another is one thing, but switching from a SQL server to a "NoSQL" server is going to be depend on what kind of model you're changing to. For example, MongoDB & Riak are both "NoSQL" databases but they are both quite different fundamentally.
  - deafbybeheading 14y ago
    >Depending on your application and how you're taking advantage of your database, you could drop-in replace a database. If you're doing something simple, sure, maybe. But beyond trivial things like DDL syntax and query syntax, there's query optimization (even when only going in via ORM, because schema design can affect this), tuning, backups, HA, monitoring, and a dozen other things. You're already making a huge non-portable investment in using a complex tool like a database. In comparison to this, introducing dependence on its non-standard features is pretty small change, so you might as well stop worrying (considering how seldom people actually migrate), raise a glass to YAGNI, and learn to love your database. Maybe in another five years your or some successor will end up cursing the day you made that decision, but if you could really use non-standard feature X and it's sitting right there in front of you, it's silly to shun it "just in case."
- gfodor 14y ago
  It's not a matter of drop-in portability, it's a matter of reducing the complexity of migration as well as developer confusion. Often the case for using custom data types, for example, is quite weak, when considering the tradeoffs. They move complicated logic into the database, are unfamiliar to most developers, and end up needing to be reverse engineered if you want to move your data into different storage. I think they should be an option of last resort, or used in situations where the cost/benefit ratio is so skewed in their favor you'd be crazy to not take advantage. Generally speaking these are the rarest of cases.
  - jeffdavis 14y ago
    "Often the case for using custom data types, for example, is quite weak..." Lots of people come to the postgres community because of PostGIS, which is (among other things) a custom type distributed separately from postgres. Using custom types is not bad, the mistake is thinking that making a new type is easy. For a non-trivial custom data type, you need to tie it into the indexing system (GiST+KNN, GIN, SP-GiST, BTree, Hash) as well as the optimizer (have some good stats functions) -- not to mention the basics like a good representation and a well-thought out set of functions and operators. Custom data types are really one aspect of an extension, albeit a crucial one. So don't think about it as "hey, I'll make a new phone number type because it sounds cool", think about it like "we need better support for genomic data in postgres, let's sit down and plan a serious extension". If you want a special type for a phone number, use a domain over text with a check constraint. And that's SQL standard, too.
    - einhverfr 14y ago
      The reason to use a phone number type if you want to use one is that you can, in theory, create functions against it. A good example of what might be possible with such a type can be gained by looking at the standard networking type. Now, it's rare that you are likely to get that into phone numbers, but there might be cases where you could decompose the data and do relevant searches on components might come in really handy. The way I would look at doing that if I didn't want to go into C would be a complex type and a bunch of casts, functions, and operators. BTW, I use custom types a lot for defining output tuples for stored procedures, It is relatively hard to get away from that.
- mb_72 14y ago
  And I, also, disagree. I'm using the Developer Express ORM, XPO, and it provides 'drop-in portability' between a range of databases. I've successfully run my small-mid scale app on three different backends - MS SQL, MySQl and Postgres - simply by changing the connection string. Yes, it's .NET / Windows only, but drop-in portability is definitely not a myth.
Roboprog 14y ago
At least with PostgreSQL, most of the features are really shortcuts, rather than kludges masquerading as features. E.g. - extending a table as if it were a superclass, rather than creating a supplemental "joiner" table and creating a writeable view on top of the "super" table + the "sub" table.
- einhverfr 14y ago
  I wouldn't consider them shortcuts. I use table inheritance as an actual sort of inheritance, to enforce a consistent interface over a set of relations. Yes, that could be a shortcut but it also allows for foreign keys to be against different relations on each of the partitions. Yes, that could be done with partitioning in other ways, so maybe the short cut analogy works. But others? I don't know. The ability to write a stored procedure as a quick Perl regular expression and then call it from a SQL query doesn't strike me as a short-cut. Nor does the extensible type system, or the ability to caste complex types as simple types with defined functions. A lot of these are features which make PostgreSQL a development platform in a box, rather than a data store.
  - dotborg2 14y ago
    table inheritance is cool, but it's also incomplete, docs are pretty clear on that
    - einhverfr 14y ago
      Yeah, I want inherited unique constraints for things which are guaranteed unique in an inheritance tree. However, there are ways around this. You can, for example, use a composite key with the something related to the specific table in it. For example our note table (an abstract class if you will) has a note_class field which is set to different default values and with different check constraints on the child tables. Also foreign keys are a bit of an annoyance in an inherited table environment.
einhverfr 14y ago
Depends on what you are doing. the key thing is to decide how tightly coupled the system should be. With LedgerSMB we went with a tight-coupling approach. This gives us the power of the esoteric features and the ability to to write stored procedures in whatever languages we want later. HStore and Javascript, or JSON and Javascript might be used in the future for some things if we want to. There are many applications which may not need this. In those cases, you are better off focusing on portability (only ANSI SQL features).

by gfodor 14y ago

pvh 14y ago

I absolutely disagree. Drop-in portability between databases is an operational myth for any production application anyway. Whether you're using Postgres, Riak, Mongo or Oracle, you're going to have to do a lot of work to change your database infrastructure. Further, every database, noSQL or otherwise, offers a different set of features and functionality. Why the heck wouldn't you take advantage of k-nearest-neighbors indices? Why would you want to roll your own full text search functionality? The alternative is to introduce whole other services to your infrastructure! In closing: if you've got 'em, smoke 'em.

monstrado 14y ago
Depending on your application and how you're taking advantage of your database, you could drop-in replace a database. For example, people using some sort of abstraction on top of their database (an ORM) often switch between databases. With that being said, if you're actually designing a complex database back-end for an application you will likely want to spend time becoming very familiar with the database of choice, learn the advantages and then exploit them. This could prove to be pretty difficult to just replace on a whim later on. edit: Switching between an RDBMS and a "NoSQL" database would be much different though. Switching between one SQL server to another is one thing, but switching from a SQL server to a "NoSQL" server is going to be depend on what kind of model you're changing to. For example, MongoDB & Riak are both "NoSQL" databases but they are both quite different fundamentally.
- deafbybeheading 14y ago
  >Depending on your application and how you're taking advantage of your database, you could drop-in replace a database. If you're doing something simple, sure, maybe. But beyond trivial things like DDL syntax and query syntax, there's query optimization (even when only going in via ORM, because schema design can affect this), tuning, backups, HA, monitoring, and a dozen other things. You're already making a huge non-portable investment in using a complex tool like a database. In comparison to this, introducing dependence on its non-standard features is pretty small change, so you might as well stop worrying (considering how seldom people actually migrate), raise a glass to YAGNI, and learn to love your database. Maybe in another five years your or some successor will end up cursing the day you made that decision, but if you could really use non-standard feature X and it's sitting right there in front of you, it's silly to shun it "just in case."
gfodor 14y ago
It's not a matter of drop-in portability, it's a matter of reducing the complexity of migration as well as developer confusion. Often the case for using custom data types, for example, is quite weak, when considering the tradeoffs. They move complicated logic into the database, are unfamiliar to most developers, and end up needing to be reverse engineered if you want to move your data into different storage. I think they should be an option of last resort, or used in situations where the cost/benefit ratio is so skewed in their favor you'd be crazy to not take advantage. Generally speaking these are the rarest of cases.
- jeffdavis 14y ago
  "Often the case for using custom data types, for example, is quite weak..." Lots of people come to the postgres community because of PostGIS, which is (among other things) a custom type distributed separately from postgres. Using custom types is not bad, the mistake is thinking that making a new type is easy. For a non-trivial custom data type, you need to tie it into the indexing system (GiST+KNN, GIN, SP-GiST, BTree, Hash) as well as the optimizer (have some good stats functions) -- not to mention the basics like a good representation and a well-thought out set of functions and operators. Custom data types are really one aspect of an extension, albeit a crucial one. So don't think about it as "hey, I'll make a new phone number type because it sounds cool", think about it like "we need better support for genomic data in postgres, let's sit down and plan a serious extension". If you want a special type for a phone number, use a domain over text with a check constraint. And that's SQL standard, too.
  - einhverfr 14y ago
    The reason to use a phone number type if you want to use one is that you can, in theory, create functions against it. A good example of what might be possible with such a type can be gained by looking at the standard networking type. Now, it's rare that you are likely to get that into phone numbers, but there might be cases where you could decompose the data and do relevant searches on components might come in really handy. The way I would look at doing that if I didn't want to go into C would be a complex type and a bunch of casts, functions, and operators. BTW, I use custom types a lot for defining output tuples for stored procedures, It is relatively hard to get away from that.
mb_72 14y ago
And I, also, disagree. I'm using the Developer Express ORM, XPO, and it provides 'drop-in portability' between a range of databases. I've successfully run my small-mid scale app on three different backends - MS SQL, MySQl and Postgres - simply by changing the connection string. Yes, it's .NET / Windows only, but drop-in portability is definitely not a myth.

Roboprog 14y ago

At least with PostgreSQL, most of the features are really shortcuts, rather than kludges masquerading as features. E.g. - extending a table as if it were a superclass, rather than creating a supplemental "joiner" table and creating a writeable view on top of the "super" table + the "sub" table.

einhverfr 14y ago
I wouldn't consider them shortcuts. I use table inheritance as an actual sort of inheritance, to enforce a consistent interface over a set of relations. Yes, that could be a shortcut but it also allows for foreign keys to be against different relations on each of the partitions. Yes, that could be done with partitioning in other ways, so maybe the short cut analogy works. But others? I don't know. The ability to write a stored procedure as a quick Perl regular expression and then call it from a SQL query doesn't strike me as a short-cut. Nor does the extensible type system, or the ability to caste complex types as simple types with defined functions. A lot of these are features which make PostgreSQL a development platform in a box, rather than a data store.
- dotborg2 14y ago
  table inheritance is cool, but it's also incomplete, docs are pretty clear on that
  - einhverfr 14y ago
    Yeah, I want inherited unique constraints for things which are guaranteed unique in an inheritance tree. However, there are ways around this. You can, for example, use a composite key with the something related to the specific table in it. For example our note table (an abstract class if you will) has a note_class field which is set to different default values and with different check constraints on the child tables. Also foreign keys are a bit of an annoyance in an inherited table environment.

einhverfr 14y ago

Depends on what you are doing. the key thing is to decide how tightly coupled the system should be. With LedgerSMB we went with a tight-coupling approach. This gives us the power of the esoteric features and the ability to to write stored procedures in whatever languages we want later. HStore and Javascript, or JSON and Javascript might be used in the future for some things if we want to. There are many applications which may not need this. In those cases, you are better off focusing on portability (only ANSI SQL features).