Re: pg_stat_statements has duplicate entries for the same query & queryId
Jevon Cowell wrote: > What I'm seeing is that for the same query *and* query id, > there are two rows with different statistics data *at the same time*. For > example one row can have 2 calls while another can have 4 That looks normal. The unicity to expect is on (queryid, userid, dbid, toplevel). From https://www.postgresql.org/docs/current/pgstatstatements.html : This view contains one row for each distinct combination of database ID, user ID, query ID and whether it's a top-level statement or not (up to the maximum number of distinct statements that the module can track) Best regards, -- Daniel Vérité https://postgresql.verite.pro/
Re: Logical replication, need to reclaim big disk space
On 16/05/25 21:33, Achilleas Mantzios wrote: On 16/5/25 18:45, Moreno Andreo wrote: Hi, we are moving our old binary data approach, moving them from bytea fields in a table to external storage (making database smaller and related operations faster and smarter). In short, we have a job that runs in background and copies data from the table to an external file and then sets the bytea field to NULL. (UPDATE tbl SET blob = NULL, ref = 'path/to/file' WHERE id = ) This results, at the end of the operations, to a table that's less than one tenth in size. We have a multi-tenant architecture (100s of schemas with identical architecture, all inheriting from public) and we are performing the task on one table per schema. So? toasted data are kept on separate TOAST tables, unless those bytea cols are selected, you won't even touch them. I cannot understand what you are trying to achieve here. Years ago, when I made the mistake to go for a coffee and let my developers "improvise" , the result was a design similar to what you are trying to achieve. Years after, I am seriously considering moving those data back to PostgreSQL. The "related operations" I was talking about are backups and database maintenance when needed, cluster/replica management, etc. With a smaller database size they would be easier in timing and effort, right? We are mostly talking about costs, here. To give things their names, I'm moving bytea contents (85% of total data) to files into Google Cloud Storage buckets, that has a fraction of the cost of the disks holding my database (on GCE, to be clear ). This data is not accessed frequently (just by the owner when he needs to do it), so no need to keep it on expensive hardware. I've already read in these years that keeping many big bytea fields in databases is not recommended, but might have misunderstood this. Another way would have been to move these tables to a different tablespace, in cheaper storage, but it still would have been 3 times the buckets cost. Why are you considering to get data back to database tables? The problem is: this is generating BIG table bloat, as you may imagine. Running a VACUUM FULL on an ex-22GB table on a standalone test server is almost immediate. If I had only one server, I'll process a table a time, with a nightly script, and issue a VACUUM FULL to tables that have already been processed. But I'm in a logical replication architecture (we are using a multimaster system called pgEdge, but I don't think it will make big difference, since it's based on logical replication), and I'm building a test cluster. So you use PgEdge , but you wanna lose all the benefits of multi-master , since your binary data won't be replicated ... I don't think I need it to be replicated, since this data cannot be "edited", so either it's there or it's been deleted. Buckets have protections for data deletions or events like ransomware attacks and such. Also multi-master was an absolute requirement one year ago because of a project we were building, but it has been abandoned and now a simple logical replication would be enough, but let's do one thing a time. I've been instructed to issue VACUUM FULL on both nodes, nightly, but before proceeding I read on docs that VACUUM FULL can disrupt logical replication, so I'm a bit concerned on how to proceed. Rows are cleared one a time (one transaction, one row, to keep errors to the record that issued them) PgEdge is based on the old pg_logical, the old 2ndQuadrant extension, not the native logical replication we have since pgsql 10. But I might be mistaken. Don't know about this, it keeps running on latest pg versions (we are about to upgrade to 17.4, if I'm not wrong), but I'll ask I read about extensions like pg_squeeze, but I wonder if they are still not dangerous for replication. What's pgEdge take on that, I mean the bytea thing you are trying to achieve here. They are positive, it's they that suggested to do VACUUM FULL on both nodes... I'm quite new to replication, so I'm searching some advise here. Thanks for your help. Moreno.-
Re: Logical replication, need to reclaim big disk space
On 5/19/25 09:14, Moreno Andreo wrote: On 16/05/25 21:33, Achilleas Mantzios wrote: On 16/5/25 18:45, Moreno Andreo wrote: Hi, we are moving our old binary data approach, moving them from bytea fields in a table to external storage (making database smaller and related operations faster and smarter). In short, we have a job that runs in background and copies data from the table to an external file and then sets the bytea field to NULL. (UPDATE tbl SET blob = NULL, ref = 'path/to/file' WHERE id = ) This results, at the end of the operations, to a table that's less than one tenth in size. We have a multi-tenant architecture (100s of schemas with identical architecture, all inheriting from public) and we are performing the task on one table per schema. So? toasted data are kept on separate TOAST tables, unless those bytea cols are selected, you won't even touch them. I cannot understand what you are trying to achieve here. Years ago, when I made the mistake to go for a coffee and let my developers "improvise" , the result was a design similar to what you are trying to achieve. Years after, I am seriously considering moving those data back to PostgreSQL. The "related operations" I was talking about are backups and database maintenance when needed, cluster/replica management, etc. With a smaller database size they would be easier in timing and effort, right? Ok, but you'll lose replica functionality for those blobs, which means you don't care about them, correct me if I am wrong. We are mostly talking about costs, here. To give things their names, I'm moving bytea contents (85% of total data) to files into Google Cloud Storage buckets, that has a fraction of the cost of the disks holding my database (on GCE, to be clear ). May I ask the size of the bytea data (uncompressed) ?. This data is not accessed frequently (just by the owner when he needs to do it), so no need to keep it on expensive hardware. I've already read in these years that keeping many big bytea fields in databases is not recommended, but might have misunderstood this. Ok, I assume those are unimportant data, but let me ask, what is the longevity or expected legitimacy of those ? I haven't worked with those just reading : https://cloud.google.com/storage/pricing?_gl=1*1b25r8o*_up*MQ..&gclid=CjwKCAjwravBBhBjEiwAIr30VKfaOJytxmk7J29vjG4rBBkk2EUimPU5zPibST73nm3XRL2h0O9SxRoCaogQAvD_BwE&gclsrc=aw.ds#storage-pricing would you choose e.g. "*Anywhere Cache storage" ? * Another way would have been to move these tables to a different tablespace, in cheaper storage, but it still would have been 3 times the buckets cost. can you actually mount those Cloud Storage Buckets under a supported FS in linux and just move them to tablespaces backed by this storage ? Why are you considering to get data back to database tables? Because now if we need to migrate from cloud to on-premise, or just upgrade or move the specific server which holds those data I will have an extra headache. Also this is a single point of failure, or best case a cause for fragmented technology introduced just for the sake of keeping things out of the DB. The problem is: this is generating BIG table bloat, as you may imagine. Running a VACUUM FULL on an ex-22GB table on a standalone test server is almost immediate. If I had only one server, I'll process a table a time, with a nightly script, and issue a VACUUM FULL to tables that have already been processed. But I'm in a logical replication architecture (we are using a multimaster system called pgEdge, but I don't think it will make big difference, since it's based on logical replication), and I'm building a test cluster. So you use PgEdge , but you wanna lose all the benefits of multi-master , since your binary data won't be replicated ... I don't think I need it to be replicated, since this data cannot be "edited", so either it's there or it's been deleted. Buckets have protections for data deletions or events like ransomware attacks and such. Also multi-master was an absolute requirement one year ago because of a project we were building, but it has been abandoned and now a simple logical replication would be enough, but let's do one thing a time. Multi-master is cool, you can configure your pooler / clients to take advantage of this for full load balanced architecture, but if not a strict requirement , you can live without it, as so many of us, and employ other means of load balancing the reads. I've been instructed to issue VACUUM FULL on both nodes, nightly, but before proceeding I read on docs that VACUUM FULL can disrupt logical replication, so I'm a bit concerned on how to proceed. Rows are cleared one a time (one transaction, one row, to keep errors to the record that issued them) Mind if you shared the specific doc ? PgEdge is based on the old pg_logical, the old 2ndQuadrant extension, not the native logical replication we have since pgsql 10. But
Re: Logical replication, need to reclaim big disk space
On 19/05/25 14:41, Achilleas Mantzios wrote: On 5/19/25 09:14, Moreno Andreo wrote: On 16/05/25 21:33, Achilleas Mantzios wrote: On 16/5/25 18:45, Moreno Andreo wrote: Hi, we are moving our old binary data approach, moving them from bytea fields in a table to external storage (making database smaller and related operations faster and smarter). In short, we have a job that runs in background and copies data from the table to an external file and then sets the bytea field to NULL. (UPDATE tbl SET blob = NULL, ref = 'path/to/file' WHERE id = ) This results, at the end of the operations, to a table that's less than one tenth in size. We have a multi-tenant architecture (100s of schemas with identical architecture, all inheriting from public) and we are performing the task on one table per schema. So? toasted data are kept on separate TOAST tables, unless those bytea cols are selected, you won't even touch them. I cannot understand what you are trying to achieve here. Years ago, when I made the mistake to go for a coffee and let my developers "improvise" , the result was a design similar to what you are trying to achieve. Years after, I am seriously considering moving those data back to PostgreSQL. The "related operations" I was talking about are backups and database maintenance when needed, cluster/replica management, etc. With a smaller database size they would be easier in timing and effort, right? Ok, but you'll lose replica functionality for those blobs, which means you don't care about them, correct me if I am wrong. I'm not saying I don't care about them, the opposite, they are protected with Object Versioning and soft deletion, this should assure a good protection against e.g. ransomware, if someone manages to get in there (and if this happens, we'll have bigger troubles than this) We are mostly talking about costs, here. To give things their names, I'm moving bytea contents (85% of total data) to files into Google Cloud Storage buckets, that has a fraction of the cost of the disks holding my database (on GCE, to be clear ). May I ask the size of the bytea data (uncompressed) ?. single records vary from 150k to 80 MB, the grand total is more than 8,5 TB in a circa 10 TB data footprint This data is not accessed frequently (just by the owner when he needs to do it), so no need to keep it on expensive hardware. I've already read in these years that keeping many big bytea fields in databases is not recommended, but might have misunderstood this. Ok, I assume those are unimportant data, but let me ask, what is the longevity or expected legitimacy of those ? I haven't worked with those just reading : https://cloud.google.com/storage/pricing?_gl=1*1b25r8o*_up*MQ..&gclid=CjwKCAjwravBBhBjEiwAIr30VKfaOJytxmk7J29vjG4rBBkk2EUimPU5zPibST73nm3XRL2h0O9SxRoCaogQAvD_BwE&gclsrc=aw.ds#storage-pricing would you choose e.g. "*Anywhere Cache storage" ? * Absolutely not, this is *not* unimportant data, and we are using Standard Storage, for 0,02$/GB/month + operations, that compared to a 0.17$/GB/month of an SSD or even more for the Hyperdisks we are using, is a good price drop. ** Another way would have been to move these tables to a different tablespace, in cheaper storage, but it still would have been 3 times the buckets cost. can you actually mount those Cloud Storage Buckets under a supported FS in linux and just move them to tablespaces backed by this storage ? Never tried, I mounted this via FUSE and had some simple operations in the past, but not sure it can handle database operations in terms of I/O bandwidth Why are you considering to get data back to database tables? Because now if we need to migrate from cloud to on-premise, or just upgrade or move the specific server which holds those data I will have an extra headache. Also this is a single point of failure, or best case a cause for fragmented technology introduced just for the sake of keeping things out of the DB. This is managed as an hierarchical disk structure, so the calling server may be literally everywhere, it just needs an account (or a service account) to get in there The problem is: this is generating BIG table bloat, as you may imagine. Running a VACUUM FULL on an ex-22GB table on a standalone test server is almost immediate. If I had only one server, I'll process a table a time, with a nightly script, and issue a VACUUM FULL to tables that have already been processed. But I'm in a logical replication architecture (we are using a multimaster system called pgEdge, but I don't think it will make big difference, since it's based on logical replication), and I'm building a test cluster. So you use PgEdge , but you wanna lose all the benefits of multi-master , since your binary data won't be replicated ... I don't think I need it to be replicated, since this data cannot be "edited", so either it's there or it's been deleted. Buckets have protections for data de
get speed help
Hello all, Could i get a help, postgres 1 billion records indexed table, search speed in a normal machine, no parallel processing ... for a knowledge ?? regards, Dias Thomas
Re: get speed help
On Monday, May 19, 2025, Dias Thomas wrote: > Hello all, > Could i get a help, postgres 1 billion records indexed table, search > speed in a normal machine, no parallel processing ... for a knowledge ?? > https://wiki.postgresql.org/wiki/Slow_Query_Questions David J.
Re: get speed help
On Mon, May 19, 2025 at 12:12 PM Dias Thomas wrote: > Hello all, > Could i get a help, postgres 1 billion records indexed table, search > speed in a normal machine, no parallel processing ... for a knowledge ?? > 1. How big are the records? 2. How big are the keys? 3. What is a normal machine? 4. Why no parallel processing? -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: get speed help
Spinning rust? SSD? NVMe? SATA? SCSI? Fiber? Anyway... off the cuff, for 1 giga-rows, that's about a 50GB table. Small enough that you can easily test that yourself. On Mon, May 19, 2025 at 12:38 PM Dias Thomas wrote: > Hello, > 1 field 20 chars plus primary key id field - small table > indexed on that char - 20 field , say 16 gb ram, amd normal power processor > no parallel processing, to know it, the speed in single processor > > On Mon, May 19, 2025 at 9:55 PM Ron Johnson > wrote: > >> On Mon, May 19, 2025 at 12:12 PM Dias Thomas >> wrote: >> >>> Hello all, >>> Could i get a help, postgres 1 billion records indexed table, search >>> speed in a normal machine, no parallel processing ... for a knowledge ?? >>> >> >> 1. How big are the records? >> 2. How big are the keys? >> 3. What is a normal machine? >> 4. Why no parallel processing? >> >> -- >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: Logical replication, need to reclaim big disk space
On 19/5/25 17:38, Moreno Andreo wrote: On 19/05/25 14:41, Achilleas Mantzios wrote: On 5/19/25 09:14, Moreno Andreo wrote: On 16/05/25 21:33, Achilleas Mantzios wrote: On 16/5/25 18:45, Moreno Andreo wrote: Hi, we are moving our old binary data approach, moving them from bytea fields in a table to external storage (making database smaller and related operations faster and smarter). In short, we have a job that runs in background and copies data from the table to an external file and then sets the bytea field to NULL. (UPDATE tbl SET blob = NULL, ref = 'path/to/file' WHERE id = ) This results, at the end of the operations, to a table that's less than one tenth in size. We have a multi-tenant architecture (100s of schemas with identical architecture, all inheriting from public) and we are performing the task on one table per schema. So? toasted data are kept on separate TOAST tables, unless those bytea cols are selected, you won't even touch them. I cannot understand what you are trying to achieve here. Years ago, when I made the mistake to go for a coffee and let my developers "improvise" , the result was a design similar to what you are trying to achieve. Years after, I am seriously considering moving those data back to PostgreSQL. The "related operations" I was talking about are backups and database maintenance when needed, cluster/replica management, etc. With a smaller database size they would be easier in timing and effort, right? Ok, but you'll lose replica functionality for those blobs, which means you don't care about them, correct me if I am wrong. I'm not saying I don't care about them, the opposite, they are protected with Object Versioning and soft deletion, this should assure a good protection against e.g. ransomware, if someone manages to get in there (and if this happens, we'll have bigger troubles than this). PostgreSQL has become very popular because of ppl who care about their data. We are mostly talking about costs, here. To give things their names, I'm moving bytea contents (85% of total data) to files into Google Cloud Storage buckets, that has a fraction of the cost of the disks holding my database (on GCE, to be clear ). May I ask the size of the bytea data (uncompressed) ?. single records vary from 150k to 80 MB, the grand total is more than 8,5 TB in a circa 10 TB data footprint This data is not accessed frequently (just by the owner when he needs to do it), so no need to keep it on expensive hardware. I've already read in these years that keeping many big bytea fields in databases is not recommended, but might have misunderstood this. Ok, I assume those are unimportant data, but let me ask, what is the longevity or expected legitimacy of those ? I haven't worked with those just reading : https://cloud.google.com/storage/pricing?_gl=1*1b25r8o*_up*MQ..&gclid=CjwKCAjwravBBhBjEiwAIr30VKfaOJytxmk7J29vjG4rBBkk2EUimPU5zPibST73nm3XRL2h0O9SxRoCaogQAvD_BwE&gclsrc=aw.ds#storage-pricing would you choose e.g. "*Anywhere Cache storage" ? * Absolutely not, this is *not* unimportant data, and we are using Standard Storage, for 0,02$/GB/month + operations, that compared to a 0.17$/GB/month of an SSD or even more for the Hyperdisks we are using, is a good price drop. How about hosting your data in your own storage and spend 0$/GB/month ? ** Another way would have been to move these tables to a different tablespace, in cheaper storage, but it still would have been 3 times the buckets cost. can you actually mount those Cloud Storage Buckets under a supported FS in linux and just move them to tablespaces backed by this storage ? Never tried, I mounted this via FUSE and had some simple operations in the past, but not sure it can handle database operations in terms of I/O bandwidth Why are you considering to get data back to database tables? Because now if we need to migrate from cloud to on-premise, or just upgrade or move the specific server which holds those data I will have an extra headache. Also this is a single point of failure, or best case a cause for fragmented technology introduced just for the sake of keeping things out of the DB. This is managed as an hierarchical disk structure, so the calling server may be literally everywhere, it just needs an account (or a service account) to get in there , and you are locked in a proprietary solution. and at their mercy of any future increases in cost. The problem is: this is generating BIG table bloat, as you may imagine. Running a VACUUM FULL on an ex-22GB table on a standalone test server is almost immediate. If I had only one server, I'll process a table a time, with a nightly script, and issue a VACUUM FULL to tables that have already been processed. But I'm in a logical replication architecture (we are using a multimaster system called pgEdge, but I don't think it will make big difference, since it's based on logical replication), and I'm building a te
Re: get speed help
Ok? Honestly, your questions puzzle me: PG is open source, binaries are available for Windows, every Linux distro, and even BSD. And *the database is pretty small*. Install it, and *test it yourself*. Then you'll have the answer. On Mon, May 19, 2025 at 12:57 PM Dias Thomas wrote: > Hi, > I mean any manual > I have a small code, that searches some 1 billion records in 1 > seconds thru index simple case -- I want to test that with postgres > > > On Mon, May 19, 2025 at 10:14 PM Ron Johnson > wrote: > >> Spinning rust? SSD? NVMe? SATA? SCSI? Fiber? >> >> Anyway... off the cuff, for 1 giga-rows, that's about a 50GB table. >> Small enough that you can easily test that yourself. >> >> On Mon, May 19, 2025 at 12:38 PM Dias Thomas >> wrote: >> >>> Hello, >>> 1 field 20 chars plus primary key id field - small table >>> indexed on that char - 20 field , say 16 gb ram, amd normal power >>> processor >>> no parallel processing, to know it, the speed in single processor >>> >>> On Mon, May 19, 2025 at 9:55 PM Ron Johnson >>> wrote: >>> On Mon, May 19, 2025 at 12:12 PM Dias Thomas wrote: > Hello all, > Could i get a help, postgres 1 billion records indexed table, search > speed in a normal machine, no parallel processing ... for a knowledge ?? > 1. How big are the records? 2. How big are the keys? 3. What is a normal machine? 4. Why no parallel processing? -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster! >>> >> >> -- >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: pg_stat_statements has duplicate entries for the same query & queryId
On Mon, May 19, 2025 at 09:07:35AM +0200, Daniel Verite wrote: > That looks normal. The unicity to expect is on > (queryid, userid, dbid, toplevel). > > From https://www.postgresql.org/docs/current/pgstatstatements.html : > > This view contains one row for each distinct combination of database > ID, user ID, query ID and whether it's a top-level statement or not > (up to the maximum number of distinct statements that the module can > track) Yeah. The uniqueness of the entries in the PGSS hash table is enforced by pgssHashKey in pg_stat_statements.c. Or an unknown bug has been found, but there is no data proving that here. -- Michael signature.asc Description: PGP signature
