Making Postgres scale

I don’t think Postgres will be able to benefit from direct io? I might be wrong though!

I use Postgres with 32K BLKSZ.

I am actually using default 128K zfs recordsize, in a mixed workload, I found overall performance nicer than matching at 32K, and compression is way better.

> Thanks for sharing - and cool website!

Thank you!

When it "scales", it usually means "scales up". A scalable solution is such that can withstand a large and increasing load, past the usual limitations of obvious solutions.

Being elastic is nice, but not always needed. In most cases of database usage, downsizing never happens, or expected to happen: logically, data are only added, and any packaging and archiving only exists to keep the size manageable.

a database scaling dramatically up and down /under load/ and expecting it to perform the same as steady state seems a bit weird, vs a single, beefy database with a beefy ingest job and a bunch of read only clients searching it?

like you're more likely to encounter two phases (building the DB in heavy growth mode, and using the DB in light growth heavy read mode).

A business that doesn't quite yet know what size the DB needs to be has a frightening RDS bill incoming.

I'll try to get a blog post out soon!

> Damn, that’s a chonky database. Have you written anything about the setup? I’d love to know more— is it running on a single machine? How many reader and writer DBs? What does the replication look like? What are the machine specs? Is it self-hosted or on AWS?

It's self-hosted on bare metal, with standby replication, normal settings, nothing "weird" there.

6 NVMe drives in raidz-1, 1024GB of memory, a 96 core AMD EPYC cpu.

A single database with no partitioning (I avoid PostgreSQL partitioning as it complicates queries and weakens constraint enforcement, and IHMO is not providing much benefits outside of niche use-cases).

> By the way, really cool website.

Thank you!

Well, that's why I said "ATM", it's not a sustained rate, all the time. And yes, there's a bunch of DELETEs too.

> were restoring to a new node

Zfs send / recv or replication.

> I used to run a bunch of Postgres nodes at a similar scale. The most painful parts (by far) were restoring to a new node and major version upgrades. Any tricks you used for those parts?

Replication makes this pretty painless :)

I am not optimizing too much around insertion speed. I avoid GIN, GIST and hash indexes.

The schema is nicely normalized.

I had troubles with hash indexes requiring hundreds of gigabytes of memory to rebuild.

Postgres B-Trees are painless and (very) fast.

Eg. querying one table by id (redacted):

    EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE];

    Index Scan using table_name_pkey on table_name  (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1)
     Index Cond: (id =  '[ID_VALUE]'::bigint)
    Planning Time: 0.056 ms
    Execution Time: 0.052 ms

    # zpool iostat 1
    operations     bandwidth 
     read  write   read  write
    -----  -----  -----  -----
     148K   183K  1.23G  2.43G
     151K   180K  1.25G  2.36G
     151K   177K  1.25G  2.33G
     148K   153K  1.23G  2.13G

If the database team designed a thoughtful API with stored procedures, this can actually be a quite nice way to interact with a database for specific uses.

Being 100% hard and fast on that rule seems like a bad idea though.

I think you're referring to the CockroachDB Cloud DBaaS offering vs. CockroachDB itself, correct?

This is still true. I wouldn’t use Cockroach if it were my own business. Also, they don’t offer any free version to try out the product. All you get is a short trial period and that’s it.

Automatic data transfer would be super cool to get out of the box. We had a custom multi-tenancy solution for our application that heavily used PostgreSQL schemas. One schema for each customer.

It was a pain to get that work with Cockroach since it doesn’t optimize cross schema queries and suggests one DB per customer. This was a deal breaker for us and we had to duplicate data to avoid cross schema queries.

Being able to live within Postgres has its advantages.

Heh, no chance I can introduce this at work and hard to have a personal project requiring it. :)

I think you probably need some documentation to the effect of the current state of affairs, as well as prescriptions as to how to work around it. _Most_ live workloads, even if the total dataset is huge, have a pretty small working set. So limiting DB operations to simple fetches and doing any complex operations in memory is viable, but should be prescribed as the solution or people will consider it's omission as a fault instead of a choice.

Maybe this one for Instagram? https://instagram-engineering.com/sharding-ids-at-instagram-...

It's not. We tried. Plus, it doesn't work on RDS, where most of production databases are. I think Citus was a great first step in the right direction, but it's time to scale the 99% of databases that don't run on Azure Citus already.

Depends on your schema, really. The hard part is choosing a distribution key to use for sharding- if you've got something like tenant ID that's in most of your queries and big tables, it's pretty easy, but can be a pain otherwise.

Relating to the article, better scaling. Saying run it on a bigger box is a very brute force way to optimize an application.

While they come up with some other tricks here, that's ultimately what's scaling postgres means.

If I imagine a better database, it would have native support for scaling, a postgres compatible data layer as well as first party support for NoSQL( JSONB columns don't cut it since if you have simultaneous writes unpredictable behavior tends to occur).

It needs to also have a permissible license

Came here to say this. Cockroach solves the sharding issue by adopting consistent hashing-based data distribution, as described in the Dynamo paper. However, their cloud solution is a bit expensive to get started with.