3 ms·

On a relative scale with other DBMS's, Postgres is one of the most featureful, nicest-to-use databases. In reality, though, it's really terrible... It's just th

On a relative scale with other DBMS's, Postgres is one of the most featureful, nicest-to-use databases. In reality, though, it's really terrible... It's just the least terrible. One problem is that all those fancy features are released before they are done and never completed afterwards. Thus, Postgres is a mess of special cases that prevent things from working well together. Here are just a few examples I can list off the top of my head: * You can't make arrays of domain types (domain types are a way to constrain the allowed values of some underlying more-primitive type) * You can't parse JSON into a composite that has composites as any of its field types * You can't do update..returning or insert...returning as a subquery and have to fall back to CTEs * Composites are not allowed to contain instances of themselves within any of their fields * Table inheritance doesn't work well with uniqueness constraints Additionally, both the text and binary data exchange formats are completely unspecified with the binary format being completely undocumented and the text format being woefully underdocumented. The text format is also just really really messy and the results that you get back from the server are very irregular... It's pretty much impossible to write a Postgres driver that is anything other than a pile of hacks produced by reading the code and guessing. Pretty much none of them properly handle the more completed cases (most just punt on composites, for example). Anyway: Postgres has a lot of wonderful tech in it that would take many, many years to duplicate and I would pick it from the field of actually-available RDBMSes every time but... it's still deeply terrible. All that is to say: Postgres very much feels (to me) like tech from the awful, edge-casey past that is simply so useful and so hard to replicate that we are stuck with it!

ris 8y ago
I think RDBMSs will always feel slightly "special-case"-y when compared, as you seem to be doing, with general purpose programming languages - remember that SQL was never designed to be a turing complete language (insert someone showing it doing CTE tricks that make it so...). It instead exposes a number of primitives that a database is generally good at reasoning about and in return it can "see through" a lot of the queries the user writes and find efficient ways of performing them. If you want a general purpose language as your database engine, you'll likely end up with something more like MongoDB where for any moderately complex query you'll end up hand-writing a single static query plan for everything. This is probably why people seem less fussed about some of the asymmetries you identify in RDBMSs. Different philosophies.
davidgould 8y ago
> Composites are not allowed to contain instances of themselves within any of their fields Do you mean recursive type definitions? Or? Who does allow that? And what would you use it for in an SQL context? Please elaborate, I'm curious what you are getting at here. > Table inheritance doesn't work well with uniqueness constraints This got somewhat better in postgresql 11 (beta1 is available now) in that unique works for partitioned tables. I don't know if that also includes inherited tables, they are similar but not the same. Partitioned tables are the case most people are interested in anyway.

ris 8y ago

I think RDBMSs will always feel slightly "special-case"-y when compared, as you seem to be doing, with general purpose programming languages - remember that SQL was never designed to be a turing complete language (insert someone showing it doing CTE tricks that make it so...). It instead exposes a number of primitives that a database is generally good at reasoning about and in return it can "see through" a lot of the queries the user writes and find efficient ways of performing them. If you want a general purpose language as your database engine, you'll likely end up with something more like MongoDB where for any moderately complex query you'll end up hand-writing a single static query plan for everything. This is probably why people seem less fussed about some of the asymmetries you identify in RDBMSs. Different philosophies.

davidgould 8y ago

> Composites are not allowed to contain instances of themselves within any of their fields Do you mean recursive type definitions? Or? Who does allow that? And what would you use it for in an SQL context? Please elaborate, I'm curious what you are getting at here. > Table inheritance doesn't work well with uniqueness constraints This got somewhat better in postgresql 11 (beta1 is available now) in that unique works for partitioned tables. I don't know if that also includes inherited tables, they are similar but not the same. Partitioned tables are the case most people are interested in anyway.