Postgresql 13 partitioning advice
We have a Postgresql 13 database where we have a single table with several millions of rows . We plan to partition it based on timestamp . We have been seeking advice for best practices for building this. This table will get lots of updates for the same rows during a short period of time.During this time rows would be in a single partition . After this short time these rows would move to another partition .Where no more updates take place on these rows.But might have some SELECT queries running. We plan to l have partitions based on months and then roll them up in a year and then archive these older partitions One consultant we talked with told us this row movement between the partitions will have huge complications .But this was an issue during the Postgres 10 version . So we are seeking advice on the performance perspective and things we should take care of along with manual vacuums on a regular schedule and indexing. Are there any tunables I should experiment with in particular ?
Re: Postgresql 13 partitioning advice
On Tue, 2 Aug 2022 at 19:48, Ameya Bidwalkar wrote: > We have a Postgresql 13 database where we have a single table with several > millions of rows . We plan to partition it based on timestamp . > We have been seeking advice for best practices for building this. > This table will get lots of updates for the same rows during a short period > of time.During this time rows would be in a single partition . > After this short time these rows would move to another partition .Where no > more updates take place on these rows.But might have some SELECT queries > running. > We plan to l have partitions based on months and then roll them up in a year > and then archive these older partitions > One consultant we talked with told us this row movement between the > partitions will have > huge complications .But this was an issue during the Postgres 10 version . Define "huge complications"? The capabilities of partitioned tables have changed quite a bit since the feature was added. It's very easy for knowledge to get out-dated in this area. I did quite a bit of work on them and I struggle to remember off the top of my head which versions saw which improvements. PG12 saw lots. See [1], search for "partition". One possible complication is what is mentioned in [2] about "serialization failure error". UPDATEs that cause a tuple to move to another partition can cause a serialization failure at transaction isolation level, not just serializable transactions. If it's not already, you might want to have your application retry transactions on SQL:40001 errors. Apart from that, assuming there's comparatively a small number of rows in the partition being updated compared to the partition with the static rows, then it sounds fairly efficient. As you describe it, the larger static partition is effectively INSERT only and auto-vacuum will need to touch it only for tuple freezing work. The smaller of the two tables will receive more churn but will be faster to vacuum. PG13 got a new feature that makes sure auto-vacuum also does the rounds on INSERT-only tables too, so the static partition is not going to be neglected until anti-wrap-around-autovacuums trigger, like they would have in PG12 and earlier. Another thing to consider is that an UPDATE of a non-partitioned table has a chance at being a HOT update. That's possible if the tuple can fit on the same page and does not update any of the indexed columns. A HOT update means no indexes need to be updated so these perform faster and require less space in WAL than a non-HOT update. An UPDATE that moves a tuple to another partition can never be a HOT update. That's something you might want to consider. If you're updating indexed columns already then it's not a factor to consider.There's also overhead to postgres having to find the partition for the newly updated version of the tuple. That's not hugely expensive, but it's generally measurable. RANGE partitioned tables with a large number of partitions will have the most overhead for this. HASH partitioned tables, the least. The best thing you can likely do is set up a scenario with pgbench and compare the performance. pgbench is a pretty flexible tool that will allow you to run certain queries X% of the time and even throttle the workload at what you expect your production server to experience. You could then run it overnight on a test server, or even for weeks and see how auto-vacuum keeps up when compared to the non-partitioned case. You can also check how much extra WAL is generated vs the non-partitioned case. > So we are seeking advice on the performance perspective and things we should > take care of along with manual vacuums on a regular schedule and indexing. > Are there any tunables I should experiment with in particular ? Perhaps if you want to keep a small high-chun table in check you might want to consider if autovacuum_naptime is set low enough. You may not care if the space being consumed in the standard 1min autovacuum_naptime is small enough not to be of concern. David [1] https://www.postgresql.org/docs/release/12.0/ [2] https://www.postgresql.org/docs/13/sql-update.html
Re: Postgresql 14 partitioning advice
On Mon, Aug 1, 2022 at 10:16 AM Rick Otten wrote: > >> The other problem I ran into, which I'm still building a test case for >> and I fear might be a bug if I can easily reproduce it, >> is if I did the original select in a CTE, and then did a sort outside of >> the CTE, even though the CTE found 0 rows, the database >> still spent a _ton_ of time sorting those 0 rows: >> ``` >>-> Sort (cost=70.03..72.53 rows=1000 width=112) (actual >> time=84848.452..84848.453 rows=0 loops=1) >> ``` >> Once I can reproduce this on test data I'll be able to pin down more >> closely what is happening and tell if I'm just reading >> the explain plan wrong or if something is broken. It was getting mixed >> up with the lack of pruning/index usage problem. >> >> I'll report back again next week. Anyway it is looking to me like it >> doesn't really matter (within reason) from a performance >> perspective how many partitions we use for our data set and query >> patterns. We should be able to pick the most convenient >> from an archiving and data management perspective instead. >> >> > This behavior is definitely consistent. 0 rows end up slower than when I > find some rows in my CTE: > ``` >-> Sort (cost=109.44..113.19 rows=1500 width=112) (actual > time=87110.841..87110.842 rows=0 loops=1) >-> Sort (cost=109.44..113.19 rows=1500 width=112) (actual > time=25367.867..25367.930 rows=840 loops=1) > ``` > The only thing I changed in the query was the date range. It is actually > the CTE scan step inside the Sort block that is slower when no rows are > returned than when rows are returned. It also only happens when all the > partitions are sequence scanned instead of being partition pruned. > > I'm still writing up a test case that can demo this without using > proprietary data. > After a bunch of experiments I can explain this now. :-) I had a `limit` clause in my test CTE. When sequence scanning a bunch of partitions, if the limit is reached, the subsequent partitions are marked with `never executed` and not scanned. On the other hand, when no rows are found, all of the partitions are scanned. Therefore, with many millions of rows in the partitions, and being forced to sequence scan because I put the `at time zone` clause in the `where`, the case when rows are found is always noticeably faster than the case when rows aren't found as long as at least one partition hasn't been scanned yet when the limit is hit. I'm now satisfied this is a good thing, and will move on to other problems. Thanks for hearing me out. I was scratching my head for a while over that one.
Re: PgSQL 14 - Logical Rep - Single table multiple publications?
On 02/08/22, Robert Blayzor ([email protected]) wrote: > Is it possible to have a single subscriber table contact multiple publishers > and just insert all of the data into a single table on the subscriber? ie: > merge type replication. There are no primary/FK constraints, etc. The > records are just time based audit log type data... Your use case meets, I think, the third "typical use case" listed at https://www.postgresql.org/docs/current/logical-replication.html, namely "Consolidating multiple databases into a single one (for example for analytical purposes)." I've just been testing aggregating all the data in one schema across 300 publisher databases into 5 subscriber schemas on two Postgresql 14 clusters on the same machine. Each of 60 publisher tables are aggregating into a single table on the subscriber. Special care must be taken with the "replica identity" of published tables, as set out at https://www.postgresql.org/docs/current/logical-replication-publication.html. For example, you may need a unique identifying column for each source table in addition to the normal row identifier to differentiate *this* table's id 1 row from the *other* table's id 1 row, otherwise the subscriber won't be able to identify the row to delete if this table's id 1 row is deleted (for example). Although this seems to work fine with native replication, the pglogical extension has more knobs. For instance, the pglogical.wait_for_subscription_sync_complete function is useful to ensure that sync finishes when part of a migration. Rory
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
Hello.
After more investigation, we found that pgss_query_texts.stat of a size of
2.2GB. and this deployment has a 32bit pg.
and this errors:
*postgresql-2022-07-12-20:07:15.log.gz:[2022-07-14 11:17:06.713 EDT]
207.89.58.230(46964) {62c87db0.8eb2} LOG: out of
memorypostgresql-2022-07-12-20:07:15.log.gz:[2022-07-14 11:17:06.713 EDT]
207.89.58.230(46964) {62c87db0.8eb2} DETAIL: Could not allocate
enough memory to read pg_stat_statement file
"pg_stat_tmp/pgss_query_texts.stat".*
So, my question is if pgss_query_texts.stat increases in size gradually due
to too many distincts large sql statements could it cause an overall
slowness on the engine? this slowness could cause simple statements to be
super slow to return like
"select now()" taking 20s?
Thanks in advance
Environment:
OS/version: CentOS release 6.9 (Final)
Hardware(non dedicated to the db, other services and app run the same
server):
Xeon(R) CPU E5-2690 v4 @ 2.60GHz - 56 cores - 504 GB RAM
logicaldrive 1 (1.5 TB, RAID 1, OK)
physicaldrive 1I:3:1 (port 1I:box 3:bay 1, Solid State SAS, 1600.3 GB, OK)
physicaldrive 1I:3:2 (port 1I:box 3:bay 2, Solid State SAS, 1600.3 GB, OK)
PostgresSQL 9.5.21 32bit
GUC Settings:
auto_explain.log_analyze 0
auto_explain.log_min_duration 1000
auto_explain.log_nested_statements 0
auto_explain.log_verbose 0
autovacuum_analyze_scale_factor0.1
autovacuum_analyze_threshold50
autovacuum_freeze_max_age2
autovacuum_max_workers 3
autovacuum_multixact_freeze_max_age 4
autovacuum_naptime 60
autovacuum_vacuum_cost_delay 2
autovacuum_vacuum_cost_limit 100
autovacuum_vacuum_scale_factor 0.1
autovacuum_vacuum_threshold 50
autovacuum_work_mem -1
checkpoint_timeout2700
effective_cache_size 4194304
enable_seqscan 0
log_autovacuum_min_duration250
log_checkpoints 1
log_connections 1
log_file_mode 600
log_lock_waits 1
log_min_duration_statement1000
log_rotation_age 1440
log_truncate_on_rotation 1
maintenance_work_mem262144
max_connections300
max_replication_slots 10
max_wal_senders 10
max_wal_size 1280
max_worker_processes15
min_wal_size 5
pg_stat_statements.max 1
standard_conforming_strings 1
track_commit_timestamp 1
wal_receiver_timeout 0
wal_sender_timeout 0
work_mem 8192
On Thu, Jul 21, 2022 at 2:37 PM bruno da silva wrote:
> Hello.
>
> I'm investigating an issue on a PostgresSql 9.5.21 installation that
> becomes unusable in an intermittent way. Simple queries like "select
> now();" could take 20s. commits take 2s. and all gets fixed after an engine
> restart.
>
> I look into the pg logs and no signs of errors. and checkpoints are
> always timed. The machine is well provisioned, load isn't too high, and cpu
> io wait is under 1%.
>
> any suggestions on what I should check more?
>
>
> Thanks in advance.
> --
> Bruno da Silva
>
--
Bruno da Silva
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
bruno da silva writes: > After more investigation, we found that pgss_query_texts.stat of a size of > 2.2GB. and this deployment has a 32bit pg. Hm ... we've heard one previous report of pg_stat_statements' query text file getting unreasonably large, but it's not clear how that can come to be. Do you have a lot of especially long statements being tracked in the pg_stat_statements view? Are there any other signs of distress in the postmaster log, like complaints about being unable to write pgss_query_texts.stat? regards, tom lane
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
Do you have a lot of especially long statements being tracked in the pg_stat_statements view?* well, the view was showing the query column null.* * but looking on pgss_query_texts.stat there are very large sql statements, of around ~ 400kb, multiple thousands. * Are there any other signs of distress in the postmaster log, like complaints about being unable to write pgss_query_texts.stat? *no, just complaints for reading it. * Thanks On Tue, Aug 2, 2022 at 11:59 AM Tom Lane wrote: > bruno da silva writes: > > After more investigation, we found that pgss_query_texts.stat of a size > of > > 2.2GB. and this deployment has a 32bit pg. > > Hm ... we've heard one previous report of pg_stat_statements' query text > file getting unreasonably large, but it's not clear how that can come > to be. Do you have a lot of especially long statements being tracked > in the pg_stat_statements view? Are there any other signs of distress > in the postmaster log, like complaints about being unable to write > pgss_query_texts.stat? > > regards, tom lane > -- Bruno da Silva
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
Hello. Are you quite sure this is a 9.5.21 version of the pg_stat_statements extension? *I got version 1.3 from SELECT * FROM pg_extension;* Is it possible that the pg_stat_tmp directory has been made non-writable? *hard to tell if it was made non-writable during the outage. but now it is writable.* Thanks On Tue, Aug 2, 2022 at 1:25 PM Tom Lane wrote: > bruno da silva writes: > > Do you have a lot of especially long statements being tracked > > in the pg_stat_statements view?* well, the view was showing the query > > column null.* > > * but looking on pgss_query_texts.stat there are very large sql > > statements, of around ~ 400kb, multiple thousands. * > > Hm. We try to recover from such failures by (a) resetting all the view's > query text fields to null and (b) truncating the file --- well, unlinking > it and creating it as empty. It seems like (a) happened and (b) didn't. > It's pretty hard to explain that from the code though. Are you quite > sure this is a 9.5.21 version of the pg_stat_statements extension? > Is it possible that the pg_stat_tmp directory has been made non-writable? > > regards, tom lane > > > > > > Are there any other signs of distress > > in the postmaster log, like complaints about being unable to write > > pgss_query_texts.stat? *no, just complaints for reading it. * > > > Thanks > > > On Tue, Aug 2, 2022 at 11:59 AM Tom Lane wrote: > > >> bruno da silva writes: > > After more investigation, we found that pgss_query_texts.stat of a size > >> of > > 2.2GB. and this deployment has a 32bit pg. > >> > >> Hm ... we've heard one previous report of pg_stat_statements' query text > >> file getting unreasonably large, but it's not clear how that can come > >> to be. Do you have a lot of especially long statements being tracked > >> in the pg_stat_statements view? Are there any other signs of distress > >> in the postmaster log, like complaints about being unable to write > >> pgss_query_texts.stat? > >> > >> regards, tom lane > >> > > > > -- > > Bruno da Silva > > -- Bruno da Silva
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
bruno da silva writes: > Do you have a lot of especially long statements being tracked > in the pg_stat_statements view?* well, the view was showing the query > column null.* > * but looking on pgss_query_texts.stat there are very large sql > statements, of around ~ 400kb, multiple thousands. * Hm. We try to recover from such failures by (a) resetting all the view's query text fields to null and (b) truncating the file --- well, unlinking it and creating it as empty. It seems like (a) happened and (b) didn't. It's pretty hard to explain that from the code though. Are you quite sure this is a 9.5.21 version of the pg_stat_statements extension? Is it possible that the pg_stat_tmp directory has been made non-writable? regards, tom lane > Are there any other signs of distress > in the postmaster log, like complaints about being unable to write > pgss_query_texts.stat? *no, just complaints for reading it. * > Thanks > On Tue, Aug 2, 2022 at 11:59 AM Tom Lane wrote: >> bruno da silva writes: > After more investigation, we found that pgss_query_texts.stat of a size >> of > 2.2GB. and this deployment has a 32bit pg. >> >> Hm ... we've heard one previous report of pg_stat_statements' query text >> file getting unreasonably large, but it's not clear how that can come >> to be. Do you have a lot of especially long statements being tracked >> in the pg_stat_statements view? Are there any other signs of distress >> in the postmaster log, like complaints about being unable to write >> pgss_query_texts.stat? >> >> regards, tom lane >> > -- > Bruno da Silva
Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries
I wrote: > bruno da silva writes: >> Do you have a lot of especially long statements being tracked >> in the pg_stat_statements view?* well, the view was showing the query >> column null.* >> * but looking on pgss_query_texts.stat there are very large sql >> statements, of around ~ 400kb, multiple thousands. * I see one possible piece of the puzzle here: since you're using a 32-bit build, overflowing size_t is a reachable hazard. Specifically, in this test to see if we need to garbage-collect the query text file: if (extent < pgss->mean_query_len * pgss_max * 2) return false; You said earlier that pg_stat_statements.max = 1, so a mean_query_len exceeding about 2^32 / 1 / 2 = 214748.3648 would be enough to overflow size_t and break this comparison. Now, a mean SQL query length in excess of 200kB sounds mighty improbable, but it's really the mean length of the query texts in the view. If your "normal" queries fall into just a few patterns they might be represented by a relatively small number of view entries. And if the "big" queries are sufficiently not alike, they might each get their own view entry, which could potentially drive the mean high enough to cause trouble. It'd be interesting to track what "SELECT avg(length(query)) FROM pg_stat_statements" gives. However, even if we grant that mean_query_len is that big, overflow here would make garbage collection of the query text file more likely not less so. What I'm speculating is that overflow is occurring and causing all processes to decide they need to run gc_qtexts() every time they insert a new query entry, even though the query texts file isn't actually bloated. That could possibly explain your performance issues: a garbage collection pass over a multi-gig file will take awhile, and what's worse is that it's done under an exclusive lock, meaning that all the backends stack up waiting their turn to perform a useless GC pass. What this doesn't explain is why the condition doesn't clear once you observe one of those "out of memory" complaints, because that should lead to truncating the texts file. Maybe it does get truncated, but then the cycle repeats after awhile? If you have a steady stream of incoming new 400kB queries, you could build back up to 2.2GB of text after five thousand or so of those. I'm also curious whether this installation is in the habit of doing pg_stat_statements_reset() a lot. It looks like that fails to reset mean_query_len, which might be intentional but perhaps it could play into getting a silly result here later on. regards, tom lane
Re: PgSQL 14 - Logical Rep - Single table multiple publications?
On 02/08/22, Robert Blayzor ([email protected]) wrote: > On 8/2/22 10:57, Rory Campbell-Lange wrote: > > Special care must be taken with the "replica identity" of published > > tables, as set out at > > https://www.postgresql.org/docs/current/logical-replication-publication.html. > > We would literally just be merging bulk data rows that are considered > immutable, meaning they would never be updated or deleted. We would > replicate only inserts, not deletes, updates, etc. > > Would the table identifier still be required in this case? On the page referenced above is the following: "INSERT operations can proceed regardless of any replica identity." So you should be good. Rory
