High performance DB data copying

[idle mulch]

I'm just trying to figure out the most efficient way to query lots of data. My problem is that I want to process multiple rows of data at once, as they come in, not one-by-one. However, the standard sql.Rows interface only allows you the access to a single row at a time in my understanding, leaving the buffering to the lower-level database provider implementation. Are there some lower level API's that would allow access to these without having to rebuffer the data manually by some arbitrary amount?

My problem is copying data from a database of one dbms (sql server) to another database of another dbms (postgres), and the other way around, but I want my code to be generic enough to handle copying that data to multiple other providers at the same time. I've got connections and transactions sorted, now the plan was to read batches of data from the first database, while sending the available read data to all of the other providers, skipping the current batch when it's been copied to all of the other databases.

There are subtleties with data conversion from the representation of one provider to another. Would the best way to solve this (least allocations possible) be to use an intermediate type? So like sql server -> string field (or whatever) -> postgres. Also, can I make this method reuse the same buffers for strings? Or is there a way to directly convert RawBytes of one dbms to RawBytes of another dbms without reimplementing the conversion logic? I mean some higher level helper interface or something of sorts. I imagine not, but still, it's worth asking.

To be clear, this is not an actual task on the job. I'm just trying to figure out the most efficient way to implement this for learning purposes. So far, the available abstractions just seem too high level.

High performance DB data copying

[nimble anvil]

if you are copying data from a db and this is like a one time thing i would just use the tools dedicated for that, e.g. the database export tool in their sql manager

https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding

[idle mulch]

no it's a script that's conceptually going to be called regularly and must be automated

[nimble anvil]

what is the order of the number of rows you intend to migrate

[idle mulch]

Let's say a million each time the script is run. And let's say for the sake of example that it will have to be copied to a dozen other databases

[nimble anvil]

🤔 just for my curiosity is this a manual read replica thing you're doing

[idle mulch]

yeah it's basically that, but for a subset of tables

and not synced in real time, but at intervals

[nimble anvil]

i'll answer your question, but as a word of advice i would say this is an inefficient feels like a convoluted idea, sticking to just one database type will be much more practical. but as far as 'reducing allocations' go, there isn't really all that much. you probably want to be using https://pkg.go.dev/database/sql#Conn.Raw and manual stuff. it's been a while since i've used it, but im sure the documentation will help out

[winged copper]

database/sql: Conn.Raw

      func (c *Conn) Raw(f func(driverConn any) error) (err error)
    ```
Raw executes f exposing the underlying driver connection for the duration of f. The driverConn must not be used outside of f.

Once f returns and err is not driver.ErrBadConn, the Conn will continue to be usable until Conn.Close is called.

[idle mulch]

thanks

[idle mulch]

Yeah so the problem with Raw is that almost everything is private

Even the conn type in pg is private

[nimble anvil]

lib/pq too?

[idle mulch]

yeah in that lib the connection is private

[idle mulch]

dang, even the encode functions are private in both adapters