feike

pgbackrest is the most versatile piece of backup technology for PostgreSQL and in my experience the other products do not come close.

I am therefore quite sad to see this happen. It won't be easy to get feature parity with this great product.

I sincerely hope this is a reversible decision, or perhaps the postgres project could even absorb it into contrib.

As every year, I try to solve it with 1 sql statement for every challenge, https://gitlab.com/feike/adventofcode/-/tree/master/2024, likely going to get stuck again around day 12/13 or so!

Many libraries for python, Rust, golang support COPY BINARY.

The times I've tested it, the improvement is very small as compared to plain copy, or copy with CSV, whereas it does require more work and thought upfront to ensure the binary actually works correctly.

https://www.postgresql.org/docs/current/sql-copy.html

Timescaler here, if you configure the timescaledb.compress_segmentby well, and the data suits the compression, you can achieve 20x or more compression.

(On some metrics data internally, I have 98% reduction in size of the data).

One of the reasons this works is due to only having to pay the per-tuple overhead once per grouped row, which could be as much as a 1000 rows.

The other is the compression algorithm, which can be TimescaleDB or plain PostgreSQL TOAST

https://www.timescale.com/blog/time-series-compression-algor... https://www.postgresql.org/docs/current/storage-toast.html

You should be able nowadays with PG16

    INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g

https://www.postgresql.org/docs/current/app-psql.html#APP-PS...

For older versions, you can do:

    \set v_x 'first value'
    \set v_y 'second value'
    INSERT INTO tbl1 VALUES (:'v_x', :'v_y');
    \set v_x 'next value'
    INSERT INTO tbl1 VALUES (:'v_x', :'v_y');

You can put it upright on a piano and put sheet music at eye level. I personally use flowkey + an electrical piano connected through USB. Beats a tablet for me!

PostgreSQL has used this term for decades!

The oldest I can find is from 1998 (PostgreSQL 6.3), but it was probably in use even before.

> Postgres offers substantial additional power by incorporating the following four additional basic concepts in such a way that users can easily extend the system:

classes inheritance types functions

Other features provide additional power and flexibility:

constraints triggers rules transaction integrity

These features put Postgres into the category of databases referred to as object-relational

https://www.postgresql.org/docs/6.3/c0101.htm

The trust is not just banning unsafe, it is using a limited std:

> The "trusted" version of PL/Rust uses a unique fork of Rust's std entitled postgrestd when compiling LANGUAGE plrust user functions.

https://github.com/tcdi/postgrestd

Just did a sequential run (to get some better measurements), and this is an excerpt of the things happening in the PostgreSQL instance inside the Docker container, for creating and dropping the databases:

    08:25:37.114 UTC [1456] LOG:  statement: CREATE DATABASE "test_1675239937111796557" WITH template = test_template
    [noise]
    08:25:48.002 UTC [1486] LOG:  statement: DROP DATABASE "test_1675239947937354435"

Start time of first test: 2023-02-01 08:25:03.633 UTC

Finish time of last test: 2023-02-01 08:26:13.861 UTC

82 tests, or 0.856 seconds per test (sequentially).

In parallel, we take 6.941 seconds for 82 tests, or 0.085 seconds per test.

We do this too for PostgreSQL: to ensure the tests are really fast:

    - we create a template database using the migrations
    - for *every* integration test we do `CREATE DATABASE test123 TEMPLATE test_template;`
    - we tune the PostgreSQL instance inside Docker to speed up things, for exampling disabling synchronous_commit

On a successful test, we drop the test123 database. On a failed test, we keep the database around, so we can inspect it a bit.

The really great thing about this approach (IMHO), is that you can validate certain constraint violations.

For example, exclusion constraints are great for modelling certain use cases where overlapping ranges should be avoided. In our (go) code, the test cases can use the sqlstate code, or the constraint name to figure out if we hit the error we expect to hit.

This approach is pretty much as fast as our unit tests (your mileage may vary), but it prevents way more bugs from being merged into our codebase.

If you keep reading, the words following your quote make it clear that PG15 should be supported:

"and will support PostgreSQL 13 and all the future releases of PostgreSQL."

A single query can write to multiple tables, using CTE's in PostgreSQL for example.

You could compose a SQL query that allows you to map multiple resultsets to 1 resultset, although that feels a bit awkward.

    WITH a AS (
        insert into a (k, v) values ('a', 1.0) returning *
    ), b AS (
        insert into b (k, v) values ('b', 2.0) returning *
    )
    SELECT
        row_to_json(a)
    FROM
        a
    UNION ALL
    SELECT
        row_to_json(b)
    FROM
        b;

