91–100 of 181 posts
[1] https://wiki.postgresql.org/wiki/Loose_indexscan
[2] https://stackoverflow.com/questions/28813409/are-null-bytes-...
PGQueuer is a lightweight job queue for Python, built entirely on PostgreSQL. It uses SKIP LOCKED for efficient and safe job processing, with a minimalist design that keeps things simple and performant. If you’re already using Postgres and want a Python-native way to manage background jobs without adding extra infrastructure, PGQueuer might be worth a look: GitHub - https://github.com/janbjorge/pgqueuer
What are its advantages compared to a more dedicated job queue system?
PGQueuer is a lightweight job queue for Python, built entirely on PostgreSQL. It uses SKIP LOCKED for efficient and safe job processing, with a minimalist design that keeps things simple and performant. If you’re already using Postgres and want a Python-native way to manage background jobs without adding extra infrastructure, PGQueuer might be worth a look: GitHub - https://github.com/janbjorge/pgqueuer
I always wondered about the claim that SKIP LOCKED is all that efficient. Surely there are lots of cases where this is a really suboptimal pattern. Simple example: if you have a mixture of very short jobs and longer duration jobs, then there might be hundreds or thousands of short jobs executed for each longer job. In such a case the rows in the jobs table for the longer jobs will be skipped over hundreds of times. T…
While a separate "running" table reduces skips, it adds complexity. SKIP LOCKED strikes a good balance for simplicity and performance in many use cases.
One known issue is that vacuum will become an issue if the load is persistent for longer periods leading to bloat.
IMHO the true limitations of RDBMS are not about usage, but scaling: Multi master across simple zones, High availability, Partitioning.
(IMHO it comes from ACID compliance, so I don't know if it's even solveable natively)
This is a lovely list, thank you. But what's really missing is multi master and high availability. I'm glad to see that partitioning via sharding is covered. IMHO the true limitations of RDBMS are not about usage, but scaling: Multi master across simple zones, High availability, Partitioning. (IMHO it comes from ACID compliance, so I don't know if it's even solveable natively)
That was the whole marketing spiel of Cassandra DB in 2012+ and the source of the CAP theorem
Earlier quoted context omitted.
I always wondered about the claim that SKIP LOCKED is all that efficient. Surely there are lots of cases where this is a really suboptimal pattern. Simple example: if you have a mixture of very short jobs and longer duration jobs, then there might be hundreds or thousands of short jobs executed for each longer job. In such a case the rows in the jobs table for the longer jobs will be skipped over hundreds of times. T…
Job rows could have an indexed column state so you just query for the rows with state "not-started". This way you won't need to skip over the long jobs that are in state "processing".
[flagged]
https://m.youtube.com/playlist?list=PLBrWqg4Ny6vVwwrxjgEtJgd...
While we are it - are there any good resources on how to best self host a Postgres database? Any tips and tricks, best practices, docker / no docker etc? I’m looking to self host a database server for my multiple pet projects, but I would love to get backups, optimizations and other stuff done well.
Postgres major version upgrades are the main reason I don’t self host it, though maybe I should rethink my position on that!
I was recently annoyed to find postgres indexes don't support skipping [1] you also can't have the nul character in a string (\u0000) [2]. Its great, but it has some strange WTF gaps in places. [1] https://wiki.postgresql.org/wiki/Loose_indexscan [2] https://stackoverflow.com/questions/28813409/are-null-bytes-...
I am also a bit annoyed by cache-like uses not being first-class. Unlogged tables get you far, temporary tables are nice, but still all this feels like a hurdle, awkward and not what you actually need.