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 Zero downtime Postgres upgrades
> You can create a replication slot, take a snapshot, restore the snapshot to a new instance, advance the LSN and replicate from there - boom, you have a logical replica with all the data. Then you upgrade your logical replica.

This is a great recipe but needs small but important correction. We need to be careful with plugging pg_upgrade in this physical-to-logical replica conversion process: if we first start logical replication and then running pg_upgrade, there are risks of corruption – see discussion in pgsql-hackers https://www.postgresql.org/message-id/flat/20230217075433.u5.... To solve this, we need first to create logical slot, advance the new cluster to slot's LSN position (not starting logical replication yet), then run pg_upgrade, and only then logical replication – when the new cluster is already running on new PG version.

This is exactly how we (Postgres.ai) recently have helped GitLab upgrade multiple multi-TiB clusters under heavy load without any downtime at all (also involving PgBouncer's PAUSE/RESUME) - there will be a talk by Alexander Sosna presented later this week https://www.postgresql.eu/events/pgconfeu2023/schedule/sessi... and there are some plans to publish details about it.

samokhvalov··on ClickHouse Obfuscator – A tool for dataset anonymization
> The program is installed with clickhouse-client, has no dependencies, and works on almost any flavor of Linux. You can apply it to any database dump, not just ClickHouse. For instance, you can generate test data from MySQL or PostgreSQL databases or create development databases that are similar to your production databases.
samokhvalov··on Pg_bm25: Elastic-Quality Full Text Search Inside Postgres
I checked the benchmarks and was surprised to see that native search is (a) so slow (seconds), and (b) demonstrating O(N) behavior – with indexing, it should not happen at all.

Indeed, looking at the benchmark source code (thanks for providing it!), it completely lacks index for the native case, leading to a false statement the that native full-text search indexes Postgres provides (usually GIN indexes on tsvector columns) are slow.

https://github.com/paradedb/paradedb/blob/bb4f2890942b85be3e... – here the tsvector is being built. But this is not an index. You need CREATE INDEX ... USING gin(search_vector);

This mistake could be avoided if bencharks included query plans collected with EXPLAIN (ANALYZE, BUFFERS). It would quickly become clear that for the "native" case, we're dealing with SeqScan, not IndexScan.

GINs are very fast. They are designed to be very fast for search – but they have a problem with slower UPDATEs in some cases.

Another point, fuzzy search also exists, via pg_trgm. Of course, dealing with these things require understanding, tuning, and usually a "lego game" to be played – building products out of the existing (or new) "bricks" totally makes sense to me.

samokhvalov··on Extracting Hacker News book recommendations with the ChatGPT API
it's no 29
samokhvalov··on Pg_branch: Experimental Postgres extension brings Neon-like branching
(Postgres.ai founder here – we develop https://dblab.dev)

There is one more very important difference here, not only FOSS vs proprietary.

N thin clones on Aurora give you O(1) in terms of storage costs (you pay 1 time plus only for difference, which is 0 initially), but O(N) in terms of compute (N clones = N instances).

That's super expensive and prevents most people from having great DB-related test coverage in CI/CD.

(This is true for Neon's approach to pricing of branches too, if I'm not mistaken.)

But for the tech discussed here, you can run many – dozens, hundreds – clones on a single machine, having predictable budget. It's O(1) costs for both storage and compute. This unblocks dev&test activities with full-size databases for engineers in your team.

samokhvalov··on Pg_branch: Experimental Postgres extension brings Neon-like branching
Well, understandable. This is true for any open-source product that is under control by a single company and the majority of contributions comes from this company.

Yes, we aim to have revenue (as any company) from our paid offering. But not only we're going to keep DBLab Engine open and free, we recently changed the license from AGPLv3 to Apache 2.0, and currently consider making additional pieces open.

samokhvalov··on Pg_branch: Experimental Postgres extension brings Neon-like branching
Underlying DBLab Engine is FOSS (Apache 2.0), with API, CLI and UI https://github.com/postgres-ai/database-lab-engine
samokhvalov··on CloudNativePG – Postgres Operator for Kubernetes
You'll probably like this: https://github.com/vitabaks/postgresql_cluster
samokhvalov··on CloudNativePG – Postgres Operator for Kubernetes
> Doesn’t handle automatic failover when a node dies

Patroni, the most popular autofailover solution for Postgres, is developed in Zalando. Of course it's included in Zalando operator by default.

samokhvalov··on PgBouncer is useful, important, and fraught with peril
beware of the issues related to postgres subtransactions https://postgres.ai/blog/20210831-postgresql-subtransactions...
samokhvalov··on Postgres FM
(Co-host here)

Thanks for posting!

samokhvalov··on Unexpected downsides of UUID keys in PostgreSQL
Hopefully, it will soon have it: https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc...
samokhvalov··on Unexpected downsides of UUID keys in PostgreSQL
We discussed some of these problems here during a Postgres.tv session, and developed a patch to support ULID / UUIDv7: https://www.youtube.com/watch?v=YPq_hiOE-N8

And then Andrey proposed a patch in the pgsql-hackers mailing list: https://www.postgresql.org/message-id/flat/CAAhFRxitJv%3DyoG..., https://commitfest.postgresql.org/43/4388/

Everyone who can help (test, discuss, etc.) – please participate in that discussion.

The standard is not finalized yet, but there some expectations that it will be, if it happens, it would be great to have this in future Postgres 17.

samokhvalov··on Ways to shoot yourself in the foot with Postgres
What do you do if you need to check index ideas, or new table design?
samokhvalov··on Ask HN: Postgres experts, how did you start your career?
:)
samokhvalov··on Ask HN: Postgres experts, how did you start your career?
Studied database theory at uni (20002-2004) -> a few years of work with Ora and MS -> startup, attempt to use MySQL (in 2005), cannot use it after all those others -> switched to Postgres, rewriting all the code code in a few days -> happy Postgres user since then, no regrets (though, I can name a lot of issues with Postgres, it's still the best)
samokhvalov··on Running Databases on Kubernetes
This is too reactive approach, isn't it?

Imagine a system that could do this in CI/CD pipelines:

1) first, run all the tests on a tiny DB as usual 2) extract queries 3) run them against full-size DB branch/thin clone (thin provisioning, CoW; PII is not there of course, wiped out for security/compliance) -- auto-guessing parms, that's the trickiest part, but assume it's solved 4) collect all the details about performance, focusing on IO numbers (rows, block read/writes) 5) if some queries are off – say, you forgot LIMIT – post a warning, block the change, do not allow deploying it, letting backend dev fix it.

