einhverfr

I guess window functions are advanced features, but I found it a little bit basic. Some things you could do to make it more advanced:

    * add questions about range mode
    * have questions which discuss default behavior regarding ordering and partitioning
    * Have questions which rely on things like RANGE 3 PRECEDING or other constructs

I think part of the issue is that SQL is not a programming language. You cannot really express algorithms in the normal sense in SQL. Instead you specify a result and let the db decide the algorithm. So SQL is a language you program around, not a language you program in. I.e. you can extend and consume SQL.

BTW, don't move your HTML into the db :-) Separate presentation and data logic.

(We are teaching a course[1] in Sweden on programming against PostgreSQL. It focuses on much of this as well as trying to look under the hood to deal with what happens when the abstraction fails to deliver what is expected.)

At the end, this is a longish path and one that took me personally a long time to make. But it is something that he more we share, the better off others will be I think.

[1]https://edument.se/education/categories/sql/advanced-postgre...

Fleshing out the Feb 30 comment a little more....

The problem is in the philosophy regarding data vs application in the two products. MySQL treats the data as king and allows any application to turn off safety measures. PostgreSQL treats data constraints as king and expects apps to honor them.

This means that the two aren't really competitors any more than either is a competitor to SQLite. MySQL is a fine choice when you want a dumb store for your data and trust the application to do all necessary checks. PostgreSQL is a lot more pedantic and that makes it better when you want multiple front-ends to the same data.

The only real problem with PostgreSQL replication is the plethora of choices surrounding it.

Especially if your application wants to turn off strict mode and then store Feb 30, 2016 as a date in the db! Cannot do that in PostgreSQL!

I think internally the upcoming Kosovo property tax management system will use it.

FYI with recent commits, it is mostly done.

I actually had some unpushed commits, and expect to try to finish up the selection logic this week or next.

It will be here: https://github.com/einhverfr/pg_titanides

Not sure on timeline yet. Have not had the time to actually write the first generation of the code. Here was my initial announcement (Old, I know but I haven't committed the code yet and need to do that): http://ledgersmbdev.blogspot.se/2016/08/forthcoming-new-scal...

I suspect one physical order index-only scans are supported this should be a lot faster.

SQLite is wonderful for some things but write concurrency is not one of them. This means you really need to segregate reads from writes, and use a different path for that.

I actually think SQLite in general is fairly underrated, but there are important limits.

PostgreSQL is my go-to db, but I love the ability to have the callbacks to SQL functions being in my main code. For lots of data analysis work, the way custom functions and custom aggregates work in SQLite is wonderful.

But again, a chasing hammer is not the same as a machine hammer.

The problem you run into is "we need some more information that is in the logs but we didn't thin to parse before." Here PL/Perl is awesome because you can write a function, index the output, and then query against the function output.

One reason I always store full source data in the db.

Except following blanket advance. Following blanket advice is always antipattern ;-)

It's been a little while but I actually read through the source code of it and Londiste. It's possible I missed something, but I didn't see anything that would automatically reset messages if a connection goes away between receiving the message and marking it as completed.

Not quite.

In my experience you have several critical issues:

1. What happens when a job silently fails?

2. What happens when a job takes a lot longer than expected to succeed?

If you solve the first with a timeout, the second leads to a job rerun. The best (only?) solution I have found is to have some awareness in the job queue of the fact that the job is currently being processed. In my previous work we used advisory locks for that.

BTW one example of a long-running task would be a genome assembly task. You take in raw reads in a tar of fastq files, turn it into a BAM file, and from there find overlapping read sections so you can detect continuous read zones, and then determine the consensus nucleotide sequence in each contiguous read zone.

What happens if someone trips over a power cord after a week of this job running? How do you detect and recover? What if, due to the size of data passed in, the job takes a week longer than expected?

This is why you cannot always reduce a work queue to a message queue.

But you have a fairly major problem with all these solutions and that is when a message leaves a queue. For a message queue you want it to leave the queue when delivered. For a job queue you want it to leave the queue when the work is completed but not to be sent out in the mean time. Now imagine you have processing jobs that take two weeks with 16 cores to complete.

