HNHacker News
TopNewBestAskShowJobs

samokhvalov

828 karma · joined October 27, 2019

nik@postgres.ai

https://Postgres.AI

https://twitter.com/samokhvalov

https://gitlab.com/NikolayS

https://github.com/NikolayS

submissionscomments
samokhvalov··on Neon – Serverless Postgres
The problem with Aurora's thin clones is extra cost each clone adds

For CI/CD, you want multiple clones running on the same compute power, in a shared environment, to keep the budget constant

samokhvalov··on Neon – Serverless Postgres
Agreed, this direction is underestimated and should be developed better -- we (Postgres.ai) do it for any Postgres with our Database Lab Engine [1], and Neon would bring even more power if it's installed on production

[1] https://github.com/postgres-ai/database-lab-engine

samokhvalov··on OrioleDB – solving some PostgreSQL wicked problems
Thanks for mentioning.

Alexander also presented OrioleDB in English at an EDB's event: https://vimeo.com/649616139

samokhvalov··on Ask HN: Tips on hosting your own Postgres instance
If not Kubernetes, check out this: https://github.com/vitabaks/postgresql_cluster – it's Postgres + patroni + pgBouncer + WAL-G or pgBackRest + Netdata + more, managed by Ansible.

If you do want to use Kubernetes, then check out:

- StackGres.io

- https://github.com/zalando/postgres-operator

- https://blog.flant.com/comparing-kubernetes-operators-for-po...

samokhvalov··on A woman who can descend into the sea on one breath
11 minutes is STA – static breath-holding. Of course, without movements, you can hold breath much longer. Here are current AIDA STA WR numbers: https://www.aidainternational.org/WorldRecords/History/Stati... – legendary Natalia Molchanova still holds the record. If you don't know about Natalia, and her son Alexey (who currently holds several WR records, including 131 CWT mentioned in the article) I highly recommend reading about both of them. For example, here: https://www.gq.com/story/freediver-alexey-molchanov-profile.
samokhvalov··on A woman who can descend into the sea on one breath
Women's record is mentioned a bit earlier:

> Bourke reached a depth of 73 metres – a world record – ...

All the facts are correct and provided. But I agree, how they present facts looked weird to me too.

samokhvalov··on A woman who can descend into the sea on one breath
Women's CNF record currently is 73m (AIDA version, see https://www.aidainternational.org/WorldRecords/History/Const..., switch to Women). She is not WR holder, but current record is 73m and going deeper 70m without fins is close to it.

Here is her WR attempt (73m CNF, red card – probably the episode described in the article) https://www.facebook.com/watch/?v=2285865554973859

samokhvalov··on Thin PostgreSQL Clones
Exactly! Great thought, it aligns with our vision.

(Postgres.ai founder here)

samokhvalov··on Zendesk pretends to be open source?
...and it is removed (well, edited)

Thank you for the snapshot link.

samokhvalov··on Optimizing Postgres queries at scale
But in general case, such clones are not suitable for query optimization tasks.

And as I already mentioned in my other comments here, thin cloning is a great way to solve the speed (and money) problem for larger DBs.

samokhvalov··on Optimizing Postgres queries at scale
This is great that you can do that. An experimental environment must be of the same size.

Ideally, absolutely the same physically, to keep the same physical layout as on prod – bloat, etc – though, sometimes it's not allowed.

Another problem here is that for each experiment, you might need a new environment. Plus, in larger teams, many people might want to do this work at the same time – so you end up combining efforts, synchronizing them, sharing environments. It slows you down. Or increases your cloud spending a lot.

This is a kind of problem Steve Jobs described in his famous 1980 speech [1] about Apple: if we give each person their own computer, something special happens compared to the case when 10 persons share a single computer. Here it is the same: if we can give each engineer their own full-size DB copy for experiments, something special happens. They start moving much, much faster. SQL quality improves. DB changes (DDL, massive DML) stop failing. Finally, engineers start learning SQL, it's an eye-opener – they now can see how it works on large volumes (because they didn't have good environments for experiments before!)

This is what we (Postgres.ai) have learned over the last couple of years developing Database Lab Engine [2]. It's an open-source tool for superfast cloning of Postgres databases of any size, based on copy-on-write provided by ZFS or LVM. On a single machine, you can have dozens of full-size clones up and running, conduct a lot of experiments at the same time, and still be paying only for 1 machine. And clone provisioning takes only a few seconds, it feels like magic. But the main magic is how engineering processes change.

