PostgreSQL

03 Jul 2025 10:01

That's quite significant, eh?

PostgreSQL

03 Jul 2025 09:38

Try

 select count(*), avg(pg_column_size(your_column)*1.0 / pg_column_size(your_column||'')) from your_table;

PostgreSQL

03 Jul 2025 09:36

mostly its the text,i dont have any blobs. but some of the tables have text column with very huge data

PostgreSQL

03 Jul 2025 09:26

What's the nature of the data? Maybe it's just TOAST compression that you benefit from?

PostgreSQL

03 Jul 2025 09:18

Probably each entry has its own number in those COC papers.

PostgreSQL

03 Jul 2025 09:16

Sounds like the same exact db every single insurance company used when i was looking for my car insurance

PostgreSQL

03 Jul 2025 09:10

LOL

"For manager
Database in Excel (xslx)"

PostgreSQL

03 Jul 2025 08:13

Hi guy in making a reseller app for cars and im thibking how to implement the car database
I got 50k models but they are just the base models and you know every car has some different version with different year of production, different transmisison etc… Should i let the user add these things or should i add them to the databse
Imagining that every car has atleast 4 different versione would make my db like >200k and i dont think its the best way cause the user may not be selling a version that’s in the db and other stuff like this

PostgreSQL

03 Jul 2025 00:45

I'm trying to make sense of a bunch of pg_wal files, trying to read them internally to extract transactions.

So far, i've divided everything in 8kb pages, and indeed every page starts with 0xD116 (XLOG_PAGE_MAGIC) so at least i assume pages are correctly split.

However, most of the pages i see have XLP_FIRST_IS_CONTRECORD set, including the first page of every wal file. Is this supposed to happen or am i reading the flags wrongly?

Looking later at the XLogRecord header, i see the total length hovering between 7400 and 7600 on most entries, but xl_xid is 0 and xl_prev is set to what looks like an arbitrary number.

Perhaps i'm missing something (some offset, somewhere?)

My steps are:
- take a wal file
- divide it in 8kbit pages
- read the first two bytes of every page (assert == 0xD116)
- then, read the rest of XLog[Long]PageHeaderData (either 16 or 32 bytes)
- then, read XLogRecord (first two bytes are length, then two bytes of xl_xid, then 8 bytes of xl_prev as per https://github.com/postgres/postgres/blob/fe05430ace8e0b3c945cf581564458a5983a07b6/src/include/access/xlogrecord.h#L41 )

PostgreSQL

02 Jul 2025 11:12

AFAIK
It's recommended to do 'auto-vacuum' periodically, after large data deletion 'vacuum full' or to use 'pg_repack' to rebuild the objects and remove bloat.

PostgreSQL

01 Jul 2025 13:35

So you're missing more than a year of patches and bugfixes. I'd address that first... as you see regular restarts, shouldn't take long to switch to the current binaries ;-)

PostgreSQL

01 Jul 2025 13:24

The issue is generated by a procedure. Whenever the procedure is running, it is resulting into this issue. Identified the proc and informed the application team.

PostgreSQL

01 Jul 2025 12:58

Any exotic extensions you're using? Are you on the latest minor release?

PostgreSQL

30 Jun 2025 18:33

PostgreSQL Person of the Week interview with: Samed YILDIRIM

https://postgresql.life/post/samed_yildirim/

PostgreSQL

28 Jun 2025 23:06

That would still need the catalog. Trust me... been there, done that, got the t-shirt

PostgreSQL

03 Jul 2025 09:44

count|avg                   |
-----+----------------------+
 2491|0.42825269393857546050|

count |avg                   |
------+----------------------+
315660|0.34580328641470171779|

PostgreSQL

03 Jul 2025 09:36

like base64 attachnents in it

PostgreSQL

03 Jul 2025 09:32

Probably MSSQL is heavily bloated, index fragmentation can quickly double physical size. Compare both sides after subtracting fragmentation effect.

PostgreSQL

03 Jul 2025 09:19

Hi, size of data column in above in mssql shows 48gb now (i havent shrink yet) , but overall sizeof postgresql is 24gb only

PostgreSQL

03 Jul 2025 09:17

Even they probably just bought that data 🤷‍♂

PostgreSQL

03 Jul 2025 09:14

I've been working on such a thing ~ 20 years ago, and you should definitly buy the data.
I - illegaly - scraped the DAT database (which was ugly on top of everything, it had a row for each flavor, so one row was "VW Golf", on row "VW Golf Roland Garros", one row "VW Golf Roland Garros 1.6" etc.pp. you get the idea).
Never got to the point where the DB needed an update. Or actually managed to avoid that point until the startup I did that for went broke... 🫣

PostgreSQL

03 Jul 2025 08:57

Keep data synchronized via another service/api?
May be a solution?
Something like this https://car2db.com/
Then you don't have to care about that anymore.
My 2 cents.

PostgreSQL

03 Jul 2025 02:47

1. Is it an overall size of whole database? Including transactional logs? What about only data (mdb files)?
I dont know amazon, but is there shrink operation there? If it is, can you perform it?

2. check size of data by tables in ms sql by do EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]';
if size of its like to the size in pg tables then all ok. It is bloat of ms sql )

PostgreSQL

02 Jul 2025 13:04

folks, read that again. The bloat would have been on $QL $erver side...

IIRC, it is very common in $$ to use covering indexes. Maybe that's where your space went earlier?

PostgreSQL

02 Jul 2025 07:49

Hi, i am not sure if i am doing something wrong or is it normal, i used amazon database migration service to move data from mssql (microsoft) to postgres and size of database became from 128gb to 25gb, i cross checked some data and its fine, so wondering whats the reason behind it if its abnormal or is it fine

PostgreSQL

01 Jul 2025 13:24

But not getting why its execution is resulting into this issue.

PostgreSQL

01 Jul 2025 13:08

Also helpful is to identify failing statement.

Either enable statements log — should be there as the last statement of the pid received sig11. But statements log may be huge and it's writing may slowdown the server.

Or identify the client by pid/host/port and check errors of the cluent app — it should report server dusconnect while executing thefailed statement.

PostgreSQL

01 Jul 2025 12:32

Hello experts.

One of the database is terminating connections with log message:

LOG: server process was terminated by signal 11: Segmentation fault

It is terminating all the database connections putting database in recovery mode.

Any suggestion will be helpful. Thank you.

PostgreSQL

29 Jun 2025 19:24

Hi Guys, has anyone used pgloader to transfer data from microsoft sqlserver to postgresql, does it resume automatically when i abort the data copying and rerun same command again?

PostgreSQL

28 Jun 2025 11:48

Still undecided if i should try to put files back and fake missing ones, or just straight up write a python parser of the page files ( https://www.postgresql.org/docs/17/storage-page-layout.html )