This would be proactive. And it's becoming possible with modern tools.

samokhvalov··on Running Databases on Kubernetes
not sure about petabytes (yet), but for dozens of TiB, we are fine – DB branching, thin clones, CoW are to the rescue.
samokhvalov··on Running Databases on Kubernetes
Agreed on compliance part – of course, in many cases (not in all though) PII must not be in dev/test envs.

Although, cannot agree with the former part. If your tests are running 2 days on a full size clone, and it's an OLTP case, what about users, do they suffer from long query duration too? It sounds like it's time to optimize queries and/or redesign test sets (or both).

If it's bad in testing, it will be bad in prod. That's the idea of testing.

Example: how do you check schema changes?

samokhvalov··on Running Databases on Kubernetes
> Use a helm chart and just bring your own little database for dev test and e2e tests.

dev, test, and e2e tests should be done against full-size db clones

samokhvalov··on Features I'd Like in PostgreSQL
I see to use cases:

- disable for all - disable only for my session, to check what would happen with the plan, and only then decide to proceed with disabling for all (or to drop it)

ALTER is quite invasive way, even more than "UPDATE .. SET indisvalid = false ...". It would be good to do it via SET as it was proposed in the plantuner extension long ago.

samokhvalov··on Features I'd Like in PostgreSQL
There is an opinion that it's unsafe: https://twitter.com/petervgeoghegan/status/15991919640456724...

There was an extension from Teodor some time ago, plantuner, to disable indexes in a session or globally ("set plantuner.forbid_index='id_idx2';") – but it didn't make it to core, and even to contribs. Maybe because the functionality to disable indexes was mixed with planner hints there. It's a very old story, discussion from 2009: https://www.postgresql.org/message-id/flat/47E63672-972E-452...

samokhvalov··on Ask HN: What is your favorite Tech Podcasts these days?
Thanks!! (postgres.fm co-host)
samokhvalov··on Pg_squeeze: An extension to fix table bloat
These tools remove bloat. Autovacuum removes dead tuples. They are often confused, but dead tuple and bloat are different though connected concepts.

A lot of accumulated dead tuples (row versions marked dead but still present occupying space) are converted to bloat (empty space, gaps) when VACUUM cleans them up.

