5 ms·

Postgres has much better SQL support and has features which will make you smile if you end writing complex queries for some purposes (like reporting).

pmontra 8y ago
Yesterday a friend developer told me he was trying to code a query on MySQL to get the latest message of every conversation stored in a table like conversation id, timestamp, user id, message. I did that years ago so I told him he'll find a lot of solutions on Stackoverflow, ugly ones with MySQL and much better ones for PostgreSQL which has a nrow() function. A MySQL DBA solved that problem in that project I worked on years ago without queries. He used triggers to store the id of the most recent message of every conversation in another table. That is probably the most efficient solution in any database. My friend told me he wants to code that query, no triggers, not even an INSERT OR UPDATE from his PHP code. I replied to go on, he'll learn a lot of things and I hoped this will make him pick PostgreSQL next time. Developers tend not to like triggers or stored procedures IMHO because it's code outside their reach in a language most of them don't know, but that's another story.
- Avamander 8y ago
  I've had to debug a stored procedure, they're horrible when you have to do that. Just a big headache. I literally rewrote every single one of those procedures in the software's primary language, the code can now be easily debugged, logged, replaced and read.
  - pmontra 8y ago
    I'm not a big fan of PL/SQL. Almost nobody is, so your approach is standard. It's ok if all accesses to the database go through the primary application. Triggers and stored procedures make more sense when a database serves multiple applications possibly in multiple languages. Think of the db as the microservice in front of the data store. Simple stored procedures are no that bad.
- blattimwind 8y ago
  > He used triggers to store the id of the most recent message of every conversation in another table. That is probably the most efficient solution in any database. Depends on how often that last message query is run. If it's infrequent, the trigger is just write amplification.
- tormeh 8y ago
  Stored procedures are plaintext code running on production servers and is, in general, never cleanly redeployed. So by default they discourage source control. This is my primary problem with them. Also that (at least MySQL) has shitty error messages when you're trying to build/debug the things.
  - ht85 8y ago
    Maintaining and migrating stored procedures can be a pain, especially when you're running a single tenant infrastructure, and even more if you don't migrate everyone at the same time. I've had a lot of success by creating a small build system for the database logic, that can be as simple as running a bunch of SQL files in order. The most important aspect is for the build to be "pure", rebuilding the whole thing every time instead of incrementally changing the schema. A practical way to do this is to have all your stored procedures in a separate schema from your data, and starting your build process with `DROP SCHEMA procs CASCADE`.
- twic 8y ago
  Given: create table conversations ( message_id integer primary key, conversation_id integer, timestamp timestamp, user_id integer, message varchar ); Then: select distinct on (conversation_id) * from conversations order by conversation_id, timestamp desc; Should do it. Although i believe "select distinct on" is a PostgreSQL extension rather than standard SQL. Now, what i don't know is how this is planned for a large table, or what indices would let you get a sensible plan. Maybe just an index on (conversation_id, timestamp)?
  - tfehring 8y ago
    Extension or not, that's a really useful shortcut. Coming from SQL Server, the best option I know of requires a CTE or subquery: WITH CTE_messages AS ( SELECT * ,row_nbr = ROW_NUMBER() OVER(PARTITION BY conversation_id ORDER BY timestamp DESC) FROM conversations ) SELECT * FROM CTE_messages WHERE row_nbr = 1 ORDER BY conversation_id This does give a performant plan in SQL Server with an index on (conversation_id, timestamp), for what it's worth. I imagine the same is true for this syntax in PostgreSQL. But SELECT DISTINCT sometimes does strange things to execution plans in general, and I wouldn't be surprised if that extends to SELECT DISTINCT ON.
- masklinn 8y ago
  > Developers tend not to like triggers or stored procedures IMHO because it's code outside their reach in a language most of them don't know, but that's another story. You can write PG stored procedures in close to a dozen languages between the builtins (pgSQL, Tcl, Perl and Python) and third-party (Java, Ruby, PHP, R, Scheme, JS, …). The language itself is not why developers don't like stored procedures, it's because stored procedure are an even worse version of image-based languages (like Smalltalk or Self, except they had much better UI, tooling and interaction model): hard to version, hard to update, hard to test, hard to debug, hard to cache, hard to scale, …, and your wide-spread well-supported ancillary tooling goes away pretty much entirely.

