What are your favorite Postgres features?
You could use any SQL database to create a few tables, insert some data, and do queries, while ignoring anything vendor-specific. But Postgres has a lot of other features and many extensions. What do you recommend checking out beyond the basics?
I've used a lot of databases, but it's been many years, so I assume things have changed quite a bit. I skimmed a few PostgreSQL release notes and learned that there's now a MERGE statement that looks pretty handy. (It's standard SQL.) And from Neon's list of supported extensions, the plv8 extension caught my eye. It would let me write stored procedures in JavaScript. Does anyone use that? Do you use stored procedures at all?
I wonder what Tildes uses?
(To keep discussion organized, please write about one feature per top-level comment.)
Postgres lets you store a negative (or positive) infinite date value that is less than (or greater than) any other possible date. This means you don't have to use "magic" date values like "Jan 1, 1900" or "Dec 31, 9999".
I think all of the SQL flavors have this now, but I think Postgres was one of the early ones to support JSON storage and querying. I use that pretty heavily when I have a primarily structured dataset but has one column with unstructured/variable data. That was one of my first draws to Postgres years ago, and it remains my favorite SQL flavor today.
Same, this solved my major issue with my company's weird data model without having to wrangle every other developer into giving it up. We got an "extra data"-json field in just about every model, so that external services can put shit they'll need to ideally see again later (nothing mission critical is ever allowed in there, it's more for debugging/housekeeping) in there. But now I can efficiently identify specific stuff in there when analysing.
Along similar lines, hstore columns can be pretty nice. I use them with OpenStreetMap data (alongside the also-awesome PostGIS extension), where any feature might have a set of key/value pairs with arbitrary keys that are not always known at schema definition time.
Ohh, I haven't used this yet. Definitely something to look into!
Nice! I guess it could in theory be done with JSON, but hstore looks better when you don’t need so much flexibility.
PG has built in functionality to do notifications. You can create a trigger on table changes and have your trigger send a notification, which could be the new resulting data model from the update, to anything listening on the specific channel. That means you can have your application listening and get real-time updates for any database changes. This makes keeping an in-sync application side cache pretty easy.
@skybrian if you're looking for something Tildes makes use of, triggers is it.
This notification was brought to you by
update_users_num_unread_notifications.Interesting. You wouldn’t happen to know how to get a dump of the database schema? I think it might be defined via Python decorators.
I believe theSimilarly\dncommand in pqsl gets you all the schemas.\d+ <table>can get you a specific table's schema and additional info. There are so many useful commands in pqsl to inspect stuff.If you're looking to dump a database, data and all, the
pg_dumpcommand is the one to look at. I don't remember if it has options to only output the schema or not and can't check at the moment, but it's a good place to start.Edit:
pg_dump -s, --schema-onlyoutputs only the schema, see documentation.The difficulty for Tildes would be getting a development environment running to create a test database so you can get a dump, and I have a Mac. Not sure if the instructions for doing that have been worked out yet.
Well in that case, let me do it for you. :D
Thanks!
Thanks, that will be handy. It looks like I will need caching.
All my stored procs are either SQL or plpgsql. Usually rule of thumb is if you’re not developing an extension to postgres, don’t use any of the newer stored function/proc languages because the additional functionality is waaaaay too tempting. Much of it should be kept as far away from the database as possible. And if I see any long running or external IO calls in a stored function, I’m ripping it out.
Then again, my experience is using postgres in start up APIs where there’s a lot of super spikey traffic and I’d rather force myself to pick and choose the goodies. The goodies I chose are composite types, JSONB and foreign keys.
I love Postgres so much, I’d date it if I could. CTEs are probably one of the nicest labor saving, single statement goodies I’ve come to rely on and take for granted after being stuck with MySQL 5.6 for most of my career.
PG lets you build extensions in language of your choice (almost) so for some very unique data-types you can build your own. Like, key-pairs, special PK algorithm,
Also, for a quick search implementation use the ts_vector - amazing.
I've been on PG 20+ years. Never let me down.