One of great use cases is how GitLab uses it for Postgres query optimization and change control [3].

[1] https://youtu.be/0lvMgMrNDlg?t=586

[2] https://github.com/postgres-ai/database-lab-engine

[3] https://postgres.ai/resources/case-studies/gitlab

samokhvalov··on Optimizing Postgres queries at scale
> If the database is too big, just use last N amount of rows from the database instead of a full dump.

For SQL optimization purposes, you cannot do that – there are high chances you'll get different query plans.

samokhvalov··on Optimizing Postgres queries at scale
Thin clones can help: https://github.com/postgres-ai/database-lab-engine
samokhvalov··on Ask HN: How do you handle CI/CD for databases
Take a look at Database Lab: https://postgres.ai/products/database-migration-testing

The idea is to use block-level copy-on-write (ZFS by default, LVM2 is an option too, though less powerful) to be able to get thin clones really fast. Currently supports only Postgres. The engine is open source. Result; single machine with Postgres DB of any size, you pay 1x for storage and compute, and can have dozens of independent DBs, provisioning takes a few seconds.

This is used in CI/CD context so getting temporary full-size for any tests is not a problem any more.

We constantly add new features and looking forward to new use cases.

Some case studies with numbers are here: https://postgres.ai/resources

Here is how GitLab uses it for DB testing: https://docs.gitlab.com/ee/architecture/blueprints/database_...

(disclaimer: I'm the founder of Postgres.ai, the company that develops Database Lab).

samokhvalov··on PostgreSQL Subtransactions Considered Harmful
They have an interesting section, "Beware: don't (over) use nested transactions", in their blog post: https://www.cockroachlabs.com/blog/nested-transactions-in-co...

> ...we do not recommend their use in new applications.

> The reality is that nested transactions are a product of the early days of software engineering, in the 1990s, back when systems were tightly coupled and the Internet and the Cloud were not yet very relevant. ...

> Nowadays, such tight coupling has a bad rep. This is because two additional decades of software engineering have taught us that implicit global state really, really does not play well with distributed services where some components may fail even as part of normal load ...

> Additionally, nested transactions can amplify performance anomalies ...

> Finally, nested transactions can run afoul of correctness in distributed apps. In fact, the idea of multi-component transactions in client code really evokes the idea of a bull in a china shop. As long as all is well and the transaction is due to commit, the idea somewhat makes sense. However, what happens when the database (and not the client) decides the transaction is un-committable and must be aborted, for example because of a serializability conflict or a node restarting for maintenance? It is not just the database state that must be rolled back; all the possible side effects performed by the components holding the transaction must also be rolled back.

They implemented it, but do not recommend using it.

samokhvalov··on PostgreSQL Subtransactions Considered Harmful
What does "appropriately" mean in this context?
samokhvalov··on PostgreSQL EXPLAIN Output Explained
Great extension, yes. There is overhead when enabling the timing and buffers options, but sometimes it's not big [1]

But auto_explain solves only part of the task – you can see what happened, but cannot see the answers to "what if" questions. ("What if I used this index?")

[1] https://www.pgmustard.com/blog/auto-explain-overhead-with-ti...

samokhvalov··on PostgreSQL EXPLAIN Output Explained
> tools to screw up table statistics

Perhaps you already know these, but just in case:

- https://github.com/ossc-db: pg_dbms_stats, pg_store_plans, pg_hint_plan

- https://github.com/HypoPG/hypopg

samokhvalov··on PostgreSQL EXPLAIN Output Explained
> Different table statistics can cause the planner to go in wildly different directions

Exactly. That's why my team and I (Postgres.ai) have developed Database Lab Engine [1] and a chatops tool for SQL optimization, Joe bot [2], both are open-source (AGPLv3).

EXPLAIN (ANALYZE, BUFFERS) has to be executed on the same-size DB, with properly adjusted Postgres configuration.

Interesting, that the machine you might using for query plan troubleshooting, can have less RAM and different hardware in general – it doesn't matter for the planner. Even shared_buffers doesn't matter – you can set effective_cache_size matching production (this trick we use in Database Lab when hardware is weaker than on production).

As for the cache states – very good point as well. I'm advocating for buffers- or rows-centric approach: first, optimization should be done to reduce the numbers of buffers or, if you're working with "logical" (dump/restore) copy of the database rather than "physical" (PGDATA copy, keeping the same data layout, including bloat, etc.) – the fewer the numbers, the better. Only then, you pay attention to timing – and keep in mind what can happen under the worst conditions (everything is read from disk), if it makes sense.

[1] https://postgres.ai/products/how-it-works

[2] https://postgres.ai/products/joe

samokhvalov··on Hacker rewrote complex backend with Postgres and cron, CEO fired 40% of devs
Thanks. Decided to hide it here.

On one hand, it would be good and funny to be on the 1st page with this (obviously, it was rising fast).

On another – who knows, how many people (I guess, non Enslish speakers) might do the same mistake as I did :)

