zlacker

>>hyzyla+(OP)
I think one of the biggest advantages of using Redis for job queing vs Postgres comes down to library support.

For example Python has Celery and Ruby has Sidekiq. As far as I know there's no libraries in either language that has something as battle hardened with comparable features for background tasks using Postgres as a backend.

There's a big difference between getting something to work in a demo (achievable by skimming PG's docs and rolling your own job queue) vs using something that has tens of thousands of hours of dev time and tons of real world usage.

I'm all for using PG for things like full text search when I can because it drastically reduces operation complexity if you can avoid needing to run Elasticsearch, but Redis on the other hand is a swiss army knife of awesome. It's often used for caching or as a session back-end so you probably have it as part of your stack already. It's also really easy to run, uses almost no resources and is in the same tier as nginx in terms of how crazy efficient it is and how reliable it is. I don't see not using Redis for a job queue as that big of a win.

>>nickjj+Tg
But in Postgres you could write functions in your schema to handle job queueing/dequeueing with the additional benefit of being able to use it in any language that can connect to Postgres and being able to reuse the same SQL/interface across all languages.

>>RedShi+ph
please don’t. source control/versioning/deployment become a nightmare

>>rantwa+kd1
I never understood this point:

- You have a separate schema for your procs

- You define your procs in files

- You write tests for your procs

- You put your files into git

- Then, in a transaction, you drop your old schema and deploy the new one

>>taffer+Zn1
Do you have access to your production database?

How do you write tests? With what data are you testing?

How do you handle backwards compatibility? (For the code? For the data?)

Do you do this upgrade during deployment? Can you do blue/green deployments? What about canary deployments?

Is this transaction where you are dropping the old and creating the new part of the code or part of the database? (the actual code that does this)

How do you profile the performance of the said code?

>>rantwa+PF1
Have an automated process for deployment and a small number of people who have the keys to production deployment. Don't let developers manually change things in the production system or any system at all.

You can use pgtap to write automated unit tests. For profiling, there are explain, autoexplain, and plprofiler.

Blue/Green and Canary deployments are not currently possible with Postgres. On the other hand, Postres has transactional DDL, which means there is no downtime during code deployment and automatic rollback if something goes wrong.

The database is only for data and for stored procedures or functions, which are stored in separate schemas. Your deployment scripts, migration scripts, test scripts and everything else is not stored in the database, but in your source control system, e.g. Git.

For everything else, just use conventional software engineering practices. There is no reason to treat SQL code differently than Ruby or Java code.