samokhvalov··on Pg_squeeze: An extension to fix table bloat
Not often. And depending of what is considered as base – only the core Postgres code, or contrib modules (shipped with it and supported by community). Extension/contrib -> core is a very rare event. For example, it happened with XML type and functions originated from contrib/xml2, and with contrib/tsearch2 which became FTS in core. But it's a very rare thing. And based on just these 2 examples, you should have -2 in the name:)

As for external extension becoming a contrib module, it's not a frequent event as well:

- Some "1000+ PostgreSQL EXTENSIONs" list https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8a...

- Current list of contrib modules – 58 modules https://gitlab.com/postgres/postgres/tree/master/contrib

- Same list 10 years ago, Postgres 9.2 – 53 modules https://gitlab.com/postgres/postgres/tree/REL9_2_STABLE/cont...

samokhvalov··on Pg_squeeze: An extension to fix table bloat
Good point.

I wonder if pg_squeeze would work well in a large-scale heavy-writes case. There is a very sad and hard limit of single-threaded walsender that can saturate a single core (depends on CPU and schema / writes complexity; for example, I saw systems where it happened at ~2 WAL/s, or roughly 1-2 TiB of WAL data generated per day). So at some scale, the process might not converge.

Also, we apply changes in the same system, there are 2x writes anyway – though, it happens asynchronously with pg_squeeze, unlike with triggers (though, that "delta" table pg_repack temporarily writes to, it's very light in terms of indexes, and we do only INSERTs, so this amplification is not 2x in terms of IO and timing overhead, right?)

samokhvalov··on PostgreSQL 14 Internals book – Part II
I didn't discuss money and profit, neither I discussed pgpro employees.

There is difference between people and organizations, though any organization is created by people.

The question I raised was about the global Postgres community and PostgresPro the company – is it ok for the community to deal with this company or not.

My answer is definitely it's not ok. At the same time, we should support try to keep with people and support them, even if they still work in PostgresPro for living.

Moreover, I think dealing the founders – Oleg, Teodor, etc – is also ok. But not with the company, brand, and its branded events like pgconf.ru.

samokhvalov··on Supabase – Realtime: Multiplayer Edition
It would be great to see monitoring of this thing under load – I mean the Postgres part of it, how it's loaded, how many connections used, query analysis, etc.
samokhvalov··on Semantic Diff for SQL
Comparing tree structures can be used to have diff of EXPLAIN plans. During query optimization, it might make a lot of sense.
samokhvalov··on Column order in PostgreSQL does matter
I don't think I understand you.

1 word is 8 bytes, and if you use two columns, the first one having size 2 bytes, and the second – 8 bytes, you'll end up spending 8 + 8 = 16 bytes, because the first one (2byte in length) will be aligned with 6 zeroes to fill the whole "8-byte word". For example, with smalling (which has size 2 bytes but might be aligned, depending on the "column tetris" situation we have) + int8:

  test=# select pg_column_size(1::int2);
   pg_column_size
  ----------------
                4
  (1 row)

  test=# create table tttt1 as select 1::int2 as c1, 2::int8 as c2;
  SELECT 1
  test=# create extension pageinspect;
  CREATE EXTENSION
  test=# select lp_len from heap_page_items(get_raw_page('tttt1', 0));
   lp_len
  --------
       40
  (1 row)
-- the total length of the tuple in this example is 40 bytes. Why 40? Because the tuple header is 23 bytes aligned to 3 "words", hence 24 bytes – so total is 24 + 8 + 8 = 40; though, effective data size us just 23 + 2 + 8 = 33; and 7 bytes are "wasted". And our int2 column was aligned by 6 additional bytes, to whole 8-byte word.

Of course, if the second column is of size 4, then both columns fit into a single word – let's try real + int4:

if we'd have 2 int2 columns, followed by a int4 one, we'd use just one word (8 bytes to store this data, plus 24 bytes for tuple header):

  test=# create table tttt2 as select 1::int2, 2::int2, 3::int4;
  SELECT 1
  test=# select lp_len from heap_page_items(get_raw_page('tttt2', 0));
   lp_len
  --------
       32
  (1 row)
-- in this case, the tuple length now is 32 bytes (24 bytes tuple header + 8 bytes for the data). Alignment was not needed.

Thus, I don't see what's wrong with the document – probably I just didn't understand you.

← PreviousPage 2 of 4Next →