samokhvalov··on Hacker rewrote complex backend with Postgres and cron, CEO fired 40% of devs
Obviously I did
samokhvalov··on Hacker rewrote complex backend with Postgres and cron, CEO fired 40% of devs
Disclaimer: I've posted it here – but honestly, now I'm trying to find any info about that startup and cannot find it.

It's the first time I'm having some doubts if a news about Postgres is true. Would be good to have some sort of labels/tags "unverified" for such news.

If anyone knows anything about this, please let me know.

samokhvalov··on Sidekick – A Browser Built for Work
Yeah, noticed that it's #1 there – congratulations with the impressive launch!
samokhvalov··on PostgreSQL Configuration for Humans
I use this almost daily. Last time it was an hour ago -- checked when hot_standby's default changed from off to on (it was in Postgres 10).

Highly recommend it.

samokhvalov··on Mastering PostgreSQL Administration [pdf]
pgbouncer is not a load balancer, it's a connection pooler.

The former is used to split reads and writes (and so far I haven't seen "magic" tools that automate it well, pgpool has lots of issues; this task is better to solve in app code -- most modern frameworks either already solved it such as RoR, or at least work well with 2 connections),

the latter is used to multiplex connections and improve performance similarly to nginx, haproxy, envoy, but with native Postgres protocol support.

samokhvalov··on Mastering PostgreSQL Administration [pdf]
Right, and operator from Zalando https://github.com/zalando/postgres-operator (fresh good comparison, in Russian, but google translate should work well on such texnical texts https://habr.com/ru/company/flant/blog/520616/).

All of them look interesting and developing rapidly.

samokhvalov··on Mastering PostgreSQL Administration [pdf]
1) costs, 2) ultimate observability and performance tuning - with self-managed Postgres, you have full control and can tune it better, monitor all the details, put pgBouncer or Odyssey on database nodes, there is no need to spend hours and sometimes days or even weeks with support engineers trying to understand what's happening under the hood and how to fix/improve it (but you need skills -- if you don't, managed service support may be beneficial, of course if you're lucky to reach experienced engineer), 3) specific need: have replication connection from outside, control backups, etc.

That being said, the benefits of managed offerings are clear and sound: you don't waste your time for many DBA tasks, paying some extra and losing some control. Many companies choose this. Some change their mind over time and move back to self-managed, or use hybrid approach.

One more thing. If you need to maintain dozens or more databases, you need automation. This leads to either managed offerings, or k8s (look at StackGres.io).

samokhvalov··on Understanding Recursive Queries in PostgreSQL
Check out this slide deck, it's full of interesting stuff, including recursive CTE, arrays, LATERAL, and practically useful complex examples: https://www.slideshare.net/pgdayasia/how-to-teach-an-elephan...
samokhvalov··on GoodJob – a Postgres-based ActiveJob back end for Ruby on Rails
To make it scale well, two things are required:

1. TRUNCATE if cleanup is needed (no DELETEs) + partitioning.

2. SELECT .. FOR UPDATE SKIP LOCKED or advisory locks.

It's worth learning from Skype's PgQ developed in 2000-s.

Update: found the work with advisory locks in the code, great.

samokhvalov··on Harmful Workload Generator for PostgreSQL
Great idea! Do you think such kinds of tests could be fully automated (executed in CI)?
← PreviousPage 3 of 4Next →