pmontra 8y ago

Yesterday a friend developer told me he was trying to code a query on MySQL to get the latest message of every conversation stored in a table like conversation id, timestamp, user id, message. I did that years ago so I told him he'll find a lot of solutions on Stackoverflow, ugly ones with MySQL and much better ones for PostgreSQL which has a nrow() function. A MySQL DBA solved that problem in that project I worked on years ago without queries. He used triggers to store the id of the most recent message of every conversation in another table. That is probably the most efficient solution in any database. My friend told me he wants to code that query, no triggers, not even an INSERT OR UPDATE from his PHP code. I replied to go on, he'll learn a lot of things and I hoped this will make him pick PostgreSQL next time. Developers tend not to like triggers or stored procedures IMHO because it's code outside their reach in a language most of them don't know, but that's another story.

Avamander 8y ago
I've had to debug a stored procedure, they're horrible when you have to do that. Just a big headache. I literally rewrote every single one of those procedures in the software's primary language, the code can now be easily debugged, logged, replaced and read.
- pmontra 8y ago
  I'm not a big fan of PL/SQL. Almost nobody is, so your approach is standard. It's ok if all accesses to the database go through the primary application. Triggers and stored procedures make more sense when a database serves multiple applications possibly in multiple languages. Think of the db as the microservice in front of the data store. Simple stored procedures are no that bad.
blattimwind 8y ago
> He used triggers to store the id of the most recent message of every conversation in another table. That is probably the most efficient solution in any database. Depends on how often that last message query is run. If it's infrequent, the trigger is just write amplification.
tormeh 8y ago
Stored procedures are plaintext code running on production servers and is, in general, never cleanly redeployed. So by default they discourage source control. This is my primary problem with them. Also that (at least MySQL) has shitty error messages when you're trying to build/debug the things.
- ht85 8y ago
  Maintaining and migrating stored procedures can be a pain, especially when you're running a single tenant infrastructure, and even more if you don't migrate everyone at the same time. I've had a lot of success by creating a small build system for the database logic, that can be as simple as running a bunch of SQL files in order. The most important aspect is for the build to be "pure", rebuilding the whole thing every time instead of incrementally changing the schema. A practical way to do this is to have all your stored procedures in a separate schema from your data, and starting your build process with `DROP SCHEMA procs CASCADE`.
twic 8y ago
Given: create table conversations ( message_id integer primary key, conversation_id integer, timestamp timestamp, user_id integer, message varchar ); Then: select distinct on (conversation_id) * from conversations order by conversation_id, timestamp desc; Should do it. Although i believe "select distinct on" is a PostgreSQL extension rather than standard SQL. Now, what i don't know is how this is planned for a large table, or what indices would let you get a sensible plan. Maybe just an index on (conversation_id, timestamp)?
- tfehring 8y ago
  Extension or not, that's a really useful shortcut. Coming from SQL Server, the best option I know of requires a CTE or subquery: WITH CTE_messages AS ( SELECT * ,row_nbr = ROW_NUMBER() OVER(PARTITION BY conversation_id ORDER BY timestamp DESC) FROM conversations ) SELECT * FROM CTE_messages WHERE row_nbr = 1 ORDER BY conversation_id This does give a performant plan in SQL Server with an index on (conversation_id, timestamp), for what it's worth. I imagine the same is true for this syntax in PostgreSQL. But SELECT DISTINCT sometimes does strange things to execution plans in general, and I wouldn't be surprised if that extends to SELECT DISTINCT ON.
masklinn 8y ago
> Developers tend not to like triggers or stored procedures IMHO because it's code outside their reach in a language most of them don't know, but that's another story. You can write PG stored procedures in close to a dozen languages between the builtins (pgSQL, Tcl, Perl and Python) and third-party (Java, Ruby, PHP, R, Scheme, JS, …). The language itself is not why developers don't like stored procedures, it's because stored procedure are an even worse version of image-based languages (like Smalltalk or Self, except they had much better UI, tooling and interaction model): hard to version, hard to update, hard to test, hard to debug, hard to cache, hard to scale, …, and your wide-spread well-supported ancillary tooling goes away pretty much entirely.