PostgreSQL

Found this in my feed, it's pretty neat, and at a surface level should make some of the pain points in my location based game much less difficult.

cross-posted from: https://lemmy.smeargle.fans/post/30432

HN Discussion

A relatively simple but common application of time series done with PG.

Part 1: https://programming.dev/post/603531

Hi there!

I have a pg cluster serving different services, with one of them, (let's call it SL), non-critical, but hammering the database with lots of (mostly) short lived queries.

Since I implemented a connection pooler (pgbouncer), I've noticed a great improvement in throughput, and the SL service is now much more responsive than before. That said, I think this was quite detrimental to fairness overall, because some of the other services which used to respond fairly well now happen to timeout often.

I was wondering if there's any way to prioritize queries execution (ideally by user or database) so that the high-frequency/low criticality service leaves way to anything else that comes up. To my surprise, nothing comes up from my googling of "pgbouncer prioritization" or "pgbouncer fairness". pgcat seems to offer some loadbalancing and sharding, but that seems to be only applicable for multi-server setups. Any idea/suggestion?

Thanks!

Part 2: https://programming.dev/post/792914

or is it only possible through add-ons/forks

Instantly spin-up a GraphQL API server by pointing PostGraphile at your existing PostgreSQL database

You can also follow the project on Mastodon: https://fosstodon.org/@graphile

The challenge: https://www.pgsqlphriday.com/2023/05/pgsql-phriday-009/

Another Phriday in the books, and it's time to see what all happened:

• Hari Kiran offered an introduction to the concepts and processes used in schema evolution with examples showing how to use Flyway, a popular Java-based tool.
• Grant Fritchey discussed what it means to roll a change back, why catastrophic failures are the good kind of failure, and how to work with deployment processes to adapt to failures and roll the database forward to a good state instead of trying to turn back time.
• Michael Christofides wrote about table stakes for database automation and his hopes for better integration of performance testing in automated change management.
• Andy Atkinson ran through the entire prompt item by item! Read it for a detailed look at Rails migrations in situ -- who writes them, who reviews them, what kinds of problems happen, and how to validate successful changes.
• Ryan Booz gave me ERWin flashbacks and proclaimed a decalogue for the aspiring database automator, from the foundational on up. No comment on whether I've ever achieved #7 without painstakingly restoring manual production dumps.
• finally, I covered the ideas powering a few less-usual schema evolution tools.

In a previous article, [the author] introduced what is new in SQL:2023. Now [they] want to show the status of SQL:2023 support in PostgreSQL.

Works with Postgresql, MySql, and SQLite natively.

Extends confirmed support for DuckDB, ClickHouse, BigQuery, and Snowflake via the vdsql plugin.

Link to talk slides: vyruss.org/computing/slides/pgconfde2023_dont_do_this.pdf

Inspired by: "Don't Do This" on the Postgresql wiki