RE: suggestion about time based partitioning and hibernate
От: Ron Отправлено: 18 июля 2023 г. 9:48 Кому: pgsql-general@lists.postgresql.org Тема: Re: suggestion about time based partitioning and hibernate On 7/18/23 01:18, Luca Ferrari wrote: Dear all, I'm looking for ideas here, and it could be someone already stepped into declarative partitioning of an existing database where Hibernate (a Java ORM) handles the tables. The situation is as follows: create table foo( id primary key, a_date date, ... ); Now, the trivial way to partition this would be on a range based on a_date, so that the primary key of the tables shifts from id to (id, a_date). One thing that frightens me is that Hibernate does a lot of per-row lookups by means of the id, so while the partitioning is probably going to make things more manageable and even faster in some scenarios, could lead to drawbacks when Hibernate queries by id. Moreover, hibernate will think id is unique while it is not anymore. Last but not least, referencing foreign keys are made by Hibernate thru the id column, and it means that incoming foreign keys to foo will not be in place anymore. Now, I know that I can define a composite key in hibernate, in order to match the effective new data structure, but this requires a huge rewrite of the application code. And after all, we are talking about a non-PostgreSQL related piece, so the problem is not on the PostgreSQL side. Anyone has already done a partitioning in such scenario? I am thinking that partitioning on an hash of id could be the only way to go without having to touch the hibernate side, even if this would bring up a less balanced partitioned structure. In such case, I mean partitioning by hash, having a table with 60 millions rows per 50 GB in size, what would be the rule of thumb to select the number of partitions (i.e., a suggested modulus)? We're in the exact same situation, using Hibernate and having many tables like foo, partitioned by a_date with PK of (id, a_date) and FK definitions (id, a_date). It was a massive mistake, since many queries span partitions. Within a year, I "departitioned" all tables except two giant tables that have large bytea columns. (All the formerly partitioned tables still have (id, a_date) as PK and FK. I'd like to change that, but the amount of code change is untennable given them amount of new features that need to be added.) Thus, my recommendations are: 1. only partition the tables you must, and 2. partition by id. -- Hi! It is always depends on database architecture. Partitioning should be planned in advance and your queries must be aware of it. There are a number of caveats here - if your database grows indefinitely you must have a dynamic partitioning. As for me I have created a service procedure to create new partitions to handle new data, and a backup procedure to check existence of partitions on every insert. - if database is more or less finite and predictable I have created all the partitions created in advance at once Next I give up range partitioning, I have created a surrogate key function and partition by it. Function is available on *client* and server, so every time I want to query data I can calculate this function and address data directly in known partition. In Hibernate too, just pass both elements of *composite* key. Partition key MUST be in primary key, so query becomes cheap. In case on subquery it is a matter of query optimizer if it is able to understand composite keys and do partition pruning. PG 12 can not, you have to tweak queries. To summarize, if you query by ID – create a surrogate key from ID and pass it to query with original ID. All the FK data must follow the same schema, partition key must remain the same across the tables. If on the righth, other end of database schema you need other way of partitioning – I have created extra column to store “left side” partitioning key, so joins may respect all partitioning schemas. What you should consider more – query optimizer. Once it may decide to create a general plan instead of custom. This will lead to locking all the (thousands of) partitions, than applying parameters, than pruning. In my case it was nearly half a million of locks for single query. There was a discussion here, and there was a proposed patch to exchange pruning-analyzing, I do not know it`s faith. But I given up setting force_custom_plan and this speed up my queries by more than 10x and no locks, ~ 5 per query.
fsync data directory after DB crash
I found that starting from version 9.5, PostgreSQL will do fsync on the entire data directory after DB crash. Here's a question: if I have FPW = on, why is this step still necessary?
Upgrade Failure
Hello, I was running an upgrade from 9.5 to 14.8 and encountered the below error. Running in verbose mode Performing Consistency Checks - Checking cluster versions ok The source cluster was not shut down cleanly. Failure, exiting Please how do you resolve this Kind Regards Tiamoh M
Re: Upgrade Failure
On Tue, 2023-07-18 at 06:02 -0400, Johnathan Tiamoh wrote: > I was running an upgrade from 9.5 to 14.8 and encountered the below error. > > Running in verbose mode > Performing Consistency Checks > - > Checking cluster versions ok > > The source cluster was not shut down cleanly. > Failure, exiting > > Please how do you resolve this Well, start the 9.5 server and shut it down cleanly. Yours, Laurenz Albe
Re: Upgrade Failure
Thank You Laurenz. On Tue, Jul 18, 2023 at 6:12 AM Laurenz Albe wrote: > On Tue, 2023-07-18 at 06:02 -0400, Johnathan Tiamoh wrote: > > I was running an upgrade from 9.5 to 14.8 and encountered the below > error. > > > > Running in verbose mode > > Performing Consistency Checks > > - > > Checking cluster versions ok > > > > The source cluster was not shut down cleanly. > > Failure, exiting > > > > Please how do you resolve this > > Well, start the 9.5 server and shut it down cleanly. > > Yours, > Laurenz Albe >
Re: Upgrade Failure
> On 18 Jul 2023, at 12:02, Johnathan Tiamoh wrote: > The source cluster was not shut down cleanly. > Failure, exiting > > Please how do you resolve this pg_upgrade runs bin/pg_controldata on the source cluster data directory and ensures that the "Database cluster state" says "shut down". If you run the same command, which state is the cluster reported to be in? -- Daniel Gustafsson
Re: Upgrade Failure
I used pg_ctl to stop the cluster. When I used the same pg_ctl to check status, it says no server is running On Tue, Jul 18, 2023 at 6:14 AM Daniel Gustafsson wrote: > > On 18 Jul 2023, at 12:02, Johnathan Tiamoh > wrote: > > > The source cluster was not shut down cleanly. > > Failure, exiting > > > > Please how do you resolve this > > pg_upgrade runs bin/pg_controldata on the source cluster data directory and > ensures that the "Database cluster state" says "shut down". If you run the > same command, which state is the cluster reported to be in? > > -- > Daniel Gustafsson > >
Re: Upgrade Failure
> On 18 Jul 2023, at 12:26, Johnathan Tiamoh wrote: > > I used pg_ctl to stop the cluster. > > When I used the same pg_ctl to check status, it says no server is running Right, but what does "pg_controldata" say for the cluster? -- Daniel Gustafsson
Re: Query take a long time and use no index
On Tue, 18 Jul 2023 at 06:19, basti wrote: > > Thanks a lot tomas, i will try it. > > I have find out that there is a 'aggregation' function in the frontend. > But this is MySQL specific and I have no idea the transform it to postgres. > > It looks like: > 'REPLACE INTO aggregate (channel_id, type, timestamp, value, count) Perhaps that's something like PostgreSQL's INSERT ON CONFLICT [1] David [1] https://www.postgresql.org/docs/current/sql-insert.html
Re: Query take a long time and use no index
On Mon, 17 Jul 2023 at 21:13, basti wrote:
> volkszaehler=# explain analyze SELECT COUNT(DISTINCT DATE_TRUNC('day',
> TIMESTAMP 'epoch' + timestamp * INTERVAL '1 millisecond')) FROM data
> WHERE channel_id = 5 AND timestamp >= 0;
Alternatively, you could express this as:
SELECT COUNT(*) FROM (SELECT DISTINCT DATE_TRUNC('day', TIMESTAMP
'epoch' + timestamp * INTERVAL '1 millisecond')) FROM data WHERE
channel_id = 5 AND timestamp >= 0) a;
If there was an index on (channel_id, (DATE_TRUNC('day', TIMESTAMP
'epoch' + timestamp * INTERVAL '1 millisecond'))); then the distinct
could efficiently perform a Group Aggregate. Otherwise, it could at
least hash aggregate and the distinct could be done in parallel
(assuming you're using at least PostgreSQL 15).
The yet-to-be-released PostgreSQL 16 will allow more efficient
execution of DISTINCT and ORDER BY aggregates by allowing indexed to
provide pre-sorted input. In the meantime, the query above will
probably help you.
David
Re: Upgrade Failure
pg_ctl is not pg_controldata. (I bet you ran "pg_ctl stop --mode=immediate". That's not a clean shutdown.) On 7/18/23 05:26, Johnathan Tiamoh wrote: I used pg_ctl to stop the cluster. When I used the same pg_ctl to check status, it says no server is running On Tue, Jul 18, 2023 at 6:14 AM Daniel Gustafsson wrote: > On 18 Jul 2023, at 12:02, Johnathan Tiamoh wrote: > The source cluster was not shut down cleanly. > Failure, exiting > > Please how do you resolve this pg_upgrade runs bin/pg_controldata on the source cluster data directory and ensures that the "Database cluster state" says "shut down". If you run the same command, which state is the cluster reported to be in? -- Daniel Gustafsson -- Born in Arizona, moved to Babylonia.
Effects of dropping a large table
Hi all, I'm hoping to ensure I understand the implications of dropping a large table and the space being reclaimed by the database and/or OS. We're using pg v14. This table is quite large with a primary key and one additional index—all together these are on the order of 1TB. The primary key and index take-up quite a bit more space than the table itself. Our hope is to discontinue use of this table and then eventually drop it. However, the database is under constant load and we'd like to avoid (or at least anticipate) downtime or degraded performance. The database also replicates to a standby instance. So in short, what can we expect if we drop this table? Will the strategy that pg takes to give that space back to the rest of the database and/or OS have significant effects on availability or performance? Finally, are there any other considerations that we should take into account? I appreciate your time and input, thanks! -- Devin Ivy
Re: Effects of dropping a large table
On 7/18/23 11:58, Devin Ivy wrote: Hi all, I'm hoping to ensure I understand the implications of dropping a large table and the space being reclaimed by the database and/or OS. We're using pg v14. This table is quite large with a primary key and one additional index—all together these are on the order of 1TB. The primary key and index take-up quite a bit more space than the table itself. Our hope is to discontinue use of this table and then eventually drop it. However, the database is under constant load and we'd like to avoid (or at least anticipate) downtime or degraded performance. The database also replicates to a standby instance. So in short, what can we expect if we drop this table? Will the strategy that pg takes to give that space back to the rest of the database and/or OS have significant effects on availability or performance? Finally, are there any other considerations that we should take into account? I appreciate your time and input, thanks! -- Devin Ivy You might consider deleting portions of the table in separate (consecutive) batches (maybe 5% per delete). And then truncate table is not logged so that might be an alternative.
Re: fsync data directory after DB crash
On Tue, Jul 18, 2023 at 04:50:25PM +0800, Pandora wrote: > I found that starting from version 9.5, PostgreSQL will do fsync on > the entire data directory after DB crash. Here's a question: if I > have FPW = on, why is this step still necessary? Yes, see around the call of SyncDataDirectory() in xlog.c: * - There might be data which we had written, intending to fsync it, but * which we had not actually fsync'd yet. Therefore, a power failure in * the near future might cause earlier unflushed writes to be lost, even * though more recent data written to disk from here on would be * persisted. To avoid that, fsync the entire data directory. -- Michael signature.asc Description: PGP signature
Re: fsync data directory after DB crash
On Wed, Jul 19, 2023 at 12:41 PM Michael Paquier wrote: > On Tue, Jul 18, 2023 at 04:50:25PM +0800, Pandora wrote: > > I found that starting from version 9.5, PostgreSQL will do fsync on > > the entire data directory after DB crash. Here's a question: if I > > have FPW = on, why is this step still necessary? > > Yes, see around the call of SyncDataDirectory() in xlog.c: > * - There might be data which we had written, intending to fsync it, but > * which we had not actually fsync'd yet. Therefore, a power failure in > * the near future might cause earlier unflushed writes to be lost, even > * though more recent data written to disk from here on would be > * persisted. To avoid that, fsync the entire data directory. FTR there was some discussion and experimental patches that would add recovery_init_sync_method=none and recovery_init_sync_method=wal, which are based on the OP's observation + an idea for how to make it work even without FPWs enabled: https://www.postgresql.org/message-id/flat/CA%2BhUKGKgj%2BSN6z91nVmOmTv2KYrG7VnAGdTkWdSjbOPghdtooQ%40mail.gmail.com#576caccf21cb6c3e883601fceb28d36b Only recovery_init_sync_method=syncfs actually went in from that thread. It works better for some setups (systems where opening squillions of files just do perform a no-op fsync() is painfully expensive).
Pgoutput not capturing the generated columns
Hi PG Users.
We are using Debezium to capture the CDC events into Kafka.
With decoderbufs and wal2json plugins the connector is able to capture the
generated columns in the table but not with pgoutput plugin.
We tested with the following example:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
full_name VARCHAR(100) GENERATED ALWAYS AS (first_name || ' ' || last_name)
STORED
);
// Inserted few records when the connector was running
Insert into employees (first_name, last_name) VALUES ('ABC' , 'XYZ’);
With decoderbufs and wal2json the connector is able to capture the generated
column `full_name` in above example. But with pgoutput the generated column was
not captured.
Is this a known limitation of pgoutput plugin? If yes, where can we request to
add support for this feature?
Thanks.
Rajendra.
