PostgreSQL

20 Sep 2024 23:34

I mean it's storage could pack the data by uri grouping rather effectively.

Also, some partition improvements in the timescale code may be good for you.

PostgreSQL

20 Sep 2024 23:27

But i don't care when the page was crawled actually

PostgreSQL

20 Sep 2024 22:36

Is there an order by 'url' in your query?

PostgreSQL

20 Sep 2024 21:05

Btree are always used

PostgreSQL

20 Sep 2024 17:59

Ideally, with a great performance improvement with huge data, hash index seems good (seems)

PostgreSQL

20 Sep 2024 17:50

Just curious: index on uri is a btree, hash or other type? If not hash, can a hash index increase performance without using partitioning method?
Just curious 😅

PostgreSQL

20 Sep 2024 13:18

yes thats client side error, but serves has plenty of memory

PostgreSQL

19 Sep 2024 15:51

You're aware that that's psql running into issues, right? shared_buffers don't matter...

PostgreSQL

19 Sep 2024 15:02

hi everyone, I have 5gb matherialized view, when quring via psql I get out of memory for query result, shared buffer 16gb

PostgreSQL

19 Sep 2024 13:47

Each function as a separate SQL file in Git, a bash script to build the schema update file.

PostgreSQL

19 Sep 2024 12:57

There's obviously some garbage in your source data, or you misconfigured either the export or import config. My guess is that at some place, a NULL gets converted to a binary 0 and that's in turn sent as a text.

PostgreSQL

19 Sep 2024 12:51

Yes exactly, do u know if I used schema-based sharding, certainly it will divide the database into different folders within the same data of the database across the workers nodes. Now, if I want to have an index for example to implement, will be still effective as the physical data is scattered so maybe a portion of your index data is found on worker node 1 and other on node 2, so it won't make sense, right?

PostgreSQL

19 Sep 2024 12:42

Is it possible to apply database sharding to postgreSQL? Having two cluster that working as a primary and divide the workload on both?

PostgreSQL

19 Sep 2024 08:26

Hi Everyone..I am doing migration by using the Ora2pg tool ..i am facing an issue and the error is as follows.
-------------------------------------------------------------
DBD::pg:: db pg_putcopyend failed :ERROR: invalid byte sequence for encoding "UTF8": 0x00vg : 11 recs/sec
Context: copy content, line 1 at /usr/local/share/perl5/Ora2pg.pm line 16849
FATAL : ERROR : invalid byte sequence for encoding "UTF8": 0x00
_-------_----------------------------------------------------

PostgreSQL

19 Sep 2024 07:51

Do you have any commands for that, that will be useful?

PostgreSQL

20 Sep 2024 23:27

Then it prefers a scan which baffles me

PostgreSQL

20 Sep 2024 23:26

Timescale and uri grouping?

PostgreSQL

20 Sep 2024 22:34

Doh! Seems that postgresql still prefer btree into equal comparison. Temporary remove the btree index (is it a composite or partial index?) and see what happens could a viable way without impacting your system?

PostgreSQL

20 Sep 2024 21:05

The fact is that no matter what I do postgresql seems to straight skip the hash indexes

PostgreSQL

20 Sep 2024 17:58

I've not a 2TB url table to test but googling I've found this article. I'm not sure can fit your data but generally urls are long.
https://evgeniydemin.medium.com/postgresql-indexes-hash-vs-b-tree-84b4f6aa6d61

PostgreSQL

20 Sep 2024 15:48

I have a table full of webpage snapshots, with the following columns:
- crawl_id = unique id of the webpage snapshot
- uri = url of the webpage (not unique)
- taken_on = timestamp of the snapshot

99% of the lookups are done by exact matching of uri. The table is quite big (2TB) and i would like to partition it. Is hash really the only way here with a sensible number of partitions considering my memory?

PostgreSQL

20 Sep 2024 04:51

Hello and welcome to this chat. Here we discuss PostgreSQL and provide best-effort voluntary support on Postgresql issues.
If you have any question, try asking kindly, including log files, error descriptions and perhaps what you're trying to do.

- 🚫 photos and screenshots
- 🚫 spam and offtopic
- ✅ questions with log files and clear explanation of what's gone wrong

⬇️ Press the button down here ⬇️

PostgreSQL

19 Sep 2024 15:04

Im quering it inside server via local domain socket

PostgreSQL

19 Sep 2024 14:08

Without looking at the actual data? 🤷

PostgreSQL

19 Sep 2024 13:28

So do u have any alternative to eradicate it.

PostgreSQL

19 Sep 2024 12:55

AFAIKT Citus gets told what to distribute how, incl. Indexes etc.
Anyway, if you already know you'll have data distributed in a bad way, maybe sharding is the wrong approach altogether?

Also, using partitioning and postgres_fdw seems to be a feasible option nowadays. As you need to spend time on thinking about how to split your data anyway, consider this as well.

PostgreSQL

19 Sep 2024 12:46

Not natively, you'd use something like citus

PostgreSQL

19 Sep 2024 08:27

Doing the migration from oracle to postgresql.

PostgreSQL

19 Sep 2024 08:22

How do you guys store/manage your procedures?
Using Git or something else?

PostgreSQL

18 Sep 2024 18:59

RDS manages binary replication for you, and it sets up WAL archiving as part of it. You don't have any real control over the precise configuration of binary replication: you get what RDS sets up for you.