PostgreSQL Features You May Not Have Tried but Should

That’s seriously cool. Does this simply look for the named function that takes the row type as a parameter and returns a scalar?

This is one of those things that seems like it would have limited value at first, but once you start to play with it you realize how powerful it actually is.

The first database software that I was paid to work on was UniVerse. At the time it was owned by, I believe, VMark Software, but it changed hands a few times and is now part of the Rocket U2[0] family. It has an equivalent feature, which was very heavily used, called I-Descriptors.

An I-Descriptor is an entry in a file's data dictionary that contained code to execute to calculate the value. You could generate a full name out of the firstname/lastname fields, perform conditional logic (e.g., return a specific address based on a preferred address field), etc. You could also call subroutines, where you had the full power of the BASIC language available.

One of the interesting things I did was created a field which would perform geolocation based on the postal code portion of the address. It would split out the postal code, check a cache file for the data, call a web service to retrieve the data and cache it (if it wasn't in the cache already, otherwise it would just return the cached value), and return the results. Everything needed was built in to the database, it was just a matter of coding it. The best part was that it became just another thing you could do in the query language - answering the question "list all active clients in this geographic area" became just another query.

[0] https://en.wikipedia.org/wiki/Rocket_U2

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

Cool feature indeed, though keep in mind that for it to work you need to have the "virtual field" in the `search_path` of the session user.

In PostgREST, we take advantage of this behavior to generate queries without the need for extra code to differentiate between a field or "virtual field", we call these "computed columns" though https://postgrest.org/en/v5.0/api.html#computed-columns.

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.

The most useful part of JSON support is making your query return data from multiple joins and sub-queries in one field. Then you can just json decode the result instead of fetching tons of rows. You could already do that with xmlagg but nowadays people like their JSON.

> SELECT a.id, JSONB_AGG(b.id) AS b_id FROM a, b GROUP BY a.id

We have found service broker to be one of the most brittle things. But I agree that the engine is really great. At the startup we use Postgres and as a former sql server DBA I love it. Auto sharding in 11 will be awesome.

Will always have a soft spot in my heart for MSSQL, it's always been a relative pleasure to work with (even though I had to use Windows). I worked at the company that spawned MySpace, and after we were acquired by FOX I led a project to build a white-label social networking platform that could be re-used across FOX properties. We were getting 12-14k QPS back in 2008 on a single 32-bit quad-core AMD Opteron (1st gen) with a DAS (in a time when SANs ruled supreme). That was the first season of American Idol that the website didn't crash during broadcast.

SQL Server 2005 included SQLOS, bypassing the OS for direct memory and disk access that did wonders for performance. Parallel query execution in 2010 further cemented performance gains.

From the future? Most new features have been available in Oracle for years. Still prefer postgresql to Oracle, but Oracle deserves some credits.

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!

Nice to see love for Service Broker. Sometimes I feel like I'm the only person in the world who knows about it.

I think this is being addressed now: https://blog.2ndquadrant.com/enhancements-to-partitioning-an...

Here is the section on the limitations. Having tried this out myself, please be aware that this is not really 'inheritance'. I had to ditch it completely from my design:

https://www.postgresql.org/docs/current/static/ddl-inherit.h...

For what I'm doing I got away with having insert/delete triggers on child tables that mirror their identity/type to a 'types' table. Also enforced through check constraints and FKs

Elasticsearch won’t do that out of the box either. How would you solve this in the general case without causing false positives?

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

Which has now been usurped by native partitioning as of Postgres 10 :)

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

I'm actually using table inheritance at the moment for a project and it works quite well.

I use the table inheritance to manage common fields in the database more easily instead of having to define a separate table and reference it

For example the base model, which includes fields for external objects the row might reference and certain option fields or a ratings table that allows an object to include all the necessary fields for rating a product. This way I can also copy a lot of code since the structs on the other side also can share a lot of code and data.

Yes but the fact that they are an optimization fence is a major drawback.

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.