Returns:

        row_to_json        
    --------------------------
    {"a_id":1,"k":"a","v":1}
    {"b_id":1,"k":"b","v":2}
    (2 rows)

Reminds me of Markus Winand who hands out stickers on database conferences banning offset.

His site is a great resource for anyone wanting to take a deeper dive on SQL performance:

https://use-the-index-luke.com/sql/partial-results/fetch-nex...

You can use regexes to help you split the file into pieces (this is in PostgreSQL), I expect other dbms's to have similar functions available.

    SELECT
        lineno::int              AS line,
        ((lineno-3)/6)::smallint AS card,
        ((lineno-3)%6)::smallint AS y,
        (col - 1)::smallint      AS x,
        value::smallint          AS value
    FROM
        regexp_split_to_table($1, '\n') WITH ORDINALITY AS sub(line, lineno)
    CROSS JOIN
        regexp_split_to_table(ltrim(line, ' '), '(\s+|,)') WITH ORDINALITY AS sub2(value, col)
    WHERE
        line != ''
        AND value != ''

Shameless plug, I've been doing this (without much success after day 8 or so) for a couple of years now. My rules are:

- 1 statement per part - No schema needed

https://gitlab.com/feike/adventofcode/-/tree/master/2021

Some others doing sql based solutions:

https://github.com/xocolatl/advent-of-code https://github.com/zr40/adventofcode

Patroni does have synchronous_mode_strict setting, which may be what you're looking for:

This parameter prevents Patroni from switching off the synchronous replication on the primary when no synchronous standby candidates are available. As a downside, the primary is not be available for writes (unless the Postgres transaction explicitly turns of synchronous_mode), blocking all client write requests until at least one synchronous replica comes up.

https://patroni.readthedocs.io/en/latest/replication_modes.h...

edit: seems I missed this discussion on twitter: https://twitter.com/jepsen_io/status/1265626035380346881

This postgresql mailing list thread allows you to read along with the PostgreSQL developers and Jepsen, seems like a very useful discussion: https://www.postgresql.org/message-id/flat/db7b729d-0226-d16...

> Can Patroni tell if master node is not responsive because it is busy vs dead

No. But the contract Patroni has is this:

I only serve a master (primary) if I have the lock. If I do not have the lock I will demote.

This results in that there can be only 1 primary active at any given point in time, even if the network is partitioned.

This in and of itself does not guarantee no-split-brain situations, a split-brain can occur if writes were made on the former primary, but not yet on the future primary. This however can be mitigated with synchronous replication.

One of the authors of Patroni here.

Automatic failover for PostgreSQL works great and can be done safely if combined with synchronous replication.

Multiple tools will implement this correctly:

https://patroni.readthedocs.io/en/latest/replication_modes.h... https://github.com/sorintlab/stolon/blob/master/doc/syncrepl...

Quoting a former colleague here, but "if it hurts, do it more often". That is what you should do with your PostgreSQL failovers.

I have clusters running on timelines in the hundreds without a byte of data loss due to using synchronous replication, tools that help out with leader election, and just doing it often.

I've been doing this by having a single SQL statement per puzzle. Didn't get beyond day 7 last year, but so far so good:

https://gitlab.com/feike/adventofcode/tree/master/2019

Good set of tips, however "Not NOT IN" is not true in general, as a single counterexample:

    EXPLAIN (costs off)
    SELECT relname, relpages
      FROM pg_class
     WHERE relname IN ('pg_class');

                         QUERY PLAN
  ---------------------------------------------------------
   Index Scan using pg_class_relname_nsp_index on pg_class
     Index Cond: (relname = 'pg_class'::name)
  (2 rows)

In general, I'd like to echo other's comments, learning how to use EXPLAIN and how to interpret query plans is the single most important tool, as it allows you to verify your hypothesis instead of relying on rules-of-thumb.

I was at the presentation last Thursday, they (OnGres) have fully open sourced both their methodology and their results and had a pretty strict divide between teams designing the benchmarks and teams running the benchmarks.

MongoDB could create a Pull Request/Merge Request against that repository so we can all judge those results ourselves, their current response is only words and a single table showing unlikely results.

However I do think the criticism of not tuning MongoDB is valid, however their response is dishonest:

> with their own heavily tuned PostgreSQL.

This was explicitly not the case according to OnGres other than the established norms of taking 25% memory for `shared_buffers` etc. No other tuning that is normally done for big clusters was done.

https://gitlab.com/ongresinc/benchplatform/ https://gitlab.com/ongresinc/txbenchmark