For CI/CD, you want multiple clones running on the same compute power, in a shared environment, to keep the budget constant
828 karma · joined October 27, 2019
https://Postgres.AI
https://twitter.com/samokhvalov
https://gitlab.com/NikolayS
https://github.com/NikolayS
For CI/CD, you want multiple clones running on the same compute power, in a shared environment, to keep the budget constant
Alexander also presented OrioleDB in English at an EDB's event: https://vimeo.com/649616139
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...
> 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.
Here is her WR attempt (73m CNF, red card – probably the episode described in the article) https://www.facebook.com/watch/?v=2285865554973859
(Postgres.ai founder here)
Thank you for the snapshot link.
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.
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
For SQL optimization purposes, you cannot do that – there are high chances you'll get different query plans.
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).
> ...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.
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...
Perhaps you already know these, but just in case:
- https://github.com/ossc-db: pg_dbms_stats, pg_store_plans, pg_hint_plan
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.
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 :)
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.
Highly recommend it.
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.
All of them look interesting and developing rapidly.
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).
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.