I think the article intended to highlight features specific to PostgreSQL. CTEs are undeniably great, especially recursive CTEs, but they're also part of the SQL:1999 standard and available in lots of databases.

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??

As far as I know partitioning doesn’t exist at all in Redshift. However, Redshift has sort keys which work even better than partitions for the intended workload. Partitioning would be nice for retention but I’m not sure what additional benefits it brings on top of sorting for a DW workload.

Good luck. If I remember correctly, Redshift forked off of Postgresql 7.4, so it would be a miracle if they could bring it up to modern postgresql standards.

I find it best to think of them as sets. A lot of operators treat them that way (e.g. containment), and you can use GIN indexing to accelerate access to them using those operators.

What do you want to know... SQL support is excellent including creating arrays during GROUP BY, unnesting them in SELECT, converting to strings and passing to/from user defined functions in JS/python/etc.

Arrays scale as you'd expect, i.e. even if it's allowed, I don't recommend 1MM values in an array because some/many of the aforementioned features aren't tested to scale. AFAIK array operations are single threaded and not vectorized or parallelized.

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).

They are solid and easy to use and particularly good for early prototyping. I've found they work best for passive data, if you have attributes tied to a structure that are required by the application but bear little significance to the database then they're solid.

If your data is more complex then they tend to break down a bit, but at that point you should probably be looking at a joining table or other construct that supports foreign keys and such.

Nothing special about it: you have an array in your code and you store it as an array in the database without having to split it into records. I've been using it in a Django project for a model field of type array of strings. There is a django.contrib.postgres.fields.ArrayField for that.

A caveat: I used it only for small arrays (about 10 elements maximum.) I don't know how the ORM/driver/database combination would perform with thousands or more elements.

I tend to avoid them for storage, but I've found them to be really useful in committed queries. I CSN aggregate a bunch of values into an array on a single record and perform all sorts of useful calculations on them

I've used them for storing time series data. I was storing one value per row and ran into performance problems. Worked really well.

Works well, only issue I ran into is that multidimensional arrays must all have the same number of elements. Had missed this originally when reading the documentation https://www.postgresql.org/docs/10/static/arrays.html#ARRAYS...

Constraint triggers are very useful for enforcing cross-table constraints.

Triggers themselves, beside the typical use cases of logging and notifying channels, I find useful for non-disruptive schema upgrades. You can redirect updates, or fill in non-trivial default values, using them. (Rules could probably fill the same role.)

Before triggers are nice for constraints since they should not amplify writes. But even there they can become easily overlooked by application developers.

I like having basic triggers like created_at/updated_at. No issues there... but tons of issues with 'inheritance'. There are a lot of limitations.

I find inheritance vs. composition to be much less an issue in RDBMSes vs. OOP languages. The problem with inheritance in OOP is that you inherit the code, but not the data (at least, it is supposed to be hidden). This results in overly tight coupling between your class and the other class that you just need a piece of and (I find) is almost never what you want (rather preferring composition and interfaces).

Whereas in an RDBMS, there is no "code" attached to a relation; and the data is explicitly exposed. Inheritance starts to look more like the typical pattern of structure with common fields at the top level, and disjoint fields inside a discriminated union. You can mirror this with RDBMS composition (foreign keys), but you lose the ability to enforce even basic structural constraints (tricks like mutually-referential foreign keys notwithstanding).

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).

IMO, triggers should be used sparingly. For something like updating a "last changed" timestamp on a row, they can be useful. But they are places for side-effects to hide, and later be the source of frustration and performance problems. I almost always prefer to keep any business logic in stored procedures not triggers.

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.

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

We would (we don't even use an ORM but find we have some relations that would benefit from inheritance), but lack of FK support kills it. Instead we either break out common fields to a separate table (which makes it difficult to enforce row constraints), or put all fields in a single table and make them NULLable (sometimes with a constraint to ensure the NULLs match expected usage).

Lots of limitations. The main one is if you're using FKs in your database, you're quickly going to hit issues: https://www.postgresql.org/docs/current/static/ddl-inherit.h...