The PostgreSQL consultancy 2ndQuadrant's blog covers Postgres features, replication, query planning, and performance, written by its core contributors.
Commenters cite it as a good guide to SKIP LOCKED for queues, correlated-column statistics, and CTEs as optimization fences.
> As dead tuples continue to accumulate in the index, Postgres enters a hot loop as it searches the B-tree, comes up with an invisible tuple, and repeats the process again and again, surfacing empty-handed every time. By the end of the experiment illustrated in the charts above, every worker trying to lock a job would cycle through this loop 100,000 times.
1. I am curious if the author tried setting the autovacuum more appropriately for a table that is in continuous use at 50 new rows/second.
This article (http://okigiveup.net/postgresql-vacuuming-an-introduction-for-busy-devs/) suggests:
ALTER TABLE person SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE person SET (autovacuum_vacuum_threshold = 4000);
This should result in entries being removed from the index and table regularly.
2. If I understand correctly, it seems the other part of the problem is that there are lots of rows to be individually tested to see if they can be locked, based on the way the author has implemented their queue. While lock attempts are being made on a set of rows, they aren't being deleted, since they're 'in use'. This non-deletion causes things to get out of control.
As far as I know, 9.5's SKIP LOCKED is meant to help address that (https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/). Though it appears to be doing something kinda similar under the hood.
If the author of the original article (@ brandur.org) is following this discussion I would be grateful…
I'm also using the Postgres JSON type in 9.3, but my experience is different, perhaps because I come from the ZODB world and so am not under any illusions that it is a panacea!
I've ended up with a very generalised schema, all content is in JSON. I've not had to change the Postgres schema for over a year and it's easy to upgrade schemas inline while fetching from the app. We did write our own migration tools, but they're only a couple of pages of code.
I don't understand your complaint about lack of transactional DDL when using JSON. It's still there, though probably not as important as most of your upgrades will only touch data, giving you transactional upgrades (should you desire them) in any database.
DB queries of JSON data are definitely slow in 9.3 as the values need to be parsed every query, but the lack of JSON equality has not been a problem. You simply use the double chevron text extraction operators rather than the the json extraction operators in your query, col->foo->>bar rather than col->foo->bar. From there you can cast to any type and join to other tables.
For us, the big advantage of using a generalized schema has been simple support for recording versions of data. With over 30 different content types doing this with a traditional relational schema would have resulted in maybe 90 tables. Our generic schema has six.
I'm really looking forward to 9.4 as it will make ad-hoc queries across JSONB really fast using GIN indexes. I'd love to see the option of…
Great to see MySQL adding this stuff! There's still a ton of reasons to choose Postgres and more and more silicon valley startups seem to be choosing pg - I don't remember the last time I met a startup choosing MySQL.
DBMSs are giant complex pieces of software with a million features - it's really hard to compare them. But if I had to sum it up, you can dump freaking line noise into Postgres and then hide the nastiness and manage and query it like a well-designed database. If you have a pretty database, good for you, but today's app writers have gotten lazy with NoSQL record stores and their databases resemble vomitoriums - and let's not talk about what people used to do in the 80s and early 90s.
Without further ado, Postgres vomitorium cleanup features:
- user defined functions/aggegrates/windowfuncs in your favorite language incl JavaScript, which means you can write tricky business logic once and run it where the data is, vs pulling out millions of records from the database. Language list: https://www.postgresql.org/docs/10/static/external-pl.html
- foreign data wrappers with hundreds of connectors AND a 5 minute toolkit for authoring new wrappers in python and other scripting languages.
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
http://multicorn.org/
- index goddamned anything. Postgres has the most array of index types of any open source database AND if you need, you can easily write a function (in javascript or python!!!) and create an index…
yes. the linked 2ndquadrant blog post explaining how to implement this using SKIP LOCKED is a good read: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/
i used this a few years ago to implement my own (toy-scale: 2 workers 1 db running on a potato-class thin client computer) idiosyncratic job queue in postgres. i have a number of recurring jobs that need to be run periodically, and the jobs are not independent -- different jobs depend upon shared resources -- so if lots of jobs that depend on resource R just ran, we might need to wait a while before the other jobs that also depend on R are deemed feasible to be scheduled and run. it was reasonably easy to cobble something together of the form:
each worker process does the following:
0. within a transaction
1. select a shortlist of rows for jobs that are due and are currently feasible, subject constraints ;
2. attempt to acquire one of of those rows using "select ... for update skip locked limit 1"
3. do work, update job result and status in db, commit the transaction
the only part of this that is slightly clever is that i don't actually have a hard requirement for mutual exclusion between worker processes to mediate access to shared resources, so that means each worker process can maintain its own state of its 'fair share' of resources that it is consuming, and throttle itself without needing to coordinate this with other workers.
> Just be sure to run your enqueueing process as a singleton, or each worker would be redundantly enqueueing lots of jobs. This can be guarded with a session advisory lock or a redis lock.
If you're using PostgreSQL 9.5+ you can also use the new SKIP LOCKED capability, which is perfect for this sort of usage: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/
Also not true.
9.6 is supported and the next release (scheduled soon) will support 10. Those changes will eventually end up in 12.
https://www.2ndquadrant.com/en/resources/bdr/ https://blog.2ndquadrant.com/news-and-roadmap-for-bdr-multi-master-postgresql/
I'm not sure what you mean by somewhat equivalent. Both are primarily asynchronous multimaster approaches.