
PostgreSQL Features You May Not Have Tried but Should - _1
https://pgdash.io/blog/postgres-features.html?h=
======
sntran
One of the less-known behaviour (I'm reluctant to say "features") is that you
can have some sort of virtual field in a table, that will execute a function
when the field is accessed. This is due to the logic that PostgreSQL treats
`row.property` the same as `property(row)`.

For example, we can have the function `full_name(person)` that returns the
concatenation of `person.first_name` and `person.last_name`, and we can do
`SELECT p.full_name FROM person p`. I think it's pretty neat.

~~~
ris
I had no idea about this (and I've been using the `property(row)` pattern for
years), but sure enough it is documented at the bottom of the section
[https://www.postgresql.org/docs/10/static/rowtypes.html#ROWT...](https://www.postgresql.org/docs/10/static/rowtypes.html#ROWTYPES-
USAGE). Given that it doesn't _really_ let you do anything new, I've got to
wonder if it's worth the potential confusion to people who haven't been able
to find this obscure part of the documentation and will lose hours upon hours
trying to find out where this table gets this "foobar" column defined...

~~~
zaarn
Well, usually the first step in investigating the database is to look at the
table definition (either in the migration or in the dump your database can
produce), that usually clears up such confusions.

------
Mister_Snuggles
One feature that's incredibly useful, but not on this list, is the JSON
support[0]. Being able to store schemaless documents alongside your structured
data is very handy in some instances. Being able to query the JSON documents
in a useful manner is also incredible.

[0] [https://www.postgresql.org/docs/10/static/datatype-
json.html](https://www.postgresql.org/docs/10/static/datatype-json.html)

~~~
simcop2387
Not just being able to query them, but to be able to create useful indexes on
the data at the same time still. This means you don't have to break out parts
of the JSON into separate columns and maintain both a schema and a schemaless
setup for them. The indexes aren't going to be as optimized as an index on a
single column, but it makes the whole thing a lot more robust in the end than
relying on your application or triggers to always keep mirrored columns up to
date properly.

------
krylon
Posts like this always make a little sad, because at work, we use Microsoft
SQL Server[0], and for private programming, I usually prefer SQLite. Still, in
some ways, Postgres feels like somebody invented a time machine and brought
back a database engine from the future. _Sigh_ Maybe I will find a good reason
to use Postgres one of these days... ;-)

[0] Do not get me wrong - if you can ignore the price tag, MSSQL is a great
database engine. It has never given me any trouble I have not asked for, and
it has some very nice features of its own (e.g. the Service Broker).

~~~
clhodapp
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
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.

------
fabian2k
Table inheritance has some serious limitations regarding foreign keys and some
other aspects. The following warning is in the official documentation in that
chapter:

> These deficiencies will probably be fixed in some future release, but in the
> meantime considerable care is needed in deciding whether inheritance is
> useful for your application.

~~~
pstuart
I think this is being addressed now:
[https://blog.2ndquadrant.com/enhancements-to-partitioning-
an...](https://blog.2ndquadrant.com/enhancements-to-partitioning-and-indexes-
in-postgresql-11/)

~~~
colanderman
Partitioned tables and child tables are different things; I'd be surprised if
2nd Q is actually is working on support for child tables. (Child tables I
believe were historically used to implement partitioning, but Postgres now has
native partitioning support.) There's no mention of FK support on child tables
in the v11 release notes that I can find:
[https://www.postgresql.org/docs/11/static/release-11.html](https://www.postgresql.org/docs/11/static/release-11.html)

~~~
pstuart
Thanks. My bad.

------
theandrewbailey
Seeing this has made me realize that I have one HUGE bug with native Postgres
full text search.

On my blog, I have a lot of articles about the game series Borderlands. If you
type "Borderlands" into the search box, it will find them, but if you type
"border lands", it won't. Same with "starcraft" and "star craft", etc.

It looks like I will have to implement trigrams on top of full text search to
fix:

[https://www.postgresql.org/docs/current/static/pgtrgm.html](https://www.postgresql.org/docs/current/static/pgtrgm.html)

~~~
davidgould
Take a look at [0]. I think you can solve your problem with a custom text
search dictionary or possibly a thesaurus dictionary. They can substitute or
canonicalize documents or search terms and look pretty easy to set up.

[0] [https://www.postgresql.org/docs/11/static/textsearch-
diction...](https://www.postgresql.org/docs/11/static/textsearch-
dictionaries.html#TEXTSEARCH-THESAURUS)

~~~
theandrewbailey
The downside with dictionaries is that they need to be continually updated
depending on the underlying content. Trigrams seem to be more versatile and
hands-off, and they can work with misspelled search terms.

------
ddebernardy
Table inheritance is a non-starter for the stated use-case. Just don't. If
memory serves me well even the Postgres docs recommended against using it for
that last I read them. The only sane use-case of inheritance I'm aware of is
when you're partitioning a table.

~~~
Deimorz
> Table inheritance is a non-starter for the stated use-case. Just don't.

Can you be more specific about why you shouldn't use it like this? I've used
it for similar cases and it's seemed to work fine. You have to declare some
foreign keys / constraints that you shouldn't really _need_ to, but it's not
really any harder than creating the "child" tables from scratch anyway.

~~~
ddebernardy
I haven't programed in years, but insofar as I can recollect one issue was
foreign keys. You basically cannot have a dependable foreign key on tables
with child tables. Not a useful one anyway, because the parent and child
tables are treated separately. The way foreign keys are (were?) built into
Postgres is using built-in system level triggers for on delete/update events.
Target the wrong table and all hell breaks loose.

------
brasetvik
Another feature that should probably be on that list is Common Table
Expressions:
[https://momjian.us/main/writings/pgsql/cte.pdf](https://momjian.us/main/writings/pgsql/cte.pdf)

(I still frequently run into people who work a lot with SQL that don't use
CTEs, though less than before :)

~~~
cpburns2009
Just be aware that CTEs add optimizer barriers in PostgreSQL. So a query like:

    
    
        SELECT *
        FROM ( ... ) AS results
    

may have a completely different query plan than a query like

    
    
        WITH results AS ( ... )
        SELECT *
        FROM results
    

This may or may not be desirable, and is important to be aware of.

~~~
munk-a
It is a balance always... but code maintainability isn't something to be
dismissed. If utilizing CTEs greatly improves your readability it's probably
worth utilizing CTEs until there is a performance need to switch off (and it's
pretty trivial to de-CTE a query)

------
megous
Some others more or less obscure features I used and liked:

\- data checksuming (can be enabled in initdb)

\- extending the database in C (mostly adding various functions, that would be
hard and slow to implement in SQL, but I've also been able to write functions
that generate datasets on the fly that are loaded from a custom server over a
unix socket)

\- writing ECPG clients
[https://www.postgresql.org/docs/current/static/ecpg.html](https://www.postgresql.org/docs/current/static/ecpg.html)

------
booleanbetrayal
FDW is awesome and surprisingly useful. I really want Postgres 10's
partitioning support to land in an AWS Redshift PG-reboot someday.
Partitioning support is very manual and clunky in that old fork.

~~~
gigatexal
Is there any talks by AWS engineers why they went with a fork and not say a
white box implementation of CitusDB or just use EnterpriseDB or just make it a
vanilla Postgres with some more performant defaults??

~~~
mulmen
Redshift is a massively parallel processing columnar storage database. The
storage engine is completely different. Redshift is a derivative of ParAccel
which was itself forked from Postgres and _heavily_ modified. I assume AWS
purchased it because it does things CitusDB and EnterpriseDB don’t do. The
intended workloads are completely different.

~~~
gigatexal
Ahh. Consider me learned.

------
andreareina
Anybody who's used the array types care to share their experience? It's
something that caught my eye reading the docs, but wasn't sure how it holds up
in actual use.

~~~
the_duke
Very convenient, I often use them. There's really no reason not to if your
data structure is appropriate.

The only drawback for me is that you can't have arrays of foreign keys. (You
could of course just store an array of the key values, but without the
integrity checks).

~~~
petepete
Isn't an associative table the better option here? It would take a lot of the
headaches out of dealing with arrays and constraints (ie, duplicates, limits
on relationships) while keeping joins simple.

------
wwweston
Table inheritance seems like a natural one for devs working with a lot of
class oriented languages. But also, composition over inheritance seems to be a
more frequent watchword. Any experiences using this one?

Also, triggers seem pretty controversial in the discussions I've been privy
too, most devs seem to hate them. Any positive experiences with them?

~~~
sbuttgereit
I think many developers want to treat RDBMSs as simple black-box data
stores... something, admittedly, you can largely get away with in the majority
of applications. I think this leads to a lot of developers avoiding anything
that forces a real understanding of the database since they can get a fair
amount done without it and that's understandable. Triggers and stored
procedures and database functions intrude into this solace and therefore are
frowned upon by many.

Having said all that I think it's better to have a firm understanding and a
feel for RDBMS operations and development techniques and that you can produce
more solid work if you have that understanding. I think if you have that
understanding (and actually have an appropriate problem for a RDBMS solution)
there are roles for triggers/procedures/functions in the database... just
understand where the boundaries are and delineate clear responsibilities to
the various pieces of the stack (that's the hard part and the part in which
many fail). I also think that if you are a developer needing to make use of an
RDBMS and don't have a solid understanding of your RDBMS of choice, you're
going to screw up more than just anything to do with
triggers/functions/procedures/etc. This is why I always approach projects
where ORMs are important with caution. ORMs are just a tool, true, but often
times they are also the rug under which the database can be swept... or to put
it another way, a framework for building technical debt. (Yeah, yeah, not
always, but frequently).

As for PostgreSQL table inheritance... just say no. You cannot overcome the
object/relational impedance with that feature and you end up compromising too
much of why you might want a RDBMS in the first place. I've only seen it used
well twice; one of those cases being table partitioning and that is, from a
user perspective, becoming much less necessary with new features in
PostgreSQL. PostgreSQL is an Object Relational Database Management System, but
I think you have to consider the object part of that as primarily being useful
inside the database itself: it can be useful in more sophisticated uses of the
database itself, especially if you are writing functions/triggers, but isn't a
great tool in my experience to map to OO systems outside of the database. I
have found it much clearer from an information architectural perspective and
easier to maintain data integrity using standard RDBMS modeling techniques
(with the exception of maintaining certain unstructured data as JSON blobs).

~~~
Omnius
I was one of the developers years ago. You find your language ORM library and
away you go happy coding. It was only after running into some performance
issues and other bottle necks that i put my head down and spent a week
learning the DB (granted its not a long time but you can learn A LOT about SQL
and your DB in 40 hours).

Not only did it solve my bottle neck issues it just made it very apparent that
there are a lot of things ORM do that make everything harder. They have their
place but working with the DB/SQL/Triggers/Views/Mat Views/ ... improved my
applications greatly.

Now years later anytime i am working with a new developer i always stress that
they at least learn the basics.

~~~
blattimwind
Use an ORM that doesn't get in the way. There don't seem to be that many;
sqlalchemy is one.

------
magicbuzz
Interesting that there is inverted index support (ie GIN). Elasticsearch seems
to make a big thing of using inverted indices.

~~~
ben509
Inverted indices are the underpinning of all full-text search engines. There
are different ways to do them, but ultimately, you do full-text indexing by
loading individual terms in the content into and index that references the
document, hence "inverted" from the normal sense of indexing the document to
find the content.

~~~
magicbuzz
It seems to me then that with a carefully thought out inverted index and using
the trigrams mentioned above, you could go a long way towards having the
capability of the Lucene-based implementations like Elasticsearch and Solr.

------
o_____________o
Site is down, mirror:

[https://webcache.googleusercontent.com/search?q=cache:kQC5xW...](https://webcache.googleusercontent.com/search?q=cache:kQC5xWrxqF4J:https://pgdash.io/blog/postgres-
features.html?h&num=1&hl=en&gl=us&strip=1&vwsrc=0)

------
luord
I already knew a few of this, but still, damn, PostgreSQL is such an awesome
piece of software.

------
joaodlf
Pub/Sub is a feature I had no idea about! Sounds really useful for most of my
use cases.

------
davidw
A lot of these things are neat. However, they are sharp tools with specific
uses that you should be careful with and understand well before employing
them.

------
matte_black
Also, Lateral Joins for queries you never thought possible.

------
bandrami
Are people really not using inheritance? I think of that as the main reason to
choose postgres over a non-ORDBM

~~~
psychometry
I don't mind nullable columns and would rather my ORM handle inheritance.