That's one thing that makes this a hard problem. If you have open transactions for two weeks "Deleting this...." then autovacuum is going to be totally ineffective. So failure detection and recovery is an important (and probably the hardest) part of the problem.

PgQ looks to me like it is a solution to a message queue problem not a work queue problem.

Also if your queue takes a month to clear because you bulk inserted a few hundred million jobs..... Listen/Notify isn't going to help you.

I worked on a huge (12TB) bioinformatic project which was using PostgreSQL full text search as a primary component. One would compare with Solr which also was looked at as a possible replacement.

Solr had more functionality (largely by being able to throw more nodes at the problem), but for our purposes, PostgreSQL's fts was certainly good enough. There were a few cases where we had to be careful so the full text index would be used (because data distribution did not always match the planner's assumptions) but on the whole it worked very well.

But all work queue architectures I have worked with have subtle problems which are easy to get wrong. Might we conclude that the use of a work queue is an antipattern?

It is definitely hard to get right, for sure. I am actually planning on taking what I have learned on this and releasing it soon. And a lot of cookie-cutter solutions out there don't scale.

But a lot of things are less about outlines than details and that is true here. If you do get it right, you get a work queue where jobs disappear properly when completed and if they fail remain in the queue to be retried.

The fact is, work queues are hard to get right on any technology. You have a ridiculous number of corner cases and failure conditions you need to account for and the question ends up coming down to what you want the default failure state to be.

It depends. There is a tradeoff for sure. But there is the advantage that you can tie the job completion to the db transactions of the main job.

Like a lot of things, craftmanship depends on detail far more than it does on outlines.

I know of some relatively high end systems which are doing that, which used postgrest for prototyping purposes and then found it performed too well to give up!

Nice writeup though I would add a few things.

Listen/Notify work great for short-term job queues. For longer term ones, you have some serious difficulties on PostgreSQL which require care and attention to detail to solve. In those cases, of course, you can solve them, but they take people who know what they are doing.

Also in terms of storing images in the database, this is something that really depends on what you are doing, what your database load is, and what your memory constraints are. At least when working with Perl on the middleware, decoding and presenting the image takes several times the RAM that loading it off the filesystem does. That may not be the end of the world, but it is something to think about.

Also TOAST overhead in retrieved columns doesn't show up in EXPLAIN ANALYZE because the items never get untoasted. Again by no means a deal breaker, but something to think about.

In general, PostgreSQL can be good enough but having people know know it inside and out is important as you scale. That's probably true with any technology, however.

This was actually an expanded based on the email I sent to the advocacy list last week. That email lead to the invitation to Moscow.

It is worth noting here that most of the problems listed here for active discussion are problems that occur with volume and load.

But for reference:

1. Table partitioning was used to solve autovacuum and statistical correlation problems on the work tables

2. Non-1NF designs need to understand atomicity as being relational and not ontological in nature. I.e. what you are doing with the data determines atomicity far more than the data itself, and

3. Immutable functions and functional indexes rock on PostgreSQL.

Also I cannot stress enough the need for doing real monitoring and simulation of problems.

I think one needs to recognize that git was pioneering. And one of the problems with being pioneering is that some things way not work as expected, and be difficult to fix later. In other words pioneers make the mistakes those who follow get to learn from.

Git got a whole lot of the dvcs model in general right, particularly regarding the basic data structures. I am not a fan of some other things and there are a whole lot of things I am actually quite ambivalent about.

The CEO blamed ADP for the payroll being late. When you combine that with the lack of paystubs, if alarm whistles aren't going off, you are just not paying attention.

I don't know about anyone else, but I didn't read too much bad into the account of Jessica other than a simple power struggle. Those happen all the time anywhere.

The CEO and CTO however...... The allegations there are pretty severe.

Pay people in regular ways through regular channels.

Seriously, though, ADP delivers paystubs. If they were using ADP, they would have gotten paystubs.

Totally second this.

Particularly look at:

1 How they treat their customers

2 How they treat their affiliates

3 How they encourage their affiliates to treat their customers.