PostgreSQL
/pg130
A place to discuss the PostgreSQL RDBMS
initial thoughts on AWS DSQL
1. PG compatible (pg is dominant at this point)
2. Most of the article talked about HA
3. AWS has a proprietary, internal distributed transaction log service that is supposedly the most important piece of AWS and everything in AWS depends on it. Hope that teams makes 10M annual tc each.
4. It depresses me to think that the future of compute and storage is: run it on aws
It's really neat to think about having a network storage layer that can support many database frontends. this seems like the future. Neon et al. are moving in this direction. Also something that Stonebraker has been talking about for a while now.
https://aws.amazon.com/blogs/database/introducing-amazon-aurora-dsql/
1. PG compatible (pg is dominant at this point)
2. Most of the article talked about HA
3. AWS has a proprietary, internal distributed transaction log service that is supposedly the most important piece of AWS and everything in AWS depends on it. Hope that teams makes 10M annual tc each.
4. It depresses me to think that the future of compute and storage is: run it on aws
It's really neat to think about having a network storage layer that can support many database frontends. this seems like the future. Neon et al. are moving in this direction. Also something that Stonebraker has been talking about for a while now.
https://aws.amazon.com/blogs/database/introducing-amazon-aurora-dsql/
https://avi.im/blag/2024/zero-disk-architecture/
This is a really neat idea. Low latency, CAS, and append are killer S3 features.
But kind of dystopian to think that all our data is owned and operated by a single US company.
WDYT?
This is a really neat idea. Low latency, CAS, and append are killer S3 features.
But kind of dystopian to think that all our data is owned and operated by a single US company.
WDYT?
It's quite rich that the "Tablespaces" section follows the "Destroying a Database" section since misconfiguring table spaces is an excellent way to absolutely hose your database.
pg_column_size is nice. I knew a bigint was 8 bytes, but I was curious to see the storage size for comparable numeric data.
What every programmer should know about solid-state drives
https://codecapsule.com/2014/02/12/coding-for-ssds-part-6-a-summary-what-every-programmer-should-know-about-solid-state-drives/
https://codecapsule.com/2014/02/12/coding-for-ssds-part-6-a-summary-what-every-programmer-should-know-about-solid-state-drives/
I'm behind the times—apparently folks have been running Postgres on unikernels for quite some time†.
https://www.prisma.io/blog/announcing-prisma-postgres-early-access
https://nanovms.com/dev/tutorials/running-postgres-as-a-unikernel †
https://www.prisma.io/blog/announcing-prisma-postgres-early-access
https://nanovms.com/dev/tutorials/running-postgres-as-a-unikernel †
Damn.
Screen shot from this talk on PG/ZFS
https://people.freebsd.org/%7Eseanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf
Screen shot from this talk on PG/ZFS
https://people.freebsd.org/%7Eseanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf
Fun PG-related meetup for the SF folks
I love these 5 minutes of Postgres videos. Here's a good one on upcoming IO changes:
https://www.youtube.com/watch?v=QAYzWAlxCYc
https://www.youtube.com/watch?v=QAYzWAlxCYc
Did you know that Postgres was originally written in LISP? (and C)
"We expected that it would be especially easy to write the optimizer and inference engine in LISP, since both are mostly tree processing modules"
https://dsf.berkeley.edu/papers/ERL-M90-34.pdf
"We expected that it would be especially easy to write the optimizer and inference engine in LISP, since both are mostly tree processing modules"
https://dsf.berkeley.edu/papers/ERL-M90-34.pdf
PostgreSQL's C driver is called: libpq
What's PQ?
Q was short for QUEL. QUEL is a dead query language once used by Ingress. PostgreSQL was post-Ingress.
At one time PG supported a query language called PostQUEL.
What's PQ?
Q was short for QUEL. QUEL is a dead query language once used by Ingress. PostgreSQL was post-Ingress.
At one time PG supported a query language called PostQUEL.
https://www.depesz.com/2022/07/05/understanding-pg_stat_activity/
1) Depsez is pg legend
2) This is a great explainer on pg_stat_activity
1) Depsez is pg legend
2) This is a great explainer on pg_stat_activity
just added a new chain to @indexsupply and used PG 17!
eager to see how the new vector IO impacts table scans
eager to see how the new vector IO impacts table scans
Very slick: https://postgres.new
Now make a system that takes your schema and inspects pg_stat_statements on your production system and makes actionable recommendations of migrations to improve performance.
Now make a system that takes your schema and inspects pg_stat_statements on your production system and makes actionable recommendations of migrations to improve performance.
Fantastic talk on PG's share buffers -- and memory in general
https://www.youtube.com/watch?v=u-r8VuzXeBE
https://www.youtube.com/watch?v=u-r8VuzXeBE
One of those ideas that sounds good at first but upon deeper reflection reveals otherwise (project abandoned). Might still be useful for querying information about your infrastructure, but not for creating.
https://iasql.com
https://iasql.com
Supabase CEO on user complaints the self hosted version is limited and sucks compared to the cloud version
https://www.reddit.com/r/selfhosted/s/v02qg93ao9
https://www.reddit.com/r/selfhosted/s/v02qg93ao9
How does Postgres manage to do this? 🧐
SELECT S
WHERE W
ORDER BY O
> takes 0.193s
-----
SELECT S
WHERE W
ORDER BY O
LIMIT 20
> takes 74.129s
SELECT S
WHERE W
ORDER BY O
> takes 0.193s
-----
SELECT S
WHERE W
ORDER BY O
LIMIT 20
> takes 74.129s
I enjoy using PGTune when setting up a fresh pg server. Maybe you will too:
https://pgtune.leopard.in.ua
https://pgtune.leopard.in.ua
An enjoyable read on how PG encodes NUMERIC data:
https://github.com/postgres/postgres/blob/05a5a1775c89f6beb326725282e7eea1373cbec8/src/backend/utils/adt/numeric.c#L253-L303
https://github.com/postgres/postgres/blob/05a5a1775c89f6beb326725282e7eea1373cbec8/src/backend/utils/adt/numeric.c#L253-L303
FSMs as a core data primitive are going to get more popular. Combined with a runtime library for your preferred language, and you have a way to formally verify statements about business logic in your system.
https://raphael.medaer.me/2019/06/12/pgfsm.html
https://raphael.medaer.me/2019/06/12/pgfsm.html
Anyone got a feature / fix that they are eager to see land in 17?
Here are a few I'm looking forward to:
- COPY option ON_ERROR ignore to discard error rows
- to_bin() and to_oct()
Here are a few I'm looking forward to:
- COPY option ON_ERROR ignore to discard error rows
- to_bin() and to_oct()
Impressive tool from Xata providing a solution for zero-downtime, REVERSIBLE (!!!) migrations—the holy grail.
Excited for this to eventually reach v1. Current feature set is very impressive, but it's not yet ready to handle all possible migrations just yet. Something to keep an eye on.
https://xata.io/blog/pgroll-schema-migrations-postgres
Excited for this to eventually reach v1. Current feature set is very impressive, but it's not yet ready to handle all possible migrations just yet. Something to keep an eye on.
https://xata.io/blog/pgroll-schema-migrations-postgres