Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Keeping a Postgres Queue Healthy

planetscale.com

Post reply on HN
Print view View original

21–30 of 34 posts

Re: Keeping a Postgres Queue Healthy

#21

Post by cataflutter »

nine_k wrote: post #19
cataflutter wrote: post #8

Decent article, but some remarks: 1) It seems these two statements conflict with each other: > The oldest such transaction sets the cutoff—referred to as the "MVCC horizon." Until that transaction completes, every dead tuple newer than its snapshot is retained. and > For example, imagine three analytics queries, each running for 40 seconds, staggered 20 seconds apart. No individual query would trigger a timeout for r…

For (2): the problem is that the index would still need to keep the dead tuples, until Postgres is positively certain that no transaction holds them, and runs vacuum over them. It may speed up things a bit, but would still overflow the disk storage eventually. It may still prevent other tables from being vacuumed, too!

For disk usage, yes this doesn't address anything.

But for read performance (which is IMO what the section in the article was motivated by), it doesn't actually matter to have a bunch of entries corresponding to dead tuples in your index, provided Postgres doesn't need to actually consider the dead tuples as part of your query.

So if you have a monotonically increasing `job_id` and that's indexed, then so long as you process your jobs in increasing `job_id` order, you can use the index and guarantee you don't have to keep reconsidering the dead tuples corresponding to jobs that already completed (if that makes sense).

[This is because the index is a b-tree, which supports efficient (O(log n) page reads for n entries) seeking on (any prefix of) the columns in the index.]

Re: Keeping a Postgres Queue Healthy

#22

Post by nine_k »

cataflutter wrote: post #21
nine_k wrote: post #19

Earlier quoted context omitted.

For (2): the problem is that the index would still need to keep the dead tuples, until Postgres is positively certain that no transaction holds them, and runs vacuum over them. It may speed up things a bit, but would still overflow the disk storage eventually. It may still prevent other tables from being vacuumed, too!

For disk usage, yes this doesn't address anything. But for read performance (which is IMO what the section in the article was motivated by), it doesn't actually matter to have a bunch of entries corresponding to dead tuples in your index, provided Postgres doesn't need to actually consider the dead tuples as part of your query. So if you have a monotonically increasing `job_id` and that's indexed, then so long as you…

This is fair! This should as you descripbe work with a partial index, and with picking the lowest ID that has status = pending (via that index) which is not locked (via select ... for update skip locked). The query plan should be triple-checked though to actually use the index.

Re: Keeping a Postgres Queue Healthy

#24

Post by oxidant »

dondraper36 wrote: post #16
simeonGriggs wrote: post #3

Yo! Author here, I’ll be around if anyone’s got questions!

If I understood correctly, the queue implementation in the blog post holds a transaction while an operation is in progress. I see the advice to make it as short as possible, but why can’t we update the status column to, say, “processing” and avoid potentially long transactions at all?

This works well for jobs that are long-ish. You need another process to sweep for orphaned jobs and requeue or fail them. Add a timestamp of when it got picked up to keep track

Re: Keeping a Postgres Queue Healthy

#25

Post by sroussey »

sgarland wrote: post #4
richwater wrote: post #2

It would be nice if this ad at least explained a little bit of the technical side of the solution.

It sounds vaguely like InnoDB’s concurrency control solution which uses tokens [0] as a unit of maximum work a query can perform. 0: https://dev.mysql.com/doc/refman/8.4/en/innodb-performance-t...

tickets, not tokens, but yeah

Re: Keeping a Postgres Queue Healthy

#28

Post by jmalicki »

cataflutter wrote: post #21
nine_k wrote: post #19

Earlier quoted context omitted.

For (2): the problem is that the index would still need to keep the dead tuples, until Postgres is positively certain that no transaction holds them, and runs vacuum over them. It may speed up things a bit, but would still overflow the disk storage eventually. It may still prevent other tables from being vacuumed, too!

For disk usage, yes this doesn't address anything. But for read performance (which is IMO what the section in the article was motivated by), it doesn't actually matter to have a bunch of entries corresponding to dead tuples in your index, provided Postgres doesn't need to actually consider the dead tuples as part of your query. So if you have a monotonically increasing `job_id` and that's indexed, then so long as you…

The article is sparse on what pending means, but I would guess that that where condition would be enough?

Re: Keeping a Postgres Queue Healthy

#29

Post by dukepiki »

sgarland wrote: post #4
richwater wrote: post #2

It would be nice if this ad at least explained a little bit of the technical side of the solution.

It sounds vaguely like InnoDB’s concurrency control solution which uses tokens [0] as a unit of maximum work a query can perform. 0: https://dev.mysql.com/doc/refman/8.4/en/innodb-performance-t...

MySQL’s concurrency control is a limit on the total number of threads that can be active at once, across all queries.

Traffic Control limits concurrency and resource use according to configurable metadata like the username, remote address, or the contents of any sqlcommenter tags included in the query. So you can say things like “the batch processing role can’t run more than four queries at a time.” The finer granularity is key.

Re: Keeping a Postgres Queue Healthy

#30

Post by trueno »

tibbar wrote: post #10
mikeocool wrote: post #9

Earlier quoted context omitted.

“Use Postgres for everything” is a great philosophy at low/medium scale to keep things simple, but there comes a scaling point where I want my SQL database doing as little possible. It’s basically always the bottleneck/problem source in a lot of systems.

Yes. For example you'll typically have a "budget" of 1-10k writes/sec. And a single heavy join can essentially take you offline. Even relatively modest enterprises typically need to shift some query patterns to OLAP/nosql/redis/etc. before very long.

can share our work setup we've been tinkering with at a mid size org. iceberg datalake + snowflake for our warehouse, iceberg tables live in s3, that is now shareable to postgres via the pg_lake extension which automagically context switches using duckdb under the hood to do olap queries acrossed the vast iceberg data. we keep the postgres db as an application db so apps can retrieve the broader data they want to surface in the app from the iceberg tables, but still have spicy native postgres tables to do their high volume writes.

very cool shit, it's certainly blurred the whole olap vs oltp thing a smidge but not quite. more or less makes olap and oltp available through the same db connection. writing back to iceberg is possible, we have a couple apps doing it. though one should probably batch/queue writes back as iceberg definitely doesnt have the fast-writes story. its just nice that the data warehouse analytics nerds have access to the apps data and they can do their thing in the environment they work with back on the snowflake side.

this is definitely an "i only get to play with these techs cause the company pays for it" thing. no one wants to front the cost of iceberg datalake sized mountains of data on some s3 storage somewhere, and it doesn't solve for any sort of native-postgres'ing. it just solves for companies that are doing ridic stuff under enormous sla contracts to pay for all manners of cloud services that joe developer the home guy isn't going to be tinkering with anytime soon. but definitely an interesting time to work near data, so much "sql" has been commercialized over the years and it's really great to see postgres being the peoples champ and helping us break away from the dumb attempts to lock us in under sql servers and informix dbs etc. but we still havent reached a one database for everything yet, but postgres is by and large the one carrying the torch though in my head cannon. if any of them will get there someday, it's postgres.

Post reply on HN