# Error using PostgreSQL as database backend with Gramps v5.2.2

**URL:** <https://gramps.discourse.group/t/error-using-postgresql-as-database-backend-with-gramps-v5-2-2/5294>\
**Category:** Help\
**Created:** [April 21, 2024, 12:11pm UTC](https://gramps.discourse.group/t/error-using-postgresql-as-database-backend-with-gramps-v5-2-2/5294 "2024-04-21T12:11:36Z")\
**Posts on this page:** 1\
**Showing post:** 46

<div class="post-metadata">

**Author:** ![DavidMStraub](https://yyz2.discourse-cdn.com/free1/user_avatar/gramps.discourse.group/davidmstraub/32/7_2.png) [@DavidMStraub](https://gramps.discourse.group/u/DavidMStraub)\
**Post date:** [April 27, 2024, 11:09am UTC](https://gramps.discourse.group/t/error-using-postgresql-as-database-backend-with-gramps-v5-2-2/5294/46 "2024-04-27T11:09:28Z")

</div>

Oh, interesting discussion going on here that I missed so far.

@UlrichDemlehner, @Nick-Hall, @StoltHD - a lot of the poor scaling behaviour that shows up when switching to Postgres from SQLite has nothing to do with the way the Gramps database is structured or whether we use Blobs or JSON. It is simply due to the fact that there is lots of code in Gramps where there is a loop over individual database calls to every single object in the Gramps database, which can lead to several thousand individual `SELECT` statements, such that even a very small network lag (since Postgres uses a server/client architecture while SQLite is just a file) can lead to a delay of minutes.

See for example my comment here: [Should the Plugin Registration fail gracefully for missing Prerequisites? - #14 by DavidMStraub](https://gramps.discourse.group/t/should-the-plugin-registration-fail-gracefully-for-missing-prerequisites/5147/14)

… as well as this Gramps Web API issue, which was making filtering/sorting on Postgres unbearably slow:

> <https://github.com/gramps-project/gramps-web-api/issues/433>
>
> Investigating performance bottlenecks, I realized queries that involve a sort or… a date filter can be extremely slow.
> 
> This applies for instance for the query used for the anniversaries widget or the selection of the most recent blog post (which requires sorting all sources by date first), but also for all object list views.
> 
> The reason for the slowness is that we are sorting a list of handles by a function acting on the object, and fetching the object for the handle requires one round-trip to the database, so for thousands of objects these are thousands of database queries. This is not an issue for a SQLite on a local machine, but when using a database accessed via the network (as will be the case for any medium sized deployment), even a latency of a couple of milliseconds per query can lead to many seconds of waiting time for a simple sort.
> 
> The proper solution for implementing performant sort would be to use the auxiliary database columns, but this is not implemented in Gramps, and I don't think we should start reimplenting that logic outside of Gramps.
> 
> So for the moment, I think the best solution would be to simply sort objects in memory. This is what Gramps does anyway for the Gramps internal filters. It will lead to higher memory usage, but Gramps objects are typically around 100 bytes in size, so for 10,000 objects this would be only 1 MB. So I think it's a clear tradeoff if it allows speeding up some trivial queries from half a minute to sub-second.

@UlrichDemlehner was the problem with the “can’t decode start byte” actually solved?

---

_[View the full topic](https://gramps.discourse.group/t/error-using-postgresql-as-database-backend-with-gramps-v5-2-